Hello...it's late and I may very well be brain-dead...that aside... Does a failed PREPARE close the db handle or am I doing something wrong? When a prepare fails I want to exit with a return code of 243. I don't want to waste or tie up resources, and I assume that I am still connected and execute a disconnect. But execution stops at the disconnect and never gets to the part that sets the return code.
The results are: in processError, reason PrepareFailed select table_name, noSuch_column, avg_row_len from all_tables where owner = 'SCOTT' order by table_name DBI::db=HASH(0x1dc2744) Tool completed with exit code 255 (I execute from within Textpad (no editor wars need be started :-)) ) I forced an error in the prepare by passing a bad column name on a select (noSuch_column). excerpt... <<watch out for wrap/split of lines>> my $dbConn = connect2DB($path_script[0]); #read connection info from file in script path # then connect to the database #I know I connect properly cuz the next statement returns valid data if used # regardless the connect2DB sub is included too (sorry for the length) #my $stmt1 = "select table_name, tablespace_name, avg_row_len from all_tables "; my $stmt1 = "select table_name, noSuch_column, avg_row_len from all_tables "; my $stmt2 = "where owner = 'SCOTT' "; my $stmt3 = 'order by table_name'; my $mCols = $stmt1.$stmt2.$stmt3; my $mColsth = $dbConn->prepare( $mCols ) or processError("PrepareFailed","$mCols","$dbConn"); ....snip... sub processError { my $rc; print "in processError, reason @_\n"; if ($_[0] =~ /^BadOpen/) { print RUNLOG "Could not open $_[1] \n"; print RUNLOG "Possible causes: \n"; print RUNLOG " File does not exist.\n"; print RUNLOG " Insufficient file permissions \n"; print RUNLOG "Exiting with RC=241 \n"; $rc = 241; }elsif ($_[0] =~ /^ConnErr/) { print RUNLOG "Failed to connect to $_[1] as $_[2]\n"; print RUNLOG "Received diagnostic message:\n $DBI::errstr\n"; print RUNLOG "Possible causes: \n"; print RUNLOG " ConnInfo.dat may contain bad data\n"; print RUNLOG " expecting three lines, db service name, id, and password\n"; print RUNLOG " Server may be unavailable\n"; print RUNLOG " DB Service name may not be defined\n"; print RUNLOG " Password may have been changed\n"; print RUNLOG "Exiting with RC=242 \n"; $rc = 242; }elsif ($_[0] =~ /^PrepareFailed/) { print RUNLOG "Prepare Failed for $_[1]\n"; print RUNLOG "Received diagnostic message:\n $DBI::errstr\n"; print RUNLOG "Disconnecting from DB\n"; my $dbrc = $_[2]->disconnect; print RUNLOG "Exiting with RC=243 \n"; $rc = 243; }elsif ($_[0] =~ /^ExecuteFailed/) { print RUNLOG "Execute Failed for $_[1]\n"; print RUNLOG "Received diagnostic message:\n $DBI::errstr\n"; print RUNLOG "Exiting with RC=244 \n"; $rc = 244; } gracefulEnd(@main::path_script); exit($rc); } sub connect2DB { my $connInfoFile = "$_[0]ConnInfo.dat"; #add path info, if any, to file name printIt("\nConnection Info file: $connInfoFile"); open(CONNINFO, "<$connInfoFile") or processError("BadOpen","$connInfoFile"); my @connInfo = <CONNINFO>; #read all records in the file into an array chomp @connInfo; #remove the new line characters close (CONNINFO); #hostname, uid, pwd my $connString = $connInfo[1].'@'.$connInfo[0]; my $dbh = DBI->connect('dbi:Oracle:',"$connString","$connInfo[2]") or processError ("ConnErr",@connInfo); printIt("Connected to DB Service Name: $connInfo[0] user: $connInfo[1] pwd: *******"); return $dbh; #return the database connection handle } ####end snip### My RUNLOG shows: OracleTest Log STARTED 03/26/2002 19:58:22 File U:\Perl_Info\TestDir\OracleTest_20020326.log Connection Info file: U:\Perl_Info\TestDir\ConnInfo.dat Connected to DB Service Name: mp3u user: scott pwd: ******* Executing Query for more information about the tables owned by SCOTT Prepare Failed for select table_name, noSuch_column, avg_row_len from all_tables where owner = 'SCOTT' order by table_name Received diagnostic message: ORA-00904: invalid column name (DBD: error possibly near <*> indicator at char 20 in 'select table_name, <*>noSuch_column, avg_row_len from all_tables where owner = 'SCOTT' order by table_name') Disconnecting from DB