sybperl-l Archive

From: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: Transaction Management with batch insert
Date: Dec 6 2001 12:21AM

Nicholas.Gatland@reuters.com 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

 >                     Michael Peppler                                                                               
 >                               
 >                     rg>                       cc:                                                                 
 >                     Sent by:                   Subject:     Re: Transaction Management with batch insert          
 >                     owner-SYBPERL-L@lis        Header:      Internal Use Only                                     
 >                     t.cren.net                                                                                    
 >                                                                                                                   
 >                                                                                                                   
 >                     04/12/2001 19:39                                                                              
 >                     Please respond to                                                                             
 >                     SYBPERL-L                                                                                     
 >                                                                                                                   
 >                                                                                                                   
 > 
 > 
 > 
 > Nicholas.Gatland@reuters.com 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. - http://www.mbay.net/~mpeppler
 > mpeppler@peppler.org - mpeppler@mbay.net
 > International Sybase User Group - http://www.isug.com
 > 
 > 
 > 
 > 
 > 
 > 
 > 
 > ------------------------------------------------------------- ---
 >         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.
 > 

-- 
Michael Peppler - Data Migrations Inc. - http://www.mbay.net/~mpeppler
mpeppler@peppler.org - mpeppler@mbay.net
International Sybase User Group - http://www.isug.com