Preparing and Using Statements

In this tutorial, we will further our study of ODBC programming. Specifically, we will learn how to interact with the data source via ODBC.

In the previous tutorial, you know how to make a connection to a data source. It's the first step. A connection defines a data path between you and the data source. It's passive. In order to interact with the data source, you need to use statement. You can think of a statement as a command you send to a data source. The "command" must be written in SQL. With statement, you can modify the structure of the data source, query it for data, update data and delete data.

The steps in preparing and using statement are as follows:

Allocate a statement handle
Create the SQL statement
Execute the statement
Destroy the statement

Allocate a statement handle

You allocate a statement handle by calling SQLAllocHandle, passing it the appropriate arguments. For example,

.data?
hStmt dd ?

.code
......
invoke SQLAllocHandle, SQL_HANDLE_STMT, hConn, addr hStmt

Create the SQL statement

This part, you have to help yourself. You must learn about SQL grammar. For example, if you want to create a table, you need to understand CREATE TABLE.

Execute the statement

There are four ways of executing a statement, depending on when they are compiled (prepared) by the database engine and who defines them.

Direct Execution	Your program defines the SQL statement. The statement is compiled and executed at runtime in one step.
Prepared Execution	Your program also defines the SQL statement. However, the preparation and execution are divided into two steps: first the SQL statement is prepared (compiled) and then it is executed. With this method, you can compile the SQL statement once and then execute the same SQL statement multiple times. It saves time.
Procedures	The SQL statements are compiled and stored at the data source. Your program calls those statements at run time.
Catalog	The SQL statements are hardcoded into the ODBC driver. The purpose of catalog functions is to return predefined result sets such as the names of the tables in the database. All in all, catalog functions are used to obtain information about the data source. Your program calls them at run time.

These four methods have their pros and cons. Direct execution is good when you run a particular SQL statement only once. If you need to run a particular statement several times in succession, you'd better use prepared execution because the SQL statement will be compiled the first time it runs. On subsequent runs, it will execute faster because the statement is already compiled. Procedures are the best choices if you want speed. Because the procedures are already compiled and stored at the data source, they run fast. The downside is that not all data stores support procedures. Catalog is for obtaining information about the structure of the data source.

In this tutorial, we will focus on direct execution and prepared execution because they are done on our application's side. Writing procedures (stored procedures) are DBMS-specific. We will make use of catalog functions in the future.

Direct Execution

To execute your SQL statement directly and instantly, call SQLExecDirect which has the following syntax:

SQLExecDirect proto StatementHandle:DWORD,
                                  pStatementText:DWORD,
                                  TextLength:DWORD

StatementHandle. The handle to the statement you want to use
pStatementText. The pointer to the SQL string you want to execute
TextLength. The length of the SQL string.

The possible return values are:

SQL_SUCCESS	The operation is successful.
SQL_SUCCESS_WITH_INFO	The operation is successful and it may encounter a non-fatal error.
SQL_ERROR	The operation failed
SQL_INVALID_HANDLE	The statement handle you gave to the function was invalid
SQL_NEED_DATA	If the SQL statement included one or more parameters and you failed to supply them before execution, you'll get this return value. You then need to submit the parameters via SQLParamData or SQLPutData.
SQL_NO_DATA	If your SQL statement doesn't return a result set, ie. it's just an action query, you get this value so you know that the action completed successfully but no result set is returned.
SQL_STILL_EXECUTING	If you execute the statement asynchronously, SQLExecDirect returns immediately with this value, indicating that the statement is being processed. By default, ODBC drivers operate in synchronous mode which is good if you use a multithreading OS. If you want to execute asynchronously, you can set the statement attribute with SQLSetStmtAttr.

Example:

.data
SQLStmt db "select * from Sales",0

.data?
hStmt dd ?

.code
     .....
     invoke SQLAllocHandle, SQL_HANDLE_STMT, hConn, addr hStmt
      .if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
         invoke SQLExecDirect, hStmt, addr SQLStmt, sizeof SQLStmt

Prepared Execution

The process of executing an SQL statement is divided into two distince phases. In the first phase, you must *prepare* the statement by calling SQLPrepare. Next, you call SQLExecute to actually run the statement. With prepared execution, you can call SQLExecute on the same SQL statement any number of times. Combined with using SQL parameters, this method is very effective for running the same SQL statement over and over again.

SQLPrepare takes the same three parameters as SQLExecDirect so I won't show its definition here. SQLExecute has the following syntax:

SQLExecute proto StatementHandle:DWORD

With only one parameter, I don't think it needs explanation ;)

Example:

.data
SQLStmt db "select * from Sales",0

.data?
hStmt dd ?

.code
     .....
     invoke SQLAllocHandle, SQL_HANDLE_STMT, hConn, addr hStmt
      .if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
         invoke SQLPrepare, hStmt, addr SQLStmt, sizeof SQLStmt
         invoke SQLExecute, hStmt

You may wonder what the advantage of prepared execution over direct execution is. From the above examples, it's not apparent. We need to know about statement parameters to be able to appreciate it fully.

Statement Parameters

Parameters, as mentioned here, are variables that are used by SQL statements. For example, if we have a table named "employee" that has three fields, "name", "surname", and "TelephoneNo" and we need to find the telephone no. of an employee named "Bob", we can use the following SQL statement:

select telephoneNo from employee where name='Bob'

This SQL statement works the way we want but what if you want to find the telephone no. of another employee? If you don't use a parameter, you don't have any choice but to construct a new SQL string and compile/run it again.

