ODBC from C Tutorial Part 2 - Fetching Results

Contents

• Introduction
• Pre-requisites
• Assumptions
• Fetching Results
  • Binding Columns
  • A Common Problem with Bound Data
  • SQLBindCol and Data Type
  • SQLBindCol and SQLGetData
• Returning Multiple Rows
  • Preparing to Return Multiple Rows
  • SQLExtendedFetch
  • SQLFetchScroll
• Appendix A: Resources

Introduction

This is part 2 of a series of Easysoft tutorials on using ODBC from C.

Pre-requisites

Before you start this tutorial, you need to ensure you satisfy all the pre-requisites:

1. C

   You will need an ANSI C compiler. For this tutorial, we used gcc on Linux, but with a few small alterations the C compilers from Sun, HP, IBM etc. will work just as well.

2. ODBC Driver Manager

   You link your application with an ODBC Driver Manager that will:

   • Provide the C header files that allow you to compile your application.
   • Load the driver you want to connect to.

   We recommend you use the unixODBC driver manager.

   You probably have the unixODBC driver manager installed if you have the odbcinst command (for Easysoft ODBC drivers, the unixODBC driver manager is located in /usr/local/easysoft/unixODBC and the odbcinst command in the bin subdirectory of that path).

   We used unixODBC 2.2.12 in this tutorial. You can find out the unixODBC version you are using with:

        odbcinst --version
      

3. ODBC Driver

   You will need an ODBC driver and a working database to connect to. Easysoft can supply ODBC drivers for many databases and operating systems. All Easysoft ODBC drivers include the unixODBC driver manager.

   For this tutorial, we used the Easysoft ODBC-ODBC Bridge as the ODBC driver to access a remote Microsoft SQL Server database from UNIX.

Assumptions

This tutorial does not explain the C language and how to write C. It is expected you already understand the C programming language, and are able to edit, compile and link programs.

We have assumed you have a good ODBC API reference to hand as this tutorial is not an attempt to just reproduce the ODBC Programmers Reference; it is more example based.

Operating System

This tutorial was designed on UNIX and we have assumed you are using UNIX too. However, all the C examples should work equally well on Microsoft Windows and other operating systems with some minor alterations (e.g. including windows.h on Windows and making the appropriate compiler/linker changes).

ODBC Driver Manager

We have assumed you are using the unixODBC driver manager. All discussion in this document relating to the location and definition of ODBC data sources is for unixODBC.

ODBC from C Tutorial Part 1

This tutorial is designed to follow on from ODBC from C Tutorial Part 1, and it is assumed that the reader will be familiar with that document before progressing to this one.

Fetching Results

We saw in ODBC from C Tutorial Part 1 that the process for returning data from a result set consisted of:

1. Preparing the query.
2. Checking the query metadata for the number of columns and the type of those columns.
3. Executing the query.
4. Fetching each row.
5. For each row, fetching each column from that row.

Binding Columns

We can combine part 4 and 5 in the above list through a process called binding columns. What this involves is associating memory with the result set such that at the time SQLFetch is called, the data for the columns will be copied into the bound memory using the SQLBindCol call. This call can be viewed as a extension to SQLGetData, but is only called once for each column, before the row is fetched one or more times.

If we look at the arguments to the SQLBindCol call, we see the similarity to the SQLGetData call.

SQLRETURN SQLBindCol(
  SQLHSTMT StatementHandle,
  SQLUSMALLINT ColumnNumber,
  SQLSMALLINT TargetType,
  SQLPOINTER TargetValue,
  SQLLEN BufferLength,
  SQLLEN *StrLen_or_Ind );

The above arguments are the same as in the description of the SQLGetData call. The use of this call is shown in the following code snippet. In the example code, column-wise binding (the default binding orientation) is used when SQLFetch is called.

#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

