Problem with Perl - How to get image from sql database wrote to an external file?

I've been trying to pull some binary data from a database and output this information into an image file (of any kind viewable in IE) but I'm running into some problems. For starters, I get this error in the STDERR Log: error_handler: Data-conversion resulted in overflow. I get this simply from selecting the image from the database, without even trying to display results. I am trying to display the image using the code attached. The CheckNum displays properly but I do not get an image in my link as I wish to achieve. Any ideas on what I'm doing wrong? Thanks.

my $DB21 = modules::Db->ConnectCheck21();
  my $sql = "SELECT Img, CheckNum
             FROM Image_View
             WHERE UserID = '$FORM->{StoreNum}'
             AND Acct = '$FORM->{BankAcctNum}'
             AND Routing = '$FORM->{RoutingNum}'
             AND CheckNum = '$FORM->{CheckNum}'
             AND Amt = '$FORM->{Amt}'";
 
  my $rs = $DB21->SelectHash($sql);
 
 
  print<<EOD;
 
  <html>
  <head>
  </head>
  <body>
  <table border=0 width=35%>
  <tr>
    <td><b>Check 21 Image Link</b></td>
  </tr>
EOD
  my $bgcolor;
  my $background;
  my $num = 0;
  foreach(sort{$rs->{$a}->{CheckNum} cmp $rs->{$b}->{CheckNum}}keys(%$rs)){
    $num++;
    if($bgcolor == 1){
      $bgcolor = 0;
    }elsif($bgcolor == 0){
      $bgcolor = 1;
    }
    if ($bgcolor == 1) {
        $background = "lightgrey";
    }else {
        $background = "#FFFFFF";
    }
    open OUTPUT, ">output" . $num . ".png";
    my $newdata = $rs->{$_}->{Img};
    print OUTPUT $newdata;
    close OUTPUT;
    print "<tr><td bgcolor='$background'><a href='output" . $num . ".png' target='_blank'>$rs->{$_}->{CheckNum}</a></td></tr>";
  }

Open in new window

Perl

Last Comment

linbayzak

8/22/2022 - Mon

Adam314

7/21/2008

I'm not familar with modules::Db... is this based on the DBI module? If so, you may need to set the LongReadLen property, allowing the database to get the entire image. You might also need a binmode on the OUTPUT.

linbayzak

7/21/2008

ASKER

I am guessing it is, but I did not write it so I am unsure. The sub ConnectCheck21 from this module is as follows in the attached code: I'm not really sure exactly how it works as I did not write it, so this may or may not have what you're speaking of. I also added binmode(OUTPUT); before the print OUTPUT and the my $newdata lines and still don't have anything.

sub ConnectCheck21{
  my ($class) = @_;
  my $hash = {};
 
  $ENV{'SYBASE'} = '/usr/local/etc/freetds' ;
 
  my $UserName = "xx"; // Removed for privacy
  my $Password = "xxxxxxx"; //Removed for privacy
 
  for (1 .. 10){
    $hash->{dbh} =  DBI->connect("DBI:Sybase:server=sql1", $UserName, $Password);
    if($hash->{dbh}){
      $hash->{dbh}->do("use iCapture_DB");
      print STDERR "Successful DB::ConnectCheck21 connection on attempt $_\n" if($_ > 1);
      bless ($hash, $class);
      return $hash;
    }else{
      sleep(1);
    }
  }
  print STDERR "Giving up on database connection in Db::ConnectCheck21\n";
  exit;
}

Open in new window

linbayzak

7/21/2008

ASKER

I was starting to think it might have to do with getting the data as a hash from the DB but I am somewhat new to Perl and I am unfamiliar with other ways to get this data as I need it.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Adam314

7/21/2008

In your main script, make this change. Replace largest_possible_field_here with the size (in bytes) of the largest entry in your database. Setting this larger than necessary will cause the script to use more memory than needed.

To have it set dynamically, you could do something like (from DBI documentation):
SELECT MAX(OCTET_LENGTH(long_column_name)) FROM table WHERE ...

