INFORMIX-ESQL/C Programmer's Manual Using a System-Descriptor Area

Your ESQL/C program can manipulate a system-descriptor area with the SQL statements that Figure 16-1 summarizes.

Allocating Memory for a System-Descriptor Area

To allocate memory for a system-descriptor area, use the ALLOCATE DESCRIPTOR statement. This statement performs the following tasks:

• It assigns the specified descriptor name to identify this region of memory. This name is an identifier that must be provided in all the SQL statements listed in Figure 16-1 on page 16-4 to designate the system descriptor on which to take action.
• It allocates item descriptors. By default, it allocates 100 item descriptors in the system-descriptor area. You can change this default with the WITH MAX clause.
• It initializes the COUNT field in the system-descriptor area to the number of item descriptors allocated.

Important: ALLOCATE DESCRIPTOR does not allocate memory for column data (DATA field). This memory is allocated by the DESCRIBE statement on an as-needed basis. For more information, refer to the next section.

The DESCRIBE...USING SQL DESCRIPTOR statement initializes the system-descriptor area with information about the prepared statement. This statement takes the following actions:

• It sets the COUNT field, which contains the number of item descriptors initialized with data.

This value is the number of columns and expressions in the column list (SELECT and INSERT) or the number of returned values (EXECUTE FUNCTION).

• It describes each unknown column in a prepared SELECT statement (without an INTO TEMP), EXECUTE FUNCTION, or INSERT statement.

The DESCRIBE statement initializes the fields of the item descriptor for each column, as follows:

• It allocates memory for the DATA field based on the TYPE and LENGTH information.
• It initializes the TYPE, LENGTH, NAME, SCALE, PRECISION, and NULLABLE fields to provide information from the database about a column.

For descriptions of these fields, see Figure 15-3 on page 15-7.

• It returns the type of SQL statement prepared.

For more information, see Determining the Data Type of a Column.

As noted earlier, the DESCRIBE statement provides information about the columns of a column list. Therefore, you usually use this statement after a SELECT (without an INTO TEMP clause), INSERT, or EXECUTE FUNCTION statement was prepared.

When you use the system-descriptor area to hold an input parameter, you cannot use DESCRIBE to initialize the system-descriptor area. Your code must define the input parameters with the SET DESCRIPTOR statement to explicitly set the appropriate fields of the system-descriptor area. For more information, see Specifying Input Parameter Values.

When you use a system-descriptor area to hold columns of prepared SQL statements, the ALLOCATE DESCRIPTOR statement allocates memory for the item descriptors of each column and the DESCRIBE...USING SQL DESCRIPTOR statement allocates memory for the DATA field of each item descriptor.

However, the DESCRIBE...USING SQL DESCRIPTOR statement does not allocate memory for the DATA field of a system-descriptor area when you describe a prepared SELECT statement that fetches data from a column into a host variable of type lvarchar.

Before you fetch lvarchar data into the system-descriptor area, you must explicitly assign memory to the DATA field to hold the column value, as follows:

1. Declare an lvarchar host variable of the appropriate size.

Make sure this variable is not just a pointer but has memory associated with it.

2. Assign this host variable to the DATA field with the SET DESCRIPTOR statement.

This SET DESCRIPTOR statement should occur after the DESCRIBE...USING SQL DESCRIPTOR statement but before the FETCH...USING SQL DESCRIPTOR statement.

3. Execute the FETCH...USING SQL DESCRIPTOR statement to retrieve the column data into the DATA field of the system-descriptor area.

The following code fragment shows the basic steps to allocate memory for an LVARCHAR column called lvarch_col in the table1 table:

EXEC SQL BEGIN DECLARE SECTION;

	lvarchar lvarch_val[50];

	int i;

EXEC SQL END DECLARE SECTION;



EXEC SQL allocate descriptor 'desc';

EXEC SQL prepare stmt1 from 'select opaque_col from table1';

EXEC SQL describe stmt1 using sql descriptor 'desc';

EXEC SQL declare cursor curs1 for stmt1;

EXEC SQL open curs1;

EXEC SQL set descriptor 'desc' value 1 

	data = :lvarch_val, length = 50;



while (1)

	{

	EXEC SQL fetch curs1 using sql descriptor 'desc';

	EXEC SQL get descriptor 'desc' value 1 :lvarch_val;

	printf("Column value is %s\n", lvarch_val);
	

	}

The preceding code fragment does not perform exception handling.

The following SQL statements allow your program to access the fields of the system-descriptor area:

• The SET DESCRIPTOR statement assigns values to the fields of the system-descriptor area.
• The GET DESCRIPTOR statement obtains values from the fields of the system-descriptor area.

To assign values to the system-descriptor-area fields, use the SET DESCRIPTOR statement. You can use the SET DESCRIPTOR statement to:

• set the COUNT field to match the number of items for which you provide descriptions in the system-descriptor area. This value is typically the number of input parameters in a WHERE clause.
EXEC SQL set descriptor sysdesc COUNT=:hostvar;
• set the item-descriptor fields for each column value for which you provide a description.
EXEC SQL set descriptor sysdesc VALUE :item_num
DESCRIP_FIELD=:hostvar;

In this example, item_num is the number of the item descriptor that corresponds to the desired column, and DESCRIP_FIELD is one of the item-descriptor fields that is listed in Figure 15-3 on page 15-7.

Set field values to provide values for input parameters in a WHERE clause (page 16-11) or to modify the contents of an item descriptor field after you use the DESCRIBE...USING SQL DESCRIPTOR statement to fill the system-descriptor area (page 16-12).

Informix provides data-type constants in the sqltypes.h header file to identify the data type of a column in the TYPE field (and optionally the ITYPE field) of a system-descriptor area. However, you cannot assign a data-type constant directly in a SET DESCRIPTOR statement. Instead, assign the constant value to an integer host variable and specify this variable in the SET DESCRIPTOR statement, as follows:

EXEC SQL BEGIN DECLARE SECTION;

	int i;
	

EXEC SQL END DECLARE SECTION;


i = SQLINT;

EXEC SQL set descriptor 'desc1' VALUE 1

	TYPE = :i;

For more information on the data-type constants, see Determining the Data Type of a Column. For more information about how to set individual system-descriptor fields, see the entry for the SET DESCRIPTOR statement in the Informix Guide to SQL: Syntax.

If you use an lvarchar pointer host variable with a FETCH or PUT statement that uses a system descriptor area, you must explicitly set the type to 124 (CLVCHARPTRTYPE from incl/esql/sqltypes.h) in the SET DESCRIPTOR statement. The following example illustrates:

EXEC SQL BEGIN DECLARE SECTION;

lvarchar *lv;

EXEC SQL END DECLARE SECTION;

	/* where tab has lvarchar * column */

EXEC SQL prepare stmt from "select col from tab";

EXEC SQL allocate descriptor `d';

	/* The following describe will return SQLLVARCHAR for the

		type of the column */

EXEC SQL describe stmt using sql descriptor `d';

	/* You must set type for *lv variable */

EXEC SQL set descriptor `d' value 1 DATA = :lv, TYPE = 124;

EXEC SQLdeclare c cursor for stmt;

EXEC SQL open c;

EXEC SQL fetch c using sql descriptor `d';

The GET DESCRIPTOR statement obtains values from the system-descriptor-area fields. You can use the GET DESCRIPTOR statement to:

• get the COUNT field to determine how many values are described in a system-descriptor area.
EXEC SQL get descriptor sysdesc :hostvar=COUNT;
• get the item-descriptor fields for each described column.
EXEC SQL get descriptor sysdesc VALUE :item_num
:hostvar=DESCRIP_FIELD;

In this example, item_num is the number of the item descriptor that corresponds to the desired column, and DESCRIP_FIELD is one of the item-descriptor fields listed in Figure 15-3 on page 15-7.

These item-descriptor values are typically descriptions of columns in a SELECT, INSERT, or EXECUTE FUNCTION statement. GET DESCRIPTOR is also used after a FETCH...USING SQL DESCRIPTOR to copy a column value that is returned by the database server from the system-descriptor area into a host variable (page 16-12).

