Static Embedded SQL

This section describes how to develop static embedded SQL (which from now on is referred to as "static SQL") applications, including several fundamental concepts of embedded SQL, such as how to connect to databases, how to retrieve data, and how to handle errors.

All embedded SQL programs consist of special source files. These source files can contain standard native programming language syntax, but they also can contain special embedded SQL syntax that can only be understood by a DB2 precompiler. When a source file is precompiled, all embedded SQL specific syntax is converted to DB2 library calls using native programming language syntax.

So, how does the DB2 precompiler distinguish embedded SQL syntax? The answer is simple. All embedded SQL statements begin with EXEC SQL. Embedded SQL also contains sections, which are series of statements. Sections begin with EXEC SQL BEGIN <SECTION-TYPE> SECTION and end with EXEC SQL END <SECTION-TYPE> SECTION.

Static SQL can only be used when the complete structure of an SQL statement is known at development time. At the cost of flexibility, this makes static SQL simple to develop and yields potential performance gains. Adding a static SQL query to your source file is as simple as:

EXEC SQL UPDATE T1 SET C1 = 7;

Of course, it usually won't be this simple, but even so, it doesn't get much more difficult when dealing with static SQL. For example, to dynamically specify the value of C1 in the previous query, you would use a host variable.

EXEC SQL UPDATE T1 SET C1 = :c1_value;

Host Variables

As you have probably guessed, host variables are used to pass data between an application and the database manager. To distinguish them from other variables, host variables are first declared in a DECLARE section. During precompilation, host variables are converted to corresponding host language variables.

A host variable can be used in two ways within a source file.

• Inside SQL statements.

• Outside of SQL statements.

Outside SQL statements, host variables are treated just like any other variable. Inside SQL statements, you must prefix each host variable name with a colon (:). Host variables can only be used within static SQL statements. Dynamic embedded SQL statements use parameter markers instead of host variables.

There are two types of host variables:

• Input host variables— These variables specify values to be passed to the database manager from the application during statement execution. For example, an input host variable is used in the following SQL statement:

  select product_name from product
  where product_name = <input host variable>
  

• Output host variables— These variables specify values to be returned from the database manager to the application during statement execution. For example, an output host variable is used in the following SQL statement:

  select into <output host variable> from product
  where product_name = 'Radiohead – OK Computer'
  

As stated earlier, host variables must be defined within a DECLARE section. You can specify multiple DECLARE sections in a single source module according to desired scope, however, scope is not considered during precompilation and thus all host variables must be uniquely named within a single source file. This means that a host variable cannot be redefined within the same programming module. Its definition must be compatible with the DB2 UDB data type to which it corresponds in the SQL statement.

Here is an example of a DECLARE section:

exec sql begin declare section;
/* place all host variables here */
    varcharproduct_name[21];
    smallintstore_id;
exec sql end declare section;

When developing C applications, host variables for VARCHAR and CHARACTER column types should be defined one character larger than the column definition. The extra character stores the null-terminator character. If you do not provide for the null-terminator character, the resulting value might be truncated.

DB2 UDB comes with a utility called db2dclgn. This is a declaration generator tool that makes the task of declaring host variables for database objects to be referenced in the application a little easier. Given a database and table name, it generates an output file containing the host variable declarations for the specified table. For example, to generate the host variables for the PRODUCT table in the DB2MALL database in C, you would use the following command:

db2dclgn -d db2mall -t product -l c

This produces a header file called product.h with the following contents:

struct
{
  sqlint32 product_id;
  struct
  {
    short length;
    char  data[20];
  } product_name;
  short store_id;
  double unit_cost;
  double unit_price;
  double sale_price;
  struct
  {
    short length;
    char  data[10];
  } supplier_id;
  sqlint32 units_in_stock;
  sqlint32 units_on_order;
  sqlint32 reorder_level;
  sqlint32 reorder_quantity;
  char status[1];
  SQL TYPE IS BLOB(102400) picture;
} product;

