Structure of Embedded OpenSQL Programs

In general, OpenSQL statements can be embedded anywhere in a program that host language statements are allowed. The following example shows a simple embedded OpenSQL program that retrieves an employee's name and salary from the database and prints them on a standard output device. The statements that begin with the words EXEC SQL are embedded OpenSQL statements.

begin program

exec sql include sqlca;

exec sql begin declare section;
name character_string(15);
salary float;
exec sql end declare section;

exec sql whenever sqlerror stop;

exec sql connect 'personnel/db2udb';

exec sql select ename, sal
into :name, :salary
from employee
where eno = 23;

print name, salary;

exec sql disconnect;

end program

The sequence of statements in the above example illustrates the typical structure of embedded OpenSQL programs. The first OpenSQL statement to appear is:

This statement incorporates the OpenSQL error and status handling mechanism--the SQL Communications Area (SQLCA)--into the program. The SQLCA is required by the WHENEVER statement appearing later in the example.

Next is an OpenSQL declaration section. Host language variables to OpenSQL must be declared before using the variables in embedded OpenSQL statements.

The WHENEVER statement that follows uses information from the SQLCA to control program execution under error or exception conditions. An error handling mechanism should precede all executable embedded OpenSQL statements in a program. For details about error handling, see Error Handling in the chapter “OpenSQL Features.”

Following the WHENEVER statement is a series of OpenSQL and host language statements. The first statement:

initiates access to the DB2 UDB personnel database through an Enterprise Access product. Your application must connect to a database before attempting to access the database. The slash (/) separates the database name from the server class. (The default server class is INGRES.) For details about server class, see your Enterprise Access product guide.

After connecting to the personnel database, the application issues the SELECT statement. The INTO clause specifies the host language variables into which the select statement retrieves values from the database. In the example, the variables are name and salary.

Following the SELECT statement is a host language statement that prints the values contained in the variables. Host language and embedded OpenSQL statements can be mixed in an application.