Call Level Interface Guide and Reference

Comparing Embedded SQL and DB2 CLI

DB2 CLI and embedded SQL also differ in the following ways:

• DB2 CLI does not require the explicit declaration of cursors. DB2 CLI has a supply of cursors that get used as needed. The application can then use the generated cursor in the normal cursor fetch model for multiple row SELECT statements and positioned UPDATE and DELETE statements.

• The OPEN statement is not used in DB2 CLI. Instead, the execution of a SELECT automatically causes a cursor to be opened.

• Unlike embedded SQL, DB2 CLI allows the use of parameter markers on the equivalent of the EXECUTE IMMEDIATE statement (the SQLExecDirect() function).

• A COMMIT or ROLLBACK in DB2 CLI is issued via the SQLEndTran() function call rather than by passing it as an SQL statement.

• DB2 CLI manages statement related information on behalf of the application, and provides a statement handle to refer to it as an abstract object. This handle eliminates the need for the application to use product specific data structures.

• Similar to the statement handle, the environment handle and connection handle provide a means to refer to all global variables and connection specific information. The descriptor handle describes either the parameters of an SQL statement or the columns of a result set.

• DB2 CLI uses the SQLSTATE values defined by the X/Open SQL CAE specification. Although the format and most of the values are consistent with values used by the IBM relational database products, there are differences. (There are also differences between ODBC SQLSTATES and the X/Open defined SQLSTATES). Refer to Table 196 for a cross reference of all DB2 CLI SQLSTATEs.

• DB2 CLI supports read-only scrollable cursors. With scrollable cursors, you can scroll through a static read-only cursor as follows:
  • Forward by one or more rows
  • Backward by one or more rows
  • From the first row by one or more rows
  • From the last row by one or more rows.

Despite these differences, there is an important common concept between embedded SQL and DB2 CLI: DB2 CLI can execute any SQL statement that can be prepared dynamically in embedded SQL.

Note:

DB2 CLI can also accept some SQL statements that cannot be prepared dynamically, such as compound SQL statements.

Table 220 lists each SQL statement, and indicates whether or not it can be executed using DB2 CLI. The table also indicates if the command line processor can be used to execute the statement interactively, (useful for prototyping SQL statements).

Each DBMS may have additional statements that you can dynamically prepare. In this case, DB2 CLI passes the statements to the DBMS. There is one exception: the COMMIT and ROLLBACK statement can be dynamically prepared by some DBMSs but are not passed. In this case, use the SQLEndTran() function to specify either the COMMIT or ROLLBACK statement.

[ DB2 List of Books | Search the DB2 Books ]