sybperl-l Archive

From: Nicholas dot Gatland at reuters dot com
Subject: Transaction Management with batch insert
Date: Dec 4 2001 7:14PM

Hi

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");

...if
(@@error <> 0
...rollback)

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

...if
(@@error <> 0
...rollback)

else
etc etc

...commit tran

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

Rgds
Nick



------------------------------------------------------------- ---
        Visit our Internet site at http://www.reuters.com

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.