Connect

Valid in: ESQL

The Connect statement connects the application to a database and, optionally, to a specified distributed transaction. The embedded SQL connect statement connects an application to a database, similar to the operating-system-level sql and isql commands. The connect statement must precede all statements that access the database. The connect statement cannot be issued in a dynamic SQL statement. To terminate a connection, use the disconnect statement.

Syntax

The Connect statement has the following format:

EXEC SQL CONNECT dbname
              [AS connection_name]
              [SESSION session_number]
              [IDENTIFIED BY username]
              [DBMS_PASSWORD = dbms_password]
              [OPTIONS = flag {, flag}]
              [WITH HIGHDXID = value, LOWDXID = value]

• dbname

  Specifies the database to which the session connects. Dbname can be a quoted or unquoted string literal or a host string variable. If the name includes any name extensions (such as a system or node name), string literals must be quoted.

• connection_name

  Specifies an alphanumeric identifier to be associated with the session. The connection name must be a string of up to 128 characters that identifies the session. If the as connection_name clause and the session clause are omitted, the default connection name is the specified database name.

  Connection_name must be specified using a quoted string literal or a host language variable.

• session_number

  Specifies a numeric identifier to be associated with the session. The session number must be a positive integer literal or variable, and must be unique among existing session numbers in the application.

• username

  Specifies the user identifier under which this session runs. Username can be specified using a quoted or unquoted string literal or string variable.

• dbms_password

  Specifies the valid password either as string constant or a string program variable. This parameter allows the application to specify the password at connection time if required.

• flag

  Specifies runtime options for the connection. Valid flags are those accepted by the sql command. Flags specific to the Terminal Monitor are not valid. For more information about these flags, see the System Administrator Guide.

  The maximum number of flags is 12.

  If the -R flag is specified and the role ID has a password, use the following format:

  '-Rroleid/password '

  The flags can be specified using quoted or unquoted character string literals or string variables.

• value

  Highdxid specifies the high-order 4 bytes of a distributed transaction ID. Lowdxid specifies the low-order 4 bytes of a distributed transaction ID. These options are used for two phase commit of distributed transactions. For details, see the chapter "Working with Transactions and Handling Errors."

Connecting with Distributed Transactions

To connect to a specified database and the local transaction associated with a distributed transaction, include the with clause. In a two-phase commit application, this option allows a coordinator application to re-establish a connection that was unintentionally severed due to software or hardware problems.

The distributed transaction is identified by its distributed transaction ID, an 8-byte integer that is specified by the application. In the with clause, the value specified for highdxid must be the high-order 4 bytes of this ID and the value specified for lowdxid must be the low-order 4 bytes of the distributed transaction ID. The distributed transaction ID must have been previously specified in a prepare to commit statement.

When the program issues a connect statement that includes the with clause, a commit or a rollback statement must immediately follow the connect statement. Commit commits the open local transaction, and rollback aborts it. For more information about distributed transactions, see the chapter "Transactions and Error Handling."

Creating Multiple Sessions

If your application requires more than one connection to a database, a session identifier or number can be assigned to each session, and the set connection or set_sql(session) statements can be used to switch sessions.

Using Session Identifiers

To assign a numeric session identifier to a connection, specify the session clause. For example:

exec sql connect accounting session 99;

assigns the numeric session identifier 99 to the connection to the accounting database. To determine the session identifier for the current session, use the inquire_sql(session) statement.

To switch sessions using the numeric session identifier, use the set_sql(session) statement. For example:

exec sql set_sql(session = 99);

Using Connection Names

To assign a name to a connection, specify the as clause. For example:

exec sql connect act107b as accounting;

assigns the name, accounting, to the connection to the act107b database. To switch sessions using the connection name, use the set connection statement. For example:

exec sql set connection accounting;

If the as clause is omitted, the DBMS Server assigns a default connection name-the database specified in the connect statement. This connection name can be used in subsequent set connection statements to switch sessions. If the as clause is omitted and a numeric session identifier is specified (using the session clause), the default connection name is "iin," where n is the specified numeric session identifier.

To determine the connection name for the current session, use the inquire_sql(connection_name) statement.

Permissions

All users. To use the identified by clause, you must be one of the following:

• The DBA of the specified database
• A user with the security privilege
• A user that has been granted the db_admin privilege for the database

Locking

The Connect statement takes a database lock on the specified database. Unless an exclusive lock using the -l flag is explicitly requested, the database lock is a shared lock.

Related Statements

Set

Disconnect

Examples: Connect

The following 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."

   ...