Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: Writing more robust message and error handlers
Date: Nov 10 2001 1:12AM

Dave Aiello writes:
 > I am in the process of trying to standardize 20 or 30 Perl scripts that use 
 > Sybase::DBlib.  These scripts were written by a team of programmers who were 
 > under deadline pressure, so there weren't many coding standards.  Many of these 
 > scripts are failing in testing because they do not handle Sybase messages and 
 > errors properly, although they all have handlers installed.  It's my job to 
 > write a single error handler and a single message handler, install it in a 
 > module, and make it work for all of the scripts.

 > For one thing, the handlers may be called while explicit transactions are 
 > pending.  If I get a Sybase error with a severity greater than 16, I am 
 > supposed to log the error information to a file, rollback the pending 
 > transaction(s), and exit the program.  However, I cannot depend upon blindly 
 > referencing a single well known database handle since the scripts do not have 
 > handles with the same names.

I'll probably have to come back to this and do a more complete
write-up (though you can also check the Sybase DB-Library manual on
error handling).

First, you should be aware that you can only issue certain limited
DB-Library commands from within an error handler. You can't, for
example, issue a new language command such as $dbh->dbcmd("rollback tran");

So you'll have to devise some other way to handle this properly. One
way is to set a flag in the error handler, and then check it outside
of the handler and then act accordingly, but that's going to be a lot
of hassle, and would mean that you'd have to change the code in each
of the scripts - not a pleasant prospect.

An alternative solution would be to write a module that overrides the
standard dbsqlexec, dbresults and dbnextrow subroutines and that
handle the errors the way you want them to be handled.

Such a module could look something like this (off the top of my head -
syntax approximate!)

package My::Sybase;
use Sybase::DBlib;

@ISA=qw(AutoLoader Exporter Sybase::DBlib);

sub err_handler {
   ... handle error conditions and set flag appropriately

sub dbsqlexec {
    my $self = shift;

    my $ret = $self->SUPER::dbsqlexec;   # call the real dbsqlexec;
    if($ret != SUCCESS || $errFlagIsSet) {
        do appropriate error logging, etc.


This may or may not be something you want to get into - it can get a
little bit ugly, but it can also be quite powerful.    

 > Do I need to explicitly rollback a transaction if I know my error is fatal?

Depends - if you exit from your program without commit then the
transaction(s) will get rolled back.

 > If so, can I do it without knowing what database handle began it?

No - the commit or rollback has to be on the correct handle.

 > Should I do an INT_EXIT in this case?

Maybe - it's hard to say from here.

 > Will INT_EXIT cause the Perl program to terminate?

I believe so.

 > If so, will it terminate with the equivalent of exit(1)?

Not sure - you could easily test it :-)

 > In another related direction:
 > If the error handler is called and an INT_CANCEL is issued (as in the example), 
 > the Sybase Open Client DB-Library documentation says that the DB Library 
 > routine will return with failure.  Does this mean that I still can execute the 
 > alternative condition, for example the print statement in the following line of 
 > Perl code: (?)
 > $dbh->dbcmd("foo") || print "Foo didn't work!";

I think so, but I'd be more explicit about what return values from
various calls as these may not be true/false in the perl sense.

 > Finally:
 > What is the first parameter to the message and error handlers, generally 
 > referred to as "$db"?
 > Is it the database handle where the error took place?


But read up on the limits on what calls can be made from within the
message handler.

Michael Peppler - Data Migrations Inc. - -
International Sybase User Group -