Outputting Image Data to a File

  • 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?

  • 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!

  • 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.

  • 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

  • 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