December 12, 2007 at 7:10 am
Hi all,
I have created a table with 2 columns (Myfilename varchar(255) and the myPicture Varbinary(max)).
I was able to upload a file into the table using Openrowset(Bulk ‘E:\temp\myTest.jpg’,SINGLE_BLOB) AS [file]… worked like a charm.
I now, need to retrieve the picture from the table and put in a jpeg file using TSQL. I have been searching everywhere without any success…
Would you have any idea?
It would be greatly appreciated…
Thanks
John.
December 12, 2007 at 8:41 am
jghali (12/12/2007)
Hi all,I have created a table with 2 columns (Myfilename varchar(255) and the myPicture Varbinary(max)).
I was able to upload a file into the table using Openrowset(Bulk ‘E:\temp\myTest.jpg’,SINGLE_BLOB) AS [file]… worked like a charm.
I now, need to retrieve the picture from the table and put in a jpeg file using TSQL. I have been searching everywhere without any success…
Would you have any idea?
It would be greatly appreciated…
Thanks
John.
Hi John,
writing the data out is more tricky. You could use bcp and the command shell like:
declare @SQLcommand nvarchar(4000)
set @SQLcommand = 'bcp "SELECT data FROM tempdb..binary_data WHERE id = ''c:\data.jpeg'' " queryout "c:\data.jpeg" -T -N -Sservername'
exec xp_cmdshell @SQLcommand
(replace the servername, and if not using windows authentication you will have to provide the credentials to connect to the database)
Regards,
Andras
December 12, 2007 at 9:03 am
I am curious as to why you chose to store images in the db, if you need to take them out. Have you looked at only storing the file path in the database?
December 12, 2007 at 9:10 am
Very similar to what I had... but better, since the file size is the same as the original file... The only problem is when opening the file with "windows picture and fax viewer" or Paint... the file is not recognized by these softwares while the original file opens with these same softwares.
with windows picture and fax viewer, I get "No preview available"
with Paint I get "Paint cannot read this file. this is not a valid bitmap file or its format is not currently supported"
any idea?
please advise.
December 12, 2007 at 9:13 am
It's a big debate on images in the database. I tend to leave them out so I can easily view/edit them if needed, but lots of people want to keep them in there with a single backup/restore solution.
I'm curious as well as to your reasoning. There is no correct choice, just one that works better for your situation.
FYI, in SQL 2008, they agree with storing them outside the db, but they have hooks to make them easily accessible from T-SQL.
December 12, 2007 at 9:14 am
It's an option that we are considering... but the name of the pictures will change depending on who these images will be going to... so instead of copying the image file with the new name we thought of storing them in a table and exporting them with the necessary name before sending them out.
December 12, 2007 at 9:24 am
You could create a stored procedure that updates the file path in SQL and then uses xp_cmdshell to rename the file.
December 12, 2007 at 9:31 am
You could even use a trigger to automatically rename the file upon updating the file path.
December 12, 2007 at 9:35 am
That's an interesting requirement. Why do the names change if the image doesn't? Or does it?
It does pose an interesting problem. How are you sending to the client? Is this a web page, .NET app, something else? Might not matter, but depending on what the method is, you could look to redirect the client request to the same file with multiple names.
December 12, 2007 at 10:04 am
we don't want to lose the original filename since this is the filename is the one we refer to in our system (not MS-SQL).
There are about 10 000 images (they are catalog images that 3rd party vendors will be selling under their own product numbers). They are FTP'd to different 3rd party vendors using different filenaming conventions. I could FTP using their naming convention in the destination field but I won't be able to track what was and wasn't downloaded ... there's always a way but it might be more tedious to create other tables for different clients to keep track of what went where...
That's why I'm pushing my boss to go with saving the images in a table... I'm just having a problem to retrieve the image! it's driving me crazy...
very interesting comments you have brought up.
Thanks!
John
December 12, 2007 at 10:21 am
You can retrieve the image, but it must come out as a stream, not as a part of your result set.
http://www.a1vbcode.com/vbtip-97.asp
Search for ADO Stream.
December 12, 2007 at 11:43 am
I have created a sample image and recieved the same error as you. I did notice that the binary is different between the bcp output image and the original. The attached word document will illustrate the differences between the files.
December 12, 2007 at 11:54 am
I have not had any luck with the varbinary bcp, so the way I see it you have two options:
1) If you choose to use file paths in the database, you can create the trigger to fire on update of the file path column. The trigger will put the old image path name in a history table and then copy the image, with a new name. Note: this can also be handled in stored procedure.
2) Use your application to manage the binary data, as Steve has pointed out.
** If anyone can clarify why the bcp does not work as intended, would be great, but the way I see it the same file that goes in, is not the same file coming out. **
December 12, 2007 at 12:14 pm
You have all been a great help... I will still try to find the answer... and if I do, I will let you know...
I've been searching for this answer for 2 days without success...
It's frustrating but I really believe that the answer is out there....
I won't be able to sleep properly until I find this. 😉
never give up!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply