ODBC from C tutorial part 2 — fetching results

Contents

• Prerequisites
• Assumptions
• Fetching results
  • Binding columns
  • A common problem with bound data
  • SQLBindCol and data types
  • SQLBindCol and SQLGetData
• Returning multiple rows
  • Preparing to return multiple rows
  • SQLExtendedFetch
  • SQLFetchScroll

Prerequisites

Before you start this tutorial, ensure you have:

1. C You need an ANSI C compiler. For this tutorial we used gcc on Linux, but with a few small alterations the C compilers from Oracle, HP, IBM, and so on will work just as well.
2. ODBC Driver Manager You link your application with an ODBC Driver Manager that:
   • Provides the C header files that allow you to compile your application.
   • Loads the ODBC driver you want to connect to.

   We recommend you use the unixODBC Driver Manager.

   You probably already have unixODBC installed if you have the odbcinst command. (For Easysoft ODBC drivers, the unixODBC Driver Manager is located in /usr/local/easysoft/unixODBC, by default, and the odbcinst command in the bin subdirectory of that path.)

   We used unixODBC 2.2.12 in this tutorial. You can find out your unixODBC version with:

   odbcinst --version

3. ODBC driver You need an ODBC driver and a working database to connect to.

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

Assumptions

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

We also assume you have a good ODBC API reference to hand, as this tutorial is not an attempt to reproduce the ODBC Programmer's Reference; it's more example based.

Operating system

This tutorial was developed on UNIX, and we assume you're using UNIX or Linux too. However, all the C examples should work equally well on Microsoft Windows and other operating systems with some minor alterations. (For example, include windows.h on Microsoft Windows and make the appropriate compiler and linker changes).

ODBC Driver Manager

We assume you're using the unixODBC Driver Manager. All discussion in this document relating to the location and definition of ODBC data sources is for unixODBC.

Fetching results

As described in ODBC from C tutorial (part 1), the process for returning data from a result set consists 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 step 4 and 5 in the above list through a process called binding columns. This involves 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 treated as an extension to SQLGetData, but is only called once for each column, before the row is fetched one or more times.

SQLBindCol arguments are similar to SQLGetData arguments.

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

The following example uses column-wise binding (the default binding orientation) when calling SQLFetch.

#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

Unlike in a SQLGetData call, the data isn't returned to the area indicated by TargetValue and StrLen_or_Ind until the SQLFetch is made. Because of this, it's important to remember that the values must still be in scope when the SQLFetch call is made.

The following code fails, or, at the very least, returns unexpected results as 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 types

As with SQLGetData, you specify the type the data is returned in with TargetType. 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 ];
   SQLINTEGER 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's possible to combine binding columns and fetching data with SQLGetData, but many ODBC 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 ODBC driver to find out 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.

Value	Description
SQL_GD_ANY_COLUMN	SQLGetData can be called for any unbound column, including those before the last bound column. Note that the columns must be called in order of ascending column number unless SQL_GD_ANY_ORDER is also returned.
SQL_GD_ANY_ORDER	SQLGetData can be called for unbound columns in any order. Note that SQLGetData can only be called for columns after the last bound column unless SQL_GD_ANY_COLUMN is also returned.
SQL_GB_BOUND	SQLGetData can be called for bound columns as well as unbound columns. An ODBC driver cannot return this value unless it also returns SQL_GD_ANY_COLUMN.

SQLGetData is only required to return data from unbound columns that occur after the last bound column.

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, you can use either SQLExtendedFetch or its ODBC 3 equivalent SQLFetchScroll. To specify the size of the rowset, the Statement attribute SQL_ROWSET_SIZE (SQLExtendedFetch) or SQL_ATTR_ROW_ARRAY_SIZE (SQLFetchScroll) must be set to the required value by using a SQLSetStmtAttr call. Then, memory bound to the columns using SQLBindCol 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; if the row size was 20, the bound memory must have a length of 80 bytes. The length of each array element must be 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 31 into the buffer length field in the SQLBindCol call.

For example:

/* 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 */
/* If you are using SQLExtendedFetch rather than SQLFetchScroll to fetch the data, */
/* replace SQL_ATTR_ROW_ARRAY_SIZE with SQL_ROWSET_SIZE */
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 of SQLLEN values. After the fetch, each element will contain the length of the data, or SQL_NULL_DATA if the column is null.

The SQLFetch call returns the data into the arrays.

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:

Argument	Description
StatementHandle	The statement handle.
FetchOrientation	The direction of the fetch. In this case, we use SQL_FETCH_NEXT to return the next n rows, where n is the value set in SQL_ROWSET_SIZE.
FetchOffset	The row number to return, in the case of a SQL_FETCH_NEXT, this is ignored.
RowCountPtr	The address of a value that will contain the number of rows returned after the call. This will be between 0 and SQL_ROWSET_SIZE.
RowStatusArray	An array of status values that will be returned, one for each row returned.

Each element of the RowStatusArray array contains one of the following values:

Value	Description
SQL_ROW_SUCCESS	The row was successfully returned.
SQL_ROW_SUCCESS_WITH_INFO	The row was returned but there were one or more warnings generated. This could, for example, indicate that there was insufficient space allocated to contain the data. Return the warning information as normal.
SQL_ROW_ERROR	The row was not returned and one or more errors occurred. Return the warning information as normal.
SQL_ROW_NOROW	No row was returned, normally indicating that the end of the result set has been reached.

The use of this call is shown in the following code, (which could follow on 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 demonstrates an important point: the SQLExtendedFetch call returns SQL_SUCCESS when only part of a SQL_ROWSET_SIZE set of rows is returned. This can be checked by using the pcrow value. If there a no rows to return, the call returns 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);

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 );

• About Easysoft

• Contact us
• About us
• Clients
• Blog
• Careers

• Products

• ODBC drivers
• JDBC drivers
• Bridges and gateways
• In development

• Services

• Consultancy
• Training
• Custom development

• Licensing

• Product licenses
• Prices

• Support

• Support home
• Solution wizard
• Getting Started Guides
• User Guides
• Knowledge Base

• Search

• Easysoft.com
• Documentation
• Knowledge Base

• Popular Resources

• Developer area
• Client applications

• Legal

• Privacy statement

© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.

• 
• 
•