Informix Guide to SQL: Syntax SQL Statements

Use the SET DESCRIPTOR statement to assign values to a system-descriptor area.

Use this statement with ESQL/C.

The SET DESCRIPTOR statement can be used after you have described SELECT, EXECUTE FUNCTION (or EXECUTE PROCEDURE), and INSERT statements with the DESCRIBE...USING SQL DESCRIPTOR statement. The SET DESCRIPTOR statement can assign values to a system-descriptor area in the following instances:

• To set the COUNT field of a system-descriptor area to match the number of items for which you are providing descriptions in the system-descriptor area
• To set the item descriptor for each value for which you are providing descriptions in the system-descriptor area
• To modify the contents of an item-descriptor field

If an error occurs during the assignment to any identified system-descriptor fields, the contents of all identified fields are set to 0 or null, depending on the variable type.

Use the COUNT clause to set the number of items that are to be used in the system-descriptor area.

If you allocate a system-descriptor area with more items than you are using, you need to set the COUNT field to the number of items that you are actually using. The following example shows the sequence of statements in ESQL/C that can be used in a program:

EXEC SQL BEGIN DECLARE SECTION;
int count;
EXEC SQL END DECLARE SECTION;

EXEC SQL allocate descriptor 'desc_100'; /*allocates for 100 items*/

count = 2;
EXEC SQL set descriptor 'desc_100' count = :count;

Use the VALUE clause to assign values from host variables into fields of a system-descriptor area. You can assign values for items for which you are providing a description (such as parameters in a WHERE clause), or you can modify values for items after you use a DESCRIBE statement to fill the fields for a SELECT or INSERT statement.

Use the Item Descriptor portion of the SET DESCRIPTOR statement to set value for a particular field in a system-descriptor area.

Use the following integer constants to set the value of TYPE for each item.

The following table includes additional integer constants that represent data types available with the Dynamic Server.

These TYPE constants are the same values that appear in the coltype column in the syscolumns system catalog table.

For code that is easier to maintain, use the predefined constants for these SQL data types instead of their actual integer values. These constants are defined in the sqltypes.h header file. However, you cannot use the actual constant name in the SET DESCRIPTOR statement. Instead, assign the constant to an integer host variable and specify the host variable in the SET DESCRIPTOR statement file.

The following example shows how you can set the TYPE field in ESQL/C:

main()

{

EXEC SQL BEGIN DECLARE SECTION;

	int itemno, type;

EXEC SQL END DECLARE SECTION;

...

EXEC SQL allocate descriptor 'desc1' with max 5;

...

type = SQLINT; itemno = 3;

EXEC SQL set descriptor 'desc1' value :itemno type = :type;

}

This information is identical for ITYPE. Use ITYPE when you create a dynamic program that does not comply with the X/Open standard.

If you compile without the -xopen option, the regular Informix SQL code is assigned for TYPE. You must be careful not to mix normal and X/Open modes because errors can result. For example, if a particular type is not defined under X/Open mode but is defined under normal mode, executing a SET DESCRIPTOR statement can result in an error.

Setting the TYPE Field in X/Open Programs

In X/Open mode, you must use the X/Open set of integer codes for the data type in the TYPE field.

If you use the ILENGTH, IDATA, or ITYPE fields in a SET DESCRIPTOR statement, a warning message appears. The warning indicates that these fields are not standard X/Open fields for a system-descriptor area.

For code that is easier to maintain, use the predefined constants for these X/Open SQL data types instead of their actual integer value. These constants are defined in the sqlxtype.h header file.

If you set the TYPE field for a DECIMAL or MONEY data type, and you want to use a scale or precision other than the default values, set the SCALE and PRECISION fields. You do not need to set the LENGTH field for a DECIMAL or MONEY item; the LENGTH field is set accordingly from the SCALE and PRECISION fields.

