8. SQL Statements : CONNECT : CONNECT Examples
 
Share this page                  
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."
...