Informix Guide to SQL: Syntax SQL Statements

Use the DISCONNECT statement to terminate a connection between an application and a database server.

The DISCONNECT statement lets you terminate a connection to a database server. If a database is open, it closes before the connection drops. Even if you made a connection to a specific database only, that connection to the database server is terminated by the DISCONNECT statement.

You cannot use the PREPARE statement for the DISCONNECT statement.

In ESQL/C, if you disconnect a specific connection with connection or connection_var, DISCONNECT generates an error if the specified connection is not a current or dormant connection.

A DISCONNECT statement that does not terminate the current connection does not change the context of the current environment (the connection context).

Use the DEFAULT option to identify the default connection for a DISCONNECT statement. The default connection is one of the following connections:

• An explicit default connection (a connection established with the CONNECT TO DEFAULT statement)
• An implicit default connection (any connection made with the DATABASE or CREATE DATABASE statements)

You can use DISCONNECT to disconnect the default connection. For more information, see DEFAULT Option and The Implicit Connection with DATABASE Statements.

If the DATABASE statement does not specify a database server, as shown in the following example, the default connection is made to the default database server:

EXEC SQL database 'stores_demo';

.

.

.

EXEC SQL disconnect default;

If the DATABASE statement specifies a database server, as shown in the following example, the default connection is made to that database server:

EXEC SQL database 'stores_demo@mydbsrvr';

.

.

.

EXEC SQL disconnect default;

In either case, the DEFAULT option of DISCONNECT disconnects this default connection. See DEFAULT Option and The Implicit Connection with DATABASE Statements for more information about the default database server and implicit connections.

Use the CURRENT keyword with the DISCONNECT statement as a shorthand form of identifying the current connection. The CURRENT keyword replaces the current connection name. For example, the DISCONNECT statement in the following excerpt terminates the current connection to the database server mydbsrvr:

CONNECT TO 'stores_demo@mydbsrvr'

.

.

.

DISCONNECT CURRENT

When a transaction is active, the DISCONNECT statement generates an error. The transaction remains active, and the application must explicitly commit it or roll it back. If an application terminates without issuing a DISCONNECT statement (because of a system failure or program error, for example), active transactions are rolled back.

If you issue the DISCONNECT statement in a thread-safe ESQL/C application, keep in mind that an active connection can only be disconnected from within the thread in which it is active. Therefore, one thread cannot disconnect the active connection of another thread. The DISCONNECT statement generates an error if such an attempt is made.

However, once a connection becomes dormant, any other thread can disconnect this connection unless an ongoing transaction is associated with the dormant connection (the connection was established with the WITH CONCURRENT TRANSACTION clause of CONNECT). If the dormant connection was not established with the WITH CONCURRENT TRANSACTION clause, DISCONNECT generates an error when it tries to disconnect it.

For an explanation of connections in a thread-safe ESQL/C application, see SET CONNECTION.

Use the keyword ALL to terminate all connections established by the application up to that time. For example, the following DISCONNECT statement disconnects the current connection as well as all dormant connections:

DISCONNECT ALL

In ESQL/C, the ALL keyword has the same effect on multithreaded applications that it has on single-threaded applications. Execution of the DISCONNECT ALL statement causes all connections in all threads to be terminated. However, the DISCONNECT ALL statement fails if any of the connections is in use or has an ongoing transaction associated with it. If either of these conditions is true, none of the connections is disconnected.

Related statements: CONNECT, DATABASE, and SET CONNECTION

For information on multithreaded applications, see the Informix ESQL/C Programmer's Manual.