Developing SQL Applications

The following topics describe the programming features available for SQL applications in general.

• Embedded SQL

  Instructions on how to embed SQL statements into your programs.

• Host Variables

  The purpose of host variables, how to declare them, and how to use them in your SQL applications.

• Cursors

  The purpose of cursors, how to declare them, and how to use them in SQL applications.

• Data Structures

  The purpose and use of the SQLCA and SQLDA data structures available for SQL applications.

• Dynamic SQL

  An explanation of how dynamic SQL works, and a description of its purpose, advantages, and use.

Embedded SQL

The CitOESQL preprocessor works by taking the SQL statements that you have embedded in your COBOL program and converting them to the appropriate function calls to the database.

Keywords:

In your COBOL program, each embedded SQL statement must be preceded by the introductory keywords:

EXEC SQL

and followed by the keyword:

END-EXEC

For example:

EXEC SQL
    SELECT au_lname INTO :lastname FROM authors
    WHERE au_id = '124-59-3864'
END-EXEC

The embedded SQL statement can be broken over as many lines as necessary following the normal COBOL rules for continuation, but between the EXEC SQL and END-EXEC keywords you can only code an embedded SQL statement; you cannot include any ordinary COBOL code.

The case of embedded SQL keywords in your programs is ignored. You can use all upper-case, all lower-case, or a combination of the two. For example, the following are all equivalent:

EXEC SQL CONNECT 
exec sql connect
Exec Sql Connect

Cursor names, statement names, and connection names:

The case of cursor names, statement names and connection names must match that used when the variable is declared. For example, if you declare a cursor as C1, you must always refer to it as C1 (and not as c1).

The settings for the database determines whether such things as connection names, table and column names, are case-sensitive.

SQL identifiers:

Hyphens are not permitted in SQL identifiers such as table and column names.

SQL identifiers are typically restricted regarding which characters they support. Typically, unquoted identifiers can only contain A-Z, 0-9 and underscore. Some databases might also allow lower-case characters, and/or @ and # symbols. If your SQL identifiers contain any other characters, such as a grave accent, spaces, or DBCS characters, they must be delimited. Refer to your database vendor documentation for more information, including the character to use as the delimiter.

SQL statements:

Most vendors provide SQL Reference documentation with their database software that includes full information about embedded SQL statements. Regardless of the database software, you should, for example, be able to perform the following typical operations using the statements shown:

A full syntax description is given for each of the supported embedded SQL statements, together with an example of its use, in the topics under Embedded SQL.

Host Variables

Host variables are data items defined within a COBOL program. They are used to pass values to and receive values from a database. Host variables can be defined in the File Section, Working-Storage Section, Local-Storage Section or Linkage Section of your COBOL program and can be coded using any level number between 1 and 48.

A host variable can be input or output:

• Input host variables - to specify data to be transferred from the COBOL program to the database • Output host variables
• to hold data to be returned to the COBOL program from the database

To use host variables, you must declare them in your program and then reference them in your SQL statements.

A host variable can be defined as any of the following types:

• Simple Host Variables

  To store and retrieve a single string of data.

• Host Arrays

  To store and retrieve multiple rows of data.

• Indicator Variables

  A companion variable that stores null value and data truncation information.

• Indicator Arrays

  A companion array used to store null value and data truncation information for multiple rows.

Simple Host Variables

Before you can use a host variable in an embedded SQL statement, you must declare it.

Declaring simple host variables

Generally, host variable declarations are coded as data items bracketed by the embedded SQL statements BEGIN DECLARE SECTION and END DECLARE SECTION. The following rules also apply:

• You can use groups of data items as a single host variable. However, a group item cannot be used in a WHERE clause.
• CitOESQL trims trailing spaces from character host variables. If the variable consists entirely of spaces, CitOESQL does not trim the first space character because some servers treat a zero-length string as NULL.
• With CitOESQL, you can use COBOL data items as host variables even if they have not been declared using BEGIN DECLARE SECTION and END DECLARE SECTION.
• Host variable names must conform to the COBOL rules for data items.
• Host variables can be declared anywhere that it is legal to declare COBOL data items.

Referencing simple host variables

You reference host variables from embedded SQL statements. When you code a host variable name into an embedded SQL statement, it must be preceded by a colon (:) to enable the compiler to distinguish between the host variable and tables or columns with the same name.

EXAMPLE:

EXEC SQL
    BEGIN DECLARE SECTION
END-EXEC
01 id           pic x(4).
01 name         pic x(30).
01 book-title   pic x(40).
01 book-id      pic x(5).
EXEC SQL
    END DECLARE SECTION
END-EXEC
. . .
    display "Type your identification number: "
    accept id.
* The following statement retrieves the name of the
* employee whose ID is the same as the contents of
* the host variable "id". The name is returned in
* the host variable "name".
    EXEC SQL
        SELECT emp_name INTO :name FROM employees
        WHERE emp_id=:id
    END-EXEC
    display "Hello " name.
* In the following statement, :book-id is an input
* host variable that contains the ID of the book to
* search for, while :book-title is an output host
* variable that returns the result of the search.
    EXEC SQL
        SELECT title INTO :book-title FROM titles
        WHERE title_id=:book-id
    END-EXEC

