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: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: Transaction Management with batch insert
Date: Dec 6 2001 12:21AM writes:
 > Michael
 > Tks - just what I needed - it works a treat.
 > Am I right in saying that $DB_ERROR is a temporary value? So that to actually see the sybase errors generated for each statement when the script is run I would have to modify your syntax to read something like:

Yes - $DB_ERROR will get reset each time nsql() is called.

So you either want to print out $DB_ERROR if there is an error, or
store it somewhere and print it out when the entire batch is done.


 >                     Michael Peppler                                                                               
 >                     rg>                       cc:                                                                 
 >                     Sent by:                   Subject:     Re: Transaction Management with batch insert          
 >                     owner-SYBPERL-L@lis        Header:      Internal Use Only                                     
 >                     04/12/2001 19:39                                                                              
 >                     Please respond to                                                                             
 >                     SYBPERL-L                                                                                     
 > writes:
 >  > 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.
 > No - two-phase commits are a different beast altogether.
 > What you want to do is issue a "begin tran" before the first insert
 > statement, execute the insert statements and monitor the error codes,
 > and at the end issue a rollback if there was any error, or a commit if
 > all is fine.
 > An alternative solution is to set the chained transaction mode to on
 > (equivalent to "autocommit" off for ODBC/JDBC drivers) which will
 > automatically begin a transaction for the first insert, and then issue
 > a rollback or commit as necessary at the end.
 > begin tran, rollback tran, save tran and commit tran statements can be
 > issued via nsql() or any other method at any time, and can span sql
 > statements.
 > In your case I might do something like this:
 > $dbh->nsql("begin tran");
 > $rollback = 0;
 > $dbh->nsql(first insert statement);
 > $rollback++ if $DB_ERROR;
 > $dbh->nsql(second insert statement);
 > $rollback++ if $DB_ERROR;
 > etc...
 > if($rollback) {
 >     $dbh->nsql("rollback tran");
 > } else {
 >     $dbh->nsql("commit tran");
 > }
 > The above isn't optimum in the sense that inserts #2..7 will still be
 > executed even if #1 fails (although they will get rolled back), but
 > the code may be a little cleaner.
 > One thing to keep in mind when using transactions in client code is
 > that locks will be held for the length of the transaction, so if you
 > are updating a table in an active database, or if there are a lot of
 > concurrent updating going on for that particular table you will likely
 > get deadlock errors. If the traffic is low then that is not a problem,
 > of course.
 > Michael
 > --
 > Michael Peppler - Data Migrations Inc. -
 > -
 > International Sybase User Group -
 > ------------------------------------------------------------- ---
 >         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.

Michael Peppler - Data Migrations Inc. - -
International Sybase User Group -