How to process results (ODBC)

To process results

1. Retrieve result set information.
   
   
2. If bound columns are used, for each column you want to bind to, call SQLBindCol to bind a program buffer to the column.
   
   
3. For each row in the result set:
   • Call SQLFetch to get the next row.
     
     
   • If bound columns are used, use the data now available in the bound column buffers.
     
     
   • If unbound columns are used, call SQLGetData one or more times to get the data for unbound columns after the last bound column. Calls to SQLGetData should be in increasing order of column number.
     
     
   • Call SQLGetData multiple times to get data from a text or image column.
4. When SQLFetch signals the end of the result set by returning SQL_NO_DATA, call SQLMoreResults to determine if another result set is available.
   • If it returns SQL_SUCCESS, another result set is available.
     
     
   • If it returns SQL_NO_DATA, no more result sets are available.
     
     
   • If it returns SQL_SUCCESS_WITH_INFO or SQL_ERROR, call SQLGetDiagRec to determine if the output from a PRINT or RAISERROR statement is available.

   If bound statement parameters are used for output parameters or the return value of a stored procedure, use the data now available in the bound parameter buffers. Also, when bound parameters are used, each call to SQLExecute or SQLExecDirect will have executed the SQL statement S times, where S is the number of elements in the array of bound parameters. This means that there will be S sets of results to process, where each set of results comprises all of the result sets, output parameters, and return codes usually returned by a single execution of the SQL statement.

   Note that when a result set contains compute rows, each compute row is made available as a separate result set. These compute result sets are interspersed within the normal rows and break normal rows into multiple result sets.

5. Optionally, call SQLFreeStmt with an fOption of SQL_UNBIND to release any bound column buffers.
   
   
6. If another result set is available, go to Step 1.

To cancel processing a result set before SQLFetch returns SQL_NO_DATA, call SQLCloseCursor.

Examples

This example shows how to use either SQLBindCol or SQLGetData. It has been simplified by removing all error checking. The program can be compiled with either the SQLBindCol function or the SQLGetData function commented out, the resulting executable will operate the same.

#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>

#define MAXBUFLEN   255

SQLHENV      henv = SQL_NULL_HENV;
SQLHDBC      hdbc1 = SQL_NULL_HDBC;     
SQLHSTMT      hstmt1 = SQL_NULL_HSTMT;

int main() {
   RETCODE retcode;
   // SQLBindCol variables
   SQLCHAR      szName[MAXNAME+1];
   SQLINTEGER   cbName;

    // Allocate the ODBC Environment and save handle.
   retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
   // Notify ODBC that this is an ODBC 3.0 application.
   retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                     (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
   // Allocate an ODBC connection and connect.
   retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
   retcode = SQLConnect(hdbc1,
            "MyDSN", SQL_NTS,"sa", SQL_NTS, "MyPassWord", SQL_NTS);
   
   // Allocate a statement handle.
   retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
   // Execute an SQL statement directly on the statement handle.
   // Uses a default result set because no cursor attributes are set.
   retcode = SQLExecDirect(hstmt1,
                     "SElECT au_lname FROM authors", SQL_NTS);

   // Simplified result set processing. Fetch until SQL_NO_DATA.
   // The application can be compiled with the SQLBindCol line
   // commented out to illustrate SQLGetData, or compiled with the
   // SQLGetData line commented out to illustrate SQLBindCol.
   // This sample shows that SQLBindCol is called once for the
   // result set, while SQLGetData must be called once for each
   // row in the result set.

   retcode = SQLBindCol(hstmt1, 1, SQL_C_CHAR,
                  szName, MAXNAME, &cbName);
   while ( (retcode = SQLFetch(hstmt1) ) != SQL_NO_DATA ) {
   //   SQLGetData(hstmt1, 1, SQL_C_CHAR, szName, MAXNAME, &cbName);
      printf("Name = %s\n", szName);
   }

   /* Clean up.*/
   SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
   SQLDisconnect(hdbc1);
   SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
   SQLFreeHandle(SQL_HANDLE_ENV, henv);

   return(0);
}

See Also

Assigning Storage (Binding)

Determining the Characteristics of a Result Set

Fetching Result Data

How to retrieve result set information (ODBC)

SQLBindCol

SQLCloseCursor

SQLFreeStmt

SQLGetData

SQLMoreResults