Host Arrays

An array is a collection of data items associated with a single variable name. You can define an array of host variables (called host arrays) and operate on them with a single SQL statement.

You can use host arrays as input variables in INSERT, UPDATE and DELETE statements and as output variables in the INTO clause of SELECT and FETCH statements. This means that you can use arrays with SELECT, FETCH, DELETE, INSERT and UPDATE statements to manipulate large volumes of data.

Some of the benefits to using host arrays include:

• You can perform multiple CALL, EXECUTE, INSERT or UPDATE operations by executing only one SQL statement, which can significantly improve performance, especially when the application and the database are on different systems.
• You can fetch data in batches, which can be useful when creating a scrolling list of information.

As with simple host variables, you must declare host arrays in your program and then reference them in your SQL statements.

Declaring host arrays

Host arrays are declared in much the same way as simple host variables using BEGIN DECLARE SECTION and END DECLARE SECTION. With host arrays, however, you must use the OCCURS clause to dimension the array.

Referencing host arrays

The following rules apply to coding host arrays into embedded SQL statements:

• Just as with simple host variables, you must precede a host array name with a colon (;).
• If the number of rows available is more than the number of rows defined in an array, a SELECT statement returns the number of rows defined in the array, and an SQLCODE message is issued to indicate that the additional rows could not be returned.
• Use a SELECT statement only when you know the maximum number of rows to be selected. When the number of rows to be returned is unknown, use the FETCH statement.
• If you use multiple host arrays in a single SQL statement, their dimensions must be the same.
• CitOESQL does not support the mixing of host arrays and simple host variables within a single SQL statement. They must be all simple or all arrays.
• For CitOESQL, you must define all host variables within a host array with the same number of occurrences. If one variable has 25 occurrences, all variables in that host array must have 25 occurrences.

• Optionally, use the FOR clause to limit the number of array elements processed to just those that you want. This is especially useful in UPDATE, INSERT and DELETE statements where you may not want to use the entire array. The following rules apply:

  • If the value of the FOR clause variable is less than or equal to zero, no rows are processed.
  • The number of array elements processed is determined by comparing the dimension of the host array with the FOR clause variable. The lesser value is used.

EXAMPLES:

The following example shows typical host array declarations and references.

EXEC SQL
    BEGIN DECLARE SECTION
END-EXEC
01 AUTH-REC-TABLES
    05 Auth-id OCCURS 25 TIMES PIC X(12).
    05 Auth-Lname OCCURS 25 TIMES PIC X(40).
EXEC SQL
    END DECLARE SECTION
END-EXEC.
. . .

    EXEC SQL
        CONNECT USERID 'user' IDENTIFIED BY 'pwd'
            USING 'db_alias'
END-EXEC
EXEC SQL
    SELECT au-id, au-lname
        INTO :Auth-id, :Auth-Lname FROM authors
END-EXEC
display sqlerrd(3)

The following example demonstrates the use of the FOR clause, showing 10 rows (the value of :maxitems) modified by the UPDATE statement:

EXEC SQL
    BEGIN DECLARE SECTION
END-EXEC

01 AUTH-REC-TABLES
    05 Auth-id OCCURS 25 TIMES PIC X(12).
    05 Auth-Lname OCCURS 25 TIMES PIC X(40).
01 maxitems PIC S9(4) COMP-5 VALUE 10.
EXEC SQL
    END DECLARE SECTION
END-EXEC.
. . .
    EXEC SQL
        CONNECT USERID 'user' IDENTIFIED BY 'pwd'
            USING 'db_alias'
    END-EXEC
    EXEC SQL
        FOR :maxitems
        UPDATE authors
            SET au_lname = :Auth_Lname
            WHERE au_id = :Auth_id
    END-EXEC
    display sqlerrd(3)

Indicator Variables

Use indicator variables to:

• Assign null values
• Detect null values
• Detect data truncation

Unlike COBOL, SQL supports variables that can contain null values. A null value means that no entry has been made and usually implies that the value is either unknown or undefined. A null value enables you to distinguish between a deliberate entry of zero (for numerical columns) or a blank (for character columns) and an unknown or inapplicable entry. For example, a null value in a price column does not mean that the item is being given away free, it means that the price is not known or has not been set.

Indicator variables serve an additional purpose if truncation occurs when data is retrieved from a database into a host variable. If the host variable is not large enough to hold the data returned from the database, the warning flag sqlwarn1 in the SQLCA data structure is set and the indicator variable is set to the size of the data contained in the database.

Declaring indicator variables

Indicator variables are always defined as:

pic S9(4) comp-5.

Referencing indicator variables

Together, a host variable and its companion indicator variable specify a single SQL value. The following applies to coding a host variable with a companion indicator variable:

• Both variables must be preceded by a colon (:).
• Place an indicator variable immediately after its corresponding host variable.

• Reference the host variable and indicator variable in a FETCH INTO or SELECT ...INTO statement with or without an INDICATOR clause as follows:

  :hostvar:indicvar

  or

  :hostvar INDICATOR :indicvar

