Dynamically executing a SELECT SQL statement with parameter markers

Use the SELECT statements with parameter markers.

Suppose that you want to execute SELECT statements repeatedly using a list of hospital numbers. Suppose further that users enter a list of hospital numbers to be retrieved. You must construct and execute the entire statement dynamically. Your program can:

Use parameter markers instead of constant values in the SQL statement
Use OPEN statement with the USING clause to set values for parameter markers
Use FETCH to retrieve data

Statements with parameter markers:

Dynamic SQL statements cannot use host variables. Therefore, you cannot dynamically execute an SQL statement that contains host variables. Instead, use parameter marker. A parameter marker is a question mark (?) that represents a position in a dynamic SQL statement where the application will provide a value.

Example using parameter markers:

SELECT HOSPNAME FROM PCB01.HOSPITAL WHERE HOSPCODE = ?;

You associate the host variable HOSPCODE with the parameter marker when you fetch data with the prepared statement.

Declaring a varying-length character host variable: Before you prepare and execute an SQL statement, you have to assign it into a host variable. Declare varying-length character host variable for the SQL statement. The first two bytes must contain the length of the SQL statement. The maximum length of the SQL statement is 32K. For example:

01  STMTSTR.                                   
    49  STMTSTR-LEN  PIC S9(4) COMP VALUE +180.
    49  STMTSTR-TXT  PIC X(180) VALUE SPACES.

Declaring the CURSOR:

Declare a cursor to put the results for a SELECT statement.

When you declare the cursor, use the statement name (call it S1), and give the cursor itself a name (for example, C1):

EXEC SQLIMS DECLARE C1 CURSOR FOR S1
END-EXEC.

Using the PREPARE statement:

Assume that the character host variable :STMTSTR has the value SELECT HOSPNAME FROM PCB01.HOSPITAL WHERE HOSPCODE = ?. To prepare an SQL statement from that string and assign it the name S1, write:

EXEC SQLIMS PREPARE S1 FROM :STMTSTR;

The prepared statement still contains a parameter marker, for which you must supply a value when the statement executes. After the statement is prepared, the parameter marker enables you to execute the same statement many times with different values of the hospital code.

To execute STMT, your program must open the cursor, fetch rows from the result segment, and close the cursor.

Using the OPEN statement:

The OPEN statement open a cursor for the prepared SQL statement. If the SQL statement contains parameter markers, you must use the USING clause of OPEN to provide values for all of the parameter markers. The USING clause of the OPEN statement names a list of one or more host variables or a host structure. This list supplies values for all of the parameter markers. Suppose that C1 is the cursor and the parameter value is contained in the host variable HOSPCODE, write:

OPEN C1 USING :HOSPCODE

The OPEN statement can be executed using different values for HOSPCODE.

Using more than one parameter marker: The prepared statement (S1 in the example) can contain more than one parameter marker. If it does, the USING clause of EXECUTE specifies a list of variables or a host structure. The variables must contain values that match the number and data types of parameters in S1 in the proper order. You must know the number and types of parameters in advance and declare the variables in your program.

For example, OPEN C1 USING :PARM1, :PARM2

Fetching rows from the result table:

This example shows you how to fetch data into host variables:

EXEC SQLIMS FETCH C1 INTO :HOSPNAME, :HOSPCODE
END-EXEC.

Closing the cursor:

Close the cursor when your program is finished running the FETCH statement:

EXEC SQLIMS CLOSE C1
END-EXEC.