Step 3

Step 3. Building and executing SQL statements

The third step is to build and execute an SQL statement (see Chapter 3 for an introduction to SQL language). The methods used to perform this step are likely to vary tremendously. The application might prompt the user to enter an SQL statement, build an SQL statement based on user input, or use a hard-coded SQL statement (as shown in Example 5.2).

The text of a SQL statement is always stored in a character string. If the SQL statement contains parameters, the application binds them to application variables by calling f90SQLBindParameter for each parameter. After the SQL statement is built and any parameters are bound, the statement is executed with f90SQLExecDirect. If the statement will be executed multiple times, it can be prepared with f90SQLPrepare and executed with f90SQLExecute.

The application might also forgo executing an SQL statement altogether. It may instead call a function to return a result set containing catalog information, such as the available columns or tables.

There are four ways to execute a statement, depending on when they are compiled (prepared) by the database engine and who defines them:

Direct Execution. The application defines the SQL statement. It is prepared and executed at run time in a single step (using f90SQLExecDirect). This is the simplest way to execute SQL statements. Direct execution is commonly used by generic applications that build and execute statements at run time.

Prepared Execution. The application defines the SQL statement. It is prepared (using f90SQLPrepare) and executed (with f90SQLExecute) at run time in separate steps. The statement can be prepared once and executed multiple times. This is useful if you need to run the same query with different parameters each time.

Procedures. The application can define and compile one or more SQL statements at development time, and store these statements on the data source as a procedure. The procedure is executed one or more times at run time. The application can enumerate available stored procedures using catalog functions.

Catalog Functions. The driver writer creates a function that returns a predefined result set. Usually, this function submits a predefined SQL statement or calls a procedure created for this purpose. The function is executed one or more times at run time.

A particular statement (as identified by its statement handle) can be executed any number of times. The statement can be executed with a variety of different SQL statements, or it can be executed repeatedly with the same SQL statement.

In Example 5.2 we used the Prepared Execution technique. We could also replace the f90SQLPrepare and f90SQLExecute calls for a single call to f90SQLExecDirect as follows (note that the original calls were commented out in this exert):

!STEP 3: Preparing and Executing

!Prepare statement. Note that for this case, we could have

!used f90SQLExecDirect instead, omitting this step

!call f90SQLPrepare(StmtHndl, StmtStr, iRet)

!Execute SQL statement

!call f90SQLExecute(StmtHndl, iRet)

!This is a nother way to do the same

call f90SQLExecDirect(StmtHndl, StmtStr, iRet)

A typical call to f90SQLExecDirect has this form:

call f90SQLExecDirect

(StmtHndl, {SQLStr,|SQLStrPtr,SQLStrLength,} iRet)

Where StmtHndl is a statement handle and SQLStr is a character string containing a SQL statement. Alternatively, you can also call f90SQLExecDirect with the pair of parameters SQLStrPtr and SQLStrLength. SQLStrPtr is a pointer to a character string, and SQLStrLength is the length of the string. f90SQLExecDirect will return a status code in iRet indicating whether execution was successfully completed or not.