Writing static SQL applications

For most DB2 users, static SQL provides a straightforward, efficient path to DB2 data. This section provides an overview of static SQL and describes the basic coding techniques for coding static SQL applications.

Overview of static SQL

The source form of a static SQL statement is embedded within an application program that is written in a traditional programming language such as C. The statement is prepared before the program is executed, and the operational form of the statement persists beyond the execution of the program. You can use static SQL when you know before run time what SQL statements your application needs to run.

When you use static SQL, you cannot change the form of SQL statements unless you make changes to the program. However, you can increase the flexibility of those statements by using host variables. “Using host variables” on page 187 has information about coding host variables.

Example: Assume that you are coding static SQL in a COBOL program. The following UPDATE statement can update the salary of any employee. When you write your program, you know that salaries must be updated, but you do not know until run time whose salaries should be updated, and by how much.

01    IOAREA.
      02  EMPID                    PIC X(06).
      02  NEW-SALARY               PIC S9(7)V9(2) COMP-3.
.
.
.
  (Other declarations)
READ CARDIN RECORD INTO IOAREA
  AT END MOVE 'N' TO INPUT-SWITCH.
.
.
.
  (Other COBOL statements)
EXEC SQL
 UPDATE EMP
    SET SALARY = :NEW-SALARY
    WHERE EMPNO = :EMPID
END-EXEC.

The UPDATE statement does not change, nor does its basic structure, but the input can change the results of the UPDATE statement.

Static SQL programming concepts

This section explains basic SQL coding concepts. These concepts apply to traditional programming languages: C, C++, COBOL, Fortran, PL/I, and Assembler.

Suppose that you are writing an application program to access data in a DB2 database. When your program executes an SQL statement, the program needs to communicate with DB2. When DB2 finishes processing an SQL statement, DB2 sends back a return code, called the SQL return code. Your program should test the return code to examine the results of the operation.

Unique instructions and details apply to each language. This section provides an overview of the programming concepts that apply to each language:

• “Declaring table and view definitions”

• “Accessing data with host variables, variable arrays, and structures”

• “Retrieving a set of rows” on page 190

• “Checking the execution of SQL statements” on page 193

Declaring table and view definitions

Before your program issues SQL statements that retrieve, update, delete, or insert data, you should declare the tables and views that your program accesses. Declaring tables or views is not required; however, declaring them offers advantages such as documenting application programs and providing the precompiler with information that is used to check embedded SQL statements. To declare a table or view, include an SQL DECLARE statement in your program.

Example: The DECLARE TABLE statement for the DEPT table looks like this:

