Previous Topic Next topic Print topic


DESCRIBE

Provides information on prepared dynamic SQL statements and describes the result set for an open cursor.

Syntax, Format 1:

>>---EXEC SQL-----DESCRIBE---.----------------------.----->
                             +---SELECT LIST FOR----+
                             +---BIND VARIABLES FOR-+
 
 >--.--prepared_stmt_name--.---INTO---:sqlda_struct---END-EXEC--><

Syntax, Format 2:

>>---EXEC SQL-----DESCRIBE---CURSOR---cursor_name----->

 >---INTO---:sqlda_struct---END-EXEC--><

Parameters:

prepared_stmt_name The name of a prepared SQL SELECT statement or QUERY ODBC statement.
cursor-name The name of an open cursor.
: sqlda_struct A host variable that specifies the output SQLDA data structure to be populated. The colon is optional to provide compatibility with other embedded SQL implementations.

Comments:

This statement populates the specified SQLDA data structure with the data type, length, and column name of each column returned by the specified prepared statement.

If neither SELECT LIST FOR or BIND VARIABLES FOR is specified, SELECT LIST is used by default. If BIND VARIABLES FOR is specified, information about input parameters is returned in the SQLDA rather than information about results columns.

The DESCRIBE statement inserts the number of columns into the sqld field of the SQLDA structure. If a non-select statement was prepared, sqld is set to 0. Before DESCRIBE is called, the following fields in the SQLDA data structure must be initialised by the application:

sqln The maximum number of sqlvar (column descriptor) entries that the structure can accomodate.
sqldabc The maximum size of the SQLDA:
  • 32-bit - Calculated as sqln * 44 + 16
  • 64-bit - Calculated as sqln * 56 + 16

If sqln is set to 0, no column descriptor entries are constructed, but sqld is set to the number of entries required. The DESCRIBE statement works in a similar way to a PREPARE statement with an INTO clause.

By default, the SQL types for date, time and timestamp are respectively DATE-RECORD, TIME-RECORD and TIMESTAMP-RECORD. When you use the BEHAVIOR=OPTIMIZED option for the SQL Compiler directive, OpenESQL mimics the DB2 on the mainframe for these data types, providing character strings (i.e., PIC X(n)) instead of the standard, default record constructs.

Note: Few drivers fully implement the ODBC calls necessary for DESCRIBE BIND VARIABLES.

Example:

     EXEC SQL INCLUDE SQLDA END-EXEC
     EXEC SQL BEGIN DECLARE SECTION END-EXEC
     01 statement   pic x(80).
     EXEC SQL END DECLARE SECTION END-EXEC

     EXEC SQL
         DECLARE C1 CURSOR FOR stmt1
     END-EXEC

     move "select * from dept" into statement

     move 20 to sqln
    $IF P64 SET
     compute sqldabc = 16 + 56 * sqln
    $ELSE
     compute sqldabc = 16 + 44 * sqln
    $END
     
     EXEC SQL
         PREPARE stmt1 FROM :statement
     END-EXEC
     EXEC SQL 
         DESCRIBE stmt1 INTO :sqlda 
     END-EXEC

* The data structure "sqlda" now contains a description 
* of the dynamic SQL statement.
     EXEC SQL 
         OPEN C1 
     END-EXEC

* Complete the SQLDA, by adding buffer addresses and lengths

     EXEC SQL
         FETCH C1 USING DESCRIPTOR :sqlda
     END-EXEC
Previous Topic Next topic Print topic