Detaches from declared databases and releases the aliases that
you specified in the declarations. The DISCONNECT statement also
ends the specified transactions and undoes all the changes you
made since those transactions began.
1 – Environment
You can use the DISCONNECT statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
DISCONNECT ---+-> <connection-name> -+->
+-> ALL ---------------+
+-> CURRENT -----------+
+-> DEFAULT -----------+
connection-name =
-+--> ' <literal> ' -------+---->
+--> <parameter> ---------+
+--> <parameter-marker> --+
3 – Arguments
3.1 – ALL
Specifies all active connections.
3.2 – connection-name
Specifies a name for the association between the group of
databases being attached (the environment) and the database and
queries that reference them (the session).
You can specify the connection name as the following:
o A string literal enclosed in single quotation marks
o A parameter (in module language)
o A variable (in precompiled SQL)
3.3 – CURRENT
Specifies the current connection.
3.4 – DEFAULT
Specifies the default connection.
4 – Examples
Example 1: Using the DISCONNECT statement in interactive SQL
This example in interactive SQL illustrates that the DISCONNECT
statement lets you attach a database with the same alias as a
previously attached database (in this example the alias is the
default). Use the SHOW DATABASE statement to see the database
settings.
SQL> ATTACH 'FILENAME mypers';
SQL> --
SQL> ATTACH 'FILENAME mypers';
This alias has already been declared.
Would you like to override this declaration (No)? no
%SQL-F-DEFDBDEC, A database has already been declared with the default alias
SQL> DISCONNECT DEFAULT;
SQL> ATTACH 'FILENAME mypers';
Example 2: Using the DISCONNECT statement in precompiled SQL
This example is taken from the sample program sql_connections.sc.
To use connections in a program, you must specify the
SQLOPTIONS=(CONNECT) qualifier on the precompiler command
line. This example shows an EXEC SQL DISCONNECT statement that
specifies the string literal 'al' for the connection-name and
EXEC SQL DISCONNECT statements that specify the keywords ALL and
DEFAULT.
#include <stdio.h>
#include <string.h>
#include <descrip.h>
char employee_id1[6];
char last_name1[16];
char employee_id2[16];
char degree[14];
char employee_id3[16];
char supervisor[6];
char employee_id4[6];
char last_name4[15];
void sys$putmsg();
EXEC SQL INCLUDE SQLCA;
EXEC SQL declare alias filename personnel;
EXEC SQL declare alias_1 alias filename personnel;
EXEC SQL declare alias_2 alias filename personnel;
EXEC SQL declare alias_3 alias filename personnel;
main()
{
printf("\n\n\n******* Disconnect from default ***************\n");
EXEC SQL disconnect default;
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
printf("\n\n\n");
printf("********* Establish CONNECTION 1 **********\n");
EXEC SQL connect to 'alias alias_1 filename personnel' as 'a1';
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
printf("********* Insert a record **********\n");
EXEC SQL insert into alias_1.employees (employee_id, last_name)
values ('00301','FELDMAN');
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
printf("********* Retrieve the record **********\n");
EXEC SQL select employee_id, last_name into :employee_id1,
:last_name1 from alias_1.employees where employee_id = '00301';
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
printf("\n\n\n");
printf ("Employee_id = %s\n",employee_id1);
printf ("Last_name = %s\n",last_name1);
printf("\n\n\n");
printf("********* Establish CONNECTION 2 **********\n");
EXEC SQL connect to 'alias alias_2 filename personnel' as 'a2';
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
printf("********* Insert a record **********\n");
EXEC SQL insert into alias_2.degrees (employee_id, degree_field)
values ('00901','MASTERS');
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
printf("********* Retrieve the record **********\n");
EXEC SQL select employee_id, degree_field into :employee_id2,
:degree from alias_2.degrees where employee_id = '00901';
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
printf("\n\n\n");
printf("Employee-id = %s\n",employee_id2);
printf("Degree = %s\n",degree);
printf("\n\n\n");
printf("********* Establish CONNECTION 3 **********\n");
EXEC SQL connect to 'alias alias_3 filename personnel' as 'a3';
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
printf("********* Insert a record **********\n");
EXEC SQL insert into alias_3.job_history (employee_id, supervisor_id)
values ('01501','Brown');
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
printf("********* Retrieve the record **********\n");
EXEC SQL select employee_id, supervisor_id into :employee_id3,
:supervisor from alias_3.job_history where employee_id = '01501';
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
printf("\n\n\n");
printf("Employee-id = %s\n",employee_id3);
printf("Supervisor = %s\n ",supervisor);
printf("\n\n\n");
printf("********* Establish CONNECTION DEFAULT **********\n");
EXEC SQL set connect default ;
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
printf("********* Retrieve record with id 00164 **********\n");
EXEC SQL select employee_id, last_name into :employee_id4,
:last_name4 from employees where employee_id = '00164';
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
printf("\n\n\n");
printf("Employee_id = %s\n",employee_id4);
printf("Last_name = %s\n",last_name4);
printf("\n\n\n");
printf("**** DISCONNECT, RECONNECT & TRY TO FIND RECORD *****\n");
strcpy(employee_id1," ");
strcpy(last_name1," ");
EXEC SQL disconnect 'a1';
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
EXEC SQL connect to 'alias alias_1 filename personnel' as 'a1';
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
printf("********* Retrieve the record **********\n");
EXEC SQL select employee_id, last_name into :employee_id1,
:last_name1 from alias_1.employees where employee_id = '00301';
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
printf("************** SHOULD DISPLAY NO RECORD **************\n");
printf("\n\n\n");
printf("employee_id = %s\n",employee_id1);
printf("last_name = %s\n",last_name1);
printf("\n\n\n");
printf("*************** DISCONNECT ALL CONNECTIONS ***************\n");
EXEC SQL disconnect all;
if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
EXEC SQL rollback;
}