ksublondie has asked for the wisdom of the Perl Monks concerning the following question:

I'm fairly new to perl and have hit a stumbling block. I've exhaused every solution and google search I can think of, but to no avail. I've got a script on an internal win2003 server that reads and imports gif image files into a mssql2005 db image field using DBD::ODBC module for the connection. For network simplicity and security, I've been required to store the images inside the db instead of referencing a file outside the db: 
    open(frontFILE, $imageFront.'.gif');
    read (frontFILE, $blobFront, -s "$imageFront");
    close frontFILE;                
    open(backFILE, $imageBack.'.gif');
    read (backFILE, $blobBack, -s "$imageBack");
    close backFILE;

    my $sth=$db->prepare("Insert INTO docs (FrontImg,BackImg,docID) va
+lues (?,?,?);
    $sth->execute($blobFront, $blobBack, $docID) or die $sth->errstr;

[download]
And a public web interface (debian server running apache2) that gets those records from the db (this time using DBD::Sybase for the db connection) and displays to the website inside an <img> tag: 
        my $whichImage = 'e5c59e0d-fc0e-4d8e-9fa5-7b3bac6f8f25';

    #getting the image from the db
        my $db=DBI->connect('DBI:Sybase:server='.$server,$sql_user,$sq
+l_password);
        $db->do("use ".$database) ;
        my $statement = "select FrontImg, BackImg, docid from docs whe
+re docid = '$whichImage'";
        my @rows=@{$db->selectall_arrayref($statement, {Slice => {}})}
+ ;
        unless( @rows){die "$statement \ndidn't find the image ".@rows
+;}
        #there should only be one record returned -- docid is the prim
+ary key
    my $data = $rows[0];

        #to get the same image from file instead of the db:
        open FILE, "<$filebase".'/1001-1-B.gif' ;
        my $blob;
        read(FILE, $blob, -s $Lockbox::Web::CONFIG{filebase}.'/1001-1-
+B.gif');
        close FILE;

    #the following line displays only the dreaded empty box w/a red 'x
+'
    #instead of the image from the database
    print $q->header(type=>'image/gif', Content_Length=>length($data->
+{BackImg})).$data->{BackImg} ;

    #the following line displays the image from the file correctly
    #but i need to get it from the db instead
    #print $q->header(type=>'image/gif', Content_Length=>length($blob)
+).$blob ;

        if($blob ne $data->{BackImg}){die $data->{BackImg}."\nlength="
+.length($data->{BackImg})."\nfile image = ".$blob."\nlength=".length(
+$blob);}

[download]
When I compare the output of $blob vs. $data->{BackImg}, $data->{BackImg} looks like a hex string with a length exactly double the length of $blob. $blob starts with 'GIF 89a' followed by what looks like gibberish. I'm not sure if the problem exists in inserting the image to the db, retrieving the image from the db or outputting to the webpage.

I tried playing with hex conversion, but all I ended up with was an empty string.

Any help would be appreciated...thanks

ksublondie

ps -- BTW...the original images (all 170,000+!) exist as tif's that I'm converting to gif's (so I can display inside the browser) using ImageMagick. If there's a more efficient way to do this, I'd appreciate the input.

Replies are listed 'Best First'.
Re: insert & retrieve images from DB to web
by juster (Friar) on Sep 05, 2008 at 00:08 UTC

    did you try my $rawdata = pack 'H*', $data; to convert the hex string to raw data?

    I think that should work. $data is twice as long as $blob because two hexadecimal characters represent one byte. Each byte is a character in $blob because it is raw data and not a string.

    The GIF 89a string is the beginning of the file header for GIF and comes before the binary data, so that is what you want to end up with in $rawdata above.

[reply]
[d/l]
[select]
Re: insert & retrieve images from DB to web
by stonecolddevin (Parson) on Sep 05, 2008 at 03:59 UTC

    There are actually quite a few write ups here in the Monastery regarding images and DBs, however the general rule is to put the pointer in the database (or path to the image if you will), and store the image in a directory in the filesystem. That way you reduce the load on your database server and it *should* keep things speedy.

    This node covers a pretty great deal of information involving images and databases (and even breaking up large images into chunks to keep download times sane).

    HTH

    meh.
[reply]
Re: insert & retrieve images from DB to web
by Your Mother (Archbishop) on Sep 05, 2008 at 04:25 UTC

    That seems a little weird. 89a is the animated gif format. You might be doing something you don't really want to do with your conversions. On that note, you'd probably be better off converting them to jpg instead of gif, they'll be smaller and more accurately matched files, unless the tiffs are 8-bit, in which case gif would be a directly comparable format.

[reply]
      I got chastised by this before, so I remember it.......89a handles animation, but does not mean it is animated. It is a newer format that handles other things. 87a can be animated too. See Gif 87a/89a
      I'm not really a human, but I play one on earth Remember How Lucky You Are
[reply]
        GIF89a is animated? ...interesting. This is the code that I'm using to convert the tif's to gif's: 
        foreach (@image){
my $image=Image::Magick->new();
$image->Read($_);
$image->Write(substr($image->Get('filename'), 0, length($image->Get('f
+ilename'))-4).".gif");
}

        [download]
        I manually opened one of my tif's and 'save as...' a gif in one of my graphic editing programs, opened the gif in notepad, and it still had the 'GIF89a' at the beginning of the string. What is the non-animated supposed to have?

        The original tif's are simple b&w scanned docs, so gif's are MUCH smaller than jpgs in this case. I read that node on handling blobs, but it referred to MB's of data. All the gif's ended up being between 5K-32K, so I assume (correct me if I'm wrong) that I don't have to deal with breaking the data up into chucks??? Will it speed things up if I do? It doesn't look like I'm losing any of the data in inserting or retrieving the data if the hex string is exactly double the raw data directly from the file.

        juster--I added the lines of code: 
        my $rawData = pack 'H*', $data->{BackImg};
print $q->header(type=>'image/gif', Content_Length=>length($rawData)).
+$rawData;
if($blob ne $rawData){die $rawData."\nlength=".length($rawData)."\nfil
+e image = ".$blob."\nlength=".length($blob);}

        [download]
        ...but $rawData is an empty string. What am I missing?

        Referencing the file instead of putting it in the db would certainly optimize the db, however, for this application and our network configuration (there are a total of 4 servers involved...long story, don't ask), storing the images inside the db works better.

        --ksublondie
[reply]
[d/l]
[select]
Re: insert & retrieve images from DB to web
by tmaly (Monk) on Sep 05, 2008 at 16:51 UTC
    Have you considered converting the images to base 64 and storing them as strings? I have used MIME::Base64 to do this with other binary formats
[reply]
      I'm slowly making progress. There's a sybase connection attribute (syb_binary_images) that by default returns binary as hex. Now I don't have to convert the data.

      Now I've discovered that the issue appears to be independent of the db. I've taken a larger sampling of files to read and print directly to the browser. The same images are displaying incorrectly (stupid me should have done this to begin with). I can open the gif files with either a browser or my image viewer and it looks fine. Reading the files and printing data to the browser is causing problems.

      All the sample code I've found is doing the same thing I am (see code in original node). Ideas???
[reply]

        have you tried calling binmode on the input file handle after you open it?

[reply]

Back to Seekers of Perl Wisdom