Retrieve Binary value & output as image file?

[Reefslayer]

Hello All.

I have an application using SQL at the back end I'm trying to understand in greater detail. My exposure to SQL has only been in terms of system admin, so I'm struggling a bit with this one!

My app is ASP web based and there are lots of images (Gif's) used in the GUI. I presumed that the database would simply store a path to the localy stored image files which I could easily view and edit and so maintain the image links in the app. Not So!

Upon further investigation it looks as though the images are stored as binary values. However when I look at the relevent columns I see <binary> and not the actual binary value. Why is this?

Is there any way I can retreive the value and output it to an image .Gif file using T-SQL?

Could I then upload another image.gif file as binary and so replace the original image? What I'm after is a way of maintaining this but the dev guys have got me!
Any thoughts/observations/ideas appreciated.

 

[eramgarden]

I'm working on an app that does the same thing. Not good at all...

Look into ReadText, WriteText and UpdateText in BOL.

 

[Reefslayer]

Thanks for the pointers.
I did some extra digging around. I found a util in the SQL binn directory called textcopy.exe it looks as though this should do exactly what I want i.e. give me a means of up/down loading complete images to/from the db.

The problem is when I do an output from the db the file it creates is empty! Getting quite annoying now because I feel quite close!

sample of the command line switches and the line I'm using below.

TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
[/D [database]] [/T table] [/C column] [/W"where clause"]
[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

/S sqlserver
The SQL Server to connect to. If 'sqlserver' is not
specified, the local SQL Server is used.
/U login
The login to connect with. If 'login' is not specified,
a trusted connection will be used.
/P password
The password for 'login'. If 'password' is not
specified, a NULL password will be used.
/D database
The database that contains the table with the text or
image data. If 'database' is not specified, the default
database of 'login' is used.
/T table
The table that contains the text or image value.
/C column
The text or image column of 'table'.
/W "where clause" A complete where clause (including the WHERE keyword)that specifies a single row of 'table'.
/F file
The file name.
/I
Copy text or image value into SQL Server from 'file'.
/O
Copy text or image value out of SQL Server into 'file'.
/K chunksize
Size of the data transfer buffer in bytes. Minimum
value is 1024 bytes, default value is 4096 bytes.
/Z
Display debug information while running.
/? Display this usage information.



textcopy /s SVR1 /u usr /p password /d mydb /t Images /c Id_Image /W " WHERE Im_Id=3 " /f c:\file1.gif /o /z

I looked at obvious things like NTFS permissions but the process can create the file, it just comes out at 0k.

Does anyone have any experience of this util/any idea why I'm not getting the output?

I'm now starting to suspect the data in the Image/binary column I'm trying to extract! I'm sure the answer lies eleswhere.

Any further help appreciated.

Thanks.

 

[eramgarden]

I used the stored proc in this link once: hope it helps:

http://www.mssqlcity.com/Articles/KnowHow/Textcopy.htm