DB2 Err Handling

Hello,=20
I have successfully executed SQL statements to a DB2 database from perl
5.8.2 using DBI V1.48 and DBD::DB2 V0.78 on AIX 5.2.2.0. I'm now testing
the error handling, and it is not working as I expect. For instance I
try to prepare a statement I know will fail but error handling using
'or' or '||' does not catch the error.

my $stmt =3D 'select count(*) from sysibm.badTblnm ';
my $sth =3D $dbh->prepare($stmt) or die "Prepare Failed\n";

I use this same construct with Oracle DBs and it works fine. I have an
alternate solution (checking the length of error string) but I'd like to
understand why this is necessary. Any ideas?
TIA, Paula


=20
=20
=20

Re: DB2 Err Handling

Paula J Capacio wrote:
> Hello,
> I have successfully executed SQL statements to a DB2 database from perl
> 5.8.2 using DBI V1.48 and DBD::DB2 V0.78 on AIX 5.2.2.0. I'm now testing
> the error handling, and it is not working as I expect. For instance I
> try to prepare a statement I know will fail but error handling using
> 'or' or '||' does not catch the error.
>
> my $stmt = 'select count(*) from sysibm.badTblnm ';
> my $sth = $dbh->prepare($stmt) or die "Prepare Failed\n";
>
> I use this same construct with Oracle DBs and it works fine. I have an
> alternate solution (checking the length of error string) but I'd like to
> understand why this is necessary. Any ideas?
> TIA, Paula
>
>
>
>
>
try warn rather than die and RaiseError should be set to 0 for the db2
handler . this works fine for me

Re: DB2 Err Handling

Capacio, Paula J wrote:

>my $stmt = 'select count(*) from sysibm.badTblnm ';
>my $sth = $dbh->prepare($stmt) or die "Prepare Failed\n";
>
>

DBD::DB2 doesn't seem to return false values for failures. Instead, try
checking the sqlstate of the db handle:

my $sth = $dbh->prepare($stmt);
if ($dbh->state()) {
die "Prepare Failed: " . $dbh->{errstr} . "\n";
}


->state() is available on db handles and statement handles, and contains
the five-character sqlstate code.

Hope that helps.

-johnnnnnnnn

Re: DB2 Err Handling

On Fri, Sep 02, 2005 at 05:37:57PM -0500, johnnnnnn wrote:
> Capacio, Paula J wrote:
>
> >my $stmt = 'select count(*) from sysibm.badTblnm ';
> >my $sth = $dbh->prepare($stmt) or die "Prepare Failed\n";
>
> DBD::DB2 doesn't seem to return false values for failures.

If that's true then it's broken.

It may just be that the prepare() doesn't send the sql to the server
until it's execute()'d - so any sql errors won't be noticed till then.

That's quite common across many drivers (as it saves round-trips to the
server) and is, I think, mentioned in the DBI docs from prepare().

Tim.

> Instead, try
> checking the sqlstate of the db handle:
>
> my $sth = $dbh->prepare($stmt);
> if ($dbh->state()) {
> die "Prepare Failed: " . $dbh->{errstr} . "\n";
> }
>
>
> ->state() is available on db handles and statement handles, and contains
> the five-character sqlstate code.
>
> Hope that helps.
>
> -johnnnnnnnn
>

Re: DB2 Err Handling

Tim Bunce wrote:
> On Fri, Sep 02, 2005 at 05:37:57PM -0500, johnnnnnn wrote:
>
>>Capacio, Paula J wrote:
>>
>>
>>>my $stmt = 'select count(*) from sysibm.badTblnm ';
>>>my $sth = $dbh->prepare($stmt) or die "Prepare Failed\n";
>>
>>DBD::DB2 doesn't seem to return false values for failures.
>
>
> If that's true then it's broken.
>
> It may just be that the prepare() doesn't send the sql to the server
> until it's execute()'d - so any sql errors won't be noticed till then.

Tim -- that's exactly the case. We're using deferred prepare by default
in the DBD::DB2 driver.

> That's quite common across many drivers (as it saves round-trips to the
> server) and is, I think, mentioned in the DBI docs from prepare().

If you want to disable deferred prepare, you can:
* set the db2_deferred_prepare statement attribute to false
* or set "DeferredPrepare = 0" in your db2cli.ini file

Thanks,
Dan

P.S. Sorry for the delay -- just sold our house and have been focused on
life outside of computers for the last month or so :)