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;
    }