Handling Prepared Statements
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
EXECUTEstatement, 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
Executable statements are identified by a value of zero in the
COUNTfield of the SQL descriptor area after a
DESCRIBE OUTPUTstatement. 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
OUTparameters when a call statement is prepared dynamically.
The descriptor areas referenced in the
EXECUTEstatement 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
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 IMMEDIATEcombines the functions of
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 IMMEDIATEcan 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
COUNTfield of the SQL descriptor area after
All dynamically submitted
SELECTstatements 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
OPENstatement 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
OPENstatement 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
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