Preparing a Query Statement String with Parameters

From InterBase

Go Up to Method 4: Query Statements 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 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 data type (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 data type (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 Data Types.
  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 a data type of an item (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 Data Types.

Advance To: