Bug #18926 for DBD-Oracle: DBI failed to catch DBD::Oracle exception

Wed Apr 26 01:32:01 2006 Herman.KALL [...] team.telstraclear.co.nz - Ticket created

Subject:	DBI failed to catch DBD::Oracle exception
Date:	Wed, 26 Apr 2006 17:27:01 +1200
To:	bugs-DBI [...] rt.cpan.org
From:	Herman KALL <Herman.KALL [...] team.telstraclear.co.nz>

Message body is not shown because sender requested not to inline it.

Hi, i am having a problem with DBI API which doesn't catch an error when using the database object handle in a hash reference. Thanks, Herman The problem: Oracle exceptions don't get caught by the DBI when using a hash reference for the Database handle object returned from the DBI connect method. The calling program stops when executing a SQl statement that throws an Oracle exception. The DBD writes out the Oracle error message to STDOUT. The DBI does not catch the error and therefore the control doesn't go back to the calling program. This has a critical impact on cgi-scripts which just die and show a 'page not found' error on the browser screen. test.pl does establish an Oracle DBI connection and stores the database handle Object into an Object reference( hash ref) Then it executes an insert statement which will throw an Oracle exception. running test.pl does show the error message "DBD::Oracle::st execute failed: ORA-00936: missing expression (DBD ERROR:..." on screen. It does not show the DBI error message nor does the control flow come back to the test.pl program. test_ok.pl does establish an Oracle DBI connection and stores the database handle object into a scalar variable. Then it executes an insert statement which will throw an Oracle exception. running the test_ok.pl script shows both the DBD error message AND the DBI error message. The control flow of the program doesn't stop after catching the error. environment: perl 5.8.6 on Solaris DBD/Oracle 1.17 DBI 1.50 Oracle 10g client ( client library) Oracle 8i database was working in the following environment: perl 5.003 on Solaris DBD/Oracle 0.39 DBI 0.73 Oracle 7.2.2 client ( client library) Oracle 8i database attached scripts: perlbug_out.txt test.pl test_output.pl test_ok.pl test_ok_output.pl Database.pm <<test_output.txt.txt>> <<test_ok_output.txt.txt>> <<perlbug.out.txt>> <<test.pl.txt>> <<test_ok.pl.txt>> <<Database.pm>>

Wed Oct 25 06:48:59 2006 TIMB [...] cpan.org - Correspondence added

Moved to DBD::Oracle queue as I believe that's where the problem is.

Wed Oct 25 06:49:01 2006 The RT System itself - Status changed from 'new' to 'open'

Wed Oct 25 06:49:03 2006 TIMB [...] cpan.org - Queue changed from DBI to DBD-Oracle

Tue Dec 12 08:26:27 2006 champoux [...] pythian.com - Correspondence added

