Outputting Image Data to a File

Question

Outputting Image Data to a File

Tim Foster

Old Hand

Points: 394
More actions
February 16, 2007 at 3:58 am

#172129

Hi all,
I'm trying, desperately, to output data from an Image column in a SQL 2000 db to a physical file.
I know, that using Image column is crap and believe me, had been involved with the development of the db it would never have been done like that. However, they refuse to change this methodology as the crappy Access frontend they wrote works with it, and so I'm stuck with it.
What I'm trying to do is get the image from the db to a physical jpg file so that I can create a web page. The pages are static html that are generated daily from the database for their public site (again why we're not using asp I will never understand!!!). On the pages I have a picture of the person that the page relates to. At the moment the image in the web folders is the image that I use. This image may not be the current one anymore though. I want to, as a part of my routine that creates the static page, extract the data from the Image column and write a jpg to the web folders.
I can do it by writing a small vb app and calling this with xp_cmdshell, but I'd be much happier if the routine was totally encapsulated inside the sp rather than making external calls.
I've tried bcp and I get a file, but the file it creates isn't an image (regardless of which format switch I use).
Any ideas?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

check out this recent post:

there's no native way I know of to use TSQL to create the image...the image data is usually handled by an object that can handle a stream.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=344312#bm344791

there is a vb6 function to export all images from a certain table;

it sounds like you are already running a batch to make the web pages, so the logic included here might be adapted to grab the images of the day as a one time shot, right?

you don't want to dynamically get the images on hte fly, right?

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!

David Burrows SSC Guru Points: 65142 More actions · Answer 2

One way to do the stream in sp is to use the sp_OA procs

DECLARE @conn int, @rs int, @st int, @f varchar(8000), @i int

DECLARE @hr int

DECLARE @sql varchar(255)

EXEC @hr = sp_OACreate 'ADODB.Connection', @conn OUT

EXEC @hr = sp_OACreate 'ADODB.Recordset', @rs OUT

EXEC @hr = sp_OACreate 'ADODB.Stream', @st OUT

EXEC @hr = sp_OAMethod @conn, 'Open', null, 'Provider=SQLOLEDB;Data Source=servername;Initial Catalog=databasename;Integrated Security=SSPI'

SET @sql = 'SELECT [image] FROM

'

EXEC @hr = sp_OAMethod @rs, 'Open', null, @sql, @conn

EXEC @hr = sp_OAGetProperty @rs, 'Fields', @i OUT, 0

EXEC @hr = sp_OAMethod @st, 'Open', null

EXEC @hr = sp_OASetProperty @st, 'Type', 1 --adTypeBinary

EXEC @hr = sp_OAMethod @st, 'Write', null, @i

EXEC @hr = sp_OAMethod @st, 'SaveToFile', null, 'c:\temp\test.jpg', 2 --adSaveCreateOverWrite

EXEC @hr = sp_OAMethod @st, 'Close', null

EXEC @hr = sp_OAMethod @rs, 'Close'

EXEC @hr = sp_OAMethod @conn, 'Close'

EXEC @hr = sp_OADestroy @st

EXEC @hr = sp_OADestroy @rs

EXEC @hr = sp_OADestroy @conn

Far away is close at hand in the images of elsewhere.
Anon.

Tim Foster Old Hand Points: 394 More actions · Answer 3

Hi Guys,

Thanks for the feedback. I'd tried the OA method without much luck. I was kinda fumbling around in the dark though. When I'm back in the office on Monday I'll give your OA code another go.

However, I have managed to get the image out to a file without any funky code, VB, C++ etc. And in one line of code!!!!

When I get back into the office on Monday I'll share it with you all. I'll keep you all in suspense until then though, aside from I did it with BCP

Maybe I could sell my solution

Tim Foster Old Hand Points: 394 More actions · Answer 4

As promised:

SELECT @tmp_string = 'bcp "SELECT Photograph FROM TableName WHERE ID=' +

Convert(varchar(10), @id) + '" queryout "' +

@application_path + '_' + @folder_name + '\' + @filename + '.jpg' +

'" -f "' + @application_path + @bcp_format_file +

'" -S' + @@SERVERNAME + ' -T'

And the format file is:

8.0

1

1 SQLIMAGE 0 0 "" 1 photograph ""