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
