Examples: Select (embedded)

The following examples illustrate the non-cursor Select statement:

1. Retrieve the name and salary of an employee. Drop locks by committing the following transaction.

   exec sql select ename, sal
       into :namevar, :salvar
       from employee
       where eno = :numvar;
   exec sql commit;

2. Select all columns in a row into a host language variable structure. (The emprec structure has members that correspond in name and type to columns of the employee table.)

   exec sql select *
       into :emprec
       from employee
       where eno = 23;

3. Select a constant into a variable.

   exec sql select 'Name: ', ename
       into :title, :ename
       from employee
       where eno >= 148 and age = :age;

4. Select the row in the employee table whose number and name correspond to the variables, numvar and namevar. The columns are selected into a host structure called emprec. Because this statement is issued many times (in a subprogram, perhaps), it is formulated as a repeat query.

   exec sql repeated select *
       into :emprec
       from employee
       where eno = :numvar and ename = :namevar;

5. Example of a select loop: insert new employees, and select all employees and generate a report. If an error occurs during the process, end the retrieval and back out the changes. No database statements are allowed inside the select loop (begin-end block).

   error = 0;

   exec sql insert into employee

       select * from newhires;

   exec sql select eno, ename, eage, esal, dname

           into :eno, :ename, :eage, :esal, :dname

       from employee e, dept d

       where e.edept = d.deptno

       group by ename, dname

   exec sql begin;

       generate report of information;

       if error condition then

           error = 1;

           exec sql endselect;

       end if;

   exec sql end;

   /*

   ** Control transferred here by completing the

   ** retrieval or because the endselect statement

   ** was issued.

   */

   if error = 1

       print 'Error encountered after row',

           sqlca.sqlerrd(3);

       exec sql rollback;

   else

       print 'Successful addition and reporting';

       exec sql commit;

   end if;

6. The following select statement uses a string variable to substitute for the complete search condition. The variable search_condition is constructed from an interactive forms application in query mode, and during the select loop the employees who satisfy the qualification are displayed.

   run forms in query mode;
   construct search_condition of employees;
   
   exec sql select *
       into :emprec
       from employee
       where :search_condition;
   exec sql begin;
       load emprec into a table field;
   exec sql end;
   display table field for browsing;

7. This example illustrates session switching inside a select loop. The main program processes sales orders and calls the new_customer subroutine 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 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;

   The subroutine, new_customer, which is from the select loop, contains 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);
   
   /* Reset status information before resuming
   ** select loop */
   
   sqlca.sqlcode = 0;
       sqlca.sqlwarn.sqlwarn0 = ' ';
   
   end subroutine;