INFORMIX
Informix-ESQL/C Programmer's Manual
Chapter 15: Determining SQL Statements
Home Contents Index Master Index New Book

Using Dynamic-Management Structures

 If you do not know the number or data types of values sent to or received from the database server, use a dynamic-management structure. A dynamic-management structure allows you to pass a variable-length list of data to the database server, or receive a variable-length list of data from it.

To execute dynamic SQL statements with unknown columns, you can use either of the following dynamic-management structures in your ESQL/C program:

    Because this method uses a C-language structure within SQL statements, it is language-dependent and does not conform to X/Open standards.

For a given dynamic SQL statement, the dynamic-management structure can hold any of the following information:

The ESQL/C program can then use this information to determine a host variable of appropriate length and type to hold the value.

A System-Descriptor Area

A system-descriptor area is an area of memory declared by ESQL/C to
hold data either returned from or sent by a prepared statement. It is the dynamic-management structure that conforms to the X/Open standards.

A system-descriptor area has two parts:

Figure 15-1 shows what a system-descriptor area looks like for two values.

Figure 15-1
Schematic That Shows
System-Descriptor Area for Two Values

Fixed-Length Portion

The fixed-size portion of the system-descriptor area consists of the single field, which Figure 15-2 shows.

Figure 15-2
Field in the Fixed-Size Portion of a System-Descriptor Area

Field Data Type Description

COUNT

short

The number of column values or occurrences in the system-descriptor area. This is the number of item descriptors, one for each column. The DESCRIBE...USING SQL DESCRIPTOR statement sets COUNT to the number of described columns. You must use SET DESCRIPTOR to initialize the field before you send column values to the database server.

An Item Descriptor

Each item descriptor in the system-descriptor area holds information about a data value that can be sent to or received from the database server. Each item descriptor consists of the fields that Figure 15-3 summarizes.

Figure 15-3
Fields in Each Item Descriptor of the System-Descriptor Area

(1 of 3)

Field Data Type Description

DATA

char *

A pointer to the column data that is to be sent to or received from the database server.

TYPE

short

An integer that identifies the data type of the column that is being transferred. These values are defined in the sqltypes.h and sqlxtype.h header files (see page 15-20).

LENGTH

short

The length, in bytes, of CHAR type data, the encoded qualifiers of DATETIME or INTERVAL data, or the size of a DECIMAL or MONEY value.

NAME

char *

A pointer to the character array that contains the column name or display label that is being transferred.

INDICATOR

short

An indicator variable that can contain one of two values:

0: The DATA field contains non-null data.

-1: The DATA field contains null data.

SCALE

short

Contains the scale of the column that is in the DATA field; defined only for the DECIMAL or MONEY data type.

PRECISION

short

Contains the precision of the column that is in the DATA field; defined only for the DECIMAL or MONEY data type.

NULLABLE

short

Specifies whether the column can contain a null value (after a DESCRIBE statement):

1: The column allows null values

0: The column does not allow null values.

Before an EXECUTE statement or a dynamic OPEN statement is executed, it must be set to 1 to indicate that an indicator value is specified in the INDICATOR field, and to 0 if it is not specified. (When you execute a dynamic FETCH statement, the NULLABLE field is ignored.)

IDATA

char *

User-defined indicator data or the name of a host variable that contains indicator data for the DATA field. The IDATA field is not a standard X/Open field.

ITYPE

short

The data type for a user-defined indicator variable. These values are defined in the sqltypes.h and sqlxtype.h header files. (See page 15-20.) The ITYPE field is not a standard X/Open field.

ILENGTH

short

The length, in bytes, of the user-defined indicator. The ILENGTH field is not a standard X/Open field.

EXTYPEID

long

The extended identifier for the user-defined (opaque or distinct) or complex (collection or row) data type. Refer to the Informix Guide to SQL: Reference for a description of the extended_id column of the sysxtdtypes system catalog table.

EXTYPENAME

char *

The name of the user-defined (opaque or distinct) or complex (collection or row) data type. Refer to the Informix Guide to SQL: Reference for a description of the name column of the sysxtdtypes system catalog table.

EXTYPELENGTH

short

The length, in bytes, of the string in the EXTYPENAME field.

EXTYPEOWNERNAME

char *

The name of the owner (for ANSI databases) of the user-defined (opaque or distinct) or complex (collection or row) data type. Refer to the Informix Guide to SQL: Reference for a description of the owner column of the sysxtdtypes system catalog.

EXTYPEOWNERLENGTH

short

The length, in bytes, of the string in the EXTYPEOWNERNAME field.

SOURCETYPE

short

The data-type constant (from sqltypes.h) of the source data type for a distinct-type column. Refer to the Informix Guide to SQL: Reference for a description of the type column of the sysxtdtypes system catalog.

SOURCEID

long

