Method 4: Query Statements With Parameters

There are four steps to processing a SQL query statement string with placeholder parameters:

1. Prepare an input XSQLDA to process a statement string’s parameters.
2. Prepare an output XSQLDA to process the select-list items returned when the query is executed.
3. Prepare the statement string and its parameters.
4. Use a cursor to execute the statement using input parameter values from an input XSQLDA, and to retrieve select-list items from the output XSQLDA.

Preparing the Input XSQLDA

Placeholder parameters are replaced with actual data before a prepared SQL statement string is executed. Because those parameters are unknown when the statement string is created, an input XSQLDA must be created to supply parameter values at runtime. To prepare the XSQLDA, follow these steps:

1. Declare a variable to hold the XSQLDA needed to process parameters. For example, the following declaration creates an XSQLDA called in_sqlda:

XSQLDA *in_sqlda;

2. Optionally declare a variable for accessing the XSQLVAR structure of the XSQLDA:

   XSQLVAR *var;

Declaring a pointer to the XSQLVAR structure is not necessary, but can simplify referencing the structure in subsequent statements.

3. Allocate memory for the XSQLDA using the XSQLDA_LENGTH macro. The following statement allocates storage for in_slqda:

   in_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(10));

   In this statement, space for ten XSQLVAR structures is allocated, allowing the XSQLDA to accommodate up to ten input parameters. Once structures are allocated, assign values to the sqldata fields.

4. Set the version field of the XSQLDA to SQLDA_CURRENT_VERSION, and set the sqln field of the XSQLDA to indicate the number of XSQLVAR structures allocated:

   in_sqlda->version = SQLDA_CURRENT_VERSION;

   in_sqlda->sqln = 10;

Preparing the Output XSQLDA

Most queries return one or more rows of data, referred to as a select-list. Because the number and kind of items returned are unknown when a statement string is executed, an output XSQLDA must be created to store select-list items that are returned at runtime. To prepare the XSQLDA, follow these steps:

1. Declare a variable to hold the XSQLDA needed to process parameters. For example, the following declaration creates an XSQLDA called out_sqlda:

XSQLDA *out_sqlda;

2. Optionally declare a variable for accessing the XSQLVAR structure of the XSQLDA:

   XSQLVAR *var;

   Declaring a pointer to the XSQLVAR structure is not necessary, but can simplify referencing the structure in subsequent statements.

3. Allocate memory for the XSQLDA using the XSQLDA_LENGTH macro. The following statement allocates storage for out_sqlda:

   out_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(10));

   Space for ten XSQLVAR structures is allocated in this statement, enabling the XSQLDA to accommodate up to ten select-list items.

4. Set the version field of the XSQLDA to SQLDA_CURRENT_VERSION, and set the sqln field of the XSQLDA to indicate the number of XSQLVAR structures allocated:

   out_sqlda->version = SQLDA_CURRENT_VERSION;

   out_sqlda->sqln = 10;

Preparing a Query Statement String with Parameters

After an input and an output XSQLDA are created for holding a statement string’s parameters, and the select-list items returned when the statement is executed, the statement string can be created and prepared. When a statement string is prepared, InterBase replaces the placeholder parameters in the string with information about the actual parameters used. The information about the parameters must be assigned to the input XSQLDA (and perhaps adjusted) before the statement can be executed. When the statement string is executed, InterBase stores select-list items in the output XSQLDA.

To prepare a query statement string with parameters, follow these steps:

1. Elicit a statement string from the user or create one that contains the SQL statement to be processed. For example, the following statement creates a SQL statement string with placeholder parameters:

char *str = "SELECT * FROM DEPARTMENT WHERE BUDGET = ?, LOCATION = ?";

This statement string contains two parameters: a value to be assigned to the BUDGET column and a value to be assigned to the LOCATION column.

2. Declare and initialize a SQL statement handle, then allocate it with isc_dsql_allocate():

   isc_stmt_handle stmt; /* Declare a statement handle. */

   stmt = NULL; /* Set handle to NULL before allocation. */

   . . .

   isc_dsql_allocate_statement(status_vector, &db1, &stmt);

