This chapter discusses the techniques of, and how to work with, Embedded SQL. Topics covered include:
|
|
|
Embedded SQL statements refer to SQL statements embedded in a host language such as C or Fortran. Embedded SQL statements include most interactive SQL statements and statements that fulfill the additional requirements of an embedded program.
|
|
|
Embedded SQL statements are processed by an embedded SQL (ESQL) preprocessor, which converts the ESQL statements into host language source code statements. The resulting statements are calls to a runtime library that provides the interface to Ingres (host language statements are not altered by the ESQL preprocessor). After the program has been preprocessed, it must be compiled and linked according to the requirements of the host language. For details about compiling and linking an embedded SQL program, see the Embedded SQL Companion Guide.
|
|
|
In the examples in this chapter, host language program elements are indicated by pseudocode in italics. All of the examples use the semicolon (;) as the statement terminator. In an actual program, however, the statement terminator is determined by the host language.
An embedded SQL statement has the following format:
[margin] exec sql SQL_statement [terminator]
Note: To create forms-based applications, use forms statements. For details, see the Forms-based Application Development Tools User Guide.
When creating embedded SQL (ESQL) programs, remember the following points:
Some host languages allow the placement of a line number in the margin. For information about language-dependent syntax, see the Embedded SQL Companion Guide.
|
|
|
In general, SQL statements can be embedded anywhere in a program that host language statements are allowed. The following example shows a simple embedded SQL program that retrieves an employee 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 SQL 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;
exec sql select ename, sal
into :name, :salary
from employee
where eno = 23;
print name, salary;
exec sql disconnect;
end program
The above sequence of statements illustrates a pattern common to most embedded SQL programs. The first SQL statement to appear is:
exec sql include sqlca;
This statement incorporates the SQL error and status handling mechanism—the SQL Communications Area (SQLCA)—into the program. The SQLCA is used by the whenever statement, appearing later in the example.
Next is an SQL declaration section. Host language variables must be declared to SQL prior to their use in any embedded SQL statements. Host language variables are described in detail in the next section.
The whenever statement that follows uses information from the SQLCA to control program execution under error or exception conditions. For details about error handling, see Error Handling in the chapter "Working with Transactions and Error Handling." In general, an error handling mechanism must precede all executable embedded SQL statements in a program.
Following the whenever statement is a series of SQL and host language statements. The first statement:
exec sql connect personnel;
initiates access to the personnel database. A connect statement must precede any references to a database.
Next is the familiar select statement, containing a clause that begins with the keyword, into. The into clause associates values retrieved by the select statement with host language variables in the program. Following the into keyword are the two host language variables previously declared to SQL, name and salary.
Following the select statement is a host language statement that prints the values contained in the variables.
The last database statement in the program is:
exec sql disconnect;
This statement severs the connection of the program to the database.