There are three main alternatives for coding a DB2 application:
These alternatives are also discussed in "Access to Data".
Applications can be written with SQL statements embedded within a host language. The SQL statements provide the database interface, while the host language provides the remaining support needed for the application to execute.
Figure 2 shows an SQL statement embedded in a host language application:
Figure 2. Embedding SQL Statements
| Language | Example Source Code |
|---|---|
| C/C++ | strcpy( statement, "UPDATE staff SET job = 'Clerk' WHERE job = 'Mgr'");
EXEC SQL EXECUTE IMMEDIATE :statement;
if ( SQLCODE < 0 )
printf( "Update Error: SQLCODE = %ld \n", SQLCODE );
|
| COBOL | MOVE "UPDATE staff SET job = 'Clerk' WHERE job = 'Mgr'" TO STATEMENT
EXEC SQL EXECUTE IMMEDIATE :statement END-EXEC
IF SQLCODE LESS THAN 0
DISPLAY 'UPDATE ERROR: SQLCODE = ', SQLCODE
|
| FORTRAN | statement='UPDATE staff SET job = 'Clerk' WHERE job = 'Mgr'
EXEC SQL EXECUTE IMMEDIATE :statement
if ( sqlcode .lt. 0 ) THEN
write(*,*) 'Update error: sqlcode = ', sqlcode
|
In this example, the SQLCODE field of the SQLCA structure is checked to determine whether the update was successful.
SQL statements placed in an application are not specific to the host language. The database manager provides a way to convert the SQL syntax to something the host language can process.
For the C, C++, COBOL or FORTRAN language, this conversion is handled by the DB2 precompiler. The precompiler converts embedded SQL statements directly into DB2 run-time services API calls. Note that compilers with integrated preprocessor support are available from non-IBM vendors. With these compilers, the conversion and compilation can be handled in one step without invoking the PREP command. The IBM precompiler is invoked using the PREP command.
When the precompiler processes a source file, it specifically looks for SQL statements and avoids the non-SQL host language. It can find SQL statements because they are surrounded by special delimiters. For the syntax information necessary to embed SQL statements in the language you are using, see the following:
For information on embedding SQL statements in REXX applications, see "Embedding SQL Statements".
Figure 3 shows valid embedded SQL statements in the supported compiled host languages.
Figure 3. Comments in Embedded SQL Statements
| Language | Example Source Code |
|---|---|
| C/C++ | EXEC SQL
-- SQL, or C (/* */) or C++ (//) comments allowed here
DECLARE C1 CURSOR FOR sname;
EXEC SQL ROLLBACK WORK
-- SQL comments or
/* C comments or */
// C++ comments allowed here
;
/* Only C or C++ comments allowed here */
EXEC SQL ROLLBACK WORK;
/* Only C or C++ comments allowed here */
|
| COBOL | * See COBOL documentation for comment rules.
EXEC SQL
-- SQL comments allowed here, and
* full-line COBOL comments allowed here
DECLARE C1 CURSOR FOR sname END-EXEC.
EXEC SQL ROLLBACK WORK
* -- SQL or full-line COBOL comments allowed here
END-EXEC
* Only COBOL comments allowed here
EXEC SQL ROLLBACK WORK END-EXEC
* Only COBOL comments allowed here
|
| FORTRAN | C Only FORTRAN comments allowed here
EXEC SQL -- SQL comments, and
C full-line FORTRAN comment allowed here.
+ ROLLBACK WORK
I=7 ! End of line FORTRAN comments allowed here
|
For information on the rules for embedding SQL statements into a host language or in REXX, refer to the section Embedding SQL Statements in one of the following chapters that corresponds to the host language you are using:
REXX is an interpretive language. It contains the usual language constructs such as flow-control statements, functions, procedures, and variables that are required to build applications.
REXX applications use APIs which enable them to use most of the features provided by database manager APIs and SQL. Unlike applications written in a compiled language, REXX applications are not precompiled. Instead, all SQL statements are processed by a dynamic SQL handler. By combining REXX with these callable APIs, you have access to most of the database manager capabilities. Although some APIs supported through embedded SQL are not directly supported using REXX, they can be accessed using the DB2 Command Line Processor from within the REXX application.
As REXX is an interpretive language, you may find it is easier to develop and debug your application prototypes in REXX as compared to compiled host languages. Note that while DB2 applications coded in REXX do not provide the performance of DB2 applications that use compiled languages, they do provide the ability to create DB2 applications without precompiling, compiling, linking, or using additional software.
For details of coding and building DB2 applications using REXX, see Chapter 14. "Programming in REXX".
The DB2 Call Level Interface (CLI) is IBM's callable SQL interface to the DB2 family of database servers. It is a C and C++ application programming interface for relational database access, and it uses function calls to pass dynamic SQL statements as function arguments. A callable SQL interface is an application program interface (API) for database access, which uses function calls to invoke dynamic SQL statements. It is an alternative to embedded dynamic SQL, but unlike embedded SQL, it does not require host variables or a precompiler.
DB2 CLI is based on the Microsoft** Open Database Connectivity (ODBC) specification, and the X/Open** specifications. These specifications were chosen as the basis for the DB2 CLI in an effort to follow industry standards, and to provide a shorter learning curve for application programmers that are familiar with either of these database interfaces.
Information regarding the ODBC support is provided in CLI Guide and Reference.