February 16, 2007 at 3:58 am
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?
February 16, 2007 at 4:51 am
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
February 16, 2007 at 7:57 am
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.
February 16, 2007 at 1:04 pm
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
February 19, 2007 at 7:39 am
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 ""
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply