Informix Guide to SQL: Syntax SQL Statements

Use the GET DESCRIPTOR statement to accomplish the following separate tasks:

• Determine how many items are described in a system-descriptor area
• Determine the characteristics of each column or expression that is described in the system-descriptor area
• Copy a value from the system-descriptor area into a host variable after a FETCH statement

Use this statement with ESQL/C.

Use the GET DESCRIPTOR statement after you have described EXECUTE FUNCTION, INSERT, SELECT, or UPDATE statements with the DESCRIBE...USING SQL DESCRIPTOR statement.

Use the GET DESCRIPTOR statement after you have described EXECUTE Procedure, INSERT, or SELECT statements with the DESCRIBE...USING SQL DESCRIPTOR statement.

The host variables that are used in the GET DESCRIPTOR statement must be declared in the BEGIN DECLARE SECTION of a program.

If an error occurs during the assignment to any identified host variable, the contents of the host variable are undefined.

Use the COUNT keyword to determine how many items are described in the system-descriptor area.

The following ESQL/C example shows how to use a GET DESCRIPTOR statement with a host variable to determine how many items are described in the system-descriptor area called desc1:

main()

{

EXEC SQL BEGIN DECLARE SECTION;

int  h_count;

EXEC SQL END DECLARE SECTION;



EXEC SQL allocate descriptor 'desc1' with max 20;



/* This section of program would prepare a SELECT or INSERT 

 * statement into the s_id statement id.

*/

EXEC SQL describe s_id using sql descriptor 'desc1';



EXEC SQL get  descriptor 'desc1' :h_count = count;

...

}

Use the VALUE clause to obtain information about a described column or expression or to retrieve values that the database server returns in a system descriptor area.

The item_num must be greater than zero (0) and less than the number of item descriptors that were specified when the system-descriptor area was allocated with the ALLOCATE DESCRIPTOR statement.

After you describe a SELECT, EXECUTE FUNCTION (or EXECUTE PROCEDURE), INSERT, or UPDATE statement, the characteristics of each column or expression in the select list of the SELECT statement, the characteristics of the values returned by the EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement, or the characteristics of each column in a INSERT or UPDATE statement are returned to the system-descriptor area. Each value in the system-descriptor area describes the characteristics of one returned column or expression.

The following ESQL/C example shows how to use a GET DESCRIPTOR statement to obtain data type information from the demodesc system-descriptor area:

EXEC SQL get descriptor 'demodesc' value :index

	    :type = TYPE,

	    :len = LENGTH,

	    :name = NAME;

printf("Column %d: type = %d, len = %d, name = %s\n",

		index, type, len, name);

The value that the database server returns into the TYPE field is a defined integer. To evaluate the data type that is returned, test for a specific integer value. For additional information about integer data type values, see Setting the TYPE or ITYPE Field.

In X/Open mode, the X/Open code is returned to the TYPE field. You cannot mix the two modes because errors can result. For example, if a particular data type is not defined under X/Open mode but is defined for Informix products, executing a GET DESCRIPTOR statement can result in an error.

In X/Open mode, a warning message appears if ILENGTH, IDATA, or ITYPE is used. It indicates that these fields are not standard X/Open fields for a system-descriptor area.

If the TYPE of a fetched value is DECIMAL or MONEY, the database server returns the precision and scale information for a column into the PRECISION and SCALE fields after a DESCRIBE statement is executed. If the TYPE is not DECIMAL or MONEY, the SCALE and PRECISION fields are undefined.

Each time your program fetches a row, it must copy the fetched value into host variables so that the data can be used. To accomplish this task, use a GET DESCRIPTOR statement after each fetch of each value in the select list. If three values exist in the select list, you need to use three GET DESCRIPTOR statements after each fetch (assuming you want to read all three values). The item numbers for each of the three GET DESCRIPTOR statements are 1, 2, and 3.

The following ESQL/C example shows how you can copy data from the DATA field into a host variable (result) after a fetch. For this example, it is predetermined that all returned values are the same data type.

EXEC SQL get descriptor 'demodesc' :desc_count = count;
.
.
.
EXEC SQL fetch democursor using sql descriptor 'demodesc';
for (i = 1; i <= desc_count; i++)
{
if (sqlca.sqlcode != 0) break;
EXEC SQL get descriptor 'demodesc' value :i :result = DATA;
printf("%s ", result);
}
printf("\n");

When you use GET DESCRIPTOR after a fetch, and the fetched value is null, the INDICATOR field is set to -1 (null). The value of DATA is undefined if INDICATOR indicates a null value. The host variable into which DATA is copied has an unpredictable value.

If your DATA or IDATA field contains a character string, you must specify a value for LENGTH. If you specify LENGTH=0, LENGTH is automatically set to the maximum length of the string. The DATA or IDATA field might contain a literal character string or a character string that is 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 GET 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.

The DESCRIBE statement sets the following item-descriptor fields when the column to fetch has an opaque type as its data type:

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

This integer value corresponds 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 corresponds 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 name of the opaque type.

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

This character value corresponds 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 owner name for the opaque type.

Use these field names with the GET DESCRIPTOR statement to obtain information about an opaque column.

The DESCRIBE statement sets the following item-descriptor fields when the column to fetch has a distinct type as its data type:

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

This integer value corresponds 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. This field is only set if the source data type is an opaque data type.

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

This value is the data type constant (from the sqltypes.h file) for the data type of the source type for the distinct type. The codes for the SOURCETYPE field are listed in the description of the TYPE field in the SET DESCRIPTOR statement. (For more information, see Setting the TYPE or ITYPE Field). 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.

Use these field names with the GET DESCRIPTOR statement to obtain information about a distinct-type column.

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

For more information on concepts relating to the GET DESCRIPTOR statement, see the Informix ESQL/C Programmer's Manual.

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