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

For an introduction to how to handle unknown columns in an unknown select list, see page 15-26. This section describes how to use a system-descriptor area to handle a SELECT statement.

1. Prepare the SELECT statement (with the PREPARE statement) to give it a statement identifier. The SELECT statement cannot include an INTO TEMP clause. For more information, see Assembling and Preparing the SQL Statement.
2. Allocate a system-descriptor area with the ALLOCATE DESCRIPTOR statement. For more information, see Allocating Memory for a System-Descriptor Area.
3. Determine the number and data types of the select-list columns with the DESCRIBE...USING SQL DESCRIPTOR statement. DESCRIBE fills an item descriptor for each column in the select list. For more information on DESCRIBE, see Initializing the System-Descriptor Area.
4. Save the number of select-list columns in a host variable with the GET DESCRIPTOR statement to obtain the value of the COUNT field.
5. Declare and open a cursor and then use the FETCH...USING SQL DESCRIPTOR statement to fetch column values, one row at a time, into an allocated system-descriptor area. See Putting Column Values into a System-Descriptor Area.
6. Retrieve the row data from the system-descriptor area into host variables with the GET DESCRIPTOR statement to access the DATA field. For more information on GET DESCRIPTOR, see Assigning and Obtaining Values from a System-Descriptor Area.
7. 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 input parameters in the WHERE clause, your program must also handle these input parameters with a system-descriptor area.

For more information, see Handling a Parameterized SELECT Statement.

The following sections demonstrate how to handle a SELECT statement that returns many rows and one that returns only one row.

The demo4.ec sample program, shown in this section, shows how to execute a dynamic SELECT statement with the following conditions:

• The SELECT returns more than one 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 either no input parameters or no WHERE clause.

The OPEN statement does not need to include the USING clause.

• The SELECT has unknown columns in its select list.

The FETCH statement includes the USING SQL DESCRIPTOR clause to store the return values in an sqlda structure.

This demo4 program is a version of the demo3 sample program (Executing a SELECT That Returns Multiple Rows) that uses a system-descriptor area to hold select-list columns. The demo4 program does not include exception handling.

#include <stdio.h>

EXEC SQL define NAME_LEN 15;

main()

