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 FOR 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 accommodate.
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:

      $set sql(behavior=optimized)
       working-storage section.
       EXEC SQL INCLUDE SQLCA END-EXEC.
       EXEC SQL INCLUDE SQLDA78 END-EXEC.
       EXEC SQL BEGIN DECLARE SECTION END-EXEC
       01 statement   pic x(80).
       01 host-var-block.
          03 host-var-1   pic 99.
          03 host-var-2   pic x(10).
          03 host-var-3   pic x(15).
       EXEC SQL END DECLARE SECTION END-EXEC

       PROCEDURE DIVISION.

       EXEC SQL CONNECT TO ORCL USER scott.tiger END-EXEC

       EXEC SQL
           DECLARE C1 CURSOR FOR stmt1
       END-EXEC

       move "select * from dept" to 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
      * and changeing types, as necessary and appropriate, to
      * to match host variables actually used.
      *
      * The following SQL directives can reduce the amount of effort
      * required by specifying how OpenESQL should DESCRIBE varchar
      * and date/time SQL data types:
      *    DESCRIBEVARCHARPICX
      *    DESCRIBEVARCHAR49
      *    DESCRIBEDTCHAR
      *    DESCRIBEDTREC

       move ESQL-UDISP-UNSIGN to sqltype(1)
       set sqldata(1) to address of host-var-1
       set sqldata(2) to address of host-var-2
       set sqldata(3) to address of host-var-3

       perform until exit
           EXEC SQL
               FETCH C1 USING DESCRIPTOR :sqlda
           END-EXEC
           if sqlerrd(3) not = 1
               exit perform
           end-if
           display host-var-1 ' ' host-var-2 ' ' host-var-3

       end-perform
       goback.