INFORMIX-ESQL/C Programmer's Manual Using an sqlda Structure

For an introduction on how to determine input parameters, see Determining Unknown Input Parameters. This section describes how to handle a parameterized SELECT statement with an sqlda structure. If a prepared SELECT statement has a WHERE clause with input parameters of unknown number and data type, your ESQL/C program must use an sqlda structure to define the input parameters.

1. Declare a variable to hold the address of an sqlda structure. For more information, see Defining an sqlda Structure.
2. Determine the number and data types of the input parameters of the SELECT statement. For more information, see Determining Unknown Input Parameters.
3. Allocate an sqlda structure with a system memory-allocation function such as malloc(). For more information, see Specifying Input Parameter Values and Allocating Memory for the sqlda Structure.
4. Indicate the number of input parameters in the WHERE clause with C-language statements that set the sqld field of the sqlda structure.
5. Store the definitions and values of each input parameter with C-language statements that set the sqltype, sqllen, and sqldata fields in the appropriate sqlvar_struct of the sqlda structure:

1. The sqltype field uses the ESQL/C data-type constants, which the sqltypes.h header file defines, to represent the data type of the input parameter. For more information, see Determining the Data Type of a Column.
2. For a CHAR or VARCHAR value, sqllen is the size, in bytes, of the character array. For a DATETIME or INTERVAL value, this field stores the encoded qualifiers.
3. The sqldata field of each sqlvar_struct structure contains the address of the memory allocated for the input parameter value. You might need to use the sqltype and sqllen fields for each input parameter to determine the amount of memory you need to allocate. For more information, see Allocating Memory for Column Data.

If you use an indicator variable, also set the sqlind field and perhaps the sqlidata, sqlilen, and sqlitype fields (for non-X/Open applications only).

Use an index into the sqlda.sqlvar array to identify the sqlvar_struct structure. For more information, see Assigning and Obtaining Values from an sqlda Structure.

1. Pass the defined input parameters from the sqlda structure to the database server with the USING DESCRIPTOR clause.

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

2. Release the memory that you allocated for the sqlvar_struct fields, the sqldata fields, and the sqlda structure itself with the free() system call. For more information, see Freeing Memory Allocated to an sqlda Structure.

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

The following sections demonstrate how to use an sqlda structure 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 is a modified version of the demo4.ec example program. It 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 DESCRIPTOR statement.

• The SELECT has input parameters in its WHERE clause.

The OPEN statement includes the USING DESCRIPTOR clause to provide the parameter values in an sqlda structure.

• The SELECT has unknown columns in the select list.

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

The program illustrates how to use an sqlda structure to handle both input parameters of a WHERE clause and the columns in the select list.

#include <stdio.h>

EXEC SQL include sqlda;

EXEC SQL include sqltypes;

#define FNAME 15

#define LNAME 15

#define PHONE 18

The program must include the ESQL/C sqlda.h header file to use an sqlda structure.

main()

{

char fname[ FNAME + 1 ];

char lname[ LNAME + 1 ];

char phone[ PHONE + 1 ];

int count, customer_num, i;

struct sqlvar_struct *pos;

struct sqlda *sqlda_ptr;

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

EXEC SQL connect to 'stores7';

stcopy("Carole", fname);

stcopy("Sadler", lname);

EXEC SQL prepare sql_id from

'select * from customer where fname=? and lname=?';

EXEC SQL declare slct_cursor cursor for sql_id;

Lines 9 to 11 declare variables to hold the data that is obtained from the user. The sqlda_ptr variable (line 14) is the pointer to an sqlda structure. The pos variable (line 13) points to an sqlvar_struct structure so that the code can proceed through each of the sqlvar_struct structures in the variable-length portion of sqlda. Neither of these variables is defined as an ESQL/C host variable.

These lines assemble the character string for the SELECT statement and prepare the SELECT string. This program assumes the number and data types of the input parameters. Therefore, no C code needs to determine this information at runtime. The question mark (?) indicates the input parameters in the WHERE clause. For more information on these steps, see Assembling and Preparing the SQL Statement.

This line declares the slct_cursor cursor for the prepared statement identifier, sql_id.

count=2;

whereClauseMem(&sqlda_ptr, count, fname, lname);

EXEC SQL open slct_cursor using descriptor sqlda_ptr;

free(sqlda_ptr->sqlvar);

free(sqlda_ptr);

These lines initialize the sqlda structure with the input parameter information. The program assumes two input parameters (line 22). If the number of input parameters is unknown, the program needs to parse the SELECT character string (not the prepared version) and count the number of "?" characters that it contains.

The program then calls the whereClauseMem() function to allocate and initialize the sqlda structure. For more information, see lines 69 to 77.

The database server executes the SELECT statement when it opens the cursor. You must include the USING DESCRIPTOR clause of OPEN to specify the sqlda structure as the location of the input parameter values.

Once the OPEN...USING DESCRIPTOR statement has executed, these input parameter values have been used. Deallocate this sqlda structure because it is no longer needed and so that it does not conflict with the sqlda that contains the retrieved values. Keep in mind that this second sqlda must have memory allocated before it can be used.

EXEC SQL describe sql_id into sqlda_ptr;

selectListMem(sqlda_ptr);

while(1)

{

EXEC SQL fetch slct_cursor using descriptor sqlda_ptr;

if(SQLCODE != 0)

{

printf("fetch SQLCODE %d\n", SQLCODE);

break;

}

For demonstration purposes, this program assumes that the number and data types of the select-list columns are also unknown at compile time. It uses the DESCRIBE...INTO statement (line 27) to allocate an sqlda structure, and puts information about the select-list columns into the structure to which sqlda_ptr points.

The selectListMem() function handles the allocation of memory for column values. For more information on selectListMem(), see lines 85 to 102.

The while loop executes for each row fetched from the database. The FETCH statement (line 31) includes the USING DESCRIPTOR clause to specify an sqlda structure as the location for the returned column values. For more information on how to handle unknown select-list columns, see Handling an Unknown Select List.

These lines test the value of the SQLCODE variable to determine if the FETCH was successful. If SQLCODE contains a nonzero value, then the FETCH generates the NOT FOUND condition (100) or an error (< 0). In any of these cases, line 34 prints out the SQLCODE value. To determine if the FETCH statement generated warnings, you need to examine the sqlca.sqlwarn structure.

for(i=0; i<sqlda_ptr->sqld; i++)

{

printf("\ni=%d\n", i);

prsqlda(sqlda_ptr->sqlvar[i]);

switch (i)

{

case 0:

customer_num = *(int *)(sqlda_ptr->sqlvar[i].sqldata);

break;

case 1:

stcopy(sqlda_ptr->sqlvar[i].sqldata, fname);

break;

case 2:

stcopy(sqlda_ptr->sqlvar[i].sqldata, lname);

break;

case 9:

stcopy(sqlda_ptr->sqlvar[i].sqldata, phone);

break;

}

}

printf("%d ==> |%s|, |%s|, |%s|\n",

customer_num, fname, lname, phone);

}

EXEC SQL close slct_cursor;

EXEC SQL free slct_cursor;

EXEC SQL free sql_id;

These lines access the fields of the sqlvar_struct structure for each column in the select list. The prsqlda() function (see lines 75 to 81) displays the column name (from sqlvar_struct.sqlname) and its value (from the sqlvar_struct.sqldata field). The switch (lines 41 to 55) transfers the column values from the sqlda structure into host variables of the appropriate lengths and data types.

These lines free resources after all the rows are fetched. Line 60 closes the slct_cursor cursor and line 61 frees it. Line 62 frees the sql_id statement ID.

free(sqlda_ptr->sqlvar);

free(sqlda_ptr);

EXEC SQL close database;

EXEC SQL disconnect current;

printf("\nProgram Over.\n");

}

