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';
@ret = $dbh->nsql( $sqlstr[$1] ,"ARRAY");
(@@error <> 0
@ret = $dbh->nsql( $sqlstr[$2] ,"ARRAY");
(@@error <> 0
Any tips would be welcome - including any other necessary database settings such as auto-commit etc
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.