3. Prepare the statement string with isc_dsql_prepare(). This sets the statement handle (stmt) to refer to the parsed format. The statement handle is used in subsequent calls to isc_dsql_describe(), isc_dsql_describe_bind(), and isc_dsql_execute2():

   isc_dsql_prepare(status_vector, &trans, &stmt, 0, str, 1, out_xsqlda);

4. Use isc_dsql_describe_bind() to fill the input XSQLDA with information about the parameters contained in the SQL statement:

   isc_dsql_describe_bind(status_vector, &stmt, 1, in_xsqlda);

5. Compare the sqln field of the XSQLDA to the sqld field to determine if the input descriptor can accommodate the number of parameters contained in the statement. If not, free the storage previously allocated to the input descriptor, reallocate storage to reflect the number of parameters specified by sqld, reset sqln and version, then execute isc_dsql_describe_bind() again:

   if (in_sqlda->sqld > in_sqlda->sqln) {

   n = in_sqlda->sqld;

   free(in_sqlda);

   in_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(n));

   in_sqlda->sqln = n;

   in_sqlda->version = SQLDA_CURRENT_VERSION;

   isc_dsql_describe_bind(status_vector, &stmt, 1, in_xsqlda);

   }

6. Process each XSQLVAR parameter structure in the input XSQLDA. Processing a parameter structure involves up to four steps:

   a. Coercing a parameter’s datatype (optional).

   b. Allocating local storage for the data pointed to by the sqldata field of the XSQLVAR. This step is only required if space for local variables is not allocated until runtime. The following example illustrates dynamic allocation of local variable storage space.

   c. Providing a value for the parameter consistent with its datatype (required).

   d. Providing a NULL value indicator for the parameter.

   These steps must be followed in the order presented. The following code example illustrates these steps, looping through each XSQLVAR structure in the in_sqlda XSQLDA:

   for (i=0, var = in_sqlda->sqlvar; i < in_sqlda->sqld; i++, var++) {

   /* Process each XSQLVAR parameter structure here.

   The parameter structure is pointed to by var.*/

   dtype = (var->sqltype & ~1) /* drop flag bit for now */

   switch(dtype) {

   case SQL_VARYING: /* coerce to SQL_TEXT */

   var->sqltype = SQL_TEXT;

   /* allocate proper storage */

   var->sqldata = (char *)malloc(sizeof(char)*var->sqllen);

   /* Provide a value for the parameter. See case SQL_LONG. */

   . . .

   break;

   case SQL_TEXT:

   var->sqldata = (char *)malloc(sizeof(char)*var->sqllen);

   /* Provide a value for the parameter. See case SQL_LONG. */

   . . .

   break;

   case SQL_LONG

   var->sqldata = (char *)malloc(sizeof(long));

   /* Provide a value for the parameter. */

   *(long *)(var->sqldata) = 17;

   break;

   . . .

   } /* end of switch statement */

   if (sqltype & 1) {

   /* allocate variable to hold NULL status */

   var->sqlind = (short *)malloc(sizeof(short));

   }

   } /* end of for loop */

   For more information about data type coercion and NULL indicators, see Coercing Datatypes.

7. Use isc_dsql_describe() to fill the output XSQLDA with information about the select-list items returned by the statement:

   isc_dsql_describe(status_vector, &trans, &stmt, out_xsqlda);

8. Compare the sqln field of the XSQLDA to the sqld field to determine if the output descriptor can accommodate the number of select-list items specified in the statement. If not, free the storage previously allocated to the output descriptor, reallocate storage to reflect the number of select-list items specified by sqld, reset sqln and version, and execute DESCRIBE OUTPUT again:

   if (out_sqlda->sqld > out_sqlda->sqln) {

   n = out_sqlda->sqld;

   free(out_sqlda);

   out_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(n));

   out_sqlda->sqln = n;

   out_sqlda->version = SQLDA_CURRENT_VERSION;

   isc_dsql_describe(status_vector, &trans, &stmt, out_xsqlda);

   }

