Multiple Session Examples
This section presents two examples of multiple sessions. The first example, illustrates session switching using two open sessions in a forms‑based application. These sessions gather project information for updating the projects database using the personnel database to verify employee identification numbers.
exec sql begin declare section;
empid integer;
found integer;
...
exec sql end declare section;
/* Set up two database connections */
exec sql connect 'projects/rdb' session 1;
exec sql connect 'unixbox::personnel/db2udb' session 2;
/* Set 'projects' database to be current session */
exec sql set_sql (session = 1);
exec frs display projectform;
exec frs activate field empid;
exec frs begin;
/* Validate user‑entered employee id against master
** list of employees in personnel database. */
found = 0;
exec sql getform (:empid = empid);
/* Switch to 'personnel' database session */
exec sql set_sql (session = 2);
exec sql repeated select 1 into :found from employee
where empid = :empid;
/* Switch back to 'project' database session */
exec sql set_sql (session = 1);
if (found !=1) then
exec frs message 'Invalid employee identification';
exec frs sleep 2;
else
exec frs resume next;
endif;
exec frs end;
program code to validate other fields in
'projectform'
exec frs activate menuitem 'Save';
exec frs begin;
get project information and update 'projectinfo'
table
exec frs end;
...
exec sql disconnect;
exec sql set_sql (session = 2);
exec sql disconnect;
The second example illustrates 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.
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/alb' 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 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;
Last modified date: 01/30/2023