whereClauseMem(descp, count, fname, lname)

struct sqlda **descp;

int count;

char *fname, *lname;

{

(*descp)=(struct sqlda *) malloc(sizeof(struct sqlda));

(*descp)->sqld=count;

(*descp)->sqlvar=(struct sqlvar_struct *)

calloc(count, sizeof(struct sqlvar_struct));

These free() system calls release the memory that is associated with the sqlda structure. Line 63 releases the memory allocated to the sqlvar_struct structures. Line 64 releases the memory allocated for the sqlda structure. The program does not need to deallocate memory associated with the sqldata fields because these fields have used space that is in a data buffer. For more information, see Freeing Memory Allocated to an sqlda Structure.

The whereClauseMem() function initializes the sqlda structure with the input-parameter definitions. Line 74 allocates memory for an sqlda structure to hold the input parameters in the WHERE clause. Use of a DESCRIBE...INTO statement to allocate an sqlda results in an sqlda that holds information about the select-list columns of the SELECT. Because you want to describe the input parameters in the WHERE clause, do not use DESCRIBE here.

Line 75 sets the sqld field of the sqlda structure to the value of count (2) to indicate the number of parameters that are in the WHERE clause. Lines 76 and 77 use the calloc() system function to allocate the memory so that each input parameter in the WHERE clause has an sqlvar_struct structure. These lines then set the sqlvar field of the sqlda structure so that it points to this sqlvar_struct memory.

(*descp)->sqlvar[0].sqltype = CCHARTYPE;

(*descp)->sqlvar[0].sqllen = FNAME + 1;

(*descp)->sqlvar[0].sqldata = fname;

(*descp)->sqlvar[1].sqltype = CCHARTYPE;

(*descp)->sqlvar[1].sqllen = LNAME + 1;

(*descp)->sqlvar[1].sqldata = lname;

}

selectListMem(descp)

struct sqlda *descp;

{

struct sqlvar_struct *col_ptr;

static char buf[1024];

int pos, cnt, size;

printf("\nWITHIN selectListMem: \n");

printf("number of parms: %d\n", descp->sqld);

for(col_ptr=descp->sqlvar, cnt=pos=0; cnt < descp->sqld;

cnt++, col_ptr++)

{

prsqlda(col_ptr);

pos = rtypalign(pos, col_ptr->sqltype);

col_ptr->sqldata = &buf[pos];

size = rtypmsize(col_ptr->sqltype, col_ptr->sqllen);

pos += size;

}

}

Lines 78 to 80 set the sqltype, sqllen, and sqldata fields of the sqlvar_struct structure to describe the first input parameter: a character (CCHARTYPE) host variable of length 16 (FNAME + 1) whose data is stored in the fname buffer. The fname buffer is a character buffer declared in the main() program and passed as an argument to whereClauseMem().

Lines 81 to 83 set the sqltype, sqllen, and sqldata fields of the sqlvar_struct structure to describe the second input parameter. This parameter is for the lname column. It is defined in the same way as the fname column (lines 78 to 80) but it receives its data from the lname buffer [also passed from main() to whereClauseMem()].

The selectListMem() function allocates the memory and initializes the sqlda structure for the unknown select-list columns of the parameterized SELECT statement. For more information on how to use an sqlda structure for select-list columns, see Handling a Parameterized SELECT Statement.

The instructions in the previous 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 that you write the program that the parameterized SELECT statement always returns just one row, you can omit the cursor and use the EXECUTE...USING DESCRIPTOR...INTO statement instead of the OPEN...USING DESCRIPTOR statement to specify parameters values from an sqlda structure. For more information, see Specifying Input Parameter Values.