We help IT Professionals succeed at work.

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

linbayzak
linbayzak asked
on
715 Views
Last Modified: 2010-04-21
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

Comment
Watch Question

Top Expert 2009

Commented:
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.

Author

Commented:
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

Author

Commented:
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.
Top Expert 2009

Commented:
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

Author

Commented:
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

Author

Commented:
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

Top Expert 2009

Commented:
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

Author

Commented:
LongReadLen=58594

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

Author

Commented:
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>";
  }

Open in new window

Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you so much for your help! Now I'm working with the Lead Programmer to get the permission problem fixed. Very good job!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.