Describing Prepared Statements

---

Statements returning a result set and statements containing parameter markers can be described to obtain information about the number and data types of the parameters.

There are two forms of DESCRIBE:

• DESCRIBE OUTPUT for result set values
• DESCRIBE INPUT for input and output parameters.

Both forms of DESCRIBE use the object (prepared) form of the statement as an argument. The same statement may be described in both senses if necessary.

For example:

 EXEC SQL BEGIN DECLARE SECTION;
    string SQLA1(128);
    integer MAXOCC;
    string SOURCE(255);
 EXEC SQL END DECLARE SECTION;
 ...
 
 MAXOCC := 15;
 SQLA1 := "SQL_AREA_1";
 EXEC SQL ALLOCATE DESCRIPTOR :SQLA1 WITH MAX 20;
 EXEC SQL ALLOCATE DESCRIPTOR 'SQLA2' WITH MAX :MAXOCC;
 ...
 
 EXEC SQL PREPARE 'OBJECT' FROM :SOURCE;
 EXEC SQL DESCRIBE OUTPUT 'OBJECT' USING SQL DESCRIPTOR :SQLA1;
 EXEC SQL DESCRIBE INPUT 'OBJECT' USING SQL DESCRIPTOR 'SQLA2';
 ...
 

DESCRIBE places information about the prepared statement in the SQL descriptor areas. See SQL Descriptor Area for a description of the SQL descriptor area.

The contents of the SQL descriptor area is read with the GET DESCRIPTOR statement and updated with the SET DESCRIPTOR statement.

Describing Output Variables

The items in the result set for a statement are described with the DESCRIBE OUTPUT statement. The keyword OUTPUT may be omitted.

The DESCRIBE OUTPUT statement shows:

• whether the statement returns a result set or not. This is indicated by the value of the COUNT field of the SQL descriptor area which is set to zero for statements that do not return a result set. Statements that return a result set are calls to result set procedures, see Result Set Procedures, and query-expressions (refer to the Mimer SQL Reference Manual, SELECT).
• Dynamic SQL programs must test for this after each DESCRIBE operation because the treatment of statements that return result sets differs from the treatment of those that do not, see Handling Prepared Statements. If the statement returns a result set, the DESCRIBE statement will place information about the items in the result set in the fields of the descriptor area.
• whether the current descriptor area allocation is sufficient or not. Insufficient area is indicated by the SQLSTATE variable set to a warning state and a value of COUNT (required number of items) greater than that specified in the WITH MAX ... clause of the ALLOCATE DESCRIPTOR statement, or greater than 100 if no WITH MAX ... clause was specified. If the area is insufficient, no items are described.

Describing Input Variables

The DESCRIBE INPUT statement is used to describe parameter markers.

The value of the COUNT field of the SQL descriptor area indicates the number of parameter markers in the statement (a value of zero indicates no input parameters). A value greater than that specified in WITH MAX ... indicates that the allocated SQL descriptor area is too small and the describe operation will not be performed. This situation is handled as described above for DESCRIBE OUTPUT.

Note: If the prepared statement is a call to a stored procedure that uses parameter markers, these will be described by the DESCRIBE INPUT statement. This is regardless of how the formal parameter is specified in the procedure definition. Whether the parameter is IN, INOUT or OUT can be seen from the PARAMETER_MODE field in the descriptor area.

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