<< Click to Display Table of Contents >> Navigation:  Application development > Querying databases > Executing queries in stages

Some queries require a statement handle and need to be executed in stages. A statement handle is required if the application needs to control the table type of the result set, to express preference over the degree of liveness of data, or to bind parameters for queries. The application uses a separate function call for each stage:

1.To obtain a new statement handle, call DbiQAlloc.

2.To change properties in the statement handle, call DbiSetProp. At this point you can also indicate whether you want the result set to be "live," that is, modifiable.

3.To prepare the query, call DbiQPrepare.

4.To execute the prepared query, call DbiQExec.

5.To free resources bound to the query, call DbiQFree.

DbiQAlloc

This function allocates a statement handle required for prepared query functions. It specifies the database handle and whether the query language is QBE or SQL, returning a statement handle for the prepared query. DbiQAlloc is the necessary first step in all prepared queries.

DbiSetProp

DbiSetProp is used to set a property of an object to a specified value. In this case, the object is the statement handle returned by DbiQAlloc. The property to be set can be the result table type, degree of liveness, or query mode for binding parameters. The following examples show how values are set for these properties:

DbiSetProp(hStmt, stmtANSTYPE, (UINT32) szPARADOX);

DbiSetProp(hStmt, stmtLIVENESS, (UINT32) wantLIVE);

DbiQPrepare

This function is used to prepare a SQL or QBE query for subsequent execution. It accepts a handle to a statement containing the prepared query.

Live and canned result sets

The last example above shows how you can specify your preference for live or canned result sets during query execution. A canned result set is like a snapshot or a copy of the original data selected by the query. In contrast, a live result set is a view of the original data; specifically, if you modify a live result set, the changes are reflected in the original data.

When you specify your preference for a live result set, the Query Manager attempts to give you a live result set. However, no guarantee can be made that the resulting result set will indeed be live. After the query has executed and a result set has been returned, you can check to see if it is live by examining the cursor property bTempTable. If TRUE, the result set is a temporary table, hence a copy (canned); otherwise, the result set is live.

SQL queries against SQL servers return an error if the result cannot be made live. bTempTable is valid for local queries.

The possible values for liveness are:

DbiQExec

DbiQExec executes the previously prepared query identified by the supplied statement handle and returns a cursor to the result set, if one is generated.

For all queries, remote and local, the same prepared query can be executed several times, but only after any pending results have been read or discarded (by using DbiCloseCursor on the answer set cursor).

DbiQFree

This function is always used as the final step in executing prepared queries to free all system resources allocated during preparation and use of a query. If cursors are associated with an outstanding result set produced by execution of the statement, the cursors remain valid and the dependent statement resources are not released until the last cursor has been closed or the result set is read to completion, whichever happens first.