Now let's say we can't tolerate this inefficiency anymore. We can use a parameter to our advantage. In our example above, we must replace the string/value with ? (called parameter marker). The SQL string will be:

select telephoneNo from employee where name=?

Think a bit about this: How can the ODBC driver know what value it should put in place of the parameter marker? The answer: we must supply it with the desired value. The method is called parameter binding. Simply, it's the process of associating a parameter marker to a variable in your application. In the above example, we need to create a string buffer and then tell the ODBC driver that when it needs the actual value of a parameter, it should obtain the value from the string buffer we provided. Once a parameter is bound to a variable, it remains bound to that variable until it's bound to a different variable, or until all parameters are unbound by calling SQLFreeStmt with SQL_RESET_PARAMS or until the statement is released.

You bind a parameter to a variable by calling SQLBindParameter which has the following syntax:

SQLBindParameter proto StatementHandle:DWORD,
                                         ParameterNumber:DWORD,
                                         InputOutputType:DWORD,
                                         ValueType:DWORD,
                                         ParameterType:DWORD,
                                         ColumnSize:DWORD,
                                         DecimalDigits:DWORD,
                                         ParameterValuePtr:DWORD,
                                         BufferLength:DWORD,
                                         pStrLenOrIndPtr:DWORD

StatementHandle The handle of the statement.
ParameterNumber The parameter number, starting from 1. This is the way ODBC uses to identify the parameter markers. If there are three parameters, the leftmost one is parameter no.1 and the rightmost is parameter no.3
InputOutputType A flag to specify whether this parameter is for input or output. Input here means the ODBC driver grabs the value in the parameter for its own use while Output means the ODBC driver will place the result in the parameter when the operation was done. Most of the time, we use parameter for input. Output parameter is usually associated with stored procedures. Two possible values are: SQL_PARAM_INPUT, SQL_PARAM_INPUT_OUTPUT and SQL_PARAM_OUTPUT
ValueType Specify the type of the variable or buffer in your application, that you want to bind to the parameter. There is a list of constants for the available types. Their names begin with SQL_C_
ParameterType Specify the SQL type of the parameter. For example, if the SQL parameter is a text field, you must put the value SQL_CHAR here. You need to look up the complete list from ODBC Programmer's reference on MSDN.
ColumnSize The size of the parameter or you can think of this field as the size of the column (field) associated with the parameter marker. In our example, our parameter marker is used as a criteria on the column "name". If that column is defined as 20 bytes in size, you should pass 20 in ColumnSize.
DecimalDigits The number of decimal places of the column associated with the parameter marker.
ParameterValuePtr A pointer to a buffer for the parameter's data.
BufferLength The size of the buffer pointed to by ParameterValuePtr.
pStrLenOrIndPtr This is a pointer to a dword variable that contains one of the following values:
- The length of the parameter value stored in the buffer pointed to by ParameterValuePtr. This value is ignored unless the parameter type is a text string or binary. Don't confuse this with BufferLength. An example would make it clear. Suppose the parameter is a text string. The column is 20 bytes wide. So you allocate a 21-byte buffer and pass its address to ParameterValuePtr. Just before calling SQLExecute, you put the string "Bob" into the buffer. This string is 3 bytes long thus you need to put the value 3 in the variable pointed to by pStrLenOrIndPtr.
- SQL_NTS. The parameter value is a null-terminated string.
- SQL_NULL_DATA. The parameter value is NULL.
- SQL_DEFAULT_PARAM. A procedure is to use the default value of a parameter, rather than a value retrieved from the application. This is applicable only to stored prcoedures which have default parameter values defined.
- SQL_DATA_AT_EXEC. The data for the parameter will be sent with SQLPutData. Since the data may be too large to hold in memory such as the data in a whole file, you tell the ODBC driver that you will send the data to it via SQLPutData instead.
  
  So many possible values for pStrLenOrIndPtr, you would surely say. But mostly, we will use only the first or third option

Example:

.data
SQLString db "select telephoneNo from employee where name=?",0
Sample1 db "Bob",0
Sample2 db "Mary",0

.data?
buffer db 21 dup(?)
StrLen dd ?

.code
     ........
     invoke SQLPrepare, hStmt, addr SQLString,sizeof SQLString
     invoke SQLBindParameter, hStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 20, 0, addr buffer, sizeof buffer, addr StrLen
;===========================================
; First run
;===========================================
     invoke lstrcpy, addr buffer, addr Sample1
     mov StrLen, sizeof Sample1
     invoke SQLExecute, hStmt
;===========================================
; Second run
;===========================================
     invoke lstrcpy, addr buffer, addr Sample2
     mov StrLen, sizeof Sample2
     invoke SQLExecute, hStmt

Note that we bind the parameter to a buffer only once and then we modify the content of the buffer and call SQLExecute several times. No need to call SQLPrepare again. The ODBC driver knows where to find the parameter value it needs because we "told" it by calling SQLBindParameter.

We will ignore the records returned by the query for now. Accessing and using the result set is the subject of the future tutorials.

Supposed that you're done with a particular SQL statement and you want to execute a new statement, you need not allocate a new statement handle. You should unbind the parameters (if any) by calling SQLFreeStmt with SQL_UNBIND and SQL_RESET_PARAMS. Then you can "reuse" the statement handle for the next SQL statement.

Freeing the statement

It's done by calling SQLFreeHandle.

[Iczelion's Win32 Assembly Homepage]