Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-Oracle CPAN distribution.


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.

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

Download perlbug.out.txt
text/plain 4.1k

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

Download test.pl.txt
text/plain 749b

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

Download test_ok.pl.txt
text/plain 665b

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

Download Database.pm
text/x-perl 1.7k

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

Download (untitled) / with headers
text/plain 1.9k
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>>
Moved to DBD::Oracle queue as I believe that's where the problem is.
Download (untitled) / with headers
text/plain 582b
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.
here are the files I used
Download test_jps.pl
text/x-perl 930b
# 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");
Download Database_jps.pm
text/x-perl 1.8k
################ # 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;
Download test_ok_JPS.pl
text/x-perl 845b
# 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");
closed for now
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>
Download (untitled) / with headers
text/plain 990b
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.
Ok I will close it


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.