Retrieving Results

In this tutorial, you will learn how to retrieve the records returned by executing an SQL statement.

We call a group of records returned by a query a result set (or recordset for those who are familiar with VB). The general steps in retrieving a result set are as follows:

1. Determine whether a result set is available.
2. Bind the columns of the result set to appropriate variables
3. Fetching a row

When you're done with the result set, you need to destroy it by calling SQLCloseCursor.

Determine whether a result set is available

Sometimes you already know whether a result set will be created by examination of the SQL statement. If the SQL statement is not of a type that returns a result set, you know that no result set will be available. However, sometimes you don't even know of what type the SQL statement is, such is the case when you let the user enters custom SQL statements. In that case, you must check whether a result set was created by calling SQLNumResultCols. This function returns the number of columns (fields) in the result set (if one exists) and has the following syntax:

SQLNumResultCols proto StatementHandle:DWORD, pNumCols:DWORD

• StatementHandle The handle to the statement
• pNumCols A pointer to a dword variable that will receive the number of columns in the result set.

If the value in the variable pointed to by pNumCols is 0, there is no result set.

Bind the columns

In this regard, the concept is identical to that of binding a variable to a parameter of an SQL statement. You associate (bind) a variable to a specific column in the result set. The function in this case is SQLBindCol which has the following syntax:

SQLBindCol proto StatementHandle:DWORD,
                            ColumnNumber:DWORD,
                            TargetType:DWORD,
                            TargetValuePtr:DWORD,
                            BufferLength:DWORD,
                            pStrLenOrIndPtr:DWORD

• StatementHandle The handle to the statement
• ColumnNumber Number of the column in the result set to bind. The column number starts from 1. Column 0 is the bookmark column.
• TargetType The constant that indicates the type of the variable (buffer) pointed to by TargetValuePtr.
• TargetValuePtr A pointer to the variable or buffer that will be bound to the column. When you call SQLFetch to retrieve a row from the result set, the variable or buffer will be filled with the value in the associated column.
• BufferLength The size of the buffer pointed to by TargetValuePtr.
• pStrLenOrIndPtr Look up the detail in SQLBindParameter

Example:

.data?
buffer db 21 dup(?)
DataLength dd ?     ; will be filled with the length of the string in buffer after SQLFetch is called.

.code
     .....
     invoke SQLBindCol, hStmt, 1, SQL_C_CHAR, addr buffer, 21, addr DataLength

Fetching a row

It's quite simple. Calling SQLFetch retrieves a row from the result set into the bound variables. After SQLFetch is called, the cursor is updated. You can think of a cursor as the record pointer. It indicates what row will be returned when SQLFetch is called. For example, if the result set has 4 rows, the cursor is positioned at the first row when the result set was created. When SQLFetch is called, the cursor advances by 1 row. So if you call SQLFetch 4 times, there is no more row to fetch. The cursor is said to point to the end of file (EOF). SQLFetch has the following syntax:

SQLFetch proto StatementHandle:DWORD

This function returns SQL_NO_DATA when no more row is available.

Example:

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

.code
     .....
     invoke SQLBindCol, hStmt, 1, SQL_C_CHAR, addr buffer, 21, addr DataLength
     invoke SQLFetch, hStmt

[Iczelion's Win32 Assembly Homepage]