You cannot use indicator variables in a search condition. To search for null values, use the is null construct instead.

EXAMPLES:

This example demonstrates the declaration of an indiator variable that is used in a FETCH ...INTO statement.

EXEC SQL
        BEGIN DECLARE SECTION
    END-EXEC
    01 host-var pic x(4).
    01 indicator-var pic S9(4) comp-5.
    EXEC SQL
        END DECLARE SECTION
    END-EXEC
. . .

    EXEC SQL
        FETCH myCursor INTO :host-var:indicator-var
    END-EXEC

The following shows an embedded UPDATE statement that uses a saleprice host variable with a companion indicator variable, saleprice-null:

EXEC SQL
    UPDATE closeoutsale
        SET temp_price = :saleprice:saleprice-null,
            listprice = :oldprice
END-EXEC

In this example, if saleprice-null has a value of -1, when the UPDATE statement executes, the statement is read as:

EXEC SQL
    UPDATE closeoutsale
        SET temp_price = null, listprice = :oldprice
END-EXEC

This example demonstrates the use of the is null construct to do a search:

if saleprice-null equal -1
    EXEC SQL
        DELETE FROM closeoutsale
            WHERE temp_price is null
    END-EXEC
else
    EXEC SQL
        DELETE FROM closeoutsale
            WHERE temp_price = :saleprice
    END-EXEC
end-if

Indicator Arrays

Just as an indicator variable is used as a companion to a host variable, use an indicator array as a companion to a host array to indicate the null status of each returned row or to store data truncation warning flags.

EXAMPLES:

In this example, an indicator array is set to -1 so that it can be used to insert null values into a column:

01 ix PIC 99 COMP-5.
. . .

    EXEC SQL
        BEGIN DECLARE SECTION
END-EXEC
01 sales-id     OCCURS 25 TIMES PIC X(12).
01 sales-name   OCCURS 25 TIMES PIC X(40).
01 sales-comm   OCCURS 25 TIMES PIC S9(9) COMP-5.
01 ind-comm     OCCURS 25 TIMES PIC S9(4) COMP-5.
EXEC SQL
    END DECLARE SECTION
END-EXEC.
. . .
    PERFORM VARYING iX FROM 1 BY 1 UNTIL ix > 25
        MOVE -1 TO ind-comm (ix)
    END-PERFORM.
. . .
    EXEC SQL
        INSERT INTO SALES (ID, NAME, COMM)
            VALUES (:sales_id, :sales_name, :sales_comm:ind-comm)
    END-EXEC

COBOL to SQL Data Type Mapping

SQL has a standard set of data types, but the exact implementation of these varies between databases, and many databases do not implement the full set.

Within a program, COBOL host variable declarations can serve both as COBOL host variables and as SQL database variables. To make this possible, the preprocessor converts COBOL data types to their equivalent SQL data types. We sometimes refer to this conversion process as mapping COBOL data types to SQL data types. The preprocessor looks for specific COBOL picture clause formats that identify those that require mapping to SQL data types. For mapping to be successful, you must declare your COBOL host variables using these specific COBOL picture clauses.

We provide SQL data types for the CitOESQL preprocessor. For complete information on each SQL data type and its required COBOL host variable formats, see the SQL Data Types and ODBC SQL/COBOL Data Type Mappings Reference topics.

SQL TYPEs

Manipulating SQL data that involves date, time, or binary data can be complicated using traditional COBOL host variables, and traditional techniques for handling variable-length character data can also be problematic. To simplify working with this data, we provide the SQL TYPE declaration to make it easier to specify host variables that more closely reflect the natural data types of relational data stores. This allows more applications to be built using static rather than dynamic SQL syntax and can also help to optimize code execution.

EXAMPLE:

Defining date, time, and timestamp fields as SQL TYPEs.

This example program shows date, time and timestamp escape sequences being used, and how to redefine them as SQL TYPEs. It applies to CitOESQL:

working-storage section.

EXEC SQL INCLUDE SQLCA END-EXEC
01 date-field1      pic x(29).
01 date-field2      pic x(29).
01 date-field3      pic x(29).

procedure division.
    EXEC SQL
        CONNECT TO 'Net Express 4.0 Sample 1' USER 'admin'
    END-EXEC
* If the Table is there drop it.
    EXEC SQL
        DROP TABLE DT
    END-EXEC

* Create a table with columns for DATE, TIME, and DATE/TIME
* NOTE: Access uses DATETIME column for all three.
*       Some databases will have dedicated column types.
* If you are creating DATE/TIME columns on another data
* source, refer to your database documentation to see how to * define the columns.

    EXEC SQL
        CREATE TABLE DT ( id INT,
                myDate DATE NULL,
                myTime TIME NULL,
                myTimestamp TIMESTAMP NULL)
    END-EXEC

* INSERT into the table using the ODBC Escape sequences

    EXEC SQL
        INSERT into DT values (1 ,
            {d '1961-10-08'}, *> Set just the date part
            {t '12:21:54' }, *> Set just the time part
            {ts '1966-01-24 08:21:56' } *> Set both parts
                              )
    END-EXEC

