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 Burstin <mikeb at concerto dot com>
Subject: Issues w/ error detection using DBI and multiple statements
Date: Apr 2 2002 3:14PM

I am running into some issues with detecting errors when submitting
multiple SQL statements to the dataserver using DBI/DBD::Sybase.  Any
help that anyone can give me is greatly appreciated.  I am running
this under Solaris 8, Sybase 12.5, perl 5.005_03 (Sun compiled perl
which ships w/ Solaris 8), DBI 1.14 and DBD::Sybase .91.

I am attempting to write a perl script which will parse out a SQL text
file, which, for historical reasons, used to be fed into the database
directly through isql.  I can't modify the file format, so need to
write a parser.  Here is a sample file:

EXEC sp_configure 'identity burning set factor',  50
EXEC sp_configure 'number of devices',            100
EXEC sp_configure 'number of locks',              100000
EXEC sp_configure 'number of user connections',   1024
EXEC sp_configure 'number of remote connections', 100
EXEC sp_configure 'number of open objects',       10000
EXEC sp_configure 'number of open indexes',       10000

The problem that I am running into is that if one of the statements in
the middle fails (in my case, the 'number of user connections'), I am
not getting back a bad status.  

I have set my database handle using the following:

$dbh->{RaiseError} = 1;
$dbh->{PrintError} = 1;

With this, I don't even notice an error message generated.  When I

$dbh->{syb_err_handler} = \&error_handler;
$dbh->{syb_flush_finish} = 1;

I finally do in fact see an error message printed using my error
handler, however, I don't get back a bad status on my $sth->execute.

If I only execute a single statement from above, I do back an error,
and if RaiseError is set, my script exits, if it isn't, then I get
back a $dbh->errstr which I can handle correctly.

Setting $dbh->{syb_chained_txn} doesn't appear to do anything.

My overall code (basically) looks as follows (note that printlog and
sqllog are just logging functions, basically, can be thought of as a
print statement, batch is the SQL text above, with the 'go' line

  my $dbh = DBI->connect("DBI:Sybase:server=$loginInfo->{SERVER}",
			 $loginInfo->{USER}, $loginInfo->{PASSWORD},
			 {PrintError => 1, RaiseError => 1});

  unless(defined($dbh)) {
    printlog('emergency', "Failed to login to $loginInfo->{SERVER}!");
  $dbh->{syb_err_handler} = \&error_handler;
  $dbh->{syb_flush_finish} = 1;

  my $sth = $dbh->prepare($batch);
  my $rc = $sth->execute;

  unless(defined($rc)) {
    printlog('warning', "Failed");

  if ($dbh->errstr) {
    printlog('critical', "xxx");

sub error_handler {
  my($err, $sev, $state, $line, $server, $proc, $msg) = @_;
  my @acceptableErrors = (1805, 2528, 5005, 11107, 17262, 17610, 5826, 5827);

  if ($err) {
    sqllog('debug', "$err: $msg");
    if (member(logger => $dispatcher, operation => 'num',
	       search => $err, list => \@acceptableErrors)) {
      sqllog('debug', "Acceptable error");
      return 0;
    sqllog('error', "Fatal error $err\n$msg");
    printlog('error', "Fatal Sybase error $err");
    return 1;  #<-------- this is my error here!!!!!!!!!!!!
  } else {
    sqllog('notice', "Message: $msg");
    return 0;

Michael Burstin
NPI Engineer
Concerto Software *
*Davox & Cellit Corporations are now doing business as Concerto Software