EXEC SQL
  DECLARE DEPT TABLE
   (DEPTNO      CHAR(3)            NOT NULL,
    DEPTNAME    VARCHAR(36)        NOT NULL,
    MGRNO       CHAR(6)                    ,
    ADMRDEPT    CHAR(3)            NOT NULL,
END-EXEC.

For each traditional language, you delimit an SQL statement in your program between EXEC SQL and a statement terminator. In the preceding example, the EXEC SQL and END-EXEC delimit the SQL statement in a COBOL program.

As an alternative to coding the DECLARE statement yourself, you can use the DB2 subcomponent DCLGEN, the declarations generator.

Accessing data with host variables, variable arrays, and structures

You can use host variables, host variable arrays, and host structures in your application program to exchange data between the application and the DBMS.

Using host variables

A host variable is a data item that you declare in a program for use within an SQL statement. You can:

• Retrieve data into the host variable for your application program's use

• Place data into the host variable to insert into a table or to change the contents of a row

• Use the data in the host variable when evaluating a WHERE or HAVING clause

• Assign the value in the host variable to a special register. A special register is a storage area that DB2 defines for a process to hold information that SQL statements can reference.

  Example: The CURRENT SQLID special register contains the SQL authorization ID of a process, which is set in an SQL statement. DB2 replaces the register name with the value of the authorization ID when the SQL statement runs.

• Use the host variable to indicate a null value

How you code a host variable varies according to the programming language you use. Some languages require a separate declaration section for SQL variables. In this case, you can code the BEGIN and END DECLARE SECTION statements in an application program wherever variable declarations can appear according to the rules of the host language. A host variable declaration section starts with the BEGIN DECLARE SECTION statement and ends with the END DECLARE SECTION statement.

The INTO clause of the SELECT statement names one or more host variables to contain the returned column values. For host variables and variable arrays, the named variables correspond one-to-one with the list of column names in the SELECT list.

The example that follows uses a host variable to retrieve a single row of data.

Example: Suppose that you want to retrieve the EMPNO, LASTNAME, and DEPT column values from a single row in the EMP table. You can define a host variable in your program to hold each column. The host variable consists of the local variable name, preceded by a colon. You then can name the data areas with an INTO clause, as shown:

EXEC SQL
 SELECT EMPNO, LASTNAME, DEPT
  INTO :CBLEMPNO, :CBLNAME, :CBLDEPT
  FROM EMP
  WHERE EMPNO = :EMPID
END-EXEC.

You must declare the host variables :CBLEMPNO, :CBLNAME, and :CBLDEPT in the data declaration portion of the program. The data types of the host variables must be compatible with the SQL data types of the columns EMPNO, LASTNAME, and DEPT of the EMP table.

Suppose that you don't know how many rows DB2 will return or that you expect more than one row to return. In either case, you must use an alternative to the SELECT ... INTO statement. Using a DB2 cursor, an application can process a set of rows and retrieve rows from the result table. “Retrieving a set of rows” on page 190 has information about using cursors.

Using host variable arrays

A host variable array is a data array that is declared in a host language for use within an SQL statement. You can retrieve data into host variable arrays for your application program's use and place data into host variable arrays to insert rows into a table.

You can specify host variable arrays in C, C++, COBOL, or PL/I. Each host variable array contains values for a column, and each element of the array corresponds to a value for a column. You must declare the array in the host program before you use it.

Example: The following statement uses the main host variable array, COL1, and the corresponding indicator array, COL1IND. Assume that COL1 has 10 elements. The first element in the array corresponds to the first value, and so on. COL1IND must have at least 10 entries.

EXEC SQL
 SQL FETCH FIRST ROWSET FROM C1 FOR 5 ROWS
  INTO :COL1 :COL1IND
END-EXEC.

Using host structures

A host structure is a group of host variables that an SQL statement can refer to by using a single name. When the host language environment allows it, you can use host language statements to define the host structures.

Example: Assume that your COBOL program includes the following SQL statement:

EXEC SQL
 SELECT EMPNO, FIRSTNME, LASTNAME, DEPT
  INTO :EMPNO, :FIRSTNME, :LASTNAME, :WORKDEPT
  FROM VEMP
  WHERE EMPNO = :EMPID
END-EXEC.

Now assume you want to avoid listing the host variables in the preceding example.

Example: You can substitute the name of a structure, such as :PEMP, that contains :EMPNO, :FIRSTNME, :LASTNAME, and :DEPT:

EXEC SQL
 SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT
  INTO :PEMP
  FROM VEMP
  WHERE EMPNO = :EMPID
END-EXEC.

You can declare a host structure in your program. You can also use DCLGEN to generate a COBOL record description, PL/I structure declaration, or C structure declaration that corresponds to the columns of a table.

Retrieving a set of rows

DB2 has a mechanism called a cursor. Using a cursor is like keeping your finger on a particular line of text on a printed page. In DB2, an application program uses a cursor to point to one or more rows in a set of rows that are retrieved from a table. You can also use a cursor to retrieve rows from a result set that is returned by a stored procedure. This section explains how your application program can use a cursor to retrieve rows from a table.

Overview of cursors

You can retrieve and process a set of rows that satisfy the search conditions of an SQL statement. When you use a program to select the rows, the program processes one or more rows at a time.

The SELECT statement that this section refers to must be within a DECLARE CURSOR statement and cannot include an INTO clause. The DECLARE CURSOR statement defines and names the cursor, identifying the set of rows to retrieve with the SELECT statement of the cursor. This set of rows is referred to as the result table.

After the DECLARE CURSOR statement executes, you process the result table of a cursor as follows:

Recommendation: Explicitly close the cursor when you finish using it.

Your program can have several cursors. Each cursor requires its own:

• DECLARE CURSOR statement to define the cursor

• OPEN and CLOSE statements to open and close the cursor

• FETCH statement to retrieve rows from the cursor's result table

You must declare host variables before you refer to them in a DECLARE CURSOR statement. To define and identify a set of rows that are to be accessed with a cursor, issue a DECLARE CURSOR statement. The DECLARE CURSOR statement names a cursor and specifies a SELECT statement. The SELECT statement defines the criteria for the rows that will make up the result table.

You can use cursors to fetch, update, or delete one or more rows of a table, but you cannot use them to insert a row into a table.

Examples of using cursors

Suppose that your program examines data about people in department D11 and keeps the data in the EMP table. The following examples show the SQL statements that you must include in a COBOL program to define and use a cursor. In these examples, the program uses the cursor to process a set of rows from the EMP table.

Example: Define the cursor: The following statement defines a cursor named THISEMP:

EXEC SQL
 DECLARE THISEMP CURSOR FOR
  SELECT EMPNO, LASTNAME,
  DEPT, JOB
  FROM EMP
  WHERE DEPT = 'D11'
 FOR UPDATE OF JOB
END-EXEC.

Example: Open the cursor: The following statement opens the cursor:

EXEC SQL
 OPEN THISEMP
END-EXEC.

Example: Use the cursor to retrieve a row: The following statement uses the cursor, THISEMP, to retrieve a row:

EXEC SQL
 FETCH THISEMP
  INTO :EMP-NUM, :NAME2,
  :DEPT, :JOB-NAME
END-EXEC.

Example: Update the current row using the cursor: The following statement uses the cursor, THISEMP, to update the JOB value for specific employees in department D11:

EXEC SQL
 UPDATE EMP
 SET JOB = :NEW-JOB
 WHERE CURRENT OF THISEMP
END-EXEC.

Example: Close the cursor: The following statement closes the cursor:

EXEC SQL
 CLOSE THISEMP
END-EXEC.

A scrollable cursor is a type of cursor that can nonsequentially access a result table, can scroll forward and backward, and can be repositioned at the beginning. Applications can use a powerful set of SQL statements to fetch data by using a cursor in random order. Scrollable cursors are especially useful for screen-based applications. You can specify that the data in the result table is to remain static. For example, an accounting application can require that data is to remain constant, whereas an airline reservation system application must display the latest flight availability information.

You can also define options on the DECLARE CURSOR statement that specify how sensitive a scrollable cursor is to changes in the underlying data when inserts, updates, or deletes occur.

• A sensitive cursor is sensitive to changes that are made to the database after the result table has been materialized. For example, when an application executes positioned UPDATE and DELETE statements with the cursor, those changes are visible in the result table.

• An insensitive cursor is not sensitive to inserts, updates, or deletes that are made to the underlying rows of a result table after the result table has been materialized. For example, the order of the rows and the values for each row of the result table do not change after the application opens the cursor.

To indicate that a cursor is scrollable, you declare it with the SCROLL keyword.

Example: The following example shows a declaration for an insensitive scrollable cursor:

EXEC SQL DECLARE C1 INSENSITIVE SCROLL CURSOR FOR
 SELECT DEPTNO, DEPTNAME, MGRNO
 FROM DEPT
 ORDER BY DEPTNO
END-EXEC.

To use this cursor to fetch the fifth row of the result table, you can use a FETCH statement like this:

EXEC SQL FETCH ABSOLUTE +5 C1 INTO :HVDEPTNO, :DEPTNAME, :MGRNO;

In Version 8, DB2 UDB for z/OS provides another type of cursor called a dynamic scrollable cursor. With a dynamic scrollable cursor, applications can scroll directly on a base table while accessing the most current data.

Checking the execution of SQL statements

A program that includes SQL statements can have an area that is set apart for communication with DB2—an SQL communication area (SQLCA). When DB2 processes an SQL statement in your program, it places return codes in the SQLSTATE and SQLCODE host variables or in corresponding fields of the SQLCA. The return codes indicate whether the statement executed successfully or failed.

Recommendation: Because the SQLCA is a valuable problem-diagnosis tool, include the necessary instructions to display some of the information that is in the SQLCA in your application programs.

If you prefer not to check SQLCA fields after each SQL statement runs, you have two alternatives: you can use a GET DIAGNOSTICS statement or a WHENEVER statement in your program.

• The GET DIAGNOSTICS statement returns diagnostic information about the last SQL statement that was executed. You can request specific types of diagnostic information or all available diagnostic information about a statement. For example, the GET DIAGNOSTICS statement returns the number of rows that are affected by a data insert, update, or delete.

• The WHENEVER statement allows you to specify what to do if a general condition is true. DB2 checks the SQLCA and continues processing your program. If an error, exception, or warning results when an SQL statement is executed, DB2 branches to another area in your program. The program can then examine the SQLSTATE or SQLCODE to react specifically to the error or exception.