|
|
|
Embedded SQL can maintain multiple sessions (connections to a database). An application can open an initial session and, with subsequent connect statements, open additional sessions connected with the same database or with different databases.
|
|
|
To open a session, issue the connect statement. To identify individual sessions in a multiple-session application, assign a connection name or numeric session identifier when issuing the connect statement. Create multiple sessions that connect to the same database. For each connection, specify different runtime options, including the effective user.
The current session is established when an application connects to a database (by issuing the connect statement) or switches sessions (using the set connection or set_sql(session) statements). If an error occurs when a program attempts to connect to a database, there is no current session in effect. Before the program can issue any queries, it must establish the current session by (successfully) connecting to a database or switching to a previously established session.
|
|
|
The connect statement assigns each session a numeric session identifier and a connection name. The numeric identifier must be a positive integer. The connection name must be no longer than 128 characters.
|
|
|
To switch sessions using a numeric session identifier, use the set_sql(session) statement. To switch sessions using the connection name, use the set connection statement.
To determine the numeric session identifier for the current session, use the inquire_sql)session) statement. To determine the connection name for the current statement, use the inquire_sql(session) statement.
Applications can switch sessions in the following circumstances:
The program code for the nested session must be inside a host language subroutine. If it is not, the SQL preprocessor issues an error.
Note: Sessions cannot be switched inside a database procedure.
After an application switches sessions, the error information obtained from the SQLCA or the inquire_sql statement is not updated until an SQL statement has completed in the new session.
|
|
|
To disconnect from the current session, the application issues the disconnect statement. To disconnect a session other than the current session, specify the numeric session identifier or connection name. To disconnect all connected sessions, issue the disconnect all statement. For details, see Disconnect in the chapter "SQL Statements."
After an application disconnects from the current session in a multi-session application, the application must establish the current session by issuing the set connection, set_sql(session), or connect statement. If no current session is in effect when an application issues a query, an error is returned.
|
|
|
The SQL Communications Area (SQLCA) is a data area in which the DBMS Server passes query status information to your application program. Although an application can sustain multiple sessions, there is only one SQLCA per application. However, the values returned by the inquire_sql(errorcode) and inquire_sql(errortext) statements are specific to a session.
If sessions are switched in a select loop (for example, by calling a routine that switches sessions) and database statements are executed in the alternate session, the values in the SQLCA are reset. When returning to the original session, the values in the SQLCA reflect the results of the statements issued in the alternate session and not the results of the select loop.
When sessions are switched, the values in the SQLCA fields are not updated until after the first SQL statement in the new session has completed. In contrast, the error information returned by inquire_sql (errortext and errorno) always applies to the current session. The results of the session switch are returned in SQLSTATE.
When an application switches sessions within a select loop or other block statement, the SQLCA field values are updated to reflect the status of the statements executed inside the nested session. After the application switches back to the session with the loop, the SQLCA field values reflect the status of the last statement in the nested session. Sqlcode and sqlwarn are not updated until the statement immediately following the loop completes. (The information obtained by inquire_sql is not valid either until the statement following a loop completes.) For this reason, the application must reset the sqlcode and sqlwarn fields before continuing the loop.
|
|
|
The DBMS Server treats each session in a multiple-session application as an individual application. When creating multiple-session applications, keep the following points in mind:
|
|
|
The following example shows the use of two open sessions in an application that gathers project information for updating the projects database using the personnel database to verify employee identification numbers. This example illustrates session switching and the use of connection names.
exec sql begin declare section;
empid integer;
found integer;
...
exec sql end declare section;
/* Set up two database connections */
exec sql connect projects as projects;
exec sql connect personnel as personnel;
/* Set 'projects' database to be current session */
exec sql set connection projects;
display project form
position cursor to emp id field
/* Validate user-entered employee id against
** master list of employees in personnel
** database. */
found = 0;
load empid host variable from field on form
/* Switch to 'personnel' database session */
exec sql set connection personnel;
exec sql repeated select 1 into :found
from employee
where empid = :empid;
/* Switch back to 'project' database session */
exec sql set connection projects;
if (found !=1) then
print 'Invalid employee identification'
else
position cursor to next field
endif;
end if
/* program code to validate other fields in 'projectform' */
if user selects 'Save' menu item
get project information and update 'projectinfo' table
...
exec sql disconnect personnel;
exec sql disconnect projects;
|
|
|
The following examples illustrate session switching inside a select loop and the resetting of status fields. The main program processes sales orders and calls the subroutine new_customer for every new customer. This example illustrates the use of numeric session identifiers.
The following is an example of the main program:
exec sql include sqlca;
exec sql begin declare section;
/* Include output of dclgen for declaration of
** record order_rec */
exec sql include 'decls';
exec sql end declare section;
exec sql connect customers session 1;
exec sql connect sales session 2;
...
exec sql select * into :order_rec from orders;
exec sql begin;
if (order_rec.new_customer = 1) then
call new_customer(order_rec);
endif
process order;
exec sql end;
...
exec sql disconnect;
exec sql set_sql(session = 1);
exec sql disconnect;
The following is an example of subroutine new_customer from the select loop, containing the session switch:
subroutine new_customer(record order_rec)
begin;
exec sql set_sql(session = 1);
exec sql insert into accounts values
(:order_rec);
process any errors;
exec sql set_sql(session = 2);
sqlca.sqlcode = 0;
sqlca.sqlwarn.sqlwarn0 = ' ';
end subroutine;