The output from the command matches the data definition of the PRODUCT table using host variables defined in C. You can modify the generated declarations as needed.

For more information on the db2dclgn command, you can issue the command db2dclgn -h or review the DB2 v8 Command Reference.

Connecting to Databases

The SQL statement used for connecting to a database is:

connect to dbname user userid using password

If your application is already connected to a database, you should release this connection first using the following command:

connect reset

The CONNECT RESET statement causes an implicit COMMIT to be performed. If the application needs to perform a single transaction (UOW) involving more than one database, the application must use a Type 2 Connection rather than the default Type 1. This behavior is dependent on the precompiler option, CONNECT. The PRECOMPILE command is discussed later in this chapter.

Connecting from an Application

In many cases when you connect in an application, you will only know the userid and password to use for the connection at run time. In these cases, you can use host variables in your connect statement as shown in the following example:

/**********************************************************
** Source File Name = connect.sqc
**
** PURPOSE: This program will establish a database
** connection as defined by the dbname host variable.
** STATEMENTS Used:
**       - CONNECT TO ....
** Concepts:
**       - Establishing a database connection
**********************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "MALL.h"
int Establish_Connection (char *szUserid, char *szPassword)
{
   EXEC SQL BEGIN DECLARE SECTION;
      char userid[9];
      char passwd[19];
      char dbname[19];
   EXEC SQL END DECLARE SECTION;

   strcpy (userid,szUserid);
   strcpy (passwd,szPassword);
   strcpy (dbname,"db2mall");

   EXEC SQL CONNECT TO :dbname USER :userid USING :passwd;
 return SQLCODE;

Note that the input arguments are copied to the local variables containing the userid and password. The variables containing the userid and password are local to the Establish_Connection function, but remember that to the DB2 UDB precompiler, they are valid DB2 UDB host variables and must be unique in the entire source module. A connection will establish the authorization id for this session. In this example, the authorization id is passed into the module as the variable szUserid.

Usually, your application will only use the resources of a single database at a time. This is known as a Connect Type 1 application. If more than one database is used, then other items must be considered. A special user variable called CURRENT SERVER is assigned by DB2 UDB following a successful connection.

You can establish a database connection implicitly using a default database. The default database is defined using the Registry profile variable DB2DBDFT. When the application is initialized and the first SQL statement is executed, an implicit connect will occur if an explicit connection has not be made. Implicitly establishing a database connection is not the recommended approach because it is based on a DB2 UDB Registry profile setting. Therefore, always code an explicit database connection in your application. You should also include the userid and password as shown in the previous example.

After the database connection has been established in your application, it will remain until:

• CONNECT RESET is issued to terminate the database connection.

• CONNECT TO is issued to another database.

• DISCONNECT is issued (generally used for Type 2 connection situations).

When your application terminates, always issue a CONNECT RESET before termination to release your connection to the database and explicitly complete any active transactions.

Note that the return code in the Establish_Connection function is an SQLCODE. If the database connection is unsuccessful, the SQLCODE will be a negative number. If the database connection is successful, the SQLCODE will be a value of zero. A positive value for SQLCODE indicates a warning condition. You will learn more about error handling in the following sections.

Error Handling—Using and Interpreting the SQLCA

As with most application development environments, proper error handling is essential for creating and maintaining a quality program. Every SQL statement issued from an application can result in a successful condition or an error condition. The primary means of determining the result of an SQL statement is for the application developer to examine the contents of the SQL Communications Area (SQLCA). The SQLCA is a host language data structure defined by DB2 UDB. It contains data elements that are populated by DB2 UDB during SQL processing.

Your application must declare an SQLCA prior to the issuing of any SQL statements. There are two methods of defining the SQLCA in your program.

• Using an EXEC SQL INCLUDE SQLCA statement

• Declaring a structure called sqlca as defined in the DB2 UDB header files

Table 9.1 details the SQLCA structure.

The SQLCA data structure shown in Table 9.1 is used as the primary means of error handling between the application and DB2 UDB. It is critical that your application checks the contents of the SQLCA following the processing of each SQL statement. Failure to examine the SQLCA contents can cause unexpected errors. For many errors that occur, a corresponding action is usually suggested (and can be coded into your application).

Error Handling—Message Retrieval

The SQLCA data structure is useful in determining whether errors have been encountered in your application. How do you get this information? DB2 UDB provides translated message files that can be referenced using a specific DB2 UDB API called sqlaintp. The prototype for this error message function is as follows:

SQL_API_RC SQL_API_FN
  sqlaintp      (
  char *               pBuffer
  short                BufferSize,
  short                LineWidth,
  struct sqlca *       pSqlca);

The sqlaintp API retrieves the message associated with an error condition specified by the sqlcode field of the SQLCA structure. You must allocate a buffer to store the message string prior to calling sqlaintp.

SQLCODE versus SQLSTATE

Because the SQLCODE is an integer value, it is easy to check for a negative or positive value. However, the SQLCODE for an error condition might not be standard across the entire DB2 family of database products, so it is difficult to correlate it to a specific error. To mitigate this ambiguity, the SQLSTATE field contains a standardized error consistent across the IBM DB2 database products and across SQL92-conforming database managers.

Program Logic for Error Handling

Application modules can contain numerous SQL statements for processing. The application should verify the success of each SQL statement by checking the SQLCA contents. This might require unusual coding techniques, because the program's flow of control may be interrupted by an SQL error. If you want to avoid coding a call to an error-checking routine after each SQL statement, you can use the statement:

EXEC SQL  WHENEVER SQLERROR GO TO label;

This embedded SQL statement only needs to be defined once in your source module, but must appear before the SQL statements you want to affect. Every statement that returns with a negative SQLCODE will cause execution to transfer to the statement immediately following the specified label.

Other variations on the usage of the WHENEVER statement include:

EXEC SQL WHENEVER NOT FOUND GO TO lastrow;
EXEC SQL WHENEVER SQLWARNING CONTINUE;

When processing a multirow result set, you might want to implement a common display routine. This can be accomplished as in the previous example, which checks the not found condition (SQLCODE=100). If positive values are returned in the SQLCODE, you can go to a specific label, or as shown in the example, continue program execution using the CONTINUE keyword.

Indicator Variables

An indicator variable is a special type of host variable that is used to indicate whether a column value is null or not. When an indicator variable is used as input into the database, it should be set by the application before the SQL statement is executed. When the host variable is used as output from the database, the indicator is defined by the application, but it is updated by DB2 UDB and returned. The application should then check the value of the indicator variable after the result has been returned. The following two examples show how an output indicator variable is populated:

SELECT sale_price INTO :hv1 INDICATOR :hvind
    FROM product;
SELECT sale_price INTO :hv1 :hvind FROM product

Both of the queries return the value for the SALE_PRICE column. Two host variables, one called hv1 and the other called hvind, have been defined. The hv1 host variable must represent a compatible host language data type for the SALE_PRICE column.

The second host variable, hvind, indicates the nullability of the value retrieved into the host variable hv1. The indicator host variable is either identified using the keyword INDICATOR, or simply by a blank space as shown in the second SELECT INTO statement. All indicator host variables are defined using the host language data type that corresponds to the DB2 UDB data type SMALLINT.

Null indicators must be checked when an application is retrieving data from a nullable column.

Returning a null value into an application is different from querying the null status of a column within SQL. As an application developer, it is important to note any nullable columns and always use and check the null indicator host variable in the application.

Interpreting an indicator host variable requires that you test its value. If the indicator host variable is negative, then the column contains a null value and the contents of the corresponding host variable are ignored. If the indicator host variable is any nonnegative value, then the input or output host variable contains a nonnull value.

If the application does not provide a null indicator host variable, an SQL error could be returned by DB2 on subsequent processing of the host variable. When nullable columns are referenced during UPDATE or INSERT statements, the column is set to null if the indicator variable is negative and the column is nullable.

Data Retrieval Methods

Many of the SQL statements used in database applications are used to retrieve data from the database. The SELECT statement is used to perform data retrieval, by returning a set of rows. There is no method of determining the number of rows that will be returned from any given SELECT statement.

When coding a SELECT statement in an application, the data must be handled by the application. Therefore, the data is returned into native language host variables. In static SQL programs, methods of retrieving the data include:

• Using the SELECT INTO or VALUES INTO clause

• Using a cursor and the FETCH INTO clause

Single Row Results—SELECT INTO

When the result of an SQL statement is a single row, the application can store the data directly into host variables. The SELECT statement requires the phrase INTO followed by an equal number of host variables for the number of columns being retrieved.

It is important to remember that a single row must be returned (otherwise an error occurs). Specific situations relating to SELECT INTO include:

• If the number of host variables does not match the number of columns specified, a warning flag in the SQLCA is set.

• If the row does not exist, SQLCODE +100 or SQLSTATE 02000 is returned.

• If the result set contains more than one row, an SQLCODE -811 is returned.

A SELECT INTO statement should be used if an equality predicate is specified on a primary key column. This action will always retrieve a single row result.

Single Row Result—VALUES INTO

The structure of a SELECT statement is easy to understand. It has a logical form which reads much like a language: "I want the book from the bookstore in the DB2MALL called DB2 UDB Administration Guide: Performance." This translates into the following SELECT statement:

SELECT product_name
    FROM product
    WHERE product_name = 'DB2' and store = 'dbsrus'

The structure of a VALUES INTO statement is different from the SELECT statement because the information being retrieved is not database objects, but special registers, constants, and host variables. Special registers are used to contain current information about the application that can be referenced in SQL statements. Some DB2 UDB special registers are shown in Table 9.2.

If you want to obtain the value of any of these special registers, you can use the VALUES INTO statement as shown here. You also can use them in a SELECT statement.

VALUES CURRENT DATE INTO :hvdate
VALUES CURRENT SERVER INTO :svrName

The VALUES clause also can be used with constants in the INSERT statement or when constant values need to be included in the result set. For example:

SELECT Product_ID
    FROM product
UNION VALUES ('999')

This query always returns at least one row with the value of 999 for product id, even if the PRODUCT table is empty. As you can see, the VALUES clause is similar to a SELECT statement. It also provides an easy-to-use interface to invoke UDFs:

VALUES (customer_balance (customer_ID,balance))

This statement invokes a UDF called customer_balance and passes the column values of customer_id and balance to the function. The result of the UDF is a single result (scalar value).

Multiple Row Result Sets

When the SELECT statement does not resolve to exactly one row, the application must be written to handle an arbitrary number of rows. A cursor is used to retrieve the results from the SELECT statement. A cursor is associated with an SQL statement using the DECLARE cursor statement in the application. The DECLARE statement defines and names the cursor identifying the set of rows to be retrieved using a SELECT statement. Here is an example:

DECLARE c1 CURSOR FOR
SELECT customer_id, customer_name
FROM customer
WHERE customer_name IN (:hvCusName)

The cursor c1 is declared to retrieve the test candidate ids and first and last names according to an input host variable called hvCusName. Because the predicate (WHERE clause) is not an equality predicate using the customer id (CUSTOMER_ID), it must be assumed that the result of this statement could be 0, 1, or more rows (depending on the value of hvCusName during the OPEN cursor processing).

The following steps are involved in using cursors:

Using Cursors

Cursors are used to process a multirow result set. The processing can involve simple data retrieving using a SELECT statement, or the use of a cursor for a positioned DELETE or UPDATE. As an example, when the DB2 UDB CLP is used, each SQL statement is dynamically prepared and each resulting row is displayed via multiple fetch operations using a cursor.

Generally, cursors within DB2 UDB embedded SQL programs can only be used in one direction. Since DB2 UDB V6.1, it is possible to have forward, backward, and scrollable cursors. However, backward and scrollable cursors are supported only with the DB2 CLI, ODBC, JDBC, SQLJ interfaces.

It is important to remember that the result set of the SELECT statement is usually determined during the processing of the OPEN cursor. Multiple cursors can be open on the same result set and positioned independently. This can be useful to provide backward-like scrolling for embedded SQL programs which do not support backward cursors. If a cursor is reopened, its current position is at the beginning of the result set.

The name of a cursor is only known by the application that declared it. It cannot be a host variable. The cursor name must be unique within the application module.

Cursor Types

A cursor type is determined by the declaration of the cursor, its usage, and the record blocking BIND parameters. There are three categories of cursors:

• Read-Only— The SELECT statement is a read-only SELECT statement (for example, SELECT customer_id FROM customer). There can be performance advantages, especially when the data is retrieved across a network, because it is likely that record blocking will be performed.

• Updatable— The rows can be updated. The FOR UPDATE clause is used during the cursor declaration. Only a single table or view can be referenced in the SELECT statement.

• Ambiguous— The cursor type cannot be determined by its declaration or the SELECT statement being used. The amount of record blocking for these types of cursors is determined by the BIND parameter.

If the cursor is going to be used in an UPDATE WHERE CURRENT OF statement, specify the FOR UPDATE clause during the cursor declaration. The optimizer will pick the best possible access path. Also, if you know that the cursor is only used to retrieve data, add the clause FOR READ ONLY or FOR FETCH ONLY to encourage record blocking and avoid extra locks on the result set.

The phrases FOR UPDATE ONLY, FOR FETCH ONLY, and FOR READ ONLY are actually part of the SQL statement and not part of the DECLARE cursor syntax. Let's look at an example cursor declaration and usage.

In the following example, all of the host variables are initialized in the BEGIN DECLARE section. The cursor is then defined within the SELECT statement as a read-only cursor. The phrase FOR FETCH ONLY is specified at the end of the SQL statement.

We could have specified the WITH HOLD option to maintain the cursor position across transactions. Because we did not specify this option, the cursor will no longer be accessible following a COMMIT statement. Cursors specified as WITH HOLD are useful when a large number of rows need to be examined and modified. However, the application cannot afford to lock all of the rows over the entire UOW. At commit, a cursor with hold has all locks released except those protecting the current cursor position.

EXEC SQL BEGIN DECLARE SECTION;
  long iCustomer_id;
  char szCName[30];
  char szPhone[12];
  char szStreet[50];
  char szCity[15];
  char szProvState[2];
  char szCode[7];
  char szCountry[15];
  char szCitySearch2[21];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE c1 CURSOR FOR
       SELECT
                CUSTOMER_ID,CUSTOMER_NAME,ADDR_STREET,
                ADDR_CITY,ADDR_STATE,ADDR_ZIP,
                ADDR_COUNTRY,CUSTOMER_PHONE
       FROM
                CUSTOMER
       WHERE
                CHAR(city) LIKE :szCitySearch2
       FOR FETCH ONLY;
EXEC SQL OPEN c1;
     EXEC SQL FETCH c1 INTO :icustomer_id,
                :szCName,
                :szStreet,
                :szCity,
                :szProvState,
                :szCountry,
                :szCode,
                :szPhone;
     if (SQLCODE == SQL_RC_OK) {
        printf ("Customer ID	:%s
",iCustomer_id);
        printf ("Customer Name	:%s
",szCName);
        printf ("City		:%s
",szCity);
     }
EXEC SQL CLOSE c1;
EXEC SQL COMMIT;

Let's examine the cursor specific phrases in the SELECT statement. The FOR UPDATE clause shows that it is possible to specify the column name to update.

EXEC SQL DECLARE c1 CURSOR FOR
   SELECT
          customer_id,customer_name,addr_street,
          addr_city,addr_state,addr_zip,addr_country,
          customer_phone,date_entered
   FROM
          customer
   WHERE
          CHAR(city) LIKE :szCitySearch2
   FOR UPDATE OF date_entered;

If no update or delete on a row based on the cursor position is to be done, then add the phrase FOR FETCH ONLY or FOR READ ONLY to the end of the statement instead.

Cursor Positioning

A cursor, regardless of type, can have three positions depending on its current state.

• An open cursor prior to the first fetch is positioned before the first record of the result set and the contents of the output host variables are undefined at this point.

• Following a fetch, the cursor is considered on record. The output host variables contain the values of the current row. This is the row that will be changed if a positioned UPDATE or DELETE is performed at this time.

• The third possible cursor position is following the last row. At this point, the host variables contain the values of the current (last) row, and the SQLCA contains the value of (SQLCODE +100, SQLSTATE '02000').

When a cursor is declared as a WITH HOLD cursor it will maintain its position since the last fetch operation even after the transaction has been committed. This allows you to commit changes for other applications to read without losing your current cursor position. Here is how to declare a cursor WITH HOLD:

DECLARE <cursor-name> CURSOR WITH HOLD

If the UOW is completed using the ROLLBACK statement, all of the open cursors, including any WITH HOLD cursors, are closed and the resources are released. If the UOW ends in a COMMIT, the WITH HOLD cursors remain open. If a close is issued before a COMMIT, the cursor is no longer accessible. CLOSE does not cause a COMMIT or ROLLBACK.

Advanced Cursor Usage

Scrolling in a single direction through data is the main way that cursors are used. Although DB2 supports scrollable result sets and cursors in API-based interfaces such as CLI/ODBC and JDBC, achieving the same result in embedded SQL involves using multiple cursors and/or keeping a copy of the retrieved data in application memory. Using multiple cursors allows the end-user to reposition the cursors within the data being retrieved. Let's say that an application examines a result set with two rows. The application examines the first record and then the second, followed by a modification of the first record. With a single nonscrollable cursor, this would be difficult because the cursor would have to be reopened to position the cursor to record one, and record one may have been already modified by another application.

This type of problem can be solved in embedded SQL using two cursors declared for the same result set. The second cursor is always one record behind the first cursor. By using this two-cursor technique, the end-user could update the previous record without closing the first cursor.

Keeping a copy of the data in application memory does not guarantee that the application is displaying the current data values. However, it does provide the embedded SQL application developer with the most flexibility to scroll through data. Usually, this technique involves a buffer (storage area) within the application. The end-user is allowed to examine all of the contents of this buffer. When the end-user wants to change the contents of the buffer, an update is attempted. There is no guarantee that the update will be successful. To ensure that the contents will not change, either explicitly lock the table using the LOCK TABLE statement or use an appropriate isolation level. The isolation level should use appropriate locking semantics, such as RR.

Application-Level Locking

Locking semantics are usually specified by choosing the isolation level for the application during the bind process. The isolation level affects the number of locks and period of time they are held.

The LOCK TABLE statement can be used to enforce a table-level lock in either exclusive (X) or share (S) mode. As with all types of database locking, the larger the object being locked, the less concurrency available.

Obtaining an explicit table lock can improve performance for batch applications that involve changes to a large percentage of the table. If a table lock is acquired, row locks on the table will not be acquired.

All locks use approximately the same amount of storage in the locklist. Remember that the locklist is shared by all applications accessing the database. An explicit table lock will avoid filling the locklist and defer lock escalation for other applications.

Searched Updates/Deletes

We have discussed positioned UPDATE and DELETE statements using cursors in the previous section. If a row or set of rows can be explicitly identified, then a searched UPDATE or DELETE can be used instead of cursors. Adding an appropriate WHERE clause to the end of an UPDATE or DELETE statement with proper input and/or output host variables can provide enough flexibility for the end-user.

An UPDATE statement requires the application developer to specify the columns and their corresponding values to be updated. A static embedded SQL statement cannot use host variables to represent column names in an update or select list. Therefore, to avoid having to prepare any single column UPDATE statement dynamically, use an UPDATE statement in which every column is updated. The previously retrieved values are used for the update. The updated column has a new user-input value. This is not the most efficient method of updating records in DB2 UDB, but it is a valid method. The following section discusses how to modify this part of the application using dynamic embedded SQL.