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