Using Dynamic SQL › Dynamic Insert, Update, and Delete Operations › Using PREPARE

Using PREPARE

Why You Use PREPARE

You use the PREPARE statement to dynamically compile an SQL statement that is formulated at runtime. You should prepare the statement if:

• The statement may be issued more than once during a transaction
• The statement may be a SELECT

Determining Information About the Prepared Statement

You can use either the DESCRIBE option of the PREPARE statement or a separate DESCRIBE statement to determine the following information:

• Whether the prepared statement is a SELECT
• If the prepared statement is a SELECT, the number of result columns to be returned and the name and format of each of the result columns
• The format of any dynamic parameters that must be supplied as input values when the statement is executed or an associated cursor is opened

To retrieve this information, you must allocate at least one SQL descriptor area. You need to allocate two descriptor areas if you want to retrieve information about both result columns and dynamic parameters.

Note: Descriptor areas must be defined using the SQLDA structure.

Declaring SQLDA

The program can declare the default descriptor area SQLDA with an INCLUDE statement:

EXEC SQL
  INCLUDE SQLDA
    NUMBER OF COLUMNS 20
END-EXEC.

Declaring SQLDA in CA ADS

If you are using descriptor areas in CA ADS, you can create a work record layout through IDD as described in the CA ADS User Guide. This work record must match the SQLDA layout and the initial values should conform to the data types.

The following example displays the CA ADS format of the SQLDA:

SQLDA.
      05  SQLDAID             PIC X(8).
      05  SQLN                PIC S9(9) COMP
                              VALUE  +n .
      05  SQLD                PIC S9(9) COMP.
      05  SQLVAR              OCCURS n.
          10  SQLLEN          PIC S9(9) COMP.
          10  SQLTYPE         PIC S9(4) COMP.
          10  SQLSCALE        PIC S9(4) COMP.
          10  SQLPRECISION    PIC S9(4) COMP.
          10  SQLALN          PIC S9(4) COMP.
          10  SQLNALN         PIC S9(4) COMP.
          10  SQLNULL         PIC S9(4) COMP.
          10  SQLNAME         PIC X(32).

where n is the maximum number of occurrences of SQLVAR

SQLDA Values

An SQL descriptor area used to retrieve information about the output of the prepared statement contains the following values:

The value in SQLD indicates whether the statement is:

• A SELECT statement if the value is greater than 0
• Not a SELECT statement if the value is equal to 0

  If greater than 0, SQLD is the number of columns in the result table of the SELECT statement.

The value in SQLN indicates the maximum number of columns the descriptor area can describe:

• The number specified in the NUMBER OF COLUMNS parameter of the INCLUDE statement
• If SQLD is greater than SQLN, the descriptor area is too small to describe the result table.

SQLVAR is a structure that occurs SQLN times. Each occurrence contains information about a result column.

Note: For more information, see the CA IDMS SQL Reference Guide.

PREPARE Example

In this example, the program has formulated an SQL statement and has moved the character string into the host variable STATEMENT-STRING:

EXEC SQL
  PREPARE DYNAMIC_STATEMENT
    FROM :STATEMENT-STRING
    DESCRIBE INTO SQLDA
END-EXEC.

Error-checking

If a PREPARE statement fails to execute at runtime, CA IDMS returns a negative value to SQLCODE.

If the SQLCODE value is -4, there may be a syntax error in the statement. If there is, the offset within the statement at which the syntax error occurred is returned to the SQLCSER field of the SQLCA.