DESCRIBE

The DESCRIBE statement is used in an application program to pass information about a dynamic statement between the application and ALLBASE/SQL. It must refer to a statement preprocessed with the PREPARE statement.

Scope

C and Pascal Applications Only

SQL Syntax

  DESCRIBE [OUTPUT 
            INPUT 
            RESULT] StatementName {INTO [[SQL] DESCRIPTOR] 
                                   USING [SQL] DESCRIPTOR} {SQLDA 
                                                            AreaName}

Parameters

OUTPUT

specifies that the characteristics of any output values in the prepared StatementName be described in the associated sqlda_type and sqlformat_type data structures. This applies to query result column definitions in a SELECT statement or to dynamic return status or output parameters specified as question marks in an EXECUTE PROCEDURE statement.

OUTPUT is the default.

INPUT

specifies that the characteristics of any dynamic input parameters in the prepared StatementName be described in the associated sqlda_type and sqlformat_type data structures. This applies to dynamic input parameters specified as question marks in any DML statement.

RESULT

specifies that the characteristics of any single format multiple row result sets in a procedure created using the WITH RESULT clause be described in the associated sqlda_type and sqlformat_type data structures. This applies to any prepared EXECUTE PROCEDURE statement.

StatementName

identifies a previously preprocessed (prepared) ALLBASE/SQL statement.

INTO

specifies the sqlda_type data structure where data is to be described.

USING

specifies the sqlda_type data structure where data is to be described.

SQLDA

specifies that a data structure of sqlda_type named sqlda is to be used to pass information about the prepared statement between the application and ALLBASE/SQL.

AreaName

specifies the user defined name of a data structure of sqlda_type that is to be used to pass information about the prepared statement between the application and ALLBASE/SQL.

Description

This statement cannot be used in ISQL, in COBOL and FORTRAN programs, or in procedures.
If StatementName refers to a SELECT statement, the DESCRIBE statement with the (default) OUTPUT option sets the sqld field of the associated sqlda_type data structure to the number of columns in the query result and sets the associated sqlformat_type data structure to each column's name, length, and data type. On the basis on this information, an application can parse a data buffer to obtain the column values in the query result. The application reads the query result by associating the StatementName with a select cursor and using select cursor manipulation statements (OPEN, FETCH, and CLOSE).
If StatementName does not refer to a SELECT statement, the DESCRIBE statement used with the OUTPUT option sets the sqld field of the associated sqlda_type data structure to zero.
If StatementName refers to a statement in which dynamic parameters have been specified, the DESCRIBE statement with the INPUT option obtains the number of input dynamic parameters (in the sqld field of the associated sqlda_type data structure) and sets the associated sqlformat_type data structure to each column's name, length, and data type. The application can use this information to load the appropriate data buffer with dynamic parameter values.
If StatementName refers to an EXECUTE PROCEDURE statement for a procedure with multiple row result sets, the sqlmproc field of the associated sqlda_type data structure is set to a non-zero value. The program reads the query results by associating the StatementName with a procedure cursor name and using procedure cursor manipulation statements (OPEN, ADVANCE, FETCH, and CLOSE).
If StatementName refers to an EXECUTE PROCEDURE statement containing output dynamic parameters, the DESCRIBE statement with the (default) OUTPUT option returns the number of output dynamic parameters in the sqloparm field of the associated sqlda_type data structure.
If StatementName refers to an EXECUTE PROCEDURE statement containing both input and output dynamic parameters, you can issue the EXECUTE statement specifying the USING INPUT AND OUTPUT option to execute the dynamically preprocessed statement.
If StatementName is an EXECUTE PROCEDURE statement containing single format multiple row result set(s), the DESCRIBE statement with the RESULT option returns the format information of the multiple row result set(s). If the procedure contains more than one multiple row result set, all must return rows with compatible formats.
If the RESULT option is specified when describing an EXECUTE PROCEDURE statement for a procedure created with no WITH RESULT clause, the sqld field of the related SQLDA is set to zero, and no format information is written to the SQL descriptor area.
If the RESULT option is specified when describing a statement other than an EXECUTE PROCEDURE statement, the DESCRIBE RESULT statement returns an error, and nothing is written to the SQL descriptor area.
Detailed descriptions of how to use this statement are found in the "Using Dynamic Operations" chapters of the ALLBASE/SQL C Application Programming Guide and the ALLBASE/SQL Advanced Application Programming Guide, and in the "Using Parameter Substitution in Dynamic Statements" chapter and the "Using Procedures in Application Programs" chapter of the ALLBASE/SQL Advanced Application Programming Guide.

