CONNECT Examples
Connect a coordinator application to a database and, optionally, to a specified distributed transaction:
1. Connect to the master database with the current user ID, specifying both a numeric identifier and a connection name, locking the database for exclusive use.
EXEC SQL CONNECT masterdb
AS 'master_database'
IDENTIFIED BY :user_id
OPTIONS = '-l';
2. Connect to a database passed as a parameter in a character string variable.
EXEC SQL CONNECT :dbname;
3. Assuming that the connection between the coordinator application and the local DBMS has been broken, use the CONNECT statement to reconnect the application to the specified local transactions associated with a distributed transaction.
EXEC SQL BEGIN DECLARE SECTION;
int high = 1;
int low = 200;
char branch1[24] = "annie";
char branch2[24] = "annie";
EXEC SQL END DECLARE SECTION;
define SF_BRANCH 1
define BK_BRANCH 2
define BEFORE_WILLING_COMMIT 1
define WILLING_COMMIT 2
int tx_state1 = 1;
int tx_state2 = 1;
/* Read transaction state information from file */
read_from_file(&tx_state1, &high, &low, branch1);
read_from_file(&tx_state2, &high, &low, branch2);
if (tx_state1 equals WILLING_COMMIT and
tx_state2 equals WILLING_COMMIT) then
print "Both local transactions are ready to commit."
print "Re-connect to SF to commit local trx."
EXEC SQL CONNECT :branch1 SESSION :SF_BRANCH
WITH HIGHDXID = :high, LOWDXID = :low;
EXEC SQL COMMIT;
print "Re-connect to Berkeley to commit local trx."
EXEC SQL CONNECT :branch2 SESSION :BK_BRANCH
WITH HIGHDXID = :high, LOWDXID = :low;
EXEC SQL COMMIT;
else
print "Not all local trxs are ready to commit."
print "Rollback all the local transactions."
print "Re-connect to S.F to rollback the local trx."
EXEC SQL CONNECT :branch1 session :SF_BRANCH
WITH HIGHDXID = :high, LOWDXID = :low;
EXEC SQL ROLLBACK;
print "Re-connect to Berkeley to rollback local trx."
EXEC SQL CONNECT :branch2 session :BK_BRANCH
WITH HIGHDXID = :high, LOWDXID = :low;
EXEC SQL ROLLBACK;
endif
print "Distributed transaction complete."
...