Re: DBD::ODBC Destroy bug?

[Martin J. Evans]

On 24/03/15 15:45, Joel Plotkin wrote:

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 <http://test12.pl> line 92
     -> execute for DBD::ODBC::st (DBI::st=HASH(0x156c370)~0x16e8898) 
thr#139f010
     <- execute= -1 at test12.pl <http://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 <http://test12.pl> line 70
DBD::ODBC::st DESTROY failed:     Unable to fetch information about the error at 
test12.pl <http://test12.pl> line 70.
This is a test 3
  at test12.pl <http://test12.pl> line 70.
main::joel_init() called at test12.pl <http://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

Sorry Joel, I forgot about your email but in any case this list is a good place 
to send your issue. When I run your script as it stands (with edits below) I 
get:

opening new db connection
 at mje/joel.pl line 23.
This is a test 3
 at mje/joel.pl line 45.
This is a test 4
 at mje/joel.pl line 47.
Can't return outside a subroutine at mje/joel.pl line 56.

perl 5.19.10
DBI 1.631
DBD::ODBC 1.53_3 (which is latest git)

I'm going to SQL Server enterprise edition via the Easysoft SQL Server driver.

Obviously I changed the logon details and the SQL.

BTW, I don't think you need those calls to finish (see finish in DBI docs).

Martin