* Retrieve the values we just inserted

    EXEC SQL
        SELECT   myDate
                ,myTime
                ,myTimestamp
        INTO     :date-field1
                ,:date-field2
                ,:date-field3
        FROM DT
        where id = 1
    END-EXEC

* Display the results.

    display 'where the date part has been set :'
            date-field1
    display 'where the time part has been set :'
            date-field2
    display 'NOTE, most data sources will set a default '
            'for the date part '
    display 'where both parts has been set :'
            date-field3

* Remove the table.

    EXEC SQL
        DROP TABLE DT
    END-EXEC

* Disconnect from the data source

    EXEC SQL
        DISCONNECT CURRENT
    END-EXEC

    stop run.

Alternatively, you can use host variables defined with SQL TYPEs for date/time variables. Define the following host variables:

01 my-id        pic s9(08) COMP-5.

01 my-date      sql type is date.

01 my-time      sql type is time.

01 my-timestamp sql type is timestamp.

and replace the INSERT statement with the following code:

*> INSERT into the table using SQL TYPE HOST VARS
    move 1                      to MY-ID
    move "1961-10-08"           to MY-DATE
    move "12:21:54"             to MY-TIME
    move "1966-01-24 08:21:56"  to MY-TIMESTAMP

    EXEC SQL
        INSERT into DT value (
        :MY-ID
        ,:MY-DATE
        ,:MY-TIME
        ,:MY-TIMESTAMP )
    END-EXEC

Cursors

When you write code in which the results set returned by a SELECT statement includes more than one row of data, you must declare and use a cursor. A cursor indicates the current position in a results set, in the same way that the cursor on a screen indicates the current position.

A cursor enables you to:

• Fetch rows of data one at a time
• Perform updates and deletions at a specified position within a results set.

The example below demonstrates the following sequence of events:

1. The DECLARE CURSOR statement associates the SELECT statement with the cursor Cursor1.

2. The OPEN statement opens the cursor, thereby executing the SELECT statement.

3. The FETCH statement retrieves the data for the current row from the columns au_fname and au_lname and places the data in the host variables first_name and last_name.

4. The program loops on the FETCH statement until no more data is available.

5. The CLOSE statement closes the cursor.

EXEC SQL DECLARE Cursor1 CURSOR FOR
    SELECT au_fname, au_lname FROM authors
END-EXEC
. . .

    EXEC SQL
        OPEN Cursor1
    END-EXEC
     . . .

    perform until sqlcode not = zero
        EXEC SQL
            FETCH Cursor1 INTO :first_name,:last_name
        END-EXEC
        display first_name, last_name
    end-perform
. . .

    EXEC SQL
        CLOSE Cursor1
    END-EXEC

Declaring a Cursor

Before a cursor can be used, it must be declared. This is done using the DECLARE CURSOR statement in which you specify a name for the cursor and either a SELECT statement or the name of a prepared SQL statement.

Cursor names must conform to the rules for identifiers on the database that you are connecting to, for example, some databases do not allow hyphens in cursor names.

EXEC SQL
    DECLARE Cur1 CURSOR FOR
     SELECT first_name FROM employee
      WHERE last_name = :last-name
END-EXEC

EXEC SQL
    DECLARE Cur2 CURSOR FOR stmt1
 END-EXEC
     . . .
    move "SELECT first_name FROM emp " &
        "WHERE last_name=?" to prep.
    EXEC SQL
        PREPARE stmt1 FROM :prep
    END-EXEC
     . . .
    EXEC SQL
        OPEN Cur2 USING :last-name
    END-EXEC

In this example, the DECLARE CURSOR statement references a prepared statement (stmt1). A prepared SELECT statement can contain question marks (?) which act as parameter markers to indicate that data is to be supplied when the cursor is opened. The cursor must be declared before the statement is prepared.

Opening a Cursor

Once a cursor has been declared, it must be opened before it can be used. This is done using the OPEN statement, for example:

EXEC SQL
    OPEN Cur1
END-EXEC

If the DECLARE CURSOR statement references a prepared statement that contains parameter markers, the corresponding OPEN statement must specify the host variables or the name of an SQLDA structure that will supply the values for the parameter markers, for example:

EXEC SQL
    OPEN Cur2 USING :last-name
END-EXEC

Using a Cursor to Retrieve Data

Once a cursor has been opened, it can be used to retrieve data from the database. This is done using the FETCH statement. The FETCH statement retrieves the next row from the results set produced by the OPEN statement and writes the data returned to the specified host variables (or to addresses specified in an SQLDA structure). For example:

    perform until sqlcode not = 0
        EXEC SQL
            FETCH Cur1 INTO :first_name 
        END-EXEC 
        display 'First name: ' fname 
        display 'Last name : ' lname 
        display spaces 
    end-perform

When the cursor reaches the end of the results set, a value of 100 is returned in SQLCODE in the SQLCA data structure and SQLSTATE is set to "02000".

As data is fetched from a cursor, locks can be placed on the tables from which the data is being selected.

Closing a Cursor

When your application has finished using the cursor, it should be closed using the CLOSE statement. For example:

EXEC SQL
    CLOSE Cur1
END-EXEC

