Hi,
A few notes:
1) the final "return;" at the end of Main was a typo... I was trying to
clean up the example and had consolidated an additional sub-routine into
one big main() function.... even w/ this typo, I still get the original
error as described previously.
2) I (just) tried to get the latest version from git, but I just see
$DBD::ODBC::VERSION = '1.51_4';
from ODBC.pm
I can't seem to find version 1.53_3 at git... I'm not a git expert, so I'll
ask my staff for help to see if they can find 1.53_3 for me.
3) *** I'm using perl 5.10.1 ... maybe this is the issue. I'll upgrade to
perl 5.19.10 and see if the issue goes away.
I'll keep you posted,
Joel
On Tue, Mar 24, 2015 at 12:24 PM, Martin J. Evans <martin.ev...@easysoft.com
> wrote:
> 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
>
>
