Re: DBD::ODBC Destroy bug?

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