Normally, when a cursor is closed, all locks on data and tables are released. If the cursor is closed within a transaction, however, the locks may not be released.

Positioned UPDATE and DELETE Statements

Positioned UPDATE and DELETE statements are used in conjunction with cursors and include WHERE CURRENT OF clauses instead of search condition clauses. The WHERE CURRENT OF clause specifies the corresponding cursor.

EXEC SQL
    UPDATE emp SET last_name = :last-name
     WHERE CURRENT OF Cur1
END-EXEC

This will update last_name in the row that was last fetched from the database using cursor Cur1.

EXEC SQL
    DELETE emp WHERE CURRENT OF Cur1
END-EXEC

This example will delete the row that was last fetched from the database using cursor Cur1.

CitOESQL:

With some ODBC drivers, cursors that will be used for positioned updates and deletes must include a FOR UPDATE clause. Note that positioned UPDATE and DELETE are part of the Extended ODBC Syntax and are not supported by all drivers.

Using Cursors

Cursors are very useful for handling large amounts of data; however, there are a number of issues that you should bear in mind when using cursors, namely: data concurrency, integrity, and consistency.

To ensure the integrity of your data, a database server can implement different locking methods. Some types of data access do not acquire any locks, some acquire a shared lock and some an exclusive lock. A shared lock allows other processes to access the data but not update it. An exclusive lock does not allow any other process to access the data.

When using cursors there are three levels of isolation and these control the data that a cursor can read and lock:

• Level zero

  Level zero can only be used by read-only cursors. At level zero, the cursor will not lock any rows but may be able to read data that has not yet been committed. Reading uncommitted data is dangerous (as a rollback operation will reset the data to its previous state) and is normally called a "dirty read". Not all databases will allow dirty reads.

• Level one

  Level one can be used by read-only cursors or updateable cursors. With level one, shared locks are placed on the data unless the FOR UPDATE clause is used. If the FOR UPDATE clause is used, exclusive locks are placed on the data. When the cursor is closed, the locks are released. A standard cursor, that is a cursor without the FOR UPDATE clause, will normally be at isolation level one and use shared locks.

• Level three

  Level three cursors are used with transactions. Instead of the locks being released when the cursor is closed, the locks are released when the transaction ends. With level three it is usual to place exclusive locks on the data.

It is worth pointing out that there can be problems with deadlocks or "deadly embraces" where two processes are competing for the same data. The classic example is where one process locks data A and then requests a lock on data B while a second process locks data B and then requests a lock on data A. Both processes have data that the other process requires. The database server should spot this case and send errors to one or both processes.

Data Structures

The CitOESQL preprocessor supplied with this system use two data structures:

SQL Communications Area (SQLCA)

After each embedded SQL statement is executed, error and status information are returned in the SQL Communications Area (SQLCA).

CitOESQL:

The SQLCA provided with COBOL-IT for use with CitOESQL contains two variables (SQLCODE and SQLSTATE), plus a number of warning flags which are used to indicate whether an error has occurred in the most recently executed SQL statement.

Using the SQLCA

The SQLCA structure is supplied in the file sqlca.cpy, which by default is located in the default location specified in the COBOL-IT CitOESQL files and locations section in the CitOESQL Getting Started Guide. To include it in your program, use the following statement in the data division:

EXEC SQL INCLUDE SQLCA END-EXEC

If you do not include this statement, the COBOL Compiler automatically allocates an area, but it is not addressable from within your program. However, if you declare either of the data items SQLCODE or SQLSTATE separately, the COBOL Compiler generates code to copy the corresponding fields in the SQLCA to the user-defined fields after each EXEC SQL statement.

If you declare the data item MFSQLMESSAGETEXT, it is updated with a description of the exception condition whenever SQLCODE is non-zero. MFSQLMESSAGETEXT must be declared as a character data item, PIC X(n), where n can be any legal value. This is particularly useful as ODBC error messages often exceed the 70-byte SQLCA message field.

The SQLCODE Variable

Testing the value of SQLCODE is the most common way of determining the success or failure of an embedded SQL statement.

For details of SQLCODE values, see the relevant database vendor documentation.

The SQLSTATE Variable

The SQLSTATE variable was introduced in the SQL-92 standard and is the recommended mechanism for future applications. It is divided into two components:

• The first two characters are called the class code. Any class code that begins with the letters A through H or the digits 0 through 4 indicates a SQLSTATE value that is defined by the SQL standard or another standard.
• The last three characters are called the subclass code.

A value of "00000" indicates that the previous embedded SQL statement executed successfully.

For specific details of the values returned in SQLSTATE, see the relevant database vendor documentation.

SQLWARN Flags

Some statements may cause warnings to be generated. To determine the type of warning, your application should examine the contents of the SQLWARN flags.

• W - The flag has generated a warning.
• blank (space) - The flag has not generated a warning.

The value of a flag is set to W if that particular warning occurred, otherwise the value is a blank (space).

Each SQLWARN flag has a specific meaning. For more information on the meaning of the SQLWARN flags, see the relevant database vendor documentation.

The WHENEVER Statement

