Hi all,
I hope this is the right list for this question. I am writing a short
Perl script executed on an NT machine which uses ADO to talk to a
PostgreSQL (7.0.2) database on a separate Linux machine via ODBC. The
bulk of the scripts appears below.
My problem is that when I use the transaction control methods of the
ADO Connection object, the script executes the central loop once,
then exits. However, when I comment out the BeginTrans() etc, the
loop iterateas through the entire recordset, as it's supposed to.
Any ideas would be appreciated!
Script follows.....
#-----------------------------------------
use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects 2.5';
#establish database connection
my $Conn = Win32::OLE->new('ADODB.Connection');
$Conn->Open('dsn=shopcentre;uid=postgres;pwd=postgres;');
#Get active customer ids and salaries
my $strSQL = 'SELECT custid, salary FROM shopstaff
WHERE active = \'true\'';
my $rsCust = $Conn->Execute($strSQL, , adCmdText);
while (not $rsCust->{EOF}) {
my $CustID = $rsCust->Fields('custid')->{Value};
my $Amount = $rsCust->Fields('salary')->{Value};
$Conn->BeginTrans();
$strSQL = "UPDATE accountlist SET balance = balance
+ \'$Amount\' WHERE custid = \'$CustID\'";
$Conn->Execute($strSQL, , adCmdText);
#check that no error occured - roll back transaction if it did
if (Win32::OLE->LastError()) {
$Conn->RollBackTrans();
HandleError(Win32::OLE->LastError());
} else {
print "$CustID: account credited by $Amount.\n"; #TEST
$strSQL = "UPDATE accountlist
SET balance = balance - \'$Amount\' WHERE accountnum =
\'00000000\'";
$Conn->Execute($strSQL, , adCmdText);
# check again that no error occured -
# roll back if it did, otherwise commit
if (Win32::OLE->LastError()) {
$Conn->RollBackTrans();
HandleError(Win32::OLE->LastError());
}
}
$Conn->CommitTrans();
$rsCust->MoveNext();
}
$rsCust->Close;
undef($rsCust);
$Conn->Close;
undef($Conn);
#-----------------------------------------
sub HandleError
{
my $ErrorStr = shift(@_);
print "paysalary error: ", $ErrorStr, "\n";
Win32::OLE->LastError(0); #reset the error
}
#-----------------------------------------
#EOF
--Ray.
----------------------------------------------------------------------
Raymond O'Donnell http://www.iol.ie/~rod/organ
rod@iol.ie (or rod@gti.ie) The Irish Pipe Organ Page
http://www.iol.ie/~rod
----------------------------------------------------------------------
