Informix Guide to SQL: Syntax SQL Statements

Use the DESCRIBE statement to obtain information about a prepared statement before you execute it.

Use this statement with ESQL/C.

The DESCRIBE statement allows you to determine, at runtime, the following information about a prepared statement:

• The DESCRIBE statement returns the prepared statement type.
• The DESCRIBE statement can determine whether an UPDATE or DELETE statement contains a WHERE clause.
• For a SELECT, EXECUTE FUNCTION (or EXECUTE PROCEDURE), INSERT, or UPDATE statement, the DESCRIBE statement also returns the number, data types and size of the values, and the name of the column or expression that the query returns.
• For a SELECT, EXECUTE PROCEDURE, or INSERT statement, the DESCRIBE statement also returns the number, data types and size of the values, and the name of the column or expression that the query returns.

With this information, you can write code to allocate memory to hold retrieved values and display or process them after they are fetched.

The DESCRIBE statement takes a statement identifier from a PREPARE statement as input. When the DESCRIBE statement executes, the database server sets the value of the SQLCODE field of the sqlca to indicate the statement type (that is, the keyword with which the statement begins). If the prepared statement text contains more than one SQL statement, the DESCRIBE statement returns the type of the first statement in the text.

SQLCODE is set to zero to indicate a SELECT statement without an INTO TEMP clause. This situation is the most common. For any other SQL statement, SQLCODE is set to a positive integer.

You can test the number against the constant names that are defined. In ESQL/C, the constant names are defined in the sqlstype.h header file.

The DESCRIBE statement uses the SQLCODE field differently from any other statement, possibly returning a nonzero value when it executes successfully. You can revise standard error-checking routines to accommodate this behavior, if desired.

If the DESCRIBE statement detects that a prepared statement contains an UPDATE or DELETE statement without a WHERE clause, the DESCRIBE statement sets the sqlca.sqlwarn.sqlwarn4 variable to W.

When you do not specify a WHERE clause in either a DELETE or UPDATE statement, the database server performs the delete or update action on the entire table. Check the sqlca.sqlwarn.sqlwarn4 variable to avoid unintended global changes to your table.

If the prepared statement contains parameters for which the number of parameters or parameter data types is to be supplied at runtime, you can describe these input values. If the prepared statement text includes one of the following statements, the DESCRIBE statement returns a description of each column or expression that is included in the list:

• EXECUTE FUNCTION (or EXECUTE PROCEDURE)
• INSERT
• SELECT (without an INTO TEMP clause)
• UPDATE

The IFX_UPDDESC environment variable must be set before you can DESCRIBE an UPDATE statement. For more information, see the Informix Guide to SQL: Reference.

The description includes the following information:

• The data type of the column, as defined in the table
• The length of the column, in bytes
• The name of the column or expression

If the prepared statement is an INSERT or an UPDATE statement, the DESCRIBE statement returns only the dynamic parameters in that statement, that is, only those parameters that are expressed with a question mark (?).

You can store descriptions in a system-descriptor area or in a pointer to an sqlda structure.

Use the USING SQL DESCRIPTOR clause to store the description of a statement list in a previously allocated system-descriptor area.

A system-descriptor area conforms to the X/Open standards.

When you describe one of the previously mentioned statements into a system descriptor area, the database server updates the system-descriptor area in the following ways:

• It sets the COUNT field in the system-descriptor area to the number of values in the statement list. If COUNT is greater than the number of item descriptors in the system-descriptor area, the system returns an error.
• It sets the TYPE, LENGTH, NAME, SCALE, PRECISION, and NULLABLE fields in system-descriptor area.

If the column has an opaque data type, the database server sets the EXTYPEID, EXTYPENAME, EXTYPELENGTH, EXTYPEOWNERLENGTH, and EXTYPEOWNERNAME fields of the item descriptor.

• It allocates memory for the DATA field for each item descriptor, based on the TYPE and LENGTH information.

