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

Handling a Parameterized SELECT Statement

For an introduction on how to determine input parameters, see page 15-26. 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.

To use a system-descriptor area to define input parameters for a WHERE clause:

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

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.

Executing a Parameterized SELECT That Returns Multiple Rows

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.

FETCH...USING SQL DESCRIPTOR

The SELECT has input parameters in its WHERE clause.

USING SQL DESCRIPTOR

The SELECT has unknown columns in the select list.

USING SQL DESCRIPTOR

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.

1 #include <stdio.h>

2 EXEC SQL include sqltypes;

4 EXEC SQL define NAME_LEN 15;

5 EXEC SQL define MAX_IDESC 4;



	6	 main()

	7	 {


	8	 EXEC SQL BEGIN DECLARE SECTION;


	9	 	int i;


	10	 	int desc_count;


	11	 	char demoquery[80];


	12	 	char queryvalue[2];


	13	 	char result[ NAME_LEN + 1 ];


	14	 EXEC SQL END DECLARE SECTION;



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

	16	 	EXEC SQL connect to 'stores7';

Lines 8 to 14

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

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

18 * the value for the parameter. This information could have

19 * been entered from the terminal and placed into the strings

20 * demoquery and queryvalue, respectively.

22 sprintf(demoquery, "%s %s",

23 "select fname, lname from customer",

24 "where lname < ? ");

25 EXEC SQL prepare demoid from :demoquery;

26 EXEC SQL declare democursor cursor for demoid;

27 EXEC SQL allocate descriptor 'demodesc' with max MAX_IDESC;

Lines 17 to 25

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

Line 26

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

Line 27

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

28 /* This section of the program must evaluate :demoquery

29 * to count how many question marks are in the where

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

31 * question mark.

32 * For this example, there is one parameter of type

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

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

35 * the next line.

37 sprintf(queryvalue, "C");

38 desc_count = 1;



	39	 	if(desc_count > MAX_IDESC)

	40	 		{


	41	 		EXEC SQL deallocate descriptor 'demodesc';


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


	43	 		}



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

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

Lines 28 to 38

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.

Lines 39 to 43

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.

Lines 44 and 45

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

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

47 i = SQLCHAR;

48 EXEC SQL set descriptor 'demodesc' VALUE 1

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



	50	 	/* Associate the cursor with the parameter value */

	51	 	EXEC SQL open democursor using sql descriptor :demodesc;



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

	53	 	EXEC SQL describe qid using sql descriptor 'demodesc';


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


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


	56	 	/* Print out what DESCRIBE returns */


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


	58	 		prsysdesc(i);


	59	 	printf("\n\n");

Lines 47 to 49

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.

Lines 50 and 51

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.

Lines 52 to 59

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 page 16-13.

62 EXEC SQL fetch democursor using sql descriptor 'demodesc';



	63	 		if (sqlca.sqlcode != 0) break;

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


	65	 			{


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


	67	 			printf("%s ", result);


	68	 			}


	69	 		printf("\n");


	70	 		}


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


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



	73	 	EXEC SQL close democursor;


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

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



	76	 	/* free system-descriptor area */

	77	 	EXEC SQL deallocate descriptor 'demodesc';



	78	 	EXEC SQL disconnect current;

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


	80	 }

Lines 60 to 70

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.

Line 73

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

Lines 74 to 77

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

Executing a Parameterized Singleton SELECT

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

Informix-ESQL/C Programmer's Manual, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.