INFORMIX-ESQL/C Programmer's Manual Determining SQL Statements

This section provides a brief introduction to the DESCRIBE statement. For more information about the DESCRIBE statement, refer to its entry in the Informix Guide to SQL: Syntax.

The DESCRIBE statement obtains information about database columns or expressions in a prepared statement. It can put this information in one of the following dynamic-management structures:

• DESCRIBE...USING SQL DESCRIPTOR stores information in a system-descriptor area.

Each item descriptor describes a column. The COUNT field is set to the number of item descriptors (the number of columns in the column list). You can access this information with the GET DESCRIPTOR statement. For more information about the fields of a system-descriptor area, see Figures 15-1 through 15-3.

• DESCRIBE...INTO sqlda_ptr stores information in an sqlda structure whose address is stored in sqlda_ptr.

Each sqlvar_struct structure describes a column. The sqld field is set to the number of sqlvar_struct structures (the number of columns in the column list). You can access this information through the fields in the sqlvar_struct structures. For more information about the fields of an sqlda structure, see Figures 15-4 through 15-8.

Important: If the Deferred-PREPARE feature is enabled, you cannot use the DESCRIBE statement before an OPEN statement executes.

For more information, see Deferring Execution of the PREPARE Statement.

If the DESCRIBE is successful, it obtains the following information about a prepared statement:

• The SQLCODE value indicates the type of statement that was prepared. For more information, see Determining the Statement Type.
• A dynamic-management structure contains information about the number and data types of the columns in a column list of a SELECT, INSERT, or EXECUTE FUNCTION statement.

For information on the column descriptions returned by DESCRIBE, see Handling an Unknown Select List and Handling an Unknown Column List on page 15-26 and page 15-27, respectively. For information on the data type values returned by DESCRIBE, see Determining the Data Type of a Column.

• When the DESCRIBE statement describes a DELETE or UPDATE statement, it can indicate whether the statement includes a WHERE clause. For more information, see Checking for a WHERE Clause.

The sqlstype.h file contains the defined integer constants for the SQL statements that can be prepared. The DESCRIBE statement returns one of these values in the SQLCODE (sqlca.sqlcode) variable to identify a prepared statement. That is, SQLCODE indicates whether the statement was an INSERT, SELECT, CREATE TABLE, or any other SQL statement.

Within an ESQL/C program that uses dynamic SQL statements, you can use the constants that Figure 15-9 shows to determine which SQL statement was prepared.

Tip: Check the sqlstype.h header file on your system for the most updated list of SQL statement-type values.

To determine the type of SQL statement that was prepared dynamically, your ESQL/C program must take the following actions:

• Use the include directive to include the sqlstype.h header file.
• Compare the value in the SQLCODE variable (sqlca.sqlcode) against the constants defined in the sqlstype.h file.

The sample program that starts on page 16-25 uses the SQ_EXECPROC constant to verify that an EXECUTE FUNCTION statement has been prepared.

The DESCRIBE statement identifies the data type of a column with an integer value. After DESCRIBE analyzes a prepared statement, it stores this value in a dynamic-management structure, as follows:

• In a system-descriptor area, in the TYPE field of the item descriptor for each column described
• In an sqlda structure, in the sqltype field of the sqlvar_struct structure for each column described

ESQL/C provides defined constants for these data types in the following two header files:

• The sqltypes.h header file contains defined constants for the Informix-specific SQL data types. These values are the default that the DESCRIBE statement uses.
• The sqlxtype.h header file contains defined constants for the X/Open SQL data types. DESCRIBE uses these values when you compile your ESQL/C source file with the -xopen option of the preprocessor.

Use the SQL data-type constants from sqltypes.h or sqlxtype.h to analyze the information returned by a DESCRIBE statement or to set the data type of a column before execution.

Tip: When you set the data type of a column in a system-descriptor area, you assign a data-type constant to the TYPE field (and optionally the ITYPE field) of an item descriptor with the SET DESCRIPTOR statement. For more information, see Assigning and Obtaining Values from a System-Descriptor Area. When you set the data type of a column in an sqlda structure, you assign a data-type constant to the sqltype field (and optionally the sqlitype field) of an sqlvar structure. For more information, see Assigning and Obtaining Values from an sqlda Structure.

Informix-Specific SQL Data Types

The Informix-specific SQL data types are available to a column in an Informix database. The Informix Guide to SQL: Reference describes these data types. If you do not include the -xopen option when you compile your ESQL/C program, the DESCRIBE statement uses these data types to specify the data type of a column or the return value of a user-defined function. Constants for these Informix SQL data types are defined in the ESQL/C sqltypes.h header file.

Figure 15-10 shows some of the SQL data-type entries in sqltypes.h.

#define SQLCHAR						0

#define SQLSMINT						1

#define SQLINT						2

#define SQLFLOAT						3

#define SQLSMFLOAT						4

#define SQLDECIMAL						5

#define SQLSERIAL						6

#define SQLDATE						7

#define SQLMONEY						8

For a complete list of constants for SQL data types, see Figure 3-4 on page 3-8. The integer values in Figure 15-10 are language-independent constants; they are the same in all Informix embedded products.

The X/Open standards support only a subset of the Informix-specific SQL data types. To conform to the X/Open standards, you must use the X/Open SQL data-type constants. The DESCRIBE statement uses these constants to specify the data type of a column (or a return value) when you compile your ESQL/C program with the -xopen option.

The X/Open data type constants are defined in the sqlxtype.h header file. For more information, see X/Open Data Type Constants.

The sqltypes.h header file also contains defined constants for the ESQL/C data types. The ESQL/C data types are assigned to host variables in an ESQL/C program. If your program initializes a column description, it usually obtains the column value from an ESQL/C host variable. To set the column data type for this value, the program must use the ESQL/C data types.

Figure 15-11 shows only some of the ESQL/C data type entries in the sqltypes.h header file. For a complete list of constants for ESQL/C data types, see Figure 3-4 on page 3-8.

#define CCHARTYPE						100

#define CSHORTTYPE						101

#define CINTTYPE						102

#define CLONGTYPE						103

#define CFLOATTYPE						104

#define CDOUBLETYPE						105

Within an ESQL/C program that uses dynamic SQL statements, you can use the constants that are shown in Figure 15-11 to set the data types of the associated host variables. Use the ESQL/C data-type constants to set the data types of host variables used as input parameters to a dynamically defined SQL statement or as storage for column values that are returned by the database server. The sample program on page 16-34 stores a TEXT value into a database table.

When DESCRIBE analyzes a prepared DELETE or UPDATE statement, it indicates if the statement includes a WHERE clause, as follows:

• It sets the sqlca.sqlwarn.sqlwarn0 and sqlca.sqlwarn.sqlwarn4 fields to W if the prepared statement was an UPDATE or DELETE without a WHERE clause.
• It sets the SQLSTATE variable to an Informix-specific warning value of "01I07".

Your program can check for either of these conditions to determine the type of DELETE or UPDATE statement that was executed. If the DELETE or UPDATE does not contain a WHERE clause, the database server deletes or updates all rows in the table. For more information about how to execute DELETE and UPDATE statements dynamically with a system-descriptor area, see page 16-52; for information on how to use an sqlda structure, see page 17-46.