After a DESCRIBE statement is executed, the SCALE and PRECISION fields contain the scale and precision of the column, respectively. If SCALE and PRECISION are set in the SET DESCRIPTOR statement, and TYPE is set to DECIMAL or MONEY, the LENGTH field is modified to adjust for the scale and precision of the decimal value. If TYPE is not set to DECIMAL or MONEY, the values for SCALE and PRECISION are not set, and LENGTH is unaffected.

You can modify the system-descriptor-area information with the SET DESCRIPTOR statement. You must modify the system-descriptor area to show the address in memory that is to receive the described value. You can change the data type to another compatible type. This change causes data conversion to take place when the data is fetched.

You can use the system-descriptor area in statements that support a USING SQL DESCRIPTOR clause, such as EXECUTE, FETCH, OPEN, and PUT.

The following examples show the use of a system descriptor in a DESCRIBE statement. In the first example, the descriptor is a quoted string; in the second example, it is an embedded variable name.

main()

{

. . .

EXEC SQL allocate descriptor 'desc1' with max 3;

EXEC SQL prepare curs1 FROM 'select * from tab';

EXEC SQL describe curs1 using sql descriptor 'desc1';

}





EXEC SQL describe curs1 using sql descriptor :desc1var;

Use the INTO sqlda_pointer clause to allocate memory for an sqlda structure, and store its address in an sqlda pointer. The DESCRIBE statement fills in the allocated memory with descriptive information.

The DESCRIBE statement sets the sqlda.sqld field to the number of values in the statement list. The sqlda structure also contains an array of data descriptors (sqlvar structures), one for each value in the statement list. After a DESCRIBE statement is executed, the sqlda.sqlvar structure has the sqltype, sqllen, and sqlname fields set.

If the column has an opaque data type, DESCRIBE...INTO sets the sqlxid, sqltypename, sqltypelen, sqlownerlen, and sqlownername fields of the item descriptor.

The DESCRIBE statement allocates memory for an sqlda pointer once it is declared in a program. However, the application program must designate the storage area of the sqlda.sqlvar.sqldata fields.

The DESCRIBE statement can provide information about a collection variable when you use the USING SQL DESCRIPTOR or INTO clause.

You must perform the DESCRIBE statement after you open the select or insert cursor. Otherwise, DESCRIBE cannot get information about the collection variable because it is the OPEN...USING statement that specifies the name of the collection variable to use.

The following ESQL/C code fragment shows how to dynamically select the elements of the :a_set collection variable into a system-descriptor area called desc1:

EXEC SQL BEGIN DECLARE SECTION;
client collection a_set;
int i, set_count;
int element_type, element_value;
EXEC SQL END DECLARE SECTION;

EXEC SQL allocate collection :a_set;
EXEC SQL allocate descriptor 'desc1';

EXEC SQL select set_col into :a_set from table1;

EXEC SQL prepare set_id from
'select * from table(?)'
EXEC SQL declare set_curs cursor for set_id;
EXEC SQL open set_curs using :a_set;
EXEC SQL describe set_id using sql descriptor 'desc1';

do
{
EXEC SQL fetch set_curs using sql descriptor 'desc1';
...
EXEC SQL get descriptor 'desc1' :set_count = count;
for (i = 1; i <= set_count; i++)
{
EXEC SQL get descriptor 'desc1' value :i
:element_type = TYPE;
switch
{

case SQLINTEGER:
EXEC SQL get descriptor 'desc1' value :i
:element_value = data;
...
} /* end switch */
} /* end for */
} while (SQLCODE == 0);

EXEC SQL close set_curs;
EXEC SQL free set_curs;
EXEC SQL free set_id;

EXEC SQL deallocate collection :a_set;
EXEC SQL deallocate descriptor 'desc1';

Related statements: ALLOCATE DESCRIPTOR, DEALLOCATE DESCRIPTOR, DECLARE, EXECUTE, FETCH, GET DESCRIPTOR, OPEN, PREPARE, PUT, and SET DESCRIPTOR

For a task-oriented discussion of the DESCRIBE statement, see the Informix Guide to SQL: Tutorial.

For more information about how to use a system-descriptor area and sqlda, refer to the Informix ESQL/C Programmer's Manual.