ADO transaction funny

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
----------------------------------------------------------------------