sybperl-l Archive

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:

		      ----------Begin----------
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
go
		      -----------End-----------

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
set:

$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
removed):

  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}!");
    die;
  }
  $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");
    die;
  }

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

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 *
978-952-0842
mikeb@concerto.com
*Davox & Cellit Corporations are now doing business as Concerto Software