main() {
   SQLHENV env;
   SQLHDBC dbc;
   SQLHSTMT stmt;
   SQLSMALLINT columns; /* number of columns in result-set */
   SQLCHAR buf[ 5 ][ 64 ];
   int row = 0;
   SQLINTEGER indicator[ 5 ];
   int i;

   /* Allocate an environment handle */
   SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

   /* We want ODBC 3 support */
   SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);

   /* Allocate a connection handle */
   SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

   /* Connect to the DSN mydsn */
   /* You will need to change mydsn to one you have created */
   /* and tested */
   SQLDriverConnect(dbc, NULL, "DSN=mydsn;", SQL_NTS,
                    NULL, 0, NULL, SQL_DRIVER_COMPLETE);

   /* Allocate a statement handle */
   SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
   /* Retrieve a list of tables */

   SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, "TABLE", SQL_NTS);
   /* How many columns are there */

   SQLNumResultCols(stmt, &columns);

   /* Loop through the rows in the result-set binding to */
   /* local variables */
   for (i = 0; i < columns; i++) {
      SQLBindCol( stmt, i + 1, SQL_C_CHAR,
            buf[ i ], sizeof( buf[ i ] ), &indicator[ i ] );
   }

   /* Fetch the data */
   while (SQL_SUCCEEDED(SQLFetch(stmt))) {
      /* display the results that will now be in the bound area's */
      for ( i = 0; i < columns; i ++ ) {
         if (indicator[ i ] == SQL_NULL_DATA) {
            printf("  Column %u : NULL\n", i);
         }
         else {
            printf("  Column %u : %s\n", i, buf[ i ]);
         }
      }
   }
}

A Common Problem with Bound Data

One very important difference that must be remembered is that unlike the SQLGetData call, the data will not be returned to the area indicated by TargetValue and StrLen_or_Ind variables until the SQLFetch is performed. Because of this, it is important to remember that the values must still be in scope when the SQLFetch call is made.

The following code will fail or at the very least return unexpected values because the values bound are no longer in scope when SQLFetch is called.

#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

/* Bind the columns, but by binding to local data, a problem is created */
/* for the future */
int bind_col( SQLHSTMT stmt, int col ) {
   SQLCHAR buf[ 64 ];
   SQLINTEGER indicator;
   return SQLBindCol( stmt, col + 1, SQL_C_CHAR,
         buf, sizeof( buf), &indicator );
}

/* Fetch the data, the driver will be writing to data out of scope at */
/* this point */
void fetch_data( SQLHSTMT stmt ) {
   int return;
   while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
      printf( "fetched\n" );
   }
}

main() {
   SQLHENV env;
   SQLHDBC dbc;
   SQLHSTMT stmt;
   SQLRETURN ret; /* ODBC API return status */
   SQLSMALLINT columns; /* number of columns in result-set */
   int row = 0;
   int i;

   /* Allocate an environment handle */
   SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

   /* We want ODBC 3 support */
   SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);

   /* Allocate a connection handle */
   SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

   /* Connect to the DSN mydsn */
   /* You will need to change mydsn to one you have created and tested */
   SQLDriverConnect(dbc, NULL, "DSN=mydsn;", SQL_NTS,
                    NULL, 0, NULL, SQL_DRIVER_COMPLETE);

   /* Allocate a statement handle */
   SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
   /* Retrieve a list of tables */

   SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, "TABLE", SQL_NTS);
   /* How many columns are there */

   SQLNumResultCols(stmt, &columns);

   /* Loop through the rows in the result-set binding to */
   /* local variables */
   for (i = 0; i < columns; i++) {
      ret = bind_col( stmt, i );
   }

   fetch_data( stmt );
}

SQLBindCol and Data Type

As with SQLGetData, the type the data is returned in is selected by the TargetType used to bind, and the values passed into the TargetValue field must match the type, and there must be sufficient memory available to store the returned information.

Of course, each column may be bound to different types, as this example shows.

#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