Looking at the latest version of DBD 1.19 I have traced back all the code to back to DBI and there is now bug that I can find. This might be a miss interprotaion of what RaiseError and PrintError will do If there is an SQL error and RaiseError is set to 0 then execution of the perl program will continue. If it is set to 1 it will throw a Perl error which can be trapped in an eval {}; I have tested the two scrips and the work (well don't work actually) for me as expected. Try your sripts with the same Raise and Print Error values and you will see how it works.

Tue Dec 12 08:26:40 2006 champoux [...] pythian.com - Status changed from 'open' to 'resolved'

Tue Dec 12 08:49:25 2006 champoux [...] pythian.com - Correspondence added

here are the files I used

# use DBI; require Database; my $database = Database::open_bill_database(); my $dbh = $database->dbh; my $sql_st = "insert into UPSTREAM_AUTHCD (NI102_SRC_FLG, NI102_AUTH_CD, NI102_SRC_NBR, NI102_TRAN_TYP, NI102_CREATE_TS, NI102_UNITS, NI102_PROD_CD, NI102_USERID, NI102_SID_CD) values ('A', $auth_cd, '0', 'E', 'sysdate', '0', '56', '$siteid', '0')"; #my $rv =($dbh->do ($sql_st)); # with Raise error set to 0 it will do the below If it is set to 1 it will not get below # if you wrap it in an Eval it will continue eval { my $rv =($dbh->do ($sql_st)); }; if ( $DBI::err ) { # it doesn'nt come here! print("\n\nCannot insert row: return_value = $rv $DBI::errstr\n"); } # neither it comes to here print("\n\n End of program: return code= $rv\n\n");

################ # Database # ################ ################################################ ### Turn word wrap OFF to edit this file ### ################################################ # 9/10/96 Modified to work with DBI/DBD ( mSQL ) # This is a library of useful perl database routines # that use perl5 objects. package Database; require Version; require Debug; require DBI; sub open_test_database { my $user; my $password; $ENV{'ORACLE_HOME'} = '/oracle/share/product/10.2.0/client'; $ENV{'TNS_ADMIN'} = '/var/opt/oracle'; $user = 'nutst02'; $password = 'nutst02'; $ENV{'TWO_TASK'} = 'oratst4.external'; my $dbname = $ENV{'TWO_TASK'}; my $dbh = DBI->connect("dbi:Oracle:$dbname",$user,$password, {RaiseError =>1 ,AutoCommit =>1}) or $status=1; return($dbh); } sub open_bill_database { my $user = "nutst02"; my $password = "nutst02"; $ENV{'ORACLE_HOME'} = '/oracle/share/product/10.2.0/client'; $ENV{'TWO_TASK'} = 'oratst4.external'; $ENV{'TNS_ADMIN'}= '/var/opt/oracle'; my $dbname = $ENV{'TWO_TASK'}; $database = new Database( $dbname, $user, $password ); return( $database ); } # Create a new Database object sub new { my ( $class, $name, $user, $pwd ) = @_; #Debug::log("4712 $name - $user - $pwd"); my $dbh = DBI->connect( "dbi:Oracle:$name", $user, $pwd , { RaiseError =>0, AutoCommit => 1 } ); if ( $dbh ) { my $self = {}; bless $self; $self->{'dbh'}=$dbh; $self->{'name'}=$name; return( $self ); } else { Debug::on; Debug::backtrace; die "Cannot open $name database ( $name : $DBI::errstr)"; return( undef ); } } sub dbh { my ($self) = @_; return($self->{'dbh'}); } 1;

# use DBI; require Database; my $dbh = Database::open_test_database(); my $sql_st = "insert into UPSTREAM_AUTHCD (NI102_SRC_FLG, NI102_AUTH_CD, NI102_SRC_NBR, NI102_TRAN_TYP, NI102_CREATE_TS, NI102_UNITS, NI102_PROD_CD, NI102_USERID, NI102_SID_CD) values ('A', $auth_cd, '0', 'E', 'sysdate', '0', '56', '$siteid', '0')"; #my $rv =($dbh->do ($sql_st)); # with Raise error set to 0 it will do the below If it is set to 1 it will not get below # if you wrap it in an Eval it will continue eval { my $rv =($dbh->do ($sql_st)); }; if ( $DBI::err ) { print("\n\nCannot insert row: return_value = $rv $DBI::errstr\n"); } print("\n\n End of program: return code= $rv\n\n");

Tue Dec 12 08:49:33 2006 The RT System itself - Status changed from 'resolved' to 'open'

Tue Dec 12 08:51:39 2006 champoux [...] pythian.com - Correspondence added

closed for now

Tue Dec 12 08:51:46 2006 champoux [...] pythian.com - Status changed from 'open' to 'resolved'

Tue Dec 12 19:44:28 2006 Herman.KALL [...] team.telstraclear.co.nz - Correspondence added

Subject:	RE: [rt.cpan.org #18926] DBI failed to catch DBD::Oracle exception
Date:	Wed, 13 Dec 2006 13:42:17 +1300
To:	bug-DBD-Oracle [...] rt.cpan.org
From:	Herman KALL <Herman.KALL [...] team.telstraclear.co.nz>

Hi, thanks for that. retrieving the return value vai block eval solves the poblem i had. Cheers, Herman Show quoted text

-----Original Message----- From: via RT [mailto:bug-DBD-Oracle@rt.cpan.org] Sent: Wednesday, 13 December 2006 02:27 To: Herman KALL Subject: [rt.cpan.org #18926] DBI failed to catch DBD::Oracle exception <URL: http://rt.cpan.org/Ticket/Display.html?id=18926 > Looking at the latest version of DBD 1.19 I have traced back all the code to back to DBI and there is now bug that I can find. This might be a miss interprotaion of what RaiseError and PrintError will do If there is an SQL error and RaiseError is set to 0 then execution of the perl program will continue. If it is set to 1 it will throw a Perl error which can be trapped in an eval {}; I have tested the two scrips and the work (well don't work actually) for me as expected. Try your sripts with the same Raise and Print Error values and you will see how it works.

Tue Dec 12 19:44:29 2006 The RT System itself - Status changed from 'resolved' to 'open'

Wed Dec 13 06:41:21 2006 champoux [...] pythian.com - Correspondence added

Ok I will close it

Wed Dec 13 06:41:44 2006 champoux [...] pythian.com - Status changed from 'open' to 'resolved'

Bug #18926 for DBD-Oracle: DBI failed to catch DBD::Oracle exception

Preferred bug tracker