Introduction to embedded SQL programming
Structured Query Language (SQL) is a standardized language used to manipulate database objects and the data they contain. SQL is comprised of several different statements that are used to define, alter, and destroy database objects, and to insert, modify, delete, and retrieve data values. But because SQL is nonprocedural in nature, SQL is not a general-purpose programming language. (SQL statements are executed by the DB2 Database Manager, not by the operating system.) 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. This technique is known as embedded SQL programming.
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 finds 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. (We will take a closer look at host variables in section below entitled "Declaring host variables.") 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 (
A static SQL statement is an SQL statement that can be hardcoded in an application program at development time because information about the structure and objects (i.e., tables, column, and data types) with which it is intended to interact with is known in advance. Since the details of a static SQL statement are known at development time, the work of analyzing the statement and selecting the optimum data access plan for executing the statement is performed by the DB2 optimizer as part of the development process. Because their operational form is stored in the database (as a package) and does not have to be generated at application runtime, static SQL statements execute quickly.
The downside to this approach is that all static SQL statements must be prepared (in other words, their access plans must be generated and stored in the database) before they can be executed. Furthermore, static SQL statements cannot be altered at runtime, and each application that uses static SQL must bind its operational packages to every database with which the application will interact. Additionally, because static SQL applications require prior knowledge of database objects, changes made to those objects after an application has been developed can produce undesirable results.
The following are examples of static SQL statements:
SELECT COUNT(*) FROM employee UPDATE employee SET lastname = 'Jones' WHERE empid = '001' SELECT MAX(salary), MIN(salary) INTO :MaxSalary, :MinSalary FROM employee
Generally, static SQL statements are well suited for high-performance applications that execute predefined operations against a known set of database objects.
Although static SQL statements are relatively easy to incorporate into an application, their use is somewhat limited because their format must be known in advance. Dynamic SQL statements, on the other hand, are much more flexible because they can be constructed at application runtime; information about a dynamic SQL statement's structure and the objects with which it plans to interact does not need to be known in advance. Furthermore, because dynamic SQL statements do not have a precoded, fixed format, the data objects they reference can change without affecting the statement (provided that objects referenced by the statement are not deleted).
Even though dynamic SQL statements are generally more flexible than static SQL statements, they are usually more complicated to incorporate into an application. And because the work of analyzing the statement to select the best access plan is performed at application runtime (again, by the DB2 optimizer), dynamic SQL statements can take longer to execute than their static SQL counterparts. (Because dynamic SQL statements can take advantage of the database statistics available at application runtime, there are some cases in which a dynamic SQL statement will execute faster than an equivalent static SQL statement, but those are the exception and not the norm.)
The following are examples of dynamic SQL statements:
SELECT COUNT(*) FROM ? INSERT INTO EMPLOYEES VALUES (?, ?) DELETE FROM DEPARTMENT WHERE DEPTID = ?
Generally, 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.