Connection Management in ESQL-
SYNTAX EXEC SQL
CONNECT TO ’connect_string’
[
AS connection_name]
[
USER username]
[
USING password]
; SYNTAX DEFAULT|
db_nameNumber of the communications port where the SQL Server is running. Corresponds to the -S start-up parameter.
Name of the database. connection_name
The name of the connection to use in CONNECT, DISCONNECT and SET CONNECTION
statements. The connection_name must be either string literals enclosed in quotation marks or character-string C Language variables.
If a CONNECT statement omits the optional connection_name, the SQL engine assigns a connection_name which is the same as the database name. Connection names must be unique.
username
User name for authentication of the connection. The SQL engine verifies the username against a corresponding password before it connects to the database. On both UNIX and Windows-NT platforms, the value of the DH_USER environment variable determines the default username. If DH_USER is not set, the value of the USER environment variable determines the default username.
password
Password for authentication of the connection. The SQL engine verifies the password against a corresponding usernamebefore it connects to the database.
EXAMPLES
This example establishes a connection to a database using the connection_name conn_1:
The connection_namemust be unique. If you do not specify a connection_name the value of the connect_stringis used as the name of the connection.
EXEC SQL
Connection Management in ESQL-92
4–5
4.2.2
CONNECT by DEFAULT
The CONNECT statement with the DEFAULT keyword establishes a connection to a default database. The default database is the one set in the environment variable DB_NAME. To connect to the DEFAULT database, use this CONNECT statement:
The CONNECT statement does not specify a connection_name. No connection name is needed for the DEFAULT connection since you can always refer to this connection with the keyword DEFAULT. If an application executes an SQL statement before establishing a connection to a database, the SQL engine attempts to connect to an environment-defined database. If the connection is successful, the database executes the SQL statement.
4.2.3
CONNECT to a Remote Database
The connect_string specifies the database type, network protocol, the target host for the database, the port number, and the database name:
4.3
SET CONNECTION Statement
Use the SET CONNECTION statement to switch the application from one established connection to another. This resumes the connection associated with the specified
connection_name, restoring the context of that database connection to the state it was in when suspended.
EXEC SQL
CONNECT TO DEFAULT;
EXEC SQL
CONNECT TO ’progress:T:rockwell:6770:salesdb’ as ’conn_2’ ;
SYNTAX
EXEC SQL
EXAMPLES
The first example shows how to establish a database as the current database. The SET CONNECTION command sets the database associated with the connection named ’conn_1’ to the status of current database. The connection named ’conn_1’ must be associated with an established connection:
Use this statement to set current the database associated with the DEFAULT connection. This statement changes the state of the earlier ’conn_1’ connection from current to suspended:
4.4
DISCONNECT Statement
The DISCONNECT statement terminates the connection between an application and the database to which it is connected:
connection_name
Disconnects the named database. If there is also an established connection to the DEFAULT
database, the connection to the DEFAULT database becomes the current connection. If there is no DEFAULT database, there is no current connection after the SQL engine processes the
DISCONNECT.
CURRENT EXEC SQL
SET CONNECTION ’conn_1’ ;
EXEC SQL
SET CONNECTION DEFAULT ;
SYNTAX
EXEC SQL
Connection Management in ESQL-92
4–7 ALL
Disconnects all established connections. After you issue DISCONNECT ALL, there is no
CURRENT connection.
DEFAULT
Terminates the connection to the DEFAULT database. If this connection is the current connection, there is no current connection after this DISCONNECT statement is executed.
EXAMPLES
The first example illustrates CONNECT TO AS ’connection_name’ and DISCONNECT
’connection_name’:
This example illustrates CONNECT TO DEFAULT and DISCONNECT DEFAULT:
When you specify the ALL option, all established connections are disconnected. After you issue DISCONNECT ALL there is no current connection. For example:
EXEC SQL
CONNECT TO ’progress:T:localhost:6745:salesdb’ AS ’conn_1’ ; /*
** C Language and embedded SQL-92 application processing against the ** database in the connect_string
*/ . . . EXEC SQL DISCONNECT ’conn_1’ ; EXEC SQL CONNECT TO DEFAULT ; /*
** C Language and embedded SQL-92 application processing against the ** database in the connect_string
*/ . . . EXEC SQL DISCONNECT DEFAULT ; EXEC SQL DISCONNECT ALL;
The following example illustrates these steps:
• CONNECT TO ’connect_string’ AS ’connection_name’ establishes a connection to the database in the connect_string; the connection has the name ’conn_1’.
• CONNECT TO DEFAULT establishes a connection to the DEFAULT database and sets this connection current.
• DISCONNECT DEFAULT disconnects the connection to the DEFAULT database.
• SET CONNECTION ’conn_1’ sets the ’conn_1’ connection current.
• DISCONNECT CURRENT disconnects the ’conn_1’ connection.
EXEC SQL
CONNECT TO ’progress:T:localhost:6745:salesdb’ AS ’conn_1’ ; EXEC SQL
CONNECT TO DEFAULT ; /*
** Application processing against the DEFAULT database */ . . . EXEC SQL DISCONNECT DEFAULT ; EXEC SQL
SET CONNECTION ’conn_1’ ; /*
** Application processing against the database in the connect_string */ . . . EXEC SQL DISCONNECT CURRENT ;