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 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 SQL descriptor areas for input and output values. Input values are specified in a USING clause while output values are specified in an INTO clause.
A statement has output values if it is a call to a stored procedure with parameters that are specified as INOUT or OUT.
Note: All parameter markers used in a call statement are described with the DESCRIBE INPUT statement, regardless of the mode of the formal parameter. A parameter marker with PARAMETER_MODE_INOUT must be present in the descriptors specified in the INTO and the USING clause of the EXECUTE statement but not necessarily with the same physical location in the host program.
Parameter markers must be used for all INOUT or OUT parameters when a call statement is prepared dynamically.
The pseudo code that follows uses the variables RESULT and MARKERS to flag the type of statement and the presence of parameter markers respectively.exec sql ALLOCATE DESCRIPTOR 'SQLRESULT' with max 30; exec sql ALLOCATE DESCRIPTOR 'INVAL' with max 30; exec sql ALLOCATE DESCRIPTOR 'OUTVAL' with max 30; exec sql DESCRIBE OUTPUT OBJECT USING SQL DESCRIPTOR RESULT; exec sql GET DESCRIPTOR 'SQLRESULT' :NO_OUT = COUNT; if NO_OUT = 0 then RESULT := FALSE; else RESULT := TRUE; end if; exec sql DESCRIBE INPUT OBJECT USING SQL DESCRIPTOR 'INVAL'; exec sql GET DESCRIPTOR 'INVAL' :NO_IN = COUNT; if NO_IN > 0 then MARKERS := TRUE; else MARKERS := FALSE; end if; if RESULT then ... else if MARKERS then -- -- loop over inval -- if PARAMETER_MODE_INOUT then copy to OUTVAL -- if PARAMETER_MODE_OUT then move to OUTVAL, compact INVAL -- count number of input values and output values -- else NO_INVAL := 0; NO_OUTVAL := 0; end if; if NO_INVAL > 0 and NO_OUTVAL > 0 then exec sql EXECUTE OBJECT into OUTVAL using INVAL; elsif NO_INVAL > 0 then exec sql EXECUTE OBJECT using INVAL; elsif NO_OUTVAL > 0 then exec sql EXECUTE OBJECT into OUTVAL; else exec sql EXECUTE OBJECT; end if;
The descriptor areas referenced in the EXECUTE statement may be replaced by an explicit list 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 NO_IN = 0 then USERVAR := FALSE; else USERVAR := TRUE; end if; ... if RESULT_SET then if USERVAR then exec sql OPEN 'C1' USING SQL DESCRIPTOR 'SQLA2'; else exec sql OPEN 'C1'; end if; ... exec sql FETCH 'C1' INTO SQL DESCRIPTOR 'SQLA1'; ... else ... end if;
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40