Method 3: Query Statements Without Parameters

There are three steps to processing a SQL query statement string without parameters:

1. Prepare an output XSQLDA to process the select-list items returned when the query is executed.
2. Prepare the statement string.
3. Use a cursor to execute the statement and retrieve select-list items from the output XSQLDA.

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 created, 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 store the column data for each row that will be fetched. 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 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:

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

Executing a Statement String Within the Context of a Cursor

To retrieve select-list items from a prepared statement string, the string can be executed within the context of a cursor. All cursor declarations in InterBase are fixed 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 cursor is only needed to process positioned UPDATE and DELETE statements made against the rows retrieved by isc_dsql_fetch() for SELECT statements that specify an optional FOR UPDATE OF clause.

The following descriptions apply to the situations when a cursor is needed. For an example of executing a statement and fetching rows without using a cursor, see isc_dsql_fetch().

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 prepared statement with isc_dsql_execute():

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

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 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 and process the select-list items (columns) it contains with isc_dsql_fetch(). 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);

}

The process_column() function mentioned in this example processes each returned select-list item. The following skeleton code illustrates how such a function can be set up:

void process_column(XSQLVAR *var)

{

/* test for NULL value */

if ((var->sqltype & 1) && (*(var->sqlind) = -1)) {

/* process the NULL value here */

}

else {

/* process the data instead */

}

. . .

}

4. 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 Without Parameters

Once a query statement string without parameters is prepared, it can be executed as often as required in an application by closing and reopening its cursor. To reopen a cursor and process select-list items, repeat steps 2 through 4 of Executing a Statement String Within the Context of a Cursor.

Advance to Next Section

• Method 4: Query Statements With Parameters