Preparing a Query Statement String Without Parameters

Go Up to Method 3: Query Statements Without Parameters

After an XSQLDA is created for holding the items returned by a query statement string, the statement string can be created, prepared, and described. When a statement string is executed, InterBase creates the select-list of selected rows.

To prepare a query statement string, 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 that performs a query:

   char *str = "SELECT * FROM CUSTOMER";
   

   The statement appears to have only one select-list item (*). The asterisk is a wildcard symbol that stands for all of the columns in the table, so the actual number of items returned equals the number of columns in the table.
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 statements such as ­isc_dsql_describe() and isc_dsql_execute():

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

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

5. 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, then execute ­isc_dsql_describe() 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, 1, out_sqlda);
   }
   

6. Set up an XSQLVAR structure for each item returned. Setting up an item structure involves the following steps:
   1. Coercing a data type of an item (optional).
   2. 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.
   3. Providing a NULL value indicator for the parameter.

   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;
   var->sqldata = (char *)malloc(sizeof(char)*var->sqllen + 2);
   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:

• Executing a Statement String Within the Context of a Cursor