Saving image to disk (from sql)
-
hello.
I need to write stored procedure which will write image field from table directly to disk (into image).
boss wants it to be done using only buildin features, but i'm not sure if its possible.
i was looking into this :
http://www.motobit.com/tips/detpg_SQLWrFile/
as its possible to save strings, and it says its possible to save
binary to (by converting it into string), but that conversion
is done in vbs, so i wonder if there's any way to do conversion
in sql ?
or, if thats not possible, would it be possible to convert byte[] data to string in c# while inserting data into table,
and then exporting string into binary file ? would this work ?
i was trying it, with no luck, the output file is just few bytes big, as it happens that jpg files have few null bytes just after beggining, and it seems it stops writing as soon as it finds null charracter.
help would be apreciated
thanks
-
First of all, the normal soap box diatribe about all the problems associated with saving images in a table would apply here. It was a bad idea.
On to the problem... you could make a call to xp_CmdShell to run OSQL to select the column for a single row into a file using the ">" redirection character. You will need "SA" privs to use xp_CmdShell. You may have to dicker with the code page in order to get the data to transfer to the file correctly in an unmodified fashion but I'm not sure because I only store filenames of images in my databases and haven't had to do this before.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You could also do it using the other way found on the same web site you posted...
http://www.motobit.com/tips/detpg_read-write-sql-image-file/
It's still a bad idea to save images in a table, though...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I do the same as Jeff, with a little bit more data: i save the filename, file size, and the CRC of the file in a database;
I do this because we had users who had the bad habit of getting a file, renaming it, and then saving it again, with no changes....so the same file might be on disk and in the database multiple times.... so saving all that information let me track down the same file with multiple names; I've been afraid to automate it, so I validate and clean up manually, but have not found two DIFFERENT files with the same file size AND CRC, so my theory has been holding true for more than a year.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply