Informix-ESQL/C Programmer's Manual

Informix-ESQL/C Programmer's Manual
Chapter 16: Using a System-Descriptor Area

Home Contents Index Master Index New Book

Managing a System-Descriptor Area

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

Figure 16-1
SQL Statements That Can Be Used to Manipulate a System-Descriptor Area

(1 of 2)

SQL Statement Purpose See Page...
ALLOCATE
DESCRIPTOR
Allocates memory for a system-descriptor area
16-5

DESCRIBE...USING
SQL DESCRIPTOR
Initializes the system-descriptor area with information about column-list columns
16-6

GET DESCRIPTOR
Obtains information from the fields of the system-descriptor area
16-8

SET DESCRIPTOR
Places information into a system- descriptor area for the database server to access
16-8

For SELECT and EXECUTE FUNCTION statements that use cursors:

OPEN...USING
SQL DESCRIPTOR
FETCH...USING
SQL DESCRIPTOR
Takes any input parameters from the specified system-descriptor area
Puts the contents of the row into the system-descriptor area
16-10

16-11

For SELECT and EXECUTE FUNCTION statements that return only one row:

EXECUTE...INTO
SQL DESCRIPTOR
Puts the contents of the singleton row into the system-descriptor area
16-11

For non-SELECT statements:

EXECUTE...USING
SQL DESCRIPTOR
Takes any input parameters from the specified system-descriptor area
16-10

For an INSERT statement that uses an insert cursor:

PUT...USING
SQL DESCRIPTOR
Puts a row into the insert buffer, obtaining the column values from the specified system-descriptor area
16-29

DEALLOCATE
DESCRIPTOR
Frees memory allocated for the system-descriptor area when your program is finished with it
16-12

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

Initializing the System-Descriptor Area

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.

EXECUTE FUNCTION

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

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

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

The DESCRIBE Statement and Input Parameters

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

The DESCRIBE Statement and Memory Allocation

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.

DESCRIBE...USING SQL DESCRIPTOR

FETCH...USING SQL DESCRIPTOR

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.

Assigning and Obtaining Values from a System-Descriptor Area

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.

Using the SET DESCRIPTOR Statement

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

Set field values to provide values for input parameters in a WHERE clause (

DESCRIBE...USING SQL DESCRIPTOR

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.

Using the GET DESCRIPTOR Statement

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

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 (

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.

Specifying Input Parameter Values

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

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

Putting Column Values into a System-Descriptor Area

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 n