Explicitly checking the value of SQLCODE or SQLSTATE after each embedded SQL statement can involve writing a lot of code. As an alternative, check the status of the SQL statement by using a WHENEVER statement in your application.

The WHENEVER statement is not an executable statement. It is a directive to the Compiler to automatically generate code that handles errors after each executable embedded SQL statement.

The WHENEVER statement allows one of three default actions (CONTINUE, GOTO or PERFORM) to be registered for each of the following conditions:

A WHENEVER statement for a particular condition replaces all previous WHENEVER statements for that condition.

The scope of a WHENEVER statement is related to its physical position in the source program, not its logical position in the run sequence. For example, in the following code if the first SELECT statement does not return anything, paragraph A is performed, not paragraph C:

    EXEC SQL
        WHENEVER NOT FOUND PERFORM A
    END-EXEC.
    perform B.
    EXEC SQL
        SELECT col1 into :host-var1 FROM table1
         WHERE col2 = :host-var2
    END-EXEC.
A.
    display "First item not found".
B.
    EXEC SQL
        WHENEVER NOT FOUND PERFORM C.
    END-EXEC.
C.
    display "Second item not found".

SQLERRM

The SQLERRM data area is used to pass error messages to the application from the database server. The SQLERRM data area is split into two parts:

• SQLERRML - holds the length of the error message
• SQLERRMC - holds the error text.

Within an error routine, the following code can be used to display the SQL error message:

if (SQLERRML \> ZERO) and (SQLERRML \< 80) 
    display 'Error Message: ', SQLERRMC(1:SQLERRML) 
        else 
    display 'Error Message: ', SQLERRMC 
end-if.

SQLERRD

The SQLERRD data area is an array of six integer status values, set by the database vendor after an SQL error.

SQLERRD PIC X9(9) COMP-5 OCCURS 6 VALUE 0.

Please consult the relevant database vendor documentation for more detailed information on these values.

The SQL Descriptor Area (SQLDA)

When either the number of parameters to be passed, or their data types, are unknown at compilation time, you can use an SQL Descriptor Area (SQLDA) instead of host variables.

An SQLDA contains descriptive information about each input parameter or output column. It contains the column name, data type, length, and a pointer to the actual data buffer for each input or output parameter. An SQLDA is ordinarily used with parameter markers to specify input values for prepared SQL statements, but you can also use an SQLDA with the DESCRIBE statement (or the INTO option of a PREPARE statement) to receive data from a prepared SELECT statement.

Although you cannot use an SQLDA with static SQL statements, you can use a SQLDA with a cursor FETCH statement.

CitOESQL

The SQLDA structure is supplied in both the sqlda.cpy (SQLDA only) and sqlda78.cpy (SQLDA plus SQLTYPE definitions) files, which are in the default location specified COBOL-IT CitOESQL files and locations section in the CitOESQL Getting Started Guide.

You can include the SQLDA in your COBOL program by adding one or both of the following statements to your data division:

EXEC SQL
    INCLUDE SQLDA
END-EXEC

EXEC SQL
    INCLUDE SQLDA78
END-EXEC

Using the SQLDA

Before an SQLDA structure is used, your application must initialise the following fields:

SQLN: This must be set to the maximum number of SQLVAR entries that the structure can hold.

The PREPARE and DESCRIBE Statements

You can use the DESCRIBE statement (or the PREPARE statement with the INTO option) to enter the column name, data type, and other data into the appropriate fields of the SQLDA structure.

Before the statement is executed, the SQLN and SQLDABC fields should be initialised as described above.

After the statement has been executed, the SQLD field will contain the number of parameters in the prepared statement. A SQLVAR record is set up for each of the parameters with the SQLTYPE and SQLLEN fields completed.

If you do not know how big the value of SQLN should be, you can issue a DESCRIBE statement with SQLN set to 1 and SQLD set to 0. No column detail information is moved into the SQLDA structure, but the number of columns in the results set is inserted into SQLD.

The FETCH Statement

Before performing a FETCH statement using an SQLDA structure, follow the procedure below:

1. The application must initialize SQLN and SQLDABC as described above.

2. The application must then insert, into the SQLDATA field, the address of each program variable that will receive the data from the corresponding column. (The SQLDATA field is part of SQLVAR).

3. If indicator variables are used, SQLIND must also be set to the corresponding address of the indicator variable.

The data type field (SQLTYPE) and length (SQLLEN) are filled with information from a PREPARE INTO or a DESCRIBE statement. These values can be overwritten by the application prior to a FETCH statement.

The OPEN or EXECUTE Statements

To use an SQLDA structure to specify input data to an OPEN or EXECUTE statement, your application must supply the data for the fields of the entire SQLDA structure, including the SQLN, SQLD, SQLDABC, and SQLTYPE, SQLLEN, and SQLDATA fields for each variable. The following scenarios require additional attention:

• SQLTYPE field is an odd number

  If the value of the SQLTYPE field is an odd number, you must also supply the address of the indicator variable using SQLIND.

• Host variable input is COMP

  When using CitOESQL with a host variable input defined as COMP, add 8192 (x2000) to the SQLTYPE field.

