Embedded SQL database applications connect to databases and execute
embedded SQL statements. Embedded SQL statements are embedded within
a host language application. Embedded SQL database applications support
the embedding of SQL statements to be executed statically or dynamically.
You can develop embedded SQL applications for DB2® in the following host programming languages:
C, C++, COBOL, FORTRAN, and REXX.
Note: Support for embedded SQL in FORTRAN
and REXX has
been deprecated and will remain at the DB2 Universal Database™ , Version
5.2 level.
Building embedded SQL applications involves two prerequisite steps
prior to application compilation and linking.
- Preparing the source files containing embedded SQL statements
using the DB2 precompiler.
The PREP (PRECOMPILE) command is used to invoke
the DB2 precompiler, which
reads your source code, parses and converts the embedded SQL statements
to DB2 run-time services API
calls, and finally writes the output to a new modified source file.
The precompiler produces access plans for the SQL statements, which
are stored together as a package within the database.
- Binding the statements in the application to the target database.
Binding is done by default during precompilation (the PREP command).
If binding is to be deferred (for example, running the BIND command
later), then the BINDFILE option needs to be specified
at PREP time in order for a bind file to be generated.
Once you have precompiled and bound your embedded SQL application,
it is ready to be compiled and linked using the host language-specific
development tools.
To aid in the development of embedded SQL applications, you can
refer to the
embedded
SQL template in C. Examples
of working embedded SQL sample applications can also be found in
the
%DB2PATH%\SQLLIB\samples directory.
Note: %DB2PATH%
refers to the DB2 installation
directory
Static and dynamic SQL
SQL statements can
be executed in one of two ways: statically or dynamically.
- Statically executed SQL statements
- For statically executed SQL statements, the syntax is fully known
at precompile time. The structure of an SQL statement must be completely
specified for a statement to be considered static. For example, the
names for the columns and tables referenced in a statement must be
fully known at precompile time. The only information that can be
specified at run time are values for any host variables referenced
by the statement. However, host variable information, such as data
types, must still be precompiled. You precompile, bind, and compile
statically executed SQL statements before you run your application.
Static SQL is best used on databases whose statistics do not change
a great deal.
- Dynamically executed SQL statements
- Dynamically executed SQL statements are built and executed by
an application at run-time. An interactive application that prompts
the end user for key parts of an SQL statement, such as the names
of the tables and columns to be searched, is a good example of a
situation suited for dynamic SQL.