If you set the TYPE field for a DATETIME or INTERVAL value, you can set the DATA field as a literal DATETIME or INTERVAL or as a character string. If you use a character string, you must set the LENGTH field to the encoded qualifier value.

To determine the encoded qualifiers for a DATETIME or INTERVAL character string, use the datetime and interval macros in the datetime.h header file.

If you set DATA to a host variable of DATETIME or INTERVAL, you do not need to set LENGTH explicitly to the encoded qualifier integer.

When you set the DATA or IDATA field, you must provide the appropriate type of data (character string for CHAR or VARCHAR, integer for INTEGER, and so on).

When any value other than DATA is set, the value of DATA is undefined. You cannot set the DATA or IDATA field for an item without setting TYPE for that item. If you set the TYPE field for an item to a character type, you must also set the LENGTH field. If you do not set the LENGTH field for a character item, you receive an error.

If your DATA or IDATA field contains a character string, you must specify a value for LENGTH. If you specify LENGTH=0, LENGTH sets automatically to the maximum length of the string. The DATA or IDATA field can contain a 368-literal character string or a character string derived from a character variable of CHAR or VARCHAR data type. This provides a method to determine the length of a string in the DATA or IDATA field dynamically.

If a DESCRIBE statement precedes a SET DESCRIPTOR statement, LENGTH is automatically set to the maximum length of the character field that is specified in your table.

This information is identical for ILENGTH. Use ILENGTH when you create a dynamic program that does not comply with the X/Open standard.

If you want to put a null value into the system-descriptor area, set the INDICATOR field to -1, and do not set the DATA field.

If you set the INDICATOR field to 0 to indicate that the data is not null, you must set the DATA field.

The following item-descriptor fields provide information about a column that has an opaque type as its data type:

• The EXTYPEID field stores the extended identifier for the opaque type.

This integer value must correspond to a value in the extended_id column of the sysxtdtypes system catalog table.

• The EXTYPENAME field stores the name of the opaque type.

This character value must correspond to a value in the name column of the row with the matching extended_id value in the sysxtdtypes system catalog table.

• The EXTYPELENGTH field stores the length of the opaque-type name.

This integer value is the length, in bytes, of the string in the EXTYPENAME field.

• The EXTYPEOWNERNAME field stores the name of the opaque-type owner.

This character value must correspond to a value in the owner column of the row with the matching extended_id value in the sysxtdtypes system catalog table.

• The EXTYPEOWNERLENGTH field stores the length of the value in the EXTTYPEOWNERNAME field.

This integer value is the length, in bytes, of the string in the EXTYPEOWNERNAME field.

For more information on the sysxtdtypes system catalog table, see the Informix Guide to SQL: Reference.

The following item-descriptor fields provide information about a column that has an distinct type as its data type:

• The SOURCEID field stores the extended identifier for the source data type.

Set this field if the source type of the distinct type is an opaque data type. This integer value must correspond to a value in the source column for the row of the sysxtdtypes system catalog table whose extended_id value matches that of the distinct type you are setting.

• The SOURCETYPE field stores the data-type constant for the source data type.

This value is the data-type constant for the built-in data type that is the source type for the distinct type. The codes for the SOURCETYPE field are the same as those for the TYPE field (page 2-724). This integer value must correspond to the value in the type column for the row of the sysxtdtypes system catalog table whose extended_id value matches that of the distinct type you are setting.

For more information on the sysxtdtypes system catalog table, see the Informix Guide to SQL: Reference.

You can use a DESCRIBE statement to modify the contents of a system-descriptor area after it is set.

After you use a DESCRIBE statement on a SELECT or an INSERT statement, you must check to determine whether the TYPE field is set to either 11 or 12 to indicate a TEXT or BYTE data type. If TYPE contains an 11 or a 12, you must use the SET DESCRIPTOR statement to reset TYPE to 116, which indicates FILE type.

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

For more information on system-descriptor areas, refer to the Informix ESQL/C Programmer's Manual.