• SQLTYPE field is an odd number and indicator variable is COMP

  If the SQLTYPE field is an odd number, and the indicator variable is defined as a COMP, add 4096 (x1000) to the SQLTYPE field.

• Host variable input is COMP-5

  When using CitOESQL with a host variable input defined as COMP-5, no change to the SQLTYPE field is required.

The DESCRIBE Statement

After a PREPARE statement, you can execute a DESCRIBE statement to retrieve information about the data type, length and column name of each column returned by the specified prepared statement. This information is returned in the SQL Descriptor Area (SQLDA):

EXEC SQL
    DESCRIBE stmt1 INTO :sqlda
END-EXEC

If you want to execute a DESCRIBE statement immediately after a PREPARE statement, you can use the INTO option on the PREPARE statement to perform both steps at once:

EXEC SQL
    PREPARE stmt1 INTO :sqlda FROM :stmtbuf
END-EXEC

The following cases could require that you make manual changes to the SQLTYPE or SQLLEN fields in the SQLDA to accommodate differences in host variable types and lengths after executing DESCRIBE:

• SQLTYPE: Variable-length character types

  For variable-length character types you can choose to define SQLTYPE as a fixed-size COBOL host variable such as PIC X, N, or G, or a variable-length host variable such as a record with level 49 sub-fields for both length and the actual value. The SQLLEN field could be either 16 or 32 bits depending on the SQLTYPE value.

• SQLTYPE: Numeric types

  For numeric types you can choose to define SQLTYPE as COMP-3, COMP, COMP-5, or to display numeric COBOL host variables with an included or separate, and leading or trailing sign. The value returned by DESCRIBE depends on the data source. Generally, this is COMP-3 for NUMERIC or DECIMAL columns, and COMP-5 for columns of the tinyint, smallint, integer, or bigint integer types.

• SQLLEN

  DESCRIBE sets SQLLEN to the size of integer columns in COMP and COMP-5 representations, meaning a value of 1, 2, 4, or 18. You might need to adjust this depending on SQLTYPE. For NUMERIC and DECIMAL columns, it encodes the precision and scale of the result.

Dynamic SQL

If everything is known about an SQL statement when the application is compiled, the statement is known as a static SQL statement.

In some cases, however, the full text of an SQL statement may not be known when an application is written. For example, you may need to allow the end-user of the application to enter an SQL statement. In this case, the statement needs to be constructed at run-time. This is called a dynamic SQL statement.

Dynamic SQL Statement Types

There are four types of dynamic SQL statement:

These types of dynamic SQL statement are described more fully in the following sections.

Execute a Statement Once

With this type of dynamic SQL statement, the statement is executed immediately. Each time the statement is executed, it is re-parsed.

Execute a Statement More Than Once

This type of dynamic SQL statement is either a statement that can be executed more than once or a statement that requires host variables. For the second type, the statement must be prepared before it can be executed.

Select a Given List of Data

This type of dynamic SQL statement is a SELECT statement where the number and type of host variables is known. The normal sequence of SQL statements is:

1. Prepare the statement
2. Declare a cursor to hold the results
3. Open the cursor
4. Fetch the variables
5. Close the cursor.

Select any Amount of Data

This type of dynamic SQL statement is the most difficult type to code. The type and/or number of variables is only resolved at run time. The normal sequence of SQL statements is:

1. Prepare the statement
2. Declare a cursor for the statement
3. Describe the variables to be used
4. Open the cursor using the variables just described
5. Describe the variables to be fetched
6. Fetch the variables using their descriptions
7. Close the cursor.

If either the input host variables, or the output host variables are known (at compile time), then the OPEN or FETCH can name the host variables and they do not need to be described.

Preparing Dynamic SQL Statements

The PREPARE statement takes a character string containing a dynamic SQL statement and associates a name with the statement, for example:

move "INSERT INTO publishers " &
    "VALUES (?,?,?,?)" to stmtbuf
EXEC SQL
    PREPARE stmt1 FROM :stmtbuf
END-EXEC

Dynamic SQL statements can contain parameter markers - question marks (?) that act as a place holder for a value. In the example above, the values to be substituted for the question marks must be supplied when the statement is executed.

Once you have prepared a statement, you can use it in one of two ways:

• You can execute a prepared statement.
• You can open a cursor that references a prepared statement.

Executing Dynamic SQL Statements

The EXECUTE statement runs a specified prepared SQL statement.

If the prepared statement contains parameter markers, the EXECUTE statement must include either the "using :hvar" option to supply parameter values using host variables or the "using descriptor :sqlda_struct" option identifying an SQLDA data structure already populated by the application. The number of parameter markers in the prepared statement must match the number of SQLDATA entries ("using descriptor :sqlda") or host variables ("using :hvar").

move "INSERT INTO publishers " &
        "VALUES (?,?,?,?)" to stmtbuf
EXEC SQL
    PREPARE stmt1 FROM :stmtbuf
END-EXEC
...
EXEC SQL
    EXECUTE stmt1 USING :pubid,:pubname,:city,:state
END-EXEC.

In this example, the four parameter markers are replaced by the contents of the host variables supplied via the USING clause in the EXECUTE statement.

