DB2 checking for an error on select

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I want to test and see if a select statement was executed. with DBD::mySQL I have been able to use

$sth->execute;
if ($sth->errstr) {
    die "Failed to execute ($stmt) - ", $sth->errstr(), "\n";
}
[download]

When I tried this with DB2 the script is not dropping into the if statement.

use strict;
use DBI;
use DBD::DB2;

my $db2_user = 'USER';
my $db2_pass = 'PASSWORD';

my $dbh = DBI->connect('dbi:DB2:DB2', $db2_user, $db2_pass, {AutoCommi
+t => 0, RaiseError => 0, PrintError => 1})  or die "Cant connect to D
+B - $DBI::errstr\n";

my $stmt = "select ID from RULE where key = 123"; 
my $sth = $dbh->prepare($stmt) or die "Cant prepare ($stmt) - $DBI::er
+rstr\n";
$sth->execute or die "Cant execute ($stmt) - $DBI::errstr\n";

if ($sth->errstr) {
    die "Failed to execute ($stmt) - ", $sth->errstr(), "\n";
}
 
print "the execute should have failed before here\n";

my $col1;
$sth->bind_col(1,\$col1);
while ($sth->fetch) { print "id is: $col1\n"; }

print "should not get here\n";
$sth->finish() or die "Cant close sth - $DBI::errstr\n";
$dbh->disconnect() or die "Cant disconnect from DB - $DBI::errstr\n";
[download]

here is the output

C:\PerlScripts>test.pl
the execute should have failed before here
DBD::DB2::st fetch failed: [IBM][CLI Driver][DB2] SQL0904N  Unsuccessf
+ul execution caused by an unavailable resource.  Reason code: "00C900
+81", type of resource
: "00000200", and resource name: "SHTDB038.SHTT09Z1".  SQLSTATE=57011
should not get here

C:\PerlScripts>
[download]

Comment on DB2 checking for an error on select Select or Download Code

Replies are listed 'Best First'.
Re: DB2 checking for an error on select by morgon (Priest) on May 11, 2009 at 22:03 UTC
$sth->execute or die "Cant execute ($stmt) - $DBI::errstr\n"; This is the proper way to do it (checking the return value of execute) - not the one you quoted at the top (checking errstr). I don't know if your problem is related but I remember a problem I once had with DB2 where a prepare using a non-existing column did not fail and it turned out that the DB2-client was configured in such a way that no data was sent to the server before the execute. The DB2 client is highly configurable to minimize database-roundtrips and that sometimes means that errors are detected later then you would assume. In your script it seems the only thing that fails is the first fetch and maybe this is again the first time data is sent to the server. Try to talk to your admin or trace your session.	[reply]
Re^2: DB2 checking for an error on select by dgarnier (Novice) on May 12, 2009 at 19:32 UTC
I tried `$sth->execute or die "Cant execute ($stmt) - $DBI::errstr\n";` but the or condition is not reached even thought the execute failed due to the table being offline. There are conditions where I would not find any data and that is OK, but if I don't find data due to the table not being available that is a problem. So I tried checking the fetch status `$status = $sth->execute; print "Status = $status\n"; # the RC is always -1 weather the execute + worked or not my $col1; $sth->bind_col(1,\$col1); while ($sth->fetch \|\| die "Cant fetch data - $DBI::errstr\n") { print "ID: $col1\n"; }` [download] The problem with this is that when fetch runs out of a data to return it hit the \|\| and dies, or if not data was found the script then dies.	[reply] [d/l] [select]
Re^3: DB2 checking for an error on select by morgon (Priest) on May 13, 2009 at 10:52 UTC
I tried $sth->execute or die "Cant execute ($stmt) - $DBI::errstr\n"; but the or condition is not reached even thought the execute failed I think that the execute does not fail (even though it should because the table is offline) therefore the die is not reached. The reason the execute does not fail could be (I've seen it happen) that your client is configured in such a way that no data is sent to server (where the error would be detected). Probably your client sends the first packet to the server when you do the fetch, so you should check for errors there. To verify this theory try to tcpdump your session and check where the actual conversation between your client and the server occurs.	[reply]
Re^4: DB2 checking for an error on select by dgarnier (Novice) on May 13, 2009 at 15:21 UTC
Re^5: DB2 checking for an error on select by morgon (Priest) on May 15, 2009 at 12:04 UTC
Some notes below your chosen depth have not been shown here
Re: DB2 checking for an error on select by afoken (Chancellor) on May 11, 2009 at 20:10 UTC
Set `RaiseError` to 1 to see whether `bind_col()` or `fetch()` failed. (And as a side note, I would recommend not to change `RaiseError` back to 0. It just make things easier. You don't have to check for errors, and when you expect that something may go wrong, you just add an `eval { BLOCK }`.) Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]
Re^2: DB2 checking for an error on select by morgon (Priest) on May 11, 2009 at 21:50 UTC
I would recommend not to change RaiseError back to 0. It just make things easier. That depends. For one-off scripts it certainly makes things easier, however when you write larger applications you almost certainly want to raise your own exceptions so you can then catch them by type (using Exception::Class or something similar - "Perl best practices" has a chapter on it) and then you are better off doing something like `$sth->execute or MyApp::DBException( error => $sth->errstr )->throw;` [download] rather than using RaiseError.	[reply] [d/l]