The extended identifier of the source data type for a distinct-type column. Refer to the Informix Guide to SQL: Reference for a description of the source column of the sysxtdtypes system catalog.

For information on how to use a system-descriptor area to dynamically execute SQL statements, see Chapter 16, "Using a System-Descriptor Area."

An sqlda Structure

The sqlda structure is a C structure (defined in the sqlda.h header file) that holds data returned from a prepared statement. Each sqlda structure has three parts:

Figure 15-4 shows what an sqlda structure looks like for two values.

Figure 15-4
Schematic That Shows sqlda Structure for Two Values

Figure 15-4 shows the column data in the sqldata fields in a single data buffer. This data could also be stored in separate buffers. For more information, see "Allocating Memory for Column Data".

Fixed-Length Portion

Figure 15-5 describes the fixed-size portion of the sqlda structure, which consists of a single field.

Figure 15-5
Field in the Fixed-Size Portion of an sqlda Structure

Field Data Type Description

sqld

short

The number of column values or occurrences in the sqlda structure. This is the number of sqlvar_struct structures, one for each column. The DESCRIBE...INTO statement sets sqld to the number of described columns. You must set sqld to initialize the field before you send column values to the database server.

An sqlvar_struct Structure

When all of its components are fully defined, the sqlda structure points to the initial address of a sequence of sqlvar_struct structures that contain the necessary information for each variable in the set. Each sqlvar_struct structure holds a data value that can be sent to or received from the database server. Your program accesses these sqlvar_struct structures through the sqlvar field of sqlda. Figure 15-6 and Figure 15-7 summarize the variable-sized structure of sqlda.

Figure 15-6
Field to Access the Variable-Sized Portion of an sqlda Structure

Field Data Type Description

sqlvar

struct
sqlvar_struct *

A pointer to the variable-sized portion of an sqlda structure. There is one sqlvar_struct for each column value returned from or sent to the database server. The sqlvar field points to the first of the sqlvar_struct structures.

Figure 15-7 shows the fields in the sqlvar_struct structure.

Figure 15-7
Fields in the sqlvar_struct Structure

(1 of 2)

Field Data Type Description

sqltype

short

An integer that identifies the data type of the column that the database server sends or receives. These values are defined in the sqltypes.h andsqlxtype.h header files. (See page 15-20.)

sqllen

short

The length, in bytes, of CHAR type data, or the encoded qualifier of a DATETIME or INTERVAL value.

sqldata

char *

A pointer to the column data that the database server sends or receives. (See page 17-10.)

sqlind

short *

A pointer to an indicator variable for the column that can contain one of two values:

0: The sqldata field contains non-null data.

-1: The sqldata field contains null data.

sqlname

char *

A pointer to a character array that contains the column name or display label that the database server sends or receives.

sqlformat

char *

Reserved for future use.

sqlitype

short

An integer that specifies the data type of a user-defined indicator variable. These values are defined in the sqltypes.h and sqlxtype.h header files. (See page 15-20.)

sqlilen

short

The length, in bytes, of a user-defined indicator variable.

sqlidata

char *

A pointer to the data of the user-defined indicator variable.

sqlxid

long

The extended identifier for the user-defined (opaque or distinct) or complex (collection or row) data type. Refer to the Informix Guide to SQL: Reference for a description of the extended_id column of the sysxtdtypes system catalog table.

sqltypename

char *

The name of the user-defined (opaque or distinct) or complex (collection or row) data type. Refer to the Informix Guide to SQL: Reference for a description of the name column of the sysxtdtypes system catalog table.

sqltypelen

short

The length, in bytes, of the string in the sqltypename field.

sqlownername

char *

The name of the owner (for ANSI databases) of the user-defined (opaque or distinct) or complex (collection or row) data type. Refer to the Informix Guide to SQL: Reference for a description of the owner column of the sysxtdtypes system catalog.

sqlownerlen

short

The length, in bytes, of the string in the sqlownername field.

sqlsourcetype

short

The data-type constant (from sqltypes.h) of the source data type for a distinct-type column. Refer to the Informix Guide to SQL: Reference for a description of the type column of the sysxtdtypes system catalog.

sqlsourceid

long

The extended identifier of the source data type for a distinct-type column. Refer to the Informix Guide to SQL: Reference for a description of the source column of the sysxtdtypes system catalog.

sqlflags

long

For internal use only

Descriptive Information

Figure 15-8 summarizes the sqlda fields that describe the sqlda structure itself.

Figure 15-8
Descriptive Fields in the sqlda Structure

Field Data Type Description

desc_name

char[19]

The name of the descriptor; maximum of 18 characters

desc_occ

short

The size of the sqlda structure

desc_next

struct sqlda *

A pointer to the next sqlda structure

For information on how to use an sqlda structure to dynamically execute SQL statements, see Chapter 17, "Using an sqlda Structure."



Informix-ESQL/C Programmer's Manual, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.