Specify a connection name for any Embedded SQL statement that you want to execute on a connection other than the default unnamed connection. If your application program uses only one connection, you can leave the connection unnamed and omit the at clause.
The syntax for using multiple connections is:
exec sql [at connection_name] sql_statement
end-exec
where sql_statement is a Transact-SQL statement.
The following example shows how two connections can be established to different servers and used in consecutive statements:
exec sql begin declare section end-exec
01 USER PIC X(16) VALUE "myname".
01 PASSWD PIC X(16) VALUE "mypass".
01 AU-NAME PIC X(20).
01 A-VALUE PIC S9(9) COMP.
01 A-TEST PIC S9(9) COMP.
01 SERVER-1 PIC X(16).
01 SERVER-2 PIC X(16).
exec sql end declare section end-exec.
. . .
MOVE "sybase1" TO SERVER-1.
MOVE "sybase2" TO SERVER-2.
exec sql connect :USER identified by :PASSWD
using :SERVER-1 end-exec.
exec sql connect :USER identified by :PASSWD
at connection-2 using :SERVER-2 end-exec.
* This statement uses the current connection
* (connection-2)
exec sql select royalty into :A-VALUE from pubs
where author = :AU-NAME end-exec.
* This statement uses connection "SERVER-1"
IF A-VALUE = A-TEST
exec sql at SERVER-1 update titles
set column = :A-VALUE * 2
where author = :AU-NAME end-exec.
