sybperl-l Archive

From: Michael Peppler <mpeppler at MBAY dot NET>
Subject: Re: Transactions
Date: Dec 11 1997 10:48PM

Danilo Unite wrote:
> 
> Hi,
> 
> I have just started using the Sybperl package and am still trying to get
> familiar with all its functions. I would like to perform some updates,
> inserts, and deletes within a transaction block. I am not clear on
> exactly how this is done.

You only need to use transactions if you are submitting a complex
update to the server, and want to make sure that it all succeeds
or all fails.

In most cases it's better to write a stored proc to handle
the entire update, but you can also do it this way:


> 
> On first pass, this is the snippet of code that I tried:
> 
>     $dbh->ct_sql('begin transaction');
>     foreach $query (@queries){
>         $status = $dbh->ct_execute($query);
>         while ($response = $dbh->ct_results($restype) == CS_SUCCEED){
>             if($response == CS_FAIL || $response == CS_CANCELED){
add $restype == CS_CMD_FAIL

>                 $dbh->ct_cancel(CS_CANCEL_ALL);

I think you need to call ct_results first, before submitting a new
request.

>                 $dbh->ct_sql('rollback transaction');
>                 $flag = 1;
>                 last;
>             }
>             else{
>                 next unless $dbh->ct_fetchable($restype);
>                 while(($data) = $dbh->ct_fetch){}
>             }
>         }
>     }
> 
>     if(!$flag){
>         $dbh->ct_sql('commit transaction');
>     }

I've always used stored procs for this, but I would do this (untested
code:)

$dbh->ct_sql("begin tran");
$dbh->ct_execute($sql_statement);
$fail = 0;
while(($ret = $dbh->ct_results($restype)) == CS_SUCCEED) {
    if($restype == CS_CMD_FAIL) {
	$dbh->ct_cancel(CS_CANCEL_ALL);
	$fail = 1;
	next;
    }
    if($dbh->ct_fetchable($restype)) {
	while($dbh->ct_fetch()) {
	    ;   # maybe use ct_cancel here to avoid retrieving unwanted data
	}
    }
}
if($fail || $ret != CS_END_RESULTS) {
    $dbh->ct_sql("rollback tran");
} else {
    $dbh->ct_sql("commit tran");
}

Michael
-- 
Michael Peppler       -||-  Data Migrations Inc.
mpeppler@datamig.com  -||-  http://www.mbay.net/~mpeppler