sybperl-l Archive

From: Tim_Green at mercer dot com
Subject: RE: Strange transaction behavior
Date: May 20 1998 1:13PM

> Is there something unusual you need to do in order to be able to use
> begin tran/commit tran with DBLib?
> 
> Here's what I'm doing:
> 
> begin tran
> do a bunch of ad-hoc updates to user tables in various databases
> do a bunch of updates to system tables via stored procedures
> commit tran
> 
> This works fine in ISQL.  It also works without the begin tran/commit
> tran
> 
> However, when I add in the begin tran & commit tran, the 
> ad-hoc updates
> don't get applied, and the stored procedure updates get 
> applied whether
> or not I commit.
> 
I suspect you're getting an error on one of the ad-hoc updates, causing Sybase
to rollback the transaction.  Since you said NONE of the ad-hoc updates get
applied, I suspect the error occurs on the last update statement.  Since you're
not be detecting it, after Sybase rolls back the stored procedures that follow
run outside an explicit transaction so become successfully committed.

If the SQL is executed one query at a time, make sure you've got your error and
message handlers defined.  You're probably missing the error

If all the SQL is batched into one script, you should check to see if an error
has occurred after every update statement by checking to see if @@error = 0.  If
not you can simply issue a return command to bail out of the batch (but you
still may need to rollback the transaction).  You could even do something more
sophisticated like:

Update ...

If @@error > 0
   Goto Fatal_Error

Update ...
If @@error > 0
   Goto Fatal_Error

print "Success"
return

Fatal_Error:
print "Error"
If @@trancount > 0  -- With duplicate key and permission violations Sybase
   rollback tran    -- doesn't rollback the transaction.
return


Good luck,

Tim Green                                           Tim_Green@mercer.com
Administrative Solutions Group
An ADP/Mercer Alliance