PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Nicholas dot Gatland at reuters dot com
Subject: Re: Transaction Management with batch insert
Date: Dec 5 2001 4:09PM

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:

$dbh->nsql(first insert statement);
if ($DB_ERROR)
          {
          $rollback++;
          print "$DB_ERROR \n";
          }
etc
etc

The reason I ask is that if I include a print "$DB_ERROR \n"; at the end of my script with the rollback tran / commit tran statements, nothing is returned?

Is there a more efficient equivalent of my suggestion above?

Tks
Nick


                                                                                                                  
                    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.