SET CONNECTION

Syntax:

>>--EXEC SQL--SET CONNECTION---.-name-----.---END-EXEC--><
                               +-DEFAULT--+

Parameters:

`name`	Specifies the name of a database connection. Must match the connection name specified in a previous CONNECT statement. The name can be either the connection's literal name or the name of a host variable containing character values.
DEFAULT	If you have established a connection using the CONNECT statement but omitting the connection name, you can refer to the connection established as DEFAULT.

Comment:

If you are using connections across compilation modules you must use named connections.

Example:

     EXEC SQL CONNECT TO "srv1" AS server1 USER "sa." END-EXEC
     EXEC SQL CONNECT TO "srv2" AS server2 USER "sa." END-EXEC
  
* server2 is the current connection
     EXEC SQL CREATE TABLE phil1
        (charbit    CHAR(5))
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not create table.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF

     EXEC SQL INSERT INTO phil1 VALUES('hello') END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not insert data.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
      END-IF

* set the current connection to server1
     EXEC SQL SET CONNECTION server1 END-EXEC
     EXEC SQL
        SELECT first_name
           INTO :fname
           FROM staff
           WHERE staff_id = 10
     END-EXEC

     DISPLAY fname ' says ' WITH NO ADVANCING

* set the current connection back to server2
     EXEC SQL SET CONNECTION server2 END-EXEC
     EXEC SQL
        SELECT charbit
           INTO :fname
           WHERE charbit = 'hello'
           FROM phil1
     END-EXEC

     DISPLAY fname
     EXEC SQL DISCONNECT server1 END-EXEC
     EXEC SQL DISCONNECT server2 END-EXEC
     STOP RUN