BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Store/Retrieve Multiple Attachment to/from SQL Server IMAGE          BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) View previous topic :: View next topic   Author Message Nirav Patel Guest Posted: Fri Mar 03, 2006 4:03 am    Post subject: Store/Retrieve Multiple Attachment to/from SQL Server IMAGE Hey Guyz, I am trying to make a Windows Service for our application where I need to send emails to clients based on the processed data. There is an EMail table with few fields. ToAddres : NTEXT FromAddress : NTEXT SMTPServer : NVARCHAR Port : INTEGER Attachments : IMAGE Now I have suggested to my project manager that we just store the path to the attachments in the database and that way it will be easy to achieve we want but he want to avoid the file system interaction all together. So here is my question. 1) Is it possible to store more than 1 file (different types - PDF, TXT, JPEG) to Attachments field and be able to retrieve them? 2) If YES then is it possible to retrieve them somehow so I don't have to retrieve them to a folder on the harddisk but directly retrieve it in MEMORY in correct format and attach it to the email so the receipient can receive it in correct format. PDF, TXT, JPEG? 3) What are my other options only if above is not possible? If above is possible then I would appreciate some code sample to help me get started. Any help is appreciated. Cheers, Nirav Patel Back to top Steve Zimmelman Guest Posted: Fri Mar 03, 2006 4:03 am    Post subject: Re: Store/Retrieve Multiple Attachment to/from SQL Server IM Quote: 1) Is it possible to store more than 1 file (different types - PDF, TXT, JPEG) to Attachments field and be able to retrieve them? Yes. You store images in an Image Blob Field. We have a 22 gig database full of multi-image tiffs, and are adding 150 - 250 megs (6,000 - 7,000 images) per day of image data. Quote: 2) If YES then is it possible to retrieve them somehow so I don't have to retrieve them to a folder on the harddisk but directly retrieve it in MEMORY in correct format and attach it to the email so the receipient can receive it in correct format. PDF, TXT, JPEG? Yes and no. You can retrieve the image from the blob field directly into a TMemoryStream. However, the easiest way to send it as an email attachment will be to create temp file. But the file can be deleted as soon as the email is sent. We do this all time in our application. We write a temp file into the user's temp folder, send the mail, then delete the file. //Save Image to blob MemStream.LoadFromFile('SomeImageFile.tif'); AdoQuery.Edit ; MemStream.Position := 0 ; TBlobField(AdoQuery.FielByName('Attachments')).LoadFromStream(MemStream); AdoQuery.Post ; //Load Image from blob to memory stream. TBlobField(AdoQuery.FielByName('Attachments')).SaveToStream(MemStream); MemStream.Position := 0 ; MemStream.SaveToFile('SomeFileName.tif'); // Attach to mail and send Delete('SomeFileName.tif') I would add an ID int Identity field to your table and create a primary key on it. It'll make your life easier. -Steve- Back to top Nirav Patel Guest Posted: Fri Mar 03, 2006 5:03 am    Post subject: Re: Store/Retrieve Multiple Attachment to/from SQL Server IM Thanks again Steve, How can I store a PDF as TIFF..? Besides I am going to suggest my project manager to further normalize the database and have a seperate table for Attachments which will allow me to store as many attachments as I like for each raw and then I can easily go through them. What do you think..? Probably 1 more question..! How can I know what the original file type/name was when it's time to retrieve it and store it on the hard disk temporary..? Cheers :) "Steve Zimmelman" <skz (AT) charter (DOT) nospam.net> wrote in message news:4407c195 (AT) newsgroups (DOT) borland.com... Quote: You can still do it, it just a little more tricky. You'll need come up with your way to concatenate the streams before storing them, then break them apart. You can use some type of unique marker. Another option would be to convert them all to multi-page tiff. Then separate the pages from inside the tiff. I use ImageEN from hicomponents.com. They have worked out quite well for me. -Steve- "Nirav Patel" <niravp (AT) maxit (DOT) com.au> wrote in message news:4407bebb (AT) newsgroups (DOT) borland.com... Thanks for the quick reply Steve :) We do have an ID field which is an AUTO INCREMENT. I just forgot to mention it. What if I want to store 2 files as 1 record.? Cheeers :) Back to top Steve Zimmelman Guest Posted: Fri Mar 03, 2006 5:03 am    Post subject: Re: Store/Retrieve Multiple Attachment to/from SQL Server IM You can still do it, it just a little more tricky. You'll need come up with your way to concatenate the streams before storing them, then break them apart. You can use some type of unique marker. Another option would be to convert them all to multi-page tiff. Then separate the pages from inside the tiff. I use ImageEN from hicomponents.com. They have worked out quite well for me. -Steve- "Nirav Patel" <niravp (AT) maxit (DOT) com.au> wrote in message news:4407bebb (AT) newsgroups (DOT) borland.com... Quote: Thanks for the quick reply Steve :) We do have an ID field which is an AUTO INCREMENT. I just forgot to mention it. What if I want to store 2 files as 1 record.? Cheeers Back to top Nirav Patel Guest Posted: Fri Mar 03, 2006 5:03 am    Post subject: Re: Store/Retrieve Multiple Attachment to/from SQL Server IM Thanks for the quick reply Steve :) We do have an ID field which is an AUTO INCREMENT. I just forgot to mention it. What if I want to store 2 files as 1 record.? Cheeers :) "Steve Zimmelman" <skz (AT) charter (DOT) nospam.net> wrote in message news:4407ba14$1 (AT) newsgroups (DOT) borland.com... Quote: 1) Is it possible to store more than 1 file (different types - PDF, TXT, JPEG) to Attachments field and be able to retrieve them? Yes. You store images in an Image Blob Field. We have a 22 gig database full of multi-image tiffs, and are adding 150 - 250 megs (6,000 - 7,000 images) per day of image data. 2) If YES then is it possible to retrieve them somehow so I don't have to retrieve them to a folder on the harddisk but directly retrieve it in MEMORY in correct format and attach it to the email so the receipient can receive it in correct format. PDF, TXT, JPEG? Yes and no. You can retrieve the image from the blob field directly into a TMemoryStream. However, the easiest way to send it as an email attachment will be to create temp file. But the file can be deleted as soon as the email is sent. We do this all time in our application. We write a temp file into the user's temp folder, send the mail, then delete the file. //Save Image to blob MemStream.LoadFromFile('SomeImageFile.tif'); AdoQuery.Edit ; MemStream.Position := 0 ; TBlobField(AdoQuery.FielByName('Attachments')).LoadFromStream(MemStream); AdoQuery.Post ; //Load Image from blob to memory stream. TBlobField(AdoQuery.FielByName('Attachments')).SaveToStream(MemStream); MemStream.Position := 0 ; MemStream.SaveToFile('SomeFileName.tif'); // Attach to mail and send Delete('SomeFileName.tif') I would add an ID int Identity field to your table and create a primary key on it. It'll make your life easier. -Steve- Back to top Steve Zimmelman Guest Posted: Fri Mar 03, 2006 5:03 am    Post subject: Re: Store/Retrieve Multiple Attachment to/from SQL Server IM Quote: How can I store a PDF as TIFF..? You can't. Well, not easily. I would just store the PDF. Quote: Besides I am going to suggest my project manager to further normalize the database and have a seperate table for Attachments which will allow me to store as many attachments as I like for each raw and then I can easily go through them. What do you think..? That would be how I would do it. If multiple images/attachments are required, it makes sense to have a 1-to-many table relationship. Quote: Probably 1 more question..! How can I know what the original file type/name was when it's time to retrieve it and store it on the hard disk temporary..? Add one more field to your attachments table, and store it there. You probably don't need the full path, just the filename with the extension. If you don't need the original filename, then just store the type so you know how to create it when necessary. -Steve- Back to top Wayne Niddery [TeamB] Guest Posted: Fri Mar 03, 2006 4:03 pm    Post subject: Re: Store/Retrieve Multiple Attachment to/from SQL Server IM Nirav Patel wrote: Quote: What if I want to store 2 files as 1 record.? You'd be much better off having a detail table for the attachments, then you can store as many attachments for each email as you like without needing to concatenate/parse them. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the Internet, we know this is not true." — Robert Wilensky Back to top Nirav Patel Guest Posted: Mon Mar 06, 2006 2:03 am    Post subject: Re: Store/Retrieve Multiple Attachment to/from SQL Server IM Thanks Steve, I have added the FileName field which is just the filename excluding the path sicne the user will have facility to specify the temporary extract path. "Steve Zimmelman" <skz (AT) charter (DOT) nospam.net> wrote in message news:4407cac0$1 (AT) newsgroups (DOT) borland.com... Quote: How can I store a PDF as TIFF..? You can't. Well, not easily. I would just store the PDF. Besides I am going to suggest my project manager to further normalize the database and have a seperate table for Attachments which will allow me to store as many attachments as I like for each raw and then I can easily go through them. What do you think..? That would be how I would do it. If multiple images/attachments are required, it makes sense to have a 1-to-many table relationship. Probably 1 more question..! How can I know what the original file type/name was when it's time to retrieve it and store it on the hard disk temporary..? Add one more field to your attachments table, and store it there. You probably don't need the full path, just the filename with the extension. If you don't need the original filename, then just store the type so you know how to create it when necessary. -Steve- Back to top Nirav Patel Guest Posted: Mon Mar 06, 2006 3:03 am    Post subject: Re: Store/Retrieve Multiple Attachment to/from SQL Server IM Thanks Wayne, I have done that now, which now come to think of it has made my task easier than I thought. Cheers :) "Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote in message news:440856e0 (AT) newsgroups (DOT) borland.com... Quote: Nirav Patel wrote: What if I want to store 2 files as 1 record.? You'd be much better off having a detail table for the attachments, then you can store as many attachments for each email as you like without needing to concatenate/parse them. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the Internet, we know this is not true." - Robert Wilensky Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) All times are GMT Page 1 of 1   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.