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, then there are two ways to handle this.
The first option is to explicitly choose a connection for each SQL statement, for example:
EXEC SQL AT connection-name SELECT ... END-EXEC.
This option is particularly suitable if the application needs to use several connections in mixed order.
If your 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. If each thread uses its own connection, you will need to use the AT clause to specify which connection the thread will use.
The second option is to execute a statement to switch the current connection. That statement is:
EXEC SQL SET CONNECTION connection-name END-EXEC.
This option is particularly convenient if many statements are to be executed on the same connection. It is not thread-aware.
Here is an example program managing multiple database connections:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 DBNAME PIC X(7).
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL CONNECT TO testdb1 AS con1 USER testuser END-EXEC.
EXEC SQL CONNECT TO testdb2 AS con2 USER testuser END-EXEC.
EXEC SQL CONNECT TO testdb3 AS con3 USER testuser END-EXEC.
* This query would be executed in the last opened database "testdb3".
EXEC SQL SELECT current_database() INTO :DBNAME END-EXEC.
DISPLAY "current=" DBNAME " (should be testdb3)".
* Using "AT" to run a query in "testdb2"
EXEC SQL AT con2 SELECT current_database() INTO :DBNAME END-EXEC.
DISPLAY "current=" DBNAME " (should be testdb2)".
* Switch the current connection to "testdb1".
EXEC SQL SET CONNECTION con1 END-EXEC.
EXEC SQL SELECT current_database() INTO :DBNAME END-EXEC.
DISPLAY "current=" DBNAME " (should be testdb1)".
EXEC SQL DISCONNECT ALL END-EXEC.This example would produce this output:
current=testdb3 (should be testdb3) current=testdb2 (should be testdb2) current=testdb1 (should be testdb1)