{

EXEC SQL BEGIN DECLARE SECTION;

mint i;

mint desc_count;

char demoquery[80];

char colname[19];

char result[ NAME_LEN + 1 ];

EXEC SQL END DECLARE SECTION;

These lines declare host variables to hold the data that is obtained from the user and the column values that are retrieved from the system-descriptor area.

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

EXEC SQL connect to 'stores7';

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

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

* been entered from the terminal and placed into the strings

* demoquery and the query value string (queryvalue), respectively.

*/

sprintf(demoquery, "%s %s",

"select fname, lname from customer",

"where lname < 'C' ");

EXEC SQL prepare demo4id from :demoquery;

EXEC SQL declare demo4cursor cursor for demo4id;

EXEC SQL allocate descriptor 'demo4desc' with max 4;

EXEC SQL open demo4cursor;

These lines assemble the character string for the statement (in demoquery) and prepare it as the demo4id statement identifier. For more information on these steps, see Assembling and Preparing the SQL Statement.

This line declares the demo4cursor cursor for the prepared statement identifier, demo4id. All non-singleton SELECT statements must have a declared cursor.

To be able to use a system-descriptor area for the select-list columns, you must first allocate it. This ALLOCATE DESCRIPTOR statement allocates the demo4desc system-descriptor area with four item descriptors.

The database server executes the SELECT statement when it opens the demo4cursor cursor. If the WHERE clause of your SELECT statement contains input parameters, you also need to specify the USING SQL DESCRIPTOR clause of the OPEN statement. (See Handling a Parameterized SELECT Statement.)

EXEC SQL describe demo4id using sql descriptor 'demo4desc';

EXEC SQL get descriptor 'demo4desc' :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");

The DESCRIBE statement describes the select-list columns for the prepared statement in the demo4id statement identifier. For this reason, the DESCRIBE must follow the PREPARE. This DESCRIBE includes the USING SQL DESCRIPTOR clause to specify the demo4desc system-descriptor area as the location for these column descriptions.

Line 27 uses the GET DESCRIPTOR statement to obtain the number of select-list columns found by the DESCRIBE. This number is read from the COUNT field of the demo4desc system-descriptor area and saved in the desc_count host variable. Line 28 displays this information to the user.

This for loop goes through the item descriptors for the columns of the select list. It uses the desc_count host variable to determine the number of item descriptors initialized by the DESCRIBE statement. For each item descriptor, the for loop calls the prsysdesc() function (line 31) to save information such as the data type, length, and name of the column in host variables. See lines 58 to 77 for a description of prsysdesc().

for (;;)

{

EXEC SQL fetch demo4cursor using sql descriptor 'demo4desc';

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

break;

/* Print out the returned values */

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

{

EXEC SQL get descriptor 'demo4desc' VALUE :i

:colname=NAME, :result = DATA;

printf("Column: %s\tValue:%s\n ", colname, result);

}

printf("\n");

}

This inner for loop executes for each row fetched from the database. The FETCH statement (line 35) includes the USING SQL DESCRIPTOR clause to specify the demo4desc system-descriptor area as the location of the column values. After this FETCH executes, the column values are stored in the specified system-descriptor area.

The if statement (lines 36 and 37) tests the value of the SQLSTATE variable to determine if the FETCH was successful. If SQLSTATE contains a class code other than "00", then the FETCH generates a warning ("01"), the NOT FOUND condition ("02"), or an error (> "02"). In any of these cases, line 37 ends the for loop.

Lines 39 to 45 access the fields of the item descriptor for each column in the select list. After each FETCH statement, the GET DESCRIPTOR statement (lines 41 and 42) loads the contents of the DATA field into a host variable of the appropriate type and length. The second for loop (lines 39 to 44) ensures that GET DESCRIPTOR is called for each column in the select list.

Important: In this GET DESCRIPTOR statement, the demo4 program assumes that the returned columns are of the CHAR data type. If the program did not make this assumption, it would need to check the TYPE and LENGTH fields to determine the appropriate data type for the host variable to hold the DATA value.

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

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

EXEC SQL close demo4cursor;

/* free resources for prepared statement and cursor*/

EXEC SQL free demo4id;

EXEC SQL free demo4cursor;

/* free system-descriptor area */

EXEC SQL deallocate descriptor 'demo4desc';

EXEC SQL disconnect current;

printf("\nDEMO4 Sample Program Over.\n\n");

}

Outside the for loop, the program tests the SQLSTATE variable again so that it can notify the user in the event of successful execution, a runtime error, or a warning (class code not equal to "02").

After all the rows are fetched, the CLOSE statement closes the demo4cursor cursor.

These FREE statements release the resources that are allocated for the prepared statement (line 51) and the database cursor (line 52).

The DEALLOCATE DESCRIPTOR statement (line 54) releases the memory allocated to the demo4desc system-descriptor area. For more information, see Freeing Memory Allocated to a System-Descriptor Area.

prsysdesc(index)

EXEC SQL BEGIN DECLARE SECTION;

PARAMETER mint index;

EXEC SQL END DECLARE SECTION;

{

EXEC SQL BEGIN DECLARE SECTION;

mint type;

mint len;

mint nullable;

char name[40];

EXEC SQL END DECLARE SECTION;

EXEC SQL get descriptor 'demo4desc' VALUE :index

:type = TYPE,

:len = LENGTH,

:nullable = NULLABLE,

:name = NAME;

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

index, type, len, nullable, name);

}

The prsysdesc() function displays information about a select-list column. It uses the GET DESCRIPTOR statement to access one item descriptor from the demo4desc system-descriptor area.

The GET DESCRIPTOR statement (lines 70 to 74) accesses the TYPE, LENGTH, NULLABLE, and NAME fields from an item descriptor in demo4desc to provide information about a column. It stores this information in host variables of appropriate lengths and data types. The VALUE keyword indicates the number of the item descriptor to access.

The demo4 program, described in the previous section, assumes that the SELECT statement returns more than one row and therefore the program associates the statement with a cursor. If you know at the time that you write the program that the dynamic SELECT always returns just one row, you can omit the cursor and use the EXECUTE...INTO SQL DESCRIPTOR statement instead of the FETCH...USING SQL DESCRIPTOR. You need to use the DESCRIBE statement to define the select-list columns. For more information, see Putting Column Values into a System-Descriptor Area.