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.

Purpose

The SET CONNECTION statement makes the named connection the current connection.

Note:

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