Method 4: Query Statements with Parameters (Embedded SQL Guide)

Go Up to DSQL Programming Methods (Embedded SQL Guide)

Preparing an input XSQLDA to process the parameters of a statement string.
Preparing an output XSQLDA to process the select-list items returned when the query is executed.
Preparing the statement string and its parameters.
Using 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 run time. To prepare the XSQLDA, follow these steps:

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;
```
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.
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 10 XSQLVAR structures is allocated, allowing the XSQLDA to accommodate up to 10 input parameters. Once structures are allocated, assign values to the sqldata field in each XSQLVAR.
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 to Query Statements With Parameters

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 run time. To prepare the XSQLDA, follow these steps:

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;
```
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.
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 10 XSQLVAR structures is allocated in this statement, enabling the XSQLDA to accommodate up to 10 select-list items.
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 parameters of a statement string, 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 field and a value to be assigned to the LOCATION field.

2. Prepare and name the statement string with PREPARE. The name is used in subsequent calls to DESCRIBE and EXECUTE:

EXEC SQL
PREPARE SQL_STMT FROM :str;

SQL_STMT is the name assigned to the prepared statement string.

3. Use DESCRIBE INPUT to fill the input XSQLDA with information about the parameters contained in the SQL statement:

EXEC SQL
DESCRIBE INPUT SQL_STMT USING SQL DESCRIPTOR in_sqlda;

4. 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 DESCRIBE INPUT 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;
EXEC SQL
DESCRIBE INPUT SQL_STMT USING SQL DESCRIPTOR in_sqlda;
}

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

Coercing a data type of a parameter (optional).
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 run time. The following example illustrates dynamic allocation of local variable storage space.
Providing a value for the parameter consistent with its data type (required).
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 Data Types.

6. Use DESCRIBE OUTPUT to fill the output XSQLDA with information about the select-list items returned by the statement:

EXEC SQL
DESCRIBE OUTPUT SQL_STMT INTO SQL DESCRIPTOR out_sqlda;

7. 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;
EXEC SQL
DESCRIBE OUTPUT SQL_STMT INTO SQL DESCRIPTOR out_sqlda;
}

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

Coercing a data type of an item (optional).
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 run time. The following example illustrates dynamic allocation of local variable storage space.
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 Data Types.

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:

Declare a cursor for the statement string. For example, the following statement declares a cursor, DYN_CURSOR, for the prepared SQL statement string, SQL_STMT:
```
EXEC SQL
DECLARE DYN_CURSOR CURSOR FOR SQL_STMT;
```
Open the cursor, specifying the input descriptor:
```
EXEC SQL
OPEN DYN_CURSOR USING SQL DESCRIPTOR in_sqlda;
```
Opening the cursor causes the statement string to be executed, and an active set of rows to be retrieved. For more information about cursors and active sets, see Working with Data.
Fetch one row at a time 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 (here called process_column()):
```
while (SQLCODE == 0)
{
EXEC SQL
FETCH DYN_CURSOR USING SQL DESCRIPTOR out_sqlda;
if (SQLCODE == 100)
break;
for (i = 0; i < out_sqlda->sqld; i++)
{
process_column(out_sqlda->sqlvar[i]);
}
}
```
When all the rows are fetched, close the cursor:
```
EXEC SQL
CLOSE DYN_CURSOR;
```

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–5 of Preparing a Query Statement String with Parameters.

To provide new information to the output XSQLDA, follow steps 6–8 of Preparing a Query Statement String with Parameters.

To reopen a cursor and process select-list items, repeat steps 2–4 of Executing a Query Statement String Within the Context of a Cursor.

Advance To:

Embedded SQL Guide