This tutorial was designed to introduce you to embedded SQL programming and to walk you through the steps that are used to develop an embedded SQL application. Structured Query Language (SQL) is a standardized language used to manipulate database objects and the data they contain. Because SQL is nonprocedural in nature, it is not a general-purpose programming language. Therefore, database applications are usually developed by combining the decision and sequence control of a high-level programming language with the data storage, manipulation, and retrieval capabilities of SQL. Several methods are available for merging SQL with a high-level programming language, but the simplest approach is to embed SQL statements directly into the high-level programming language source code files that are used to create an application.
One of the drawbacks to developing applications using embedded SQL is that high-level programming language compilers do not recognize, and therefore cannot interpret, SQL statements embedded in a source code file. Because of this, source code files containing embedded SQL statements must be preprocessed (by a process known as precompiling) before they can be compiled and linked to produce an executable application. To facilitate this preprocessing, each SQL statement embedded in a high-level programming language source code file must be prefixed with the keywords
EXEC SQL and terminated with either a semicolon (in C/C++) or the keyword
END-EXEC (in COBOL). Preprocessing is performed by a special tool known as the SQL precompiler; when the SQL precompiler encounters the
EXEC SQL keywords, it replaces the text that follows (until it encounters a semicolon (
;) or the keyword
END-EXEC) with a DB2-specific function call that forwards the SQL statement encountered to the DB2 Database Manager for processing.
Likewise, the DB2 Database Manager cannot work directly with high-level programming language variables. Instead, it must use special variables known as host variables to move data between an application and a database. Host variables look like any other high-level programming language variable; so, to be set apart, they must be defined in a special section known as a declare section. Also, in order for the SQL precompiler to distinguish host variables from other text in an SQL statement, all references to host variables must be preceded by a colon (
In order to perform any type of operation against a database, you must first establish a connection to that database. With embedded SQL applications, database connections are made (and in some cases are terminated) by executing the
CONNECT SQL statement. During the connection process, information needed to establish a connection -- such as the authorization ID and a corresponding password of an authorized user -- is passed to the appropriate database for validation. Often, this information is collected at application runtime and forwarded to the
CONNECT statement by way of one or more host variables.
Embedded SQL applications are comprised of static and dynamic SQL statements. Static SQL statements are well suited for high-performance applications that execute predefined operations against a known set of database objects. Dynamic SQL statements are well suited for applications that interact with a rapidly changing database or that allow users to define and execute ad-hoc queries. When static SQL statements are embedded in an application program, they are executed as they are encountered. However, when dynamic SQL statements are used, they can be processed in one of two ways:
Prepare and execute: This approach separates the preparation of the SQL statement from its actual execution and is typically used when an SQL statement is to be executed repeatedly. This method is also used when an application needs advance information about the columns that will exist in the result data set produced when a
SELECTSQL statement is executed. The SQL statements
EXECUTEare used to process dynamic SQL statements in this manner.
Execute immediately: This approach combines the preparation and the execution of an SQL statement into a single step and is typically used when an SQL statement is to be executed only once. This method is also used when the application does not need additional information about the result data set that will be produced, if any, when the SQL statement is executed. The SQL statement
EXECUTE IMMEDIATEis used to process dynamic SQL statements in this manner.
When multiple rows are returned to an application by a query, DB2 can use a mechanism known as a cursor to retrieve values from the result data set produced. A DB2 cursor indicates the current position in a result data set (i.e., the current row) and identifies the row of data that will be returned to the application next. The following steps must be performed, in the order shown, if a cursor is to be incorporated into an embedded SQL application:
Declare (define) a cursor along with its type (read-only or updatable), and associate it with the desired query.
Open the cursor. This will cause the corresponding query to be executed and a result data set to be produced.
Retrieve (fetch) each row stored in the result data set, one by one, until an end-of-data condition occurs.
Close the cursor. This action will cause the result data set that was produced when the corresponding query was executed to be deleted.
The SQL Communications Area (SQLCA) data structure contains a collection of elements that are updated by the DB2 Database Manager each time an SQL statement is executed. One element of that structure, the
sqlcode element, is assigned a value that indicates the success or failure of the SQL statement executed. (A value of
0 indicates successful execution, a positive value indicates successful execution with warnings, and a negative value indicates that an error occurred.) At a minimum, an embedded SQL application should always check the
sqlcode value produced (often referred to as the SQL return code) immediately after an SQL statement is executed. If an SQL statement fails to execute as expected, users should be notified that an error or warning condition occurred; whenever possible, they should be provided with diagnostic information sufficient to allow them to locate and correct the problem.
Once a source code file has been written, the following steps must be performed, in the order shown, before an application that interacts with a DB2 database will be created:
All source code files containing embedded SQL statements must be precompiled.
Once a source code file containing embedded SQL statements has been processed by the SQL precompiler, the source code file produced -- along with any additional source code files needed -- must be compiled by a high-level programming language compiler.
After all source code files needed to build an application have been successfully compiled, the resulting object modules must be linked with high-level programming language libraries and DB2 libraries to create an executable program.
If the packages for the files that were processed by the SQL precompiler have not already been bound to the appropriate database, they must be bound using the bind files produced during the precompile process. This is done using a tool known as the DB2 Binder (or simply the Binder).