8. SQL Statements : PREPARE TO COMMIT : PREPARE TO COMMIT Example
 
Share this page                  
PREPARE TO COMMIT Example
The following example shows a portion of a banking application that uses the PREPARE TO COMMIT statement:
...
exec sql begin declare section;
       from_account          integer;
       to_account            integer;
       amount                integer;
       high                  integer;
       low                   integer;
       acc_number            integer;
       balance               integer;
exec sql end declare section;
 
define         sf_branch 1
define         bk_branch 2
define         before_willing_commit 1
define         willing_commit 2
 
exec sql whenever sqlerror stop;
 
/* connect to the branch database in s.f */
 
exec sql connect annie session :sf_branch;
 
/* program assigns value to from_account,
** to_account, and amount
*/
 
/* begin a local transaction on s.f branch to
** update the balance in the from_account.
*/
 
exec sql update account
       set balance = balance - :amount
       where acc_number = :from_account;
 
/* connect to the branch database in berkeley. */
 
exec sql connect aaa session :bk_branch;
 
/* begin a local transaction on berkeley branch
** to update the balance in the to_account.
*/
 
exec sql update account
       set balance = balance + :amount
       where acc_number = :to_account;
 
/* ready to commit the fund transfer transaction.
** switch to s.f branch to issue the prepare to
** commit statement.*/
 
exec sql set_sql (session = :sf_branch);
 
/* store the transaction state information */
 
store_state_of_xact(sf_branch,
       before_willing_commit, high, low, "annie"
 
exec sql prepare to commit with highdxid = :high,
       lowdxid = :low;
 
/* store the transaction state information */
store_state_of_xact(sf_branch, willing_commit,
       high, low, "aaa");
 
/* switch to berkeley branch to issue the prepare
** to commit statement.*/
 
exec sql set_sql (session = :bk_branch);
 
/* store the transaction state information */
 
store_state_of_xact(bk_branch,
       before_willing_commit, high, low, "aaa");
 
exec sql prepare to commit with highdxid = :high,
       lowdxid = :low;
 
       /* store the transaction state information */
 
store_state_of_xact(bk_branch, willing_commit,
       high, low, "aaa");
 
/* both branches are ready to commit; commit the
** fund transfer transaction. */
/* switch to s.f branch to commit the
** local transaction. */
 
exec sql set_sql (session = :sf_branch);
 
exec sql commit;
 
/* switch to berkeley branch to commit the
** local transaction. */
 
exec sql set_sql (session = :bk_branch);
 
exec sql commit;
 
/* distributed transaction complete */