Mimer SQL Programmer's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


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

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
dbtechnology@upright.se
Mimer SQL Programmer's Manual TOC PREV NEXT INDEX