Managing Connections

SQL statements in embedded SQL programs are by default executed on the current connection, that is, the most recently opened one. If an application needs to manage multiple connections, use either of the following methods:

• Method 1: Explicitly select a connection for each SQL statement.

  EXEC SQL AT connection-name SELECT ...;

  This method is particularly suitable if the application needs to use several connections in mixed order.

  If the application uses multiple threads of execution, they cannot share a connection concurrently. You must either explicitly control access to the connection (using mutexes) or use a connection for each thread.

• Method 2: Execute a statement to switch the connection.

  EXEC SQL SET CONNECTION connection-name;

  This method is particularly suitable if many statements are executed on the same connection.

#include <stdio.h>  
EXEC SQL BEGIN DECLARE SECTION;
    char dbname[1024]; 
EXEC SQL END DECLARE SECTION;  

int main() 
{
     EXEC SQL CONNECT TO testdb1 AS con1 USER testuser;
     EXEC SQL CONNECT TO testdb2 AS con2 USER testuser;
     EXEC SQL CONNECT TO testdb3 AS con3 USER testuser;

     /* This query will be executed in the most recently opened database testdb3. */
     EXEC SQL SELECT current_database() INTO :dbname;
     printf("current=%s (should be testdb3)\n", dbname);

     /* Use AT to run a query in testdb2. */
     EXEC SQL AT con2 SELECT current_database() INTO :dbname;
     printf("current=%s (should be testdb2)\n", dbname);

     /* Switch to connection to testdb1. */
     EXEC SQL SET CONNECTION con1;

     EXEC SQL SELECT current_database() INTO :dbname;
     printf("current=%s (should be testdb1)\n", dbname);

     EXEC SQL DISCONNECT ALL;
     return 0; 
}

Example output:

current=testdb3 (should be testdb3)
current=testdb2 (should be testdb2) 
current=testdb1 (should be testdb1)

• In multi-thread mode, different threads cannot use the same connection name. The connection name of each thread must be unique.
• A connection must be established and closed in the same process or thread.