Authorization

To describe a previously preprocessed SELECT statement, you must have authority that would permit you to execute the SELECT statement. To describe a previously preprocessed EXECUTE PROCEDURE statement, you must have authority that would permit you to execute the procedure. You do not need authorization to describe other previously preprocessed statements.

Examples

Prepared statement with known format
If you know in advance that the statement to be dynamically preprocessed is neither a SELECT statement nor an EXECUTE PROCEDURE statement with results, and does not contain dynamic parameters nor input/output host variables, you can prepare it and execute it in one step, as follows:
EXECUTE IMMEDIATE :Dynam1
Prepared statement with unknown format
In other instances, it is more appropriate to prepare and execute the statement in separate operations. For example, if you don't know the format of a statement, you could do the following:
PREPARE Dynamic1 FROM :Dynam1
The statement stored in :Dynam1 is dynamically preprocessed.
DESCRIBE Dynamic1 INTO SqldaOut
If Dynamic1 is neither a SELECT statement (Sqld field of the Sqlda data structure is 0) nor an EXECUTE PROCEDURE statement with results (sqlmproc = 0) and you know there are no dynamic parameters in the prepared statement, use the EXECUTE statement to execute the dynamically preprocessed statement.
If Dynamic1 is an EXECUTE PROCEDURE statement containing dynamic output parameters, the sqloparm field of the Sqlda data structure contains the number of such parameters in the statement. You can access the appropriate format array and data buffer to obtain the data.
If it is possible that dynamic input parameters are present in the prepared statement or that the statement is an EXECUTE PROCEDURE statement for a procedure with multiple row result sets, you must further describe it. See the exproc function below which emphasizes steps needed to process an EXECUTE PROCEDURE statement for a procedure with multiple row result sets.
To check for dynamic input parameters in any type of DML statement, describe the statement for input:
DESCRIBE INPUT Dynamic1 USING SQL DESCRIPTOR SqldaIn
If dynamic input parameters are present, the appropriate data buffer or host variables must be loaded with the values of any dynamic parameters. Then if the statement is not a query, it can be executed, as in this example using a data buffer:
EXECUTE Dynamic1 USING SQL DESCRIPTOR SqldaIn
If Dynamic1 is a SELECT statement and the language you are using supports dynamically defined SELECT statements, use a cursor to manipulate the rows in the query result:
DECLARE Dynamic1Cursor CURSOR FOR Dynamic1
Place the appropriate values into the SQL descriptor areas. Use the USING DESCRIPTOR clause of the OPEN statement to identify where dynamic input parameter information is located. Load related dynamic parameter data into the input data buffer.
OPEN Dynamic1Cursor USING SQL DESCRIPTOR SqldaIn
Use the USING DESCRIPTOR clause of the FETCH statement to identify where to place the rows selected.
FETCH Dynamic1Cursor USING DESCRIPTOR SqldaOut . . .
When all rows have been processed, close the cursor:
CLOSE Dynamic1Cursor

Prepared statement is EXECUTE PROCEDURE

If the described statement is an EXECUTE PROCEDURE statement for a procedure with multiple row result sets, the sqlmproc field of the sqlda data structure contains the number of multiple row result sets (0 if there are none) following execution of the DESCRIBE statement with default OUTPUT option. For example, if the statement you described looks like the following, and the procedure was created with two multiple row result SELECT statements and a WITH RESULT clause:

   DynamicCmd = "EXECUTE PROCEDURE ? = proc(?, ? OUTPUT)"
 
   PREPARE cmd FROM :DynamicCmd

Assuming you don't know the format of this prepared statement:

DESCRIBE OUTPUT cmd INTO sqldaout

The sqld of sqlda is set to 0, sqlmproc to 2, and sqloparm to 2.

DESCRIBE INPUT cmd USING sqldain

The sqld of sqlda is set to 2, sqlmproc to 2, and sqloparm to 0.

If sqldaout. sqlmproc <> 0 then, use procedure cursor processing statements to process multiple row result set(s) from the procedure.

      DESCRIBE RESULT cmd USING sqldaresult
        .
        .
        .
        DECLARE Dynamic1Cursor CURSOR FOR cmd
        OPEN Dynamic1Cursor USING sqldain
        .
        .
        .
        FETCH Dynamic1Cursor using DESCRIPTOR sqldaresult
        .
        .
        .
        CLOSE Dynamic1Cursor USING sqldaout
        .
        .
        .

Else, execute the procedure with both input and output dynamic parameters.

EXECUTE cmd USING DESCRIPTOR INPUT sqldain AND OUTPUT sqldaout;