sybperl-l Archive

From: salvatore dot sferrazza at ny dot ubs dot com
Subject: BEGIN, COMMIT, ROLLBACK
Date: Oct 15 1997 2:41PM

     What I am trying to do is set up a $dbh with a BEGIN TRANSACTION 
     statement, do a series of inserts, updates, etc and if there is an
     error, roll them all back else commit them (pretty much database 101).
     
     Anyway, in Sybperl I am a little stuck as to how to implement this.  
     The $dbh will be passed through a series of subs as outlined below.  
     My big issue is how do I perform my &get_last_err procedure to get
     any errors returned from that connection ($dbh).  The way my code is 
     now I get the following error:
     
     Sybase error: Attempt to initiate a new SQL Server operation with 
     results pending.
     
     I would open up a new $dbh, but then I wouldn't have the error 
     information would I?
     
     Thanks in advance and if I am overlooking something really obvious I 
     apologize.
     
     Regards,
     
     Sal
     
     *********************************************************************
     
     
     
     $dbh->dbcmd("BEGIN TRANSACTION\n");
     
     $dbh->dbcmd("INSERT INTO Foo (MyID, Name, Number)
                  VALUES (1, 'bar', 34)\n");
     
     &test_ins ($dbh);
     
     #################################################################
     # Subroutines...                                                
     #################################################################
     
     sub test_ins {
     
         my $dbh = @_[0];
     
         $dbh->dbcmd("INSERT INTO Foo (MyID, Name, Number) 
                      VALUES (2, 'foo', 'bad value')\n");
     
         $dbh->dbsqlexec; $dbh->dbresults;
     
         if (&get_last_err ($dbh)) {
     
        $dbh->dbcmd("ROLLBACK TRANSACTION")         
            
         } else {
     
        $dbh->dbcmd("COMMIT TRANSACTION")
     
         }          
     
         $dbh->dbsqlexec; $dbh->dbresults;
     
     }
     
     sub get_last_err {
     
         my $dbh = @_[0];
     
         $dbh->dbcmd("SELECT \@\@error\n");
     
         $dbh->dbsqlexec; $dbh->dbresults;
     
         return $dbh->dbnextrow(1);
     
     }