The data type of the host variable must be compatible with the type of the associated system-descriptor area field. When you interpret the TYPE field, make sure that you use the data type values that match your environment. For some data types, X/Open values differ from Informix values. For more information, see Determining the Data Type of a Column.

For more information about how to get individual system-descriptor fields, see the entry for the GET DESCRIPTOR statement in the Informix Guide to SQL: Syntax.

Because the DESCRIBE...USING SQL DESCRIPTOR statement does not analyze a WHERE clause, your program must store the number, data types, and values of the input parameters in the fields of the system-descriptor area to explicitly describe these parameters. For general information on how to define input parameters dynamically, see page 15-28.

When you execute a parameterized statement, you must specify the system-descriptor area as the location of input parameter values with the USING SQL DESCRIPTOR clause, as follows:

• For input parameters in the WHERE clause of a SELECT, use the OPEN...USING SQL DESCRIPTOR statement. This statement handles a sequential, scrolling, hold, or update cursor. If you are certain that the SELECT will return only one row, you can use the EXECUTE...INTO...USING SQL DESCRIPTOR statement instead of a cursor. See Handling an Unknown Select List.
• For input parameters in the WHERE clause of a non-SELECT statement such as DELETE or UPDATE, use the EXECUTE...USING SQL DESCRIPTOR statement. See Handling a Parameterized UPDATE or DELETE Statement.
• For input parameters in the VALUES clause of an INSERT statement, use the EXECUTE...USING SQL DESCRIPTOR statement. If the INSERT statement is associated with an insert cursor, use the PUT...USING SQL DESCRIPTOR statement instead. For more information, see Handling an Unknown Column List.

When you create a SELECT statement dynamically, you cannot use the INTO host_var clause of FETCH because you cannot name the host variables in the prepared statement. To fetch column values into a system-descriptor area, use the USING SQL DESCRIPTOR clause of FETCH instead of the INTO clause. The FETCH...USING SQL DESCRIPTOR statement puts each column value into the DATA field of its item descriptor.

Use of the FETCH...USING SQL DESCRIPTOR statement assumes the existence of a cursor associated with the prepared statement. You must always use a cursor for SELECT statements and cursory functions (EXECUTE FUNCTION statements that return multiple rows). However, if the SELECT (or EXECUTE FUNCTION) returns only one row, you can omit the cursor and retrieve the column values into a system-descriptor area with the EXECUTE...INTO SQL DESCRIPTOR statement.

Warning: If you execute a SELECT statement or user-defined function that returns more than one row and do not associate the statement with a cursor, your program generates a runtime error. When you associate a singleton SELECT (or EXECUTE FUNCTION) statement with a cursor, ESQL/C does not generate an error. Therefore, it is a good practice to always associate a dynamic SELECT or EXECUTE FUNCTION statement with a cursor and to use a FETCH...USING SQL DESCRIPTOR statement to retrieve the column values from this cursor into the system-descriptor area.

Once the column values are in the system-descriptor area, you can use the GET DESCRIPTOR statement to transfer these values from their DATA fields to the appropriate host variables. You must use the LENGTH and TYPE fields to determine, at runtime, the data types for these host variables. You might need to perform data type or length conversions between the SQL data types in the TYPE fields and the ESQL/C data types that are needed for host variables that hold the return value.

For more information on how to execute SELECT statements dynamically, see Handling an Unknown Select List. For more information on how to execute user-defined functions dynamically, see Handling Unknown Return Values.

The DEALLOCATE DESCRIPTOR statement deallocates, or frees, memory that the specified system-descriptor area uses. The freed memory includes memory used by the item descriptors to hold data (in the DATA fields). Make sure you deallocate a system-descriptor area only after you no longer have need of it. A deallocated system-descriptor area cannot be reused.

For information about how to free other program resources, see Freeing Resources. For more information about DEALLOCATE DESCRIPTOR, see the Informix Guide to SQL: Syntax.