main() {
   SQLHENV env;
   SQLHDBC dbc;
   SQLHSTMT stmt;
   SQLRETURN ret; /* ODBC API return status */
   SQLSMALLINT columns; /* number of columns in result-set */
   SQLCHAR table[ 64 ];
   SQLCHAR column[ 64 ];
   long type;
   SQLLEN indicator[ 3 ];
   int i;

   /* Allocate an environment handle */
   SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

   /* We want ODBC 3 support */
   SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);

   /* Allocate a connection handle */
   SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

   /* Connect to the DSN mydsn */
   /* You will need to change mydsn to one you have created and tested */
   SQLDriverConnect(dbc, NULL, "DSN=mydsn;", SQL_NTS,
                 NULL, 0, NULL, SQL_DRIVER_COMPLETE);

   /* Allocate a statement handle */
   SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
   /* Retrieve a list of columns */

   SQLColumns(stmt, NULL, 0, NULL, 0, "tablename", SQL_NTS, NULL, 0);

   ret = SQLBindCol( stmt, 3, SQL_C_CHAR,
         table, sizeof( table ), &indicator[ 0 ] );
   ret = SQLBindCol( stmt, 4, SQL_C_CHAR,
         column, sizeof( column ), &indicator[ 1 ] );
   ret = SQLBindCol( stmt, 5, SQL_C_LONG, &type, 0, &indicator[ 2 ] );

   /* Fetch the data */
   while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
      printf( "%s.%s type %d\n", table, column, type );
   }
}

SQLBindCol and SQLGetData

The previous example also shows another feature of SQLBindCol -- not all the columns in a result set need to be bound.

It is possible to mix bound columns and getting data with SQLGetData, but many drivers have restrictions on how this can be done. One common restriction is that SQLGetData may only be called on columns after the last bound column. The application can query the driver to find how calls to SQLGetData and bound parameters may be intermixed.

The SQLGetInfo value, SQL_GETDATA_EXTENSIONS, returns a bitmask with the following values that relate to bound columns.

SQLGetData is only required to return data from unbound columns that occur after the last bound column, are called in order of increasing column number and are not in a row in a block of rows.

Returning Multiple Rows

The previous section described how to bind columns and return data one row at a time. The next step is to return data for more than one row at once. SQLBindCol allows this to be done, by binding the address of an array of values to transfer into instead of a single value.

Preparing to Return Multiple Rows

To return multiple rows, the Statement attribute SQL_ATTR_ROW_ARRAY_SIZE must be set to the required value using the SQLSetStmtAttr call. Then, memory bound to the columns using the SQLBindCol call must be large enough to contain the returned data. For example, if the type was bound as a SQL_C_LONG that is a 4 byte value, and the row size was 20, the bound memory must have a length of 80 bytes. The length of each element of the array must be of the same size, so if binding to a VARCHAR(30) field, the application would bind 31 characters (including the space for the null) for each row, and pass the length of 31 into the buffer length field in the SQLBindCol call.

To give an example of these calls, consider the following snippet of code.

/* variables to contain returned data */
SQLCHAR table[ 20 ][ 31 ];
SQLCHAR column[ 20 ][ 31 ];
SQLINTEGER type[ 20 ];
SQLLEN indicator1[ 20 ], indicator2[ 20 ], indicator3[ 20 ];

/* Set the row size to 20 */
SQLSetStmtAttr( stmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) 20, 0 );

/* Retrieve a list of columns */
SQLColumns(stmt, NULL, 0, NULL, 0, "tablename", SQL_NTS, NULL, 0);

ret = SQLBindCol( stmt, 3, SQL_C_CHAR,
      table, sizeof( table[ 0 ] ), indicator1 );
ret = SQLBindCol( stmt, 4, SQL_C_CHAR,
      column, sizeof( column[ 0 ] ), indicator2 );
ret = SQLBindCol( stmt, 5, SQL_C_LONG,
      &type, 0, indicator3 );

Notice that the indicator variables also become an array, in this case, of SQLLEN values. After the fetch, each element will contain the length of the data, or SQL_NULL_DATA if the column is NULL.

To return the data into the arrays, the SQLFetch call is used.

SQLExtendedFetch

The SQLExtendedFetch call allows the application to pass in arguments that indicate how many rows were read from the database. While the application may request 10 rows, the database may only have 5 remaining to return. The arguments used in SQLExtendedFetch are as follows:

SQLRETURN SQLExtendedFetch(
  SQLHSTMT StatementHandle,
  SQLUSMALLINT FetchOrientation,
  SQLINTEGER   FetchOffset,
  SQLUINTEGER *RowCountPtr,
  SQLUSMALLINT *RowStatusArray);

where the above arguments are:

Each element of the RowStatusArray array will contain one of the following values:

The use of this call is shown in the following code, (which could follow from the previous snippet):

SQLROWSETSIZE row_count;
SQLUSMALLINT row_status[ 20 ];
SQLRETURN ret;