EXECUTE IMMEDIATE Statement

If the dynamic SQL statement does not contain any parameter markers, you can use EXECUTE IMMEDIATE instead of PREPARE followed by EXECUTE, for example:

move "DELETE FROM emp " &
        "WHERE last_name = 'Smith'" to stmtbuf
EXEC SQL
    EXECUTE IMMEDIATE :stmtbuf
END-EXEC

When using EXECUTE IMMEDIATE, the statement is re-parsed each time it is executed. If a statement is likely to be used many times it is better to PREPARE the statement and then EXECUTE it when required.

Dynamic SQL Statements and Cursors

If a dynamic SQL statement returns a result, you cannot use the EXECUTE statement. Instead, you must declare and use a cursor.

First, declare the cursor using the DECLARE CURSOR statement:

EXEC SQL
    DECLARE C1 CURSOR FOR dynamic_sql
END-EXEC

In the example above, dynamic_sql is the name of a dynamic SQL statement. You must use the PREPARE statement to prepare the dynamic SQL statement before the cursor can be opened, for example:

move "SELECT char_col FROM mfesqltest " &
    "WHERE int_col = ?" to sql-text
EXEC SQL
    PREPARE dynamic_sql FROM :sql-text
END-EXEC

Now, when the OPEN statement is used to open the cursor, the prepared statement is executed:

EXEC SQL
    OPEN C1 USING :int-col
END-EXEC

If the prepared statement uses parameter markers, then the OPEN statement must supply values for those parameters by specifying either host variables or an SQLDA structure.

Once the cursor has been opened, the FETCH statement can be used to retrieve data, for example:

EXEC SQL
    FETCH C1 INTO :char-col
END-EXEC

Finally, the cursor is closed using the CLOSE statement:

EXEC SQL
    CLOSE C1
END-EXEC

CALL Statements

A CALL statement can be prepared and executed as dynamic SQL.

• You can use parameter markers (?) in dynamic SQL wherever you use host variables in static SQL
• Use of the IN, INPUT, OUT, OUTPUT, INOUT and CURSOR keyword following parameter markers is the same as their use after host variable parameters in static SQL.

• The whole call statement must be enclosed in braces to conform to ODBC cannonical stored procedure syntax (the CitOESQL precompiler does this for you in static SQL). For example:

  move ‘{call myproc(?, ? out)}’ to sql-text
  exec sql
      prepare mycall from :sql-text
  end-exec
  exec sql
      execute mycall using :parm1, :parm2
  end-exec
  

• If you use parameter arrays, you can limit the number of elements used with a FOR clause on the EXECUTE, for example:

  move 5 to param-count
  exec sql
      for :param-count
      execute mycall using :parm1, :param2
  end-exec
  

EXAMPLE:

The following is an example of a program that creates a stored procedure "mfexecsptest" using data source "SQLServer 2000" and then retrieves data from "publishers" table using a cursor "c1" with dynamic SQL.

\$SET SQL
    WORKING-STORAGE SECTION.

EXEC SQL INCLUDE SQLCA END-EXEC

\*\> after an sql error this has the full message text
 01 MFSQLMESSAGETEXT    PIC X(250).
 01 IDX                 PIC X(04) COMP-5.

EXEC SQL BEGIN DECLARE SECTION END-EXEC
\*\> Put your host variables here if you need to port
| \*\> to other COBOL compilers     

 01 stateParam          pic xx.   
 01 pubid               pic x(4).   
 01 pubname             pic x(40). 
 01 pubcity             pic x(20).

 01 sql-stat            pic x(256).

EXEC SQL END DECLARE SECTION END-EXEC

PROCEDURE DIVISION.     

    EXEC SQL   
        WHENEVER SQLERROR perform OpenESQL-Error   
    END-EXEC   

    EXEC SQL   
        CONNECT TO 'SQLServer 2000' USER 'SA'   
    END-EXEC    

\*\> Put your program logic/SQL statements here     

    EXEC SQL 
        create procedure mfexecsptest   
            (@stateParam char(2) = 'NY' ) as   

        select pub_id, pub_name, city from publishers 
         where state = @stateParam   
    END-EXEC   

    exec sql 
        declare c1 scroll cursor for dsql2 for read only 
    end-exec  

    move "{call mfexecsptest(?)}" to sql-stat
    exec sql prepare dsql2 from :sql-stat end-exec

    move "CA" to stateParam
    exec sql
        open c1 using :stateParam
    end-exec

    display "Testing cursor with stored procedure"
    perform until exit
        exec sql
            fetch c1 into :pubid, :pubname, :pubcity
        end-exec

        if sqlcode = 100
            exec sql close c1 end-exec
            exit perform
        else
            display pubid " " pubname " " pubcity
        end-if
    end-perform

    EXEC SQL close c1 END-EXEC

    EXEC SQL DISCONNECT CURRENT END-EXEC
    EXIT PROGRAM.
    STOP RUN.
*> Default sql error routine / modify to stop program if
*> needed
OpenESQL-Error Section.

    display "SQL Error = " sqlstate " " sqlcode
    display MFSQLMESSAGETEXT
    *> stop run
exit.