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

Hi all,
I want to extract and IMAGE BLOB from a Microsoft SQL 2000 server via a perl script on Solaris.

I have perl 5.6.1, freetds 0.61 and an early version of DBD::Sybase installed and I can run basic queries just fine.

I have used an explicit SET TEXTSIZE to avoid out-of-memory problems.

There is conflicting info in the DBD::Sybase vs freetds docs as to whether this is at all possible.

Right now, I get a segmentation fault in the following code: 

my $sqlquery = "SELECT Date_, Graphic_Image from GRAPHIC_VIEW ...";

$dbHandle->{syb_binary_images} = 1; #return image as binary not hex
my $sth = $dbHandle->prepare($sqlquery);
$sth->{syb_no_bind_blob} = 1;
$sth->execute;
my $row;
my $len;
while(1){
 # segementation fault in next statement
 $len = $sth->func(2,$imgchunk,2048,'ct_get_data');
 last if $len != 2048;
}

[download]

Replies are listed 'Best First'.
Re: Retrieve MS SQL Image to per/UNIX
by cbro (Pilgrim) on Jun 06, 2003 at 12:58 UTC
    From the DBD::Sybase docs:
    "ct_get_data() takes 3 parameters: The column number (starting at 1) of the query, a scalar ref and a byte count. If the byte count is 0 then we read as many bytes as possible".

    (e.g $sth->func($column, \$data, 1024, 'ct_get_data');)
    You did not pass a scalar ref, but instead just a scalar. Otherwise, you followed the docs to the tee. Try correcting that reference, and see if that makes a difference.
    HTH,
    Chris
[reply]
[d/l]
      Chris,
      Thanks. I missed the '\'.
      Now, unfortunately, it's throwing me 'out of memory' errors, despite my set textsize 2048 and setting the default in freetds.conf
[reply]
[d/l]
[select]
        cooperman,
        Try:
        $dbh->do("set textsize <somesize>");
        From this site:
        Sybase defaults the TEXTSIZE attribute (aka LongReadLen) to 32k, but MS-SQL 7 doesn't seem to do that correctly, resulting in very large memory requests when querying tables with TEXT/IMAGE data columns. The work-around is to set TEXTSIZE to some decent value via $dbh->{LongReadLen} (if that works - I haven't had any confirmation that it does) or via $dbh->do(``set textsize <somesize>'');
[reply]
[d/l]
Re: Retrieve MS SQL Image to per/UNIX
by mpeppler (Vicar) on Jun 06, 2003 at 12:47 UTC
    I don't think that ct_get_data() is supported by FreeTDS yet.

    I suggest just fetch the image as any other datatype - you'll just have to set TEXTSIZE to a value at least as big as the largest image that you want to fetch.

    Michael

[reply]

Back to Seekers of Perl Wisdom