SQL-PL offers the possibility of simultaneously working with up to eight databases and/or user areas. For this purpose a (symbolic) database name has to be specified in SQL statements between the keyword SQL and the statement. Before using this statement for the first time, a user area of a started database must be assigned to this symbolic database name. This is done by means of the CONNECT statement.
The CONNECT statement establishes the specified database connection. The user area to be assigned to the symbolic database name must be defined here. The parameters for user name, password, SERVERDB, and SERVERNODE can be specified either as constants (enclosed in single quotes) or as an arbitrary expression. The specification of a SERVERNODE is only necessary when the database is located at another node than the node of the current database.
SQL-PL routines which work with several database connections can only be compiled when the used symbolic database names are assigned to a user area by means of XUSER. The symbolic database name corresponds to the USERKEY in XUSER. Care must be taken with upper and lower cases.
The symbolic database name can also be specified as a variable in the CONNECT statement.
Examples:
If errors occur during the execution of the CONNECT statement, $RC is assigned appropriately. Any routines defined for the current program for the handling of SQL errors are not called implicitly.
If the database addressed in the CONNECT statement is not available, this is also reported in $RC and the program is continued. If, however, the attempt is made to establish more than eight database connections, the program is interrupted.
The program is also terminated if no CONNECT was performed beforehand for the symbolic database name used in an SQL statement.
The RELEASE statement cancels the connection to the specified database.
Example:
If a database connection is not explicitly cancelled with RELEASE, it is maintained until the database session is terminated or another CONNECT with the same symbolic database name is performed.
Syntax:
All SQL statements can relate to the database designated by <dbname> by specifying a <dbname> behind the keyword SQL.