my $DB21 = modules::Db->ConnectCheck21();  #existing line
$DB21->{dbh}->{LongReadLen} = largest_possible_field_here;    #new line
 
...

Open in new window

linbayzak

7/21/2008

ASKER

Ok, I added the code below just before the query after reading your post and basically copying straight out of the DBI documentation and got a new error: db selectrow_array failed: Server message number=195 severity=15 state=10 line=1 server=SQL1 text='OCTET_LENGTH' is not a recognized built-in function name. which of course was followed by Setting of CS_OPT_TEXTSIZE failed. Any ideas?

 $DB21->{dbh}->{LongReadLen} = $DB21->{dbh}->selectrow_array(qq{SELECT MAX(OCTET_LENGTH(Img)) FROM Image_View});

Open in new window

linbayzak

7/21/2008

ASKER

The documentation also says to try DATALENGTH() or LENGTHB() if OCTET_LENGTH doesn't work. After trying those when OCTET_LENGTH gave me that error I get the same error message for LENGTHB() but not for DATALENGTH(), however, I still get the problem of Setting of CS_OPT_TEXTSIZE failed after using DATALENGTH().

$DB21->{dbh}->{LongReadLen} = $DB21->{dbh}->selectrow_array(qq{SELECT MAX(DATALENGTH(Img)) FROM Image_View});

Open in new window

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Adam314

7/21/2008

Try breaking it into two statements, for debugging. What is displayed for LongReadLen?

my $LongReadLen = $DB21->{dbh}->selectrow_array(qq{SELECT MAX(DATALENGTH(Img)) FROM Image_View});
print "LongReadLen=$LongReadLen\n";     #For debugging
$DB21->{dbh}->{LongReadLen} = $LongReadLen;

Open in new window

linbayzak

7/21/2008

ASKER

LongReadLen=58594

So I am getting data there...but for some reason it doesn't let me set LongReadLen

linbayzak

7/21/2008

ASKER

I read on another site that a possible fix would be in the code snippet below. Although I am not getting any more errors, the output file is not there. Here's what I have now:

  my $LongReadLen = $DB21->{dbh}->selectrow_array(qq{SELECT MAX(DATALENGTH(Img)) FROM Image_View});
# $DB21->{dbh}->{LongReadLen} = $LongReadLen;
  $DB21->{dbh}->do("set textsize $LongReadLen");
 
 
  my $sql2 = "SELECT Img, CheckNum
              FROM Image_View
              WHERE UserID = '$FORM->{StoreNum}'
              AND Acct = '$FORM->{BankAcctNum}'
              AND Routing = '$FORM->{RoutingNum}'
              AND CheckNum = '$FORM->{CheckNum}'
              AND Amt = '$FORM->{Amt}'";
 
  my $rs = $DB21->SelectHash($sql2);
 
  print<<EOD;
 
  <html>
  <head>
  </head>
  <body>
  <table border=0 width=35%>
  <tr>
    <td><b>Check 21 Image Link</b></td>
  </tr>
EOD
  my $bgcolor;
  my $background;
  my $num = 0;
  foreach(sort{$rs->{$a}->{CheckNum} cmp $rs->{$b}->{CheckNum}}keys(%$rs)){
    $num++;
    if($bgcolor == 1){
      $bgcolor = 0;
    }elsif($bgcolor == 0){
      $bgcolor = 1;
    }
    if ($bgcolor == 1) {
        $background = "lightgrey";
    }else {
        $background = "#FFFFFF";
    }
    binmode(OUTPUT);
    open OUTPUT, ">output" . $num . ".png";
    my $newdata = $rs->{$_}->{Img};
    binmode(OUTPUT);
    print OUTPUT $newdata;
    close OUTPUT;
    print "<tr><td bgcolor='$background'><a href='output" . $num . ".png' target='_blank'>$rs->{$_}->{CheckNum}</a></td></tr>";
  }