Handling Prepared Statements

---

After PREPARE and DESCRIBE, the way in which submitted statements are handled differs according to whether the statement is executable or whether it returns a result set.

• Executable statements are executed using the EXECUTE statement, with the object (prepared) form of the submitted statement as the argument.
• Result set a cursor is used for these statements, associated with the object form of the prepared statement and are executed with OPEN and FETCH.

Executable Statements

Executable statements are identified by a value of zero in the COUNT field of the SQL descriptor area after a DESCRIBE OUTPUT statement. If the statement does not contain any parameter markers, it may be executed directly.

If, on the other hand, the statement contains parameter markers, the statement must be executed with an SQL descriptor area for input and output values.

Note: All parameter markers used in a call statement are described with the DESCRIBE INPUT statement, regardless of the mode of the formal parameter.

Parameter markers must be used for all INOUT or OUT parameters when a call statement is prepared dynamically.

The descriptor areas referenced in the EXECUTE statement may be replaced by explicit lists of host variables, provided that the number and data types of the user variables in the source statement are known when the program is written (so that variables can be declared and the appropriate variable list written into the EXECUTE statement).

This facility is of limited use, since the occasions when the user constructs freely chosen SQL statements with a predetermined number of user variables are rare.

The shorthand form EXECUTE IMMEDIATE combines the functions of PREPARE and EXECUTE. This form may only be used for executable statements with no parameter markers and is therefore of value only in contexts where the user is restricted to this type of statement. (Data definition and security control statements fall into this category, since user variables are not permitted in the syntax of these statements. EXECUTE IMMEDIATE can therefore be useful for application programs designed specifically to handle database definition statements).

Result Set Statements

Statements returning a result set are identified by a non-zero value in the COUNT field of the SQL descriptor area after DESCRIBE OUTPUT.

All dynamically submitted SELECT statements and calls to result set procedures must be handled through cursors. Cursors are declared or allocated for the object (prepared) form of submitted result set returning statements.

Note: A DECLARE CURSOR statement must precede the PREPARE statement in the program code. If ALLOCATE CURSOR is used instead of DECLARE CURSOR, the statement must have been prepared before the cursor can be allocated. The SQL statement must also be prepared before the cursor is opened.

If the source form of the result set returning statement contains parameter markers, these must be described before the cursor is opened and the OPEN statement must reference the relevant descriptor area. In the rare case where the number and data type of the user variables are known when the program is first written, the OPEN statement may reference an explicit variable list instead of a descriptor area.

The descriptor area used for the submitted result set returning statement is referenced when data is retrieved with the FETCH statement.

Example:

 ...
 EXEC SQL ALLOCATE DESCRIPTOR 'SQLA1' WITH MAX 30;
 EXEC SQL ALLOCATE DESCRIPTOR 'SQLA2' WITH MAX 30;
 ...
 
 EXEC SQL PREPARE 'OBJECT' FROM :SOURCE;
 ...
 
 EXEC SQL DESCRIBE OUTPUT 'OBJECT' USING SQL DESCRIPTOR 'SQLA1';
 EXEC SQL GET DESCRIPTOR 'SQLA1' :NO_OUT = COUNT;
 if NO_OUT = 0 then
    RESULT_SET := FALSE;
 else
    EXEC SQL ALLOCATE 'C1' CURSOR FOR 'OBJECT';
    RESULT_SET := TRUE;
 end if;
 ...
 
 EXEC SQL DESCRIBE INPUT 'OBJECT' USING SQL DESCRIPTOR 'SQLA2';
 EXEC SQL GET DESCRIPTOR 'SQLA2' :NO_IN = COUNT;
 ...
 
 if RESULT_SET then
    EXEC SQL OPEN 'C1' USING SQL DESCRIPTOR 'SQLA2';
    ...
    EXEC SQL FETCH 'C1' INTO SQL DESCRIPTOR 'SQLA1';
    ...
 else
    ...
 end if;

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se