9. Set up an XSQLVAR structure for each item returned. Setting up an item structure involves the following steps:

a. Coercing an item’s datatype (optional).

b. Allocating local storage for the data pointed to by the sqldata field of the XSQLVAR. This step is required only if space for local variables is not allocated until runtime. The following example illustrates dynamic allocation of local variable storage space.

c. Providing a NULL value indicator for the parameter (optional).

The following code example illustrates these steps, looping through each XSQLVAR structure in the out_sqlda XSQLDA:

for (i=0, var = out_sqlda->sqlvar; i < out_sqlda->sqld; i++, var++) {

dtype = (var->sqltype & ~1) /* drop flag bit for now */

switch(dtype) {

case SQL_VARYING:

var->sqltype = SQL_TEXT;

break;

case SQL_TEXT:

var->sqldata = (char *)malloc(sizeof(char)*var->sqllen);

break;

case SQL_LONG:

var->sqldata = (char *)malloc(sizeof(long));

break;

/* process remaining types */

} /* end of switch statements */

if (sqltype & 1) {

/* allocate variable to hold NULL status */

var->sqlind = (short *)malloc(sizeof(short));

}

} /* end of for loop */

For more information about data type coercion and NULL indicators, see Coercing Datatypes.

Executing a Query Statement String Within the Context of a Cursor

To retrieve select-list items from a statement string, the string must be executed within the context of a cursor. All cursor declarations in InterBase are fixed, embedded statements inserted into the application before it is compiled. DSQL application developers must anticipate the need for cursors when writing the application and declare them ahead of time.

A looping construct is used to fetch a single row at a time from the cursor and to process each select-list item (column) in that row before the next row is fetched.

To execute a statement string within the context of a cursor and retrieve rows of select-list items, follow these steps:

1. Execute the statement with isc_dsql_execute2():

isc_dsql_execute2(status_vector, &trans, &stmt, 1, in_xsqlda, out_xsqlda);

2. Declare and open a cursor for the statement string with isc_dsql_set_cursor_name(). For example, the following statement declares a cursor, dyn_cursor, for the prepared SQL statement string, stmt:

isc_dsql_set_cursor_name(status_vector, &stmt, "dyn_cursor", NULL);

Opening the cursor causes the statement string to be executed, and an active set of rows to be retrieved.

3. Fetch one row at a time with isc_dsql_fetch() and process the select-list items (columns) it contains. For example, the following loops retrieve one row at a time from dyn_cursor and process each item in the retrieved row with an application-specific function called process_column():

while ((fetch_stat = isc_dsql_fetch(status_vector, &stmt, 1, out_sqlda)) == 0)

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

{process_column(sqlda->sqlvar[i]);

}

}

if (fetch_stat != 100L) {

/* isc_dsql_fetch returns 100 if no more rows remain to be retrieved */

SQLCODE = isc_sqlcode(status_vector);

isc_print_sqlerror(SQLCODE, status_vector);

return(1);

}

When all the rows are fetched, close the cursor with isc_dsql_free_statement():

isc_dsql_free_statement(status_vector, &stmt, DSQL_close);

Re-executing a Query Statement String With Parameters

Once a query statement string with parameters is prepared, it can be used as often as required in an application. Before each subsequent use, the input XSQLDA can be supplied with new parameter and NULL indicator data. The cursor must be closed and reopened before processing can occur.

• To provide new parameters to the input XSQLDA, follow steps 3 to 5 of Preparing a Query Statement String with Parameters.
• To provide new information to the output XSQLDA, follow steps 6 to 8 of Preparing a Query Statement String with Parameters.
• To reopen a cursor and process select-list items, repeat steps 2 to 4 of Executing a Query Statement String Within the context of a Cursor.

Advance to Next Section

• Determining an Unknown Statement Type at Runtime