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: Nicholas dot Gatland at reuters dot com
Subject: Transaction Management with batch insert
Date: Dec 4 2001 7:14PM


I need a little help with transaction handling using Sybperl. I have read Michael Peppler's documentation concerning Two Phase Commit but I am unsure as to whether this will do what I want.

Currently I am using $DB_ERROR to good effect for error checking on an insert statement - if $DB_ERROR,  the insert is cancelled. Essentially this is replicating a 'begin' and 'rollback'. However the scenario changes slightly when say for example I am running 7 insert statements and the sybase error occurs during the insert of the last statement. In this instance only 'this' insert statement is cancelled, leaving the data entered as a result of the previous 6 stored procedures still in the database. Hence I want to use the more robust transaction management utilised in Transact SQL.

I need to incorporate error checking after each insert statement is run, with the intention of rolling back if an error is encountered at any time. I know the begin,rollback and commit syntax for  SQL  but am a bit stuck with the $dbh commands for Sybperl. Essentially the structure of my code will be as follows:

$dbh = new Sybase::DBlib 'u_name', 'p_word', 'server';

......begin tran
@ret = $dbh->nsql( $sqlstr[$1] ,"ARRAY");

(@@error <> 0

@ret = $dbh->nsql( $sqlstr[$2] ,"ARRAY");

(@@error <> 0

etc etc

...commit tran

Any tips would be welcome - including any other necessary database settings such as auto-commit etc


------------------------------------------------------------- ---
        Visit our Internet site at

Any views expressed in this message are those of  the  individual
sender,  except  where  the sender specifically states them to be
the views of Reuters Ltd.