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

For an introduction on how to determine input parameters, see page 15-28. This section describes how to handle a parameterized SELECT statement with a system-descriptor area. If a prepared SELECT statement has a WHERE clause with input parameters of unknown number and data type, your ESQL/C program must use a system-descriptor area to define the input parameters.

1. Determine the number and data types of the input parameters of the SELECT statement. For more information, see Determining Unknown Input Parameters.
2. Allocate a system-descriptor area and assign it a name with the ALLOCATE DESCRIPTOR statement. For more information on ALLOCATE DESCRIPTOR, see Allocating Memory for a System-Descriptor Area.
3. Indicate the number of input parameters in the WHERE clause with the SET DESCRIPTOR statement, which sets the COUNT field.
4. Store the definition and value of each input parameter with the SET DESCRIPTOR statement, which sets the DATA, TYPE, and LENGTH fields in the appropriate item descriptor:
• The TYPE field must use the ESQL/C data type constants defined in the sqltypes.h header file to represent the data types of the input parameters. For more information, see page 15-22.
• For a CHAR or VARCHAR value, LENGTH is the size, in bytes, of the character array; for a DATETIME or INTERVAL value, the LENGTH field stores the encoded qualifiers.

Important: If you use X/Open code (and compile with the -xopen flag), you must use the X/Open data type values for the TYPE and ITYPE fields. For more information, see Determining the Data Type of a Column.

If you use an indicator variable, you also need to set the INDICATOR field and perhaps the IDATA, ILENGTH, and ITYPE fields (for non-X/Open applications only). Use the VALUE keyword of SET DESCRIPTOR to identify the item descriptor. For more information on SET DESCRIPTOR, see Assigning and Obtaining Values from a System-Descriptor Area.

5. Pass the defined input parameters from the system-descriptor area to the database server with the USING SQL DESCRIPTOR clause.

The statement that provides the input parameters depends on how many rows that the SELECT statement returns. The following sections discuss how to execute each type of SELECT statement.

6. Deallocate the system-descriptor area with the DEALLOCATE DESCRIPTOR statement. For more information, see Freeing Memory Allocated to a System-Descriptor Area.

Important: If the SELECT statement has unknown columns in the select list, your program must also handle these columns with a system-descriptor area. For more information, see Handling an Unknown Select List.

The following sections demonstrate how to use a system-descriptor area to handle a parameterized SELECT statement that returns many rows and one that returns only a single row.

The sample program described on the following pages shows how to use a dynamic SELECT statement with the following conditions:

• The SELECT returns more than row.

The SELECT must be associated with a cursor, executed with the OPEN statement, and have its return values retrieved with the FETCH...USING SQL DESCRIPTOR statement.

• The SELECT has input parameters in its WHERE clause.

The OPEN statement includes the USING SQL DESCRIPTOR clause to provide the parameter values in a system-descriptor area.

• The SELECT has unknown columns in the select list.

The FETCH statement includes the USING SQL DESCRIPTOR clause to store the return values in a system-descriptor area.

This program is a version of the demo4.ec sample program; demo4 uses a system-descriptor area for select-list columns while this modified version of demo4 uses a system-descriptor area for both select-list columns and input parameters of a WHERE clause.

#include <stdio.h>

EXEC SQL include sqltypes;

EXEC SQL define NAME_LEN 15;

EXEC SQL define MAX_IDESC 4;

main()

{

EXEC SQL BEGIN DECLARE SECTION;

int i;

int desc_count;

char demoquery[80];

char queryvalue[2];

char result[ NAME_LEN + 1 ];

EXEC SQL END DECLARE SECTION;

printf("Modified DEMO4 Sample ESQL program running.\n\n");

EXEC SQL connect to 'stores7';

These lines declare host variables to hold the data obtained from the user and the column values retrieved from the system descriptor.

/* These next three lines have hard-wired both the query and

* the value for the parameter. This information could have

* been entered from the terminal and placed into the strings

* demoquery and queryvalue, respectively.

*/

sprintf(demoquery, "%s %s",

"select fname, lname from customer",

"where lname < ? ");

EXEC SQL prepare demoid from :demoquery;

EXEC SQL declare democursor cursor for demoid;

EXEC SQL allocate descriptor 'demodesc' with max MAX_IDESC;

The lines assemble the character string for the statement (in demoquery) and prepare it as the demoid statement identifier. The question mark (?) indicates the input parameter in the WHERE clause. For more information on these steps, see Assembling and Preparing the SQL Statement.

This line declares the democursor cursor for the prepared statement identifier demoid. All nonsingleton SELECT statements must have a declared cursor.

To be able to use a system-descriptor area for the input parameters, you must first allocate the system-descriptor area. This ALLOCATE DESCRIPTOR statement allocates the demodesc system-descriptor area. For more information on ALLOCATE DESCRIPTOR, see Allocating Memory for a System-Descriptor Area.

/* This section of the program must evaluate :demoquery

* to count how many question marks are in the where

* clause and what kind of data type is expected for each

* question mark.

* For this example, there is one parameter of type

* char(15). It would then obtain the value for

* :queryvalue. The value of queryvalue is hard-wired in

* the next line.

*/

sprintf(queryvalue, "C");

desc_count = 1;

if(desc_count > MAX_IDESC)

{

EXEC SQL deallocate descriptor 'demodesc';

EXEC SQL allocate descriptor 'demodesc' with max :desc_count;

}

/* number of parameters to be held in descriptor is 1 */

EXEC SQL set descriptor 'demodesc' COUNT = :desc_count;

These lines simulate the dynamic entry of the input parameter value. Although the parameter value is hard-coded here (line 37), the program would more likely obtain the value from user input. Line 38 simulates code that would determine how many input parameters exist in the statement string. If you did not know this value, you would need to include C code at this point to parse the statement string for the question mark (?) character.

This if statement determines if the demodesc system-descriptor area contains enough item descriptors for the parameterized SELECT statement. It compares the number of input parameters in the statement string (desc_count) with the number of item descriptors currently allocated (MAX_IDESC). If the program has not allocated enough item descriptors, the program deallocates the existing system-descriptor area (line 41) and allocates a new one (line 42); it uses the actual number of input parameters in the WITH MAX clause to specify the number of item descriptors to allocate.

This SET DESCRIPTOR statement stores the number of input parameters in the COUNT field of the demodesc system-descriptor area.

/* Put the value of the parameter into the descriptor */

i = SQLCHAR;

EXEC SQL set descriptor 'demodesc' VALUE 1

TYPE = :i, LENGTH = 15, DATA = :queryvalue;

/* Associate the cursor with the parameter value */

EXEC SQL open democursor using sql descriptor :demodesc;

/*Reuse the descriptor to determine the contents of the Select-list*/

EXEC SQL describe qid using sql descriptor 'demodesc';

EXEC SQL get descriptor 'demodesc' :desc_count = COUNT;

printf("There are %d returned columns:\n", desc_count);

/* Print out what DESCRIBE returns */

for (i = 1; i <= desc_count; i++)

prsysdesc(i);

printf("\n\n");

This SET DESCRIPTOR statement sets the TYPE, LENGTH (for a CHAR value), and DATA fields for each of the parameters in the WHERE clause. The program only calls SET DESCRIPTOR once because it assumes that the SELECT statement has only one input parameter. If you do not know the number of input parameters at compile time, put the SET DESCRIPTOR in a loop for which the desc_count host variable controls the number of iterations.

The database server executes the SELECT statement when it opens the democursor cursor. This OPEN statement includes the USING SQL DESCRIPTOR clause to specify the demodesc system-descriptor area as the location of the input-parameter values.

The program also uses the demodesc system-descriptor area to hold the columns that are returned by the SELECT statement. The DESCRIBE statement (line 53) examines the select list to determine the number and data types of these columns. The GET DESCRIPTOR statement (line 54) then obtains the number of described columns from the COUNT field of demodesc. Lines 55 to 58 then display the column information for each returned column. For more information on how to use a system-descriptor area to receive column values, see Handling an Unknown Select List.

for (;;)

{

EXEC SQL fetch democursor using sql descriptor 'demodesc';

if (sqlca.sqlcode != 0) break;

for (i = 1; i <= desc_count; i++)

{

EXEC SQL get descriptor 'demodesc' VALUE :i :result = DATA;

printf("%s ", result);

}

printf("\n");

}

if(strncmp(SQLSTATE, "02", 2) != 0)

printf("SQLSTATE after fetch is %s\n", SQLSTATE);

EXEC SQL close democursor;

EXEC SQL free demoid; /* free resources for statement */

EXEC SQL free democursor; /* free resources for cursor */

/* free system-descriptor area */

EXEC SQL deallocate descriptor 'demodesc';

EXEC SQL disconnect current;

printf("\nModified DEMO4 Program Over.\n\n");

}

These lines access the fields of the item descriptor for each column in the select list. After each FETCH statement, the GET DESCRIPTOR statement loads the contents of the DATA field into the result host variable.

After all the rows are fetched, the CLOSE statement frees the resources allocated to the active set of the democursor cursor.

The FREE statement on line 74 frees the resources allocated to the demoid statement identifier while the FREE statement on line 75 frees the resources to the democursor cursor. The DEALLOCATE DESCRIPTOR statement frees the resources allocated to the demodesc system-descriptor area. For more information, see Freeing Memory Allocated to a System-Descriptor Area.

The instructions in the preceding section assume that the parameterized SELECT statement returns more than one row and, therefore, is associated with a cursor. If you know at the time you write the program that the parameterized SELECT statement will always return just one row, you can omit the cursor and use the EXECUTE...USING SQL DESCRIPTOR...INTO statement instead of the OPEN...USING SQL DESCRIPTOR statement to specify parameter values from a system-descriptor area. For more information, see Specifying Input Parameter Values.