Method 2: Non-query Statements With Parameters

There are two steps to processing a SQL statement string containing a non-query statement with placeholder parameters:

1. Create an input XSQLDA to process a statement string’s parameters.
2. Prepare and execute the statement string with its parameters.

Creating 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 execute time. 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_sqlda:

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

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

in_sqlda->version = SQLDA_CURRENT_VERSION;

in_sqlda->sqln = 10;

Preparing and Executing a Statement String with Parameters

After an XSQLDA is created for holding a statement string’s parameters, the statement string can be created and prepared. Local variables corresponding to the placeholder parameters in the string must be assigned to their corresponding sqldata fields in the XSQLVAR structures.

To prepare and execute a non-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 = "UPDATE DEPARTMENT SET 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. Parse 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_bind() and isc_dsql_execute():

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

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_sqlda);

5. Compare the value of the sqln field of the XSQLDA to the value of the sqld field to make sure enough XSQLVARs are allocated to hold information about each parameter. sqln should be at least as large as sqld. 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_sqlda);

   }

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

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

b. Allocate 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. Provide a value for the parameter consistent with its datatype (required).

d. Provide a NULL value indicator for the parameter.

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.

* Var points to the parameter structure. */

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

switch(dtype) {

case SQL_VARYING: /* coerce to SQL_TEXT */

var->sqltype = SQL_TEXT;

/* allocate local variable storage */

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

. . .

break;

case SQL_TEXT:

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

/* provide a value for the parameter */

. . .

break;

case SQL_LONG:

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

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

break;

. . .

} /* end of switch statement */

/* allocate variable to hold NULL status */

if (sqltype & 1) {

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

}

} /* end of for loop */

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

7. Execute the named statement string with isc_dsql_execute(). For example, the following statement executes a statement string named stmt:

isc_dsql_execute(status_vector, &trans, &stmt, 1, in_sqlda);

Re-executing the Statement String

Once a non-query statement string with parameters is prepared, it can be executed as often as required in an application. Before each subsequent execution, the input XSQLDA can be supplied with new parameter and NULL indicator data.

To supply new parameter and NULL indicator data for a prepared statement, repeat step 6 of Preparing and Executing a Statement String with Parameters.

Advance to Next Topic

• Method 3: Query Statements Without Parameters