do
{
   ret = SQLExtendedFetch( stmt, SQL_FETCH_NEXT, 0,
         &row_count, row_status );

   if ( SQL_SUCCEEDED( ret ))
   {
      int row;

      /* display each row */
      for ( row = 0; row < row_count; row ++ )
      {
         printf( "Row %d >", row );
         if ( row_status[ row ] == SQL_ERROR )
         {
            printf( "ROW ERROR\n" );
         }
         else if ( row_status[ row ] == SQL_SUCCESS ||
            row_status[ row ] == SQL_SUCCESS_WITH_INFO )
         {
            /* display data */
            if ( indicator1[ row ] == SQL_NULL_DATA )
            {
               printf( "NULL<>" );
            }
            else
            {
               printf( "%s<>", table[ row ] );
            }
            if ( indicator2[ row ] == SQL_NULL_DATA )
            {
               printf( "NULL<>" );
            }
            else
            {
               printf( "%s<>", column[ row ] );
            }
            if ( indicator2[ row ] == SQL_NULL_DATA )
            {
               printf( "NULL<" );
            }
            else
            {
               printf( "%d<", type[ row ] );
            }
            printf( "\n" );
         }
      }
   }
} while( SQL_SUCCEEDED( ret ) && row_count == 20 );

The above code shows an important point: the SQLExtendedFetch call will return SQL_SUCCESS when only part of a SQL_ATTR_ROW_ARRAY_SIZE set of rows is returned. This can be checked by the pcrow value. If there a no rows to return, the call will return SQL_NO_DATA in the same way as the single row case (described in the basic fetching results section).

SQLFetchScroll

The ODBC 3 version of SQLExtendedFetch is SQLFetchScroll. The arguments to this are:

SQLRETURN   SQLFetchScroll(
  SQLHSTMT StatementHandle,
  SQLSMALLINT FetchOrientation,
  SQLROWOFFSET FetchOffset);

As can be seen, there is no reference to the row_count or row_status arguments of the SQLExtendedFetch call. These values in ODBC 3 are now replaced by the statement attributes SQL_ATTR_ROW_STATUS_PTR and SQL_ATTR_ROWS_FETCHED_PTR. The use of these statement attributes is shown in the following code, which is functionally equivalent to the previous example, but uses SQLFetchScroll instead of SQLExtendedFetch..

SQLROWSETSIZE row_count;
SQLUSMALLINT row_status[ 20 ];
SQLRETURN ret;

/*
 * set up the values to return fetch information into
 */

SQLSetStmtAttr( stmt, SQL_ATTR_ROWS_FETCHED_PTR, &row_count, 0 );
SQLSetStmtAttr( stmt, SQL_ATTR_ROW_STATUS_PTR, row_status, 0 );

do
{
   ret = SQLFetchScroll( stmt, SQL_FETCH_NEXT, 0 );

   if ( SQL_SUCCEEDED( ret ))
   {
      int row;

      /* display each row */
      for ( row = 0; row < row_count; row ++ )
      {
         printf( "Row %d >", row );
         if ( row_status[ row ] == SQL_ERROR )
         {
            printf( "ROW ERROR\n" );
         }
         else if ( row_status[ row ] == SQL_SUCCESS ||
            row_status[ row ] == SQL_SUCCESS_WITH_INFO )
         {
            /* display data */
            if ( indicator1[ row ] == SQL_NULL_DATA )
            {
               printf( "NULL<>" );
            }
            else
            {
               printf( "%s<>", table[ row ] );
            }
            if ( indicator2[ row ] == SQL_NULL_DATA )
            {
               printf( "NULL<>" );
            }
            else
            {
               printf( "%s<>", column[ row ] );
            }
            if ( indicator2[ row ] == SQL_NULL_DATA )
            {
               printf( "NULL<" );
            }
            else
            {
               printf( "%d<", type[ row ] );
            }
            printf( "\n" );
         }
      }
   }
} while( SQL_SUCCEEDED( ret ) && row_count == 20 );

Appendix A: Resources

• ODBC from C Tutorial Part 1
• Easysoft Guide to ODBC Diagnostics & Error Status Codes
• The Easysoft web site