sybperl-l Archive

From: Michael Peppler <mpeppler at MBAY dot NET>
Subject: Re: BEGIN, COMMIT, ROLLBACK
Date: Oct 15 1997 5:00PM

salvatore.sferrazza@ny.ubs.com wrote:
> 
>      What I am trying to do is set up a $dbh with a BEGIN TRANSACTION
>      statement, do a series of inserts, updates, etc and if there is an
>      error, roll them all back else commit them (pretty much database 101).
> 
>      Anyway, in Sybperl I am a little stuck as to how to implement this.
>      The $dbh will be passed through a series of subs as outlined below.
>      My big issue is how do I perform my &get_last_err procedure to get
>      any errors returned from that connection ($dbh).  The way my code is
>      now I get the following error:
> 
>      Sybase error: Attempt to initiate a new SQL Server operation with
>      results pending.
> 
>      I would open up a new $dbh, but then I wouldn't have the error
>      information would I?

You need to take a look at the error handling docs in the Sybase
OpenClient
manual (available on the web at http://sybooks.sybase.com if you don't
have it handy).

Anyway here's a summary of what you need to do:
1. Always check the return code from dbsqlexec and dbresults. If the
command
   fails, then one of these two will return FAIL instead of SUCCEED.
2. Always call dbresults() in a loop, and call it until it returns
NO_MORE_RESULTS.
   If dbresults returns FAIL and you wish to bail out at that point you
should
   call dbcancel.

Here is an example ExecSql() subroutine that can be used to do 
inserts/updates/deletes (ie it ignores any returned rows) and which
returns a true/false value for success:

sub ExecSql {
    my $self = shift;
    my $sql  = shift;

    my $ret;

    $self->dbcmd($sql);
    ($self->dbsqlexec == SUCCEED) or return undef;
    while(($ret = $self->dbresults) != NO_MORE_RESULTS) {
	if($ret == FAIL) {
	    $self->dbcancel;
	    return 0;
	}
	if($self->DBROWS) {	# statement returned rows - it shouldn't
	    my @data;
	    carp("SQL statement '$sql' returned rows, ignored.");
	    while(@data = $self->dbnextrow) {
		;		# Throw away the returned rows...
	    }
	}
	# If we executed a stored proc, and it has a return status, then
	# check it. If it's not 0, then the stored proc returned a fail
	# status code.
	if($self->dbhasretstat) {
	    if($self->dbretstatus != 0) {
		$self->dbcancel;
		return 0;
	    }	
	}
    }
    1;
}

Now to wrap this in a transaction, I'd do something like (error checking
ommitted):

$dbh->dbcmd("begin tran\n");
$dbh->dbsqlexec; $dbh->dbresults;
if(!ExecSql($dbh, $my_sql_to_execute)) {
   $dbh->dbcmd("rollback tran\n");
} else {
    $dbh->dbcmd("commit tran\n");
}

Michael
-- 
Michael Peppler       -||-  Data Migrations Inc.
mpeppler@datamig.com  -||-  http://www.mbay.net/~mpeppler