Hi,
I have recently ported a large (1.4M line) perl application from:
Centos 6.6
DBI version 1.6.09
DBD::ODBC version 1.23
To:
Centos 6.6
DBI version 1.6.33
DBD::ODBC version 1.50 (and same issue with 1.50_4)
The error doesn't occur in the 1.23 version, only the later 1.50* versions.
This error occurs using MS SQL Server 2005, but I don't think it's db
server dependent.
The specific error occurs when one creates a statement handle in a
subroutine and returns the statement handle via a return value. Specific
test program attached. Sample code that generates error below:
************************************
#!/usr/bin/perl
use strict;
select((select(STDOUT), $|=1)[$[]);
package main;
BEGIN {
# Standard modules
use Carp;
use DBI;
};
my $dbh;
MAIN: {
# open data connection
my $attr = {};
my $odbc = "DBI:ODBC:XXXX_odbc";
my $db_nm="XXXXX";
my $db_user_nm="XXXXX";
my $db_user_passwd="XXXXX";
carp "opening new db connection\n";
$dbh = DBI->connect($odbc, $db_user_nm, $db_user_passwd, $attr) ||
carp "Error opening database";
my $trace_filename = "/tmp/dbi_trace.dat";
$dbh->trace(2, $trace_filename);
# run query
my $sql = "SELECT Name FROM Person WHERE p_id = 123";
* my ($sth) = &do_pexect($sql);*
my $rowcache = $sth->fetchall_arrayref();
#pop off all rows. destroys $rowcache structure.
while (my $row = shift @$rowcache) {
my ($last_nm) = @$row;
}
$sth->finish();
#
# Run same sql a 2nd time
#
# undef $sth;
* ($sth) = &do_pexect($sql);*
carp "This is a test 3\n";
$rowcache = $sth->fetchall_arrayref();
carp "This is a test 4\n";
#pop off all rows. destroys $rowcache structure
while (my $row = shift @$rowcache) {
my ($last_nm) = @$row;
}
$sth->finish();
undef $sth;
return;
}
sub do_pexect {
# input parameters
my($sql) = @_;
my $out;
$out = $dbh->prepare($sql) ||
carp "prepare ($sql): ".$dbh->errstr;
$out->execute() ||
carp "execute ($sql): ".$dbh->errstr;
return($out);
}
************************************
Basic trace error:
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x13c8928)~0x1668428 '
SELECT name FROM Person WHERE p_id = 123') thr#139f010
<- prepare= DBI::st=HASH(0x156c370) at test12.pl line 92
-> execute for DBD::ODBC::st (DBI::st=HASH(0x156c370)~0x16e8898)
thr#139f010
<- execute= -1 at test12.pl line 95
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x13c8c28)~INNER) thr#139f010
!! ERROR: 1 ' Unable to fetch information about the error' (err#1)
<- DESTROY= undef at test12.pl line 70
DBD::ODBC::st DESTROY failed: Unable to fetch information about the
error at test12.pl line 70.
This is a test 3
at test12.pl line 70.
main::joel_init() called at test12.pl line 36
!! The ERROR '1' was CLEARED by call to fetchall_arrayref method
***********************
The basic error is that when the handle is created the 2nd time in the
sub-routine, DBD destroys the original $sth attached to the $dbh. Then,
when the sub-routine finishes, perl auto-trash compactor tries to destroy
$sth a 2nd time as it's the left hand value of the equate statement:
($sth) = &do_pexec($sql); but this doesn't work as the $sth has already
been destroyed in the subroutine.
This trace/error didn't occur in the earlier versions of DBD but now do...
I can get rid of the error by doing: undef $sth; before the 2nd use of the
variable but this should not be necessary.
Has anyone else experienced this issue with the newer versions of DBD?
Thanks for any insight,
Joel
