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 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-24 and page 15-25, 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".

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.

Figure 15-9

(1 of 3)

SQL Statement	Defined sqlstype.h Constant	Value
SELECT (no INTO TEMP clause)	None	0
DATABASE	SQ_DATABASE	1
	Internal use only	2
SELECT INTO TEMP	SQ_SELINTO	3
UPDATE...WHERE	SQ_UPDATE	4
DELETE...WHERE	SQ_DELETE	5
INSERT	SQ_INSERT	6
UPDATE WHERE CURRENT OF	SQ_UPDCURR	7
DELETE WHERE CURRENT OF	SQ_DELCURR	8
	Internal use only	9
LOCK TABLE	SQ_LOCK	10
UNLOCK TABLE	SQ_UNLOCK	11
CREATE DATABASE	SQ_CREADB	12
DROP DATABASE	SQ_DROPDB	13
CREATE TABLE	SQ_CRETAB	14
DROP TABLE	SQ_DRPTAB	15
CREATE INDEX	SQ_CREIDX	16
DROP INDEX	SQ_DRPIDX	17
GRANT	SQ_GRANT	18
REVOKE	SQ_REVOKE	19
RENAME TABLE	SQ_RENTAB	20
RENAME COLUMN	SQ_RENCOL	21
CREATE AUDIT	SQ_CREAUD	22
	Internal use only	23-28
ALTER TABLE	SQ_ALTER	29
UPDATE STATISTICS	SQ_STATS	30
CLOSE DATABASE	SQ_CLSDB	31
DELETE (no WHERE clause)	SQ_DELALL	32
UPDATE (no WHERE clause)	SQ_UPDALL	33
BEGIN WORK	SQ_BEGWORK	34
COMMIT WORK	SQ_COMMIT	35
ROLLBACK WORK	SQ_ROLLBACK	36
	Internal use only	37-39
CREATE VIEW	SQ_CREVIEW	40
DROP VIEW	SQ_DROPVIEW	41
	Internal use only	42
CREATE SYNONYM	SQ_CREASYN	43
DROP SYNONYM	SQ_DROPSYN	44
CREATE TEMP TABLE	SQ_CTEMP	45
SET LOCK MODE	SQ_WAITFOR	46
ALTER INDEX	SQ_ALTIDX	47
SET ISOLATION, SET TRANSACTION	SQ_ISOLATE	48
SET LOG	SQ_SETLOG	49
SET EXPLAIN	SQ_EXPLAIN	50
CREATE SCHEMA	SQ_SCHEMA	51
SET OPTIMIZATION	SQ_OPTIM	52
CREATE PROCEDURE	SQ_CREPROC	53
DROP PROCEDURE	SQ_DRPPROC	54
SET CONSTRAINTS	SQ_CONSTRMODE	55
EXECUTE PROCEDURE, EXECUTE FUNCTION	SQ_EXECPROC	56
SET DEBUG FILE TO	SQ_DBGFILE	57
CREATE OPTICAL CLUSTER	SQ_CREOPCL	58
ALTER OPTICAL CLUSTER	SQ_ALTOPCL	59
DROP OPTICAL CLUSTER	SQ_DRPOPCL	60
RESERVE (Optical)	SQ_OPRESERVE	61
RELEASE (Optical)	SQ_OPRELEASE	62
SET MOUNTING TIMEOUT	SQ_OPTIMEOUT	63
UPDATE STATS...for procedure	SQ_PROCSTATS	64
	Defined for Kanji version only	65 and 66
	Reserved	67-69
CREATE TRIGGER	SQ_CRETRIG	70
DROP TRIGGER	SQ_DRPTRIG	71
	SQ_UNKNOWN	72
SET DATASKIP	SQ_SETDATASKIP	73
SET PDQPRIORITY	SQ_PDQPRIORITY	74
ALTER FRAGMENT	SQ_ALTFRAG	75
SET	SQ_SETOBJMODE	76
START VIOLATIONS TABLE	SQ_START	77
STOP VIOLATIONS TABLE	SQ_STOP	78
	Internal use only	79
SET SESSION AUTHORIZATION	SQ_SETDAC	80
	Internal use only	81-82
CREATE ROLE	SQ_CREATEROLE	83
DROP ROLE	SQ_DROPROLE	84
SET ROLE	SQ_SETROLE	85
	Internal use only	86-89
CREATE ROW TYPE	SQ_CREANRT	90
DROP ROW TYPE	SQ_DROPNRT	91
CREATE DISTINCT TYPE	SQ_CREADT	92
CREATE CAST	SQ_CREACT	93
DROP CAST	SQ_DROPCT	94
CREATE OPAQUE TYPE	SQ_CREABT	95
DROP TYPE	SQ_DROPTYPE	96
	Reserved	97
CREATE ACCESS_METHOD	SQ_CREATEAM	98
DROP ACCESS_METHOD	SQ_DROPAM	99
	Reserved	100
CREATE OPCLASS	SQ_CREATEOPC	101
DROP OPCLASS	SQ_DROPOPC	102

The Constants for SQL Statement Types That the sqlstype.h File Defines

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 has been 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.

Determining the Data Type of a Column

• 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

• The sqltypes.h header file contains defined constants for the Informix-specific SQL data types. These values are the default that is used by the DESCRIBE statement.

  X/O

• 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.

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

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 . . .

Figure 15-10 Some Informix SQL Data-Type Constants

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

X/O

X/Open SQL Data Types

#define SQLCHAR 						1

#define SQLDECIMAL						3

#define SQLINT						4

#define SQLSMINT						5

#define SQLFLOAT						6

The DESCRIBE statement uses these data types to specify the data type of a column (or a return value) when you include the -xopen option to compile your ESQL/C program.

Constants for ESQL/C Data Types

Figure 15-12 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 2-5.

#define CCHARTYPE 100 #define CSHORTTYPE 101 #define CINTTYPE 102 #define CLONGTYPE 103 #define CFLOATTYPE 104 #define CDOUBLETYPE 105 . . .

Figure 15-12 Some ESQL/C Data-Type Constants from the sqltypes.h Header File

Within an ESQL/C program that uses dynamic SQL statements, you can use the constants that are shown in Figure 15-12 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-31 stores a TEXT value into a database table.

Checking for a WHERE Clause

• 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".