ODBC from C tutorial (part 1) — getting started

Contents

• Prerequisites
• Assumptions
• The ODBC API
  • ODBC reference
  • ODBC include files
  • ODBC=defined macros
  • ODBC handles
  • String arguments
• Allocating and freeing handles
• Listing installed drivers and data sources
  • Listing installed drivers
  • Listing installed data sources
• Setting your environment
• Connecting to a driver or data source
  • SQLDriverConnect
  • A simple connection
  • Format of the ODBC connection string
  • ODBC=defined connection attributes
  • Returned connection string
  • Full connection example
• Driver and Driver Manager information
• Basic result set generating functions
• Basic fetching results
• Handling and reporting ODBC errors and diagnostics
  • Return statuses
  • Retrieving ODBC diagnostics
  • Diagnostic fields

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.

The ODBC API

ODBC Reference

The ODBC Application Programming Interface (API) defines the functions your application can call in the ODBC Driver Manager and the arguments they take.

The reference for the ODBC API is the Microsoft ODBC 3.0 Programmer's Reference Volume 1 and 2 (ISBN 1-57231-516-4) although you may have some trouble finding this now. You can also find the entire ODBC reference in older versions of the ODBC SDK and online at Microsoft's web site.

ODBC include files

For C programmers, the definitions you require are held in the header files sql.h, sqlext.h, sqltypes.h, sqlucode.h, and odbcinst.h. For unixODBC, you'll find these in installpath/include. Further explanation of these headers can be found later.

Normally, you only need to include sql.h (which contains most of the definitions you'll need) and sqlext.h (which mostly contains additions for ODBC 3). sqlucode.h is automatically included by sqlext.h and sqltypes.h is automatically included by sql.h. odbcinst.h contains the installer and data source configuration APIs, for example, SQLConfigDataSource and SQLWriteFileDSN.

ODBC=defined macros

If you examine the ODBC header files, you'll find tests on a number of macros. The principal macros are:

• ODBCVER If you don't set this, it will default to 0x0351 for ODBC 3.51. If you want to restrict your application to ODBC 2, you could define ODBCVER=0x0200 when compiling your code.
• UNICODE If you define UNICODE, all calls to SQLxxx APIs are changed to SQLxxxW, that is, your application will be calling the wide ODBC APIs and therefore needs to pass wide characters. Normally with unixODBC, wide characters are 2 bytes, which mirrors the ODBC API on Microsoft Windows.
• ODBC_STD If you define ODBC_STD, SQLAllocHandle and SQLAllocEnv are changed to SQLAllocHandleStd for X/Open compatibility.

There are also a number of convenience macros like SQL_SUCCEEDED (used to test ODBC API return status and macros that describe something, like SQL_NTS (which specifies a provided string is null-terminated).

ODBC handles

In ODBC, there are four main handle types and you will need to know at least three to do anything useful:

• SQLHENV Environment handle.

  This is the first handle you need, as everything else is effectively in the environment. Once you have an environment handle, you can define the version of ODBC you require, enable connection pooling, and allocate connection handles with SQLSetEnvAttr and SQLAllocHandle.

• SQLHDBC Connection handle.

  You need one connection handle for each data source you're going to connect to. Like environment handles, connection handles have attributes that you can retrieve and set with SQLSetConnectAttr and SQLGetConnectAttr.

• SQLHSTMT Statement handle.

  Once you have a connection handle and connect to a data source, you allocate statement handles to execute SQL or retrieve metadata. As with the other handles, you can set and get statement attributes with SQLSetStmtAttr and SQLGetStmtAttr.

• SQLHDESC Descriptor handle.

  Descriptor handles are rarely used by applications even though they are very useful for more complex operations. Descriptor handles will be covered in later tutorials.

ODBC handles are opaque types and although the ODBC standard does not specifically say they are pointers to structures, they generally are (but you should not rely on this).

String arguments

A number of the ODBC APIs accept string pointers and return strings to user supplied buffers. In general, ODBC APIs accepting input string arguments have a pointer argument followed by a length argument. For example:

SQLRETURN SQLPrepare(SQLHSTMT stmt,
                     SQLCHAR *StatementText,
                     SQLINTEGER TextLength)

There are usually two ways of specifying the length of an input string:

1. The length in bytes of the string. For example, fred has length 4.
2. The string is null-terminated (as is usual in C). You specify null-terminated strings with the ODBC=defined macro SQL_NTS.

Where an ODBC API returns a string, it's usual for the API to require a pointer to a buffer and a pointer to an integer (of some sort) to return the length of the returned string. For example:

SQLRETURN SQLGetCursorName(SQLHSTMT StatementHandle,
                           SQLCHAR *CursorName,
                           SQLSMALLINT BufferLength,
                           SQLSMALLINT *NameLengthPtr)

In the above case, you pass a pointer to a buffer to receive the cursor name, the length of that buffer (so it is not overrun) and a pointer to a SQLSMALLINT in which is written the length of the returned string. There are a few useful points about the way the ODBC API works here:

1. In general, the buffer must be big enough for the returned string and a terminating null character. The returned length does not include the terminating null character.
2. APIs like the above generally return SQL_SUCCESS_WITH_INFO, a state of 01004 and a "String data, right truncated" message if the supplied buffer wasn't big enough for the returned string.
3. You do not have to supply a pointer to the returned length (that is, it may be NULL) but then if the buffer is truncated you won't know how big a buffer you need.
4. Strangely, you don't have to specify a buffer (that is, it may be NULL) and the buffer length may be zero. It's not uncommon for applications do something like this:

   SQLCHAR *buffer = NULL;
   SQLSMALLINT retlen;
   SQLRETURN ret;
   
   ret = SQLGetCursorName(stmt, NULL, 0, &retlen);
   if (SQL_SUCCEEDED(ret)) {
       buffer = malloc(retlen + 1); /* add one for null termination */
       SQLGetCursorName(stmt, buffer, retlen + 1, NULL);
   }

Allocating and freeing handles

There are four types of ODBC handle: environment, connection, statement and descriptor. Handles must be allocated in a specific order and each type of handle is used for different purposes. Each handle type has attributes that you can query and set and a diagnostic stack, which can be queried for errors and diagnostic information.

Allocate each handle type with SQLAllocHandle:

SQLRETURN SQLAllocHandle(
  SQLSMALLINT HandleType,
  SQLHANDLE InputHandle,
  SQLHANDLE *OutputHandlePtr)

HandleType must be one of:

• SQL_HANDLE_ENV Allocates an environment handle.
• SQL_HANDLE_DBC Allocates a connection handle.
• SQL_HANDLE_STMT Allocates a statement handle.
• SQL_HANDLE_DESC Allocates a descriptor handle.

The InputHandle argument is either SQL_NULL_HANDLE (when allocating environment handles) or the value of the enclosing handle — you pass an environment handle when allocating a connection handle and a connection handle when allocating a statement or descriptor handle.

OutputHandlePtr is a ptr to the handle to be returned.

Similarly, there is the SQLFreeHandle API to free up a handle and its associated resources:

SQLRETURN SQLFreeHandle(
  SQLSMALLINT HandleType,
  SQLHANDLE Handle)

Note that handles generally need to be freed in the opposite order to which they were allocated and that handles cannot be freed if they are in use. For example, you cannot free a connected connection handle until it's disconnected.

Ignoring descriptors for now, the following code is usually in every ODBC application:

SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
/* connect to the data source */
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
/* do something with the statement handle e.g. issue sql */
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
/* disconnect */
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);

In ODBC 2.0, descriptor handles did not exist and each handle type had its own allocation and deallocation API; SQLAllocEnv, SQLFreeEnv, SQLAllocConnect, SQLFreeConnect, SQLAllocStmt, and SQLFreeStmt. You should avoid using these APIs now and use SQLAllocHandle and SQLFreeHandle instead.

Setting your environment

You set environment attributes with SQLSetEnvAttr and retrieve them with SQLGetEnvAttr.

Once you've allocated your environment handle, you specify the ODBC version behavior you require. In ODBC 3.0, this is compulsory. If you forget to do this and attempt to use an environment handle before specifying the ODBC behaviour you want, you get a HY010, function sequence error. Currently, there are only two choices: SQL_OV_ODBC2 (for ODBC 2 behavior) and SQL_OV_ODBC3 (for ODBC 3 behavior).

If you ask for ODBC 3 behavior:

• The driver will expect and return ODBC 3 types for time, date, and timestamp columns, that is SQL_TYPE_TIME instead of SQL_TIME and so on.
• ODBC states returned (SQLSTATE) are ODBC 3 states.
• The CatalogName argument to SQLTables accepts a search pattern.

New applications should always ask for ODBC 3 behaviour:

SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);

A few ODBC drivers still don't support ODBC 3 properly, but this does not really matter as the ODBC Driver Manager will translate for your application. If you ask for ODBC 3 behaviour and the driver does not support ODBC 3, the Driver Manager returns SQL_SUCCESS_WITH_INFO from SQLDriverConnect and a state of HYC00 (The driver does not support the version of ODBC behavior that the application requested). This is not an error.

There are other environment attributes you may set like SQL_ATTR_CONNECTION_POOLING, SQL_ATTR_CP_MATCH, and SQL_ATTR_OUTPUT_NTS but they are beyond the scope of this introductory tutorial.

Listing installed drivers and data sources

Listing installed drivers

unixODBC lets you list installed ODBC drivers using:

odbcinst -q -d

To list drivers programmatically, use SQLDrivers like this:

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

main() {
  SQLHENV env;
  char driver[256];
  char attr[256];
  SQLSMALLINT driver_ret;
  SQLSMALLINT attr_ret;
  SQLUSMALLINT direction;
  SQLRETURN ret;

  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
  SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);

  direction = SQL_FETCH_FIRST;
  while(SQL_SUCCEEDED(ret = SQLDrivers(env, direction,
                                       driver, sizeof(driver), &driver_ret,
                                       attr, sizeof(attr), &attr_ret))) {
    direction = SQL_FETCH_NEXT;
    printf("%s - %s\n", driver, attr);
    if (ret == SQL_SUCCESS_WITH_INFO) printf("\tdata truncation\n");
  }
}

which produces output like:

OOB - Easysoft ODBC-ODBC Bridge
PostgreSQL - Postgres SQL Driver
EASYSOFT_ISAM - Easysoft Data Access for ISAM

You can use the returned driver name (the driver variable in the above example) when calling SQLDriverConnect to use DSN-less connections.

Listing installed Data Sources

unixODBC lets you list installed data sources using:

odbcinst -q -s

To list data sources programmatically, use SQLDataSources:

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

main() {
  SQLHENV env;
  char dsn[256];
  char desc[256];
  SQLSMALLINT dsn_ret;
  SQLSMALLINT desc_ret;
  SQLUSMALLINT direction;
  SQLRETURN ret;

  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
  SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);

  direction = SQL_FETCH_FIRST;
  while(SQL_SUCCEEDED(ret = SQLDataSources(env, direction,
                                           dsn, sizeof(dsn), &dsn_ret,
                                           desc, sizeof(desc), &desc_ret))) {
    direction = SQL_FETCH_NEXT;
    printf("%s - %s\n", dsn, desc);
    if (ret == SQL_SUCCESS_WITH_INFO) printf("\tdata truncation\n");
  }
}

which produces output like:

mydsn - OOB
pdsn - Postgres
sample_isam - EASYSOFT_ISAM

You can use the returned DSN name (the dsn variable in the above example) when calling SQLDriverConnect to connect to that DSN.

Connecting to a driver or data source

Once you have a connection handle, you can connect to your ODBC driver or data source using SQLDriverConnect (ODBC 2 applications use SQLConnect but this is much less flexible.) Connecting to your ODBC driver is perhaps one of the largest subjects in ODBC as SQLDriverConnect can be called in many different ways; this introductory tutorial covers the simplest case of connecting to a named data source that you have already created.

SQLDriverConnect

The SQLDriverConnect API is:

SQLRETURN SQLDriverConnect(
  SQLHDBC ConnectionHandle,
  SQLHWND WindowHandle,
  SQLCHAR *InConnectionString,
  SQLSMALLINT StringLength1,
  SQLCHAR *OutConnectionString,
  SQLSMALLINT BufferLength,
  SQLSMALLINT *StringLength2Ptr,
  SQLUSMALLINT DriverCompletion)

Rather than explain every argument in detail (there are plenty of references that do this) we'll provide the two simplest ways of using SQLDriverConnect. The most important arguments are ConnectionHandle, InConnectionString, StringLength1, and DriverCompletion. These are the minimum number of arguments required to connect to a data source.

• ConnectionHandle is a previously allocated connection handle.
• WindowHandle (if specified) is provided for the driver to enable it to display a dialog box (if necessary). You can specify this as NULL to avoid the driver throwing a dialog box (refer also to DriverCompletion). On UNIX and Linux, where you do not necessarily have anything like a window handle, specify a non-zero value if you want to display a dialog box.
• InConnectionString and StringLength1 describe the input connection string. This is a list of attribute and value pairs separated by semicolons. For example, DSN=mydatasource;UID=me;PWD=mypassword;
• OutConnectionString, BufferLength and StringLength2Ptr are for the ODBC driver to return a full connection string you can reuse in future connection calls.
• DriverCompletion specifies how the ODBC driver handles the input connection string especially if there are insufficient attributes to connect. There are four possible settings for DriverCompletion:
  • SQL_DRIVER_PROMPT The ODBC driver displays a dialog box, using the values from the connection string and system information (if any) as initial values. When the user exits the dialog box, the ODBC driver connects to the data source. The ODBC driver also constructs a connection string from the value of the DSN or DRIVER keyword in InConnectionString and the information returned from the dialog box. The ODBC driver places this connection string in the OutConnectionString buffer.
  • SQL_DRIVER_COMPLETE and SQL_DRIVER_COMPLETE_REQUIRED If the connection string contains enough information and that information is correct, the ODBC driver connects to the data source and copies InConnectionString to OutConnectionString. If any information is missing or incorrect, the ODBC driver takes the same action as it does for SQL_DRIVER_PROMPT, except that if DriverComplete is SQL_DRIVER_COMPLETE_REQUIRED, the driver turns off the controls for any information not required to connect to the data source.
  • SQL_DRIVER_NOPROMPT If the connection string contains enough information, the ODBC driver connects to the data source and copies InConnectionString to OutConnectionString. Otherwise, the driver returns SQL_ERROR for SQLDriverConnect. You would usually do this from a non-interactive application.

  If a dialog box is cancelled, SQLDriverConnect should return SQL_NO_DATA.

A simple connection

First, you must create your named data source (DSN) in the odbc.ini file using a text editor. For example, if you have created a DSN called mydsn:

SQLRETURN ret;
SQLHDBC dbc; /* assume already allocated */

ret = SQLDriverConnect(dbc, NULL, "DSN=mydsn;", SQL_NTS,
                       NULL, 0, NULL, SQL_DRIVER_COMPLETE);

In this call, we provide the DSN name and ask the ODBC driver to complete the connection string. We don't want an output connection string or any dialogs (WindowHandle is NULL). The ODBC Driver Manager looks at the mydsn DSN to find out what ODBC driver is required, load the driver, and call SQLDriverConnect in the driver with the same arguments. The ODBC driver looks up the mydsn DSN with SQLGetPrivateProfileString and retrieves all the attributes defined for that data source. As long as the driver now has enough information to connect to the data source, it will do so.

Format of the ODBC connection string

The ODBC connection string contains a series of attributes names and values separated by semi-colons:

connection-string::= empty-string[;] | attribute[;] | attribute; connection-string
empty-string ::=
attribute ::= attribute-keyword=attribute-value | DRIVER=[{]attribute-value[}]
attribute-keyword ::= DSN | UID | PWD | driver-defined-attribute-keyword
attribute-value ::= character-string
driver-defined-attribute-keyword = identifier

where character-string has zero or more characters; identifier has one or more characters; attribute-keyword is not case-sensitive; attribute-value may be case-sensitive. To comply with connection string grammar, keywords and attribute values that contain the characters []{}(),;?*=!@ should be avoided. The value of the DSN keyword cannot consist only of blanks, and should not contain leading blanks. Because of the grammar of the system information, keywords and data source names cannot contain the backslash (\) character. Applications don't have to add braces around the attribute value after the DRIVER keyword unless the attribute contains a semicolon (;), in which case the braces are required. If the attribute value that the ODBC driver receives includes the braces, the driver should not remove them, but they should be part of the returned connection string.

ODBC-defined connection attributes

The key to the previous example is the InConnectionString argument, which can contain some ODBC-defined attributes (there are also driver-defined attributes but these vary per driver). The ODBC-defined attributes are:

• DSN The name of the ODBC data source to connect to.

  You must create this before attempting to refer to it. You create new DSNs through the ODBC Data Source Administrator (Windows), ODBCAdmin (unixODBC's GUI manager), or in the odbc.ini file.

• DRIVER The name of the ODBC driver to connect to. You can use this in DSN-less connections.
• FILEDSN The name of a file containing the connection attributes.
• UID and PWD Any user name and password the database requires for authentication.
• SAVEFILE Request the DSN attributes are saved in this file.

Returned connection string

The connection string returned by SQLDriverConnect can be used to reconnect at a later date. You may ask why bother returning a connection string when the one passed in which presumably was sufficient to connect in the first place. The reasons for this are:

1. If you use SQL_DRIVER_COMPLETE the ODBC driver is free to retrieve any other attributes it needs to connect in addition to those you passed in.
2. If a dialog box was thrown, the user may have entered additional or changed values.

A couple of small examples with the ODBC-ODBC Bridge illustrate this:

1. Assume you have created a DSN called fred with all the necessary attributes and you call SQLDriverConnect with the connection string DSN=fred; and SQL_DRIVER_COMPLETE. The ODBC-ODBC Bridge is free to examine the DSN fred and retrieve the attributes it requires such as ServerPort, TargetDSN, LogonUser, and LogonAuth. The returned connection string is DSN=fred;ServerPort=myserver:8888;TargetDSN=mydsn;LogonUser=me;LogonAuth=password;. Now if the application stores the returned string it can reconnect without reference to the DSN, so the DSN can be changed without affecting the connection made using the stored string.
2. Assume you have created a DSN called fred but have not supplied all the attributes; perhaps because you wanted ODBC-ODBC Bridge to throw a dialog box requesting the attributes you omitted. You call SQLDriverConnect with DSN=fred; and SQL_DRIVER_COMPLETE. The ODBC-ODBC Bridge will throw its dialog allowing you to enter the missing values and then return a full connection string, which can be used to reconnect later without the need for user interaction.

Full connection example

In a simple connection we only used the minimum number of arguments to SQLDriverConnect to get connected. This example uses all arguments:

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

/*
 * see Retrieving ODBC Diagnostics
 * for a definition of extract_error().
 */
static void extract_error(
    char *fn,
    SQLHANDLE handle,
    SQLSMALLINT type);

main() {
  SQLHENV env;
  SQLHDBC dbc;
  SQLHSTMT stmt;
  SQLRETURN ret; /* ODBC API return status */
  SQLCHAR outstr[1024];
  SQLSMALLINT outstrlen;

  /* 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 */
  ret = SQLDriverConnect(dbc, NULL, "DSN=fred;", SQL_NTS,
                         outstr, sizeof(outstr), &outstrlen,
                         SQL_DRIVER_COMPLETE);
  if (SQL_SUCCEEDED(ret)) {
    printf("Connected\n");
    printf("Returned connection string was:\n\t%s\n", outstr);
    if (ret == SQL_SUCCESS_WITH_INFO) {
      printf("Driver reported the following diagnostics\n");
      extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
    }
    SQLDisconnect(dbc);               /* disconnect from driver */
  } else {
    fprintf(stderr, "Failed to connect\n");
    extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
  }
  /* free up allocated handles */
  SQLFreeHandle(SQL_HANDLE_DBC, dbc);
  SQLFreeHandle(SQL_HANDLE_ENV, env);
}

Sample output from running this code when fred is an ODBC-ODBC Bridge data source connecting to Microsoft SQL Server:

sh-2.05$ ./a.out 
Connected
Returned connection string was:
        DSN=fred;SERVERPORT=server:8888;TARGETDSN=test;UID=dbuser;PWD=dbpass;
                LOGONUSER=fred;LOGONAUTH=mypass;
Driver reported the following diagnostics

The driver reported the following diagnostics whilst running SQLDriverConnect

01000:1:5703:[unixODBC][NetConn: 141dbe18][Microsoft][ODBC SQL Server Driver][SQL Server]
        Changed language setting to us_english.
01000:2:5701:[unixODBC][NetConn: 141dbe18][Microsoft][ODBC SQL Server Driver][SQL Server]
        Changed database context to 'easysoft_fred'.

Driver and Driver Manager information

When you start working with ODBC you will inevitably find differences between ODBC drivers. ODBC defines a set of informational types you can retrieve that describe the ODBC driver, Driver Manager, and data source. You use SQLGetInfo to retrieve this information.

SQLRETURN SQLGetInfo(SQLHDBC ConnectionHandle,
                     SQLUSMALLINT InfoType,
                     SQLPOINTER InfoValuePtr,
                     SQLSMALLINT BufferLength,
                     SQLSMALLINT StringLengthPtr)

There are basically three types of information returned: strings, bitmasks, and integer values. String information types are sometimes binary values represented by Y and N. To retrieve an informational type, look it up in the ODBC specification. Find out what type it is and then use one of the following:

SQLCHAR string_val[1024];
SQLSMALLINT string_len;
SQLRETURN ret;
SQLUINTEGER u_val;
SQLUSMALLINT su_val;

/* for string values */
ret = SQLGetInfo(dbc,
                 SQL_XXX, /* e.g. SQL_DATA_SOURCE_NAME */
                 string_val,
                 sizeof(string_val),
                 &string_len);
if (SQL_SUCCEEDED(ret)) {
   if (ret == SQL_SUCCESS_WITH_INFO)
       printf("buffer too small, string truncated\n");
   printf("Returned value is %s\n", string_val);
} else {
  /* error */
}

/* for SQLUINTEGER values - mostly bitmasks */
ret = SQLGetInfo(dbc,
                 SQL_XXX, /* e.g. SQL_INSERT_STATEMENT */
                 (SQLPOINTER)&u_val,
                 0, /* ignored for SQLUINTEGER types */
                 0); /* ignored for SQLUINTEGER types */
if (SQL_SUCCEEDED(ret)) {
  printf("Returned value is %lx\n", u_val);
} else {
  /* error */
}

/* for SQLUSMALLINT values - mostly counts/limits */
ret = SQLGetInfo(dbc,
                 SQL_XXX, /* e.g. SQL_MAX_CONCURRENT_ACTIVITIES */
                 (SQLPOINTER)&su_val,
                 0, /* ignored for SQLUSMALLINT types */
                 0); /* ignored for SQLUSMALLINT types */
if (SQL_SUCCEEDED(ret)) {
  printf("Returned value is %u\n", su_val);
} else {
  /* error */
}

Note Most InfoTypes require the connection handle to be in a connected state since they are returning information about the ODBC driver.

A small example that retrieves ODBC driver information (without error checking):

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

main() {
  SQLHENV env;
  SQLHDBC dbc;
  SQLRETURN ret; /* ODBC API return status */

  /* 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 */
  ret = SQLDriverConnect(dbc, NULL, "DSN=web;", SQL_NTS,
                         NULL, 0, NULL,
                         SQL_DRIVER_COMPLETE);
  if (SQL_SUCCEEDED(ret)) {
    SQLCHAR dbms_name[256], dbms_ver[256];
    SQLUINTEGER getdata_support;
    SQLUSMALLINT max_concur_act;
    SQLSMALLINT string_len;

    printf("Connected\n");
    /*
     *  Find something out about the driver.
     */
    SQLGetInfo(dbc, SQL_DBMS_NAME, (SQLPOINTER)dbms_name,
               sizeof(dbms_name), NULL);
    SQLGetInfo(dbc, SQL_DBMS_VER, (SQLPOINTER)dbms_ver,
               sizeof(dbms_ver), NULL);
    SQLGetInfo(dbc, SQL_GETDATA_EXTENSIONS, (SQLPOINTER)&getdata_support,
               0, 0);
    SQLGetInfo(dbc, SQL_MAX_CONCURRENT_ACTIVITIES, &max_concur_act, 0, 0);

    printf("DBMS Name: %s\n", dbms_name);
    printf("DBMS Version: %s\n", dbms_ver);
    if (max_concur_act == 0) {
      printf("SQL_MAX_CONCURRENT_ACTIVITIES - no limit or undefined\n");
    } else {
      printf("SQL_MAX_CONCURRENT_ACTIVITIES = %u\n", max_concur_act);
    }
    if (getdata_support & SQL_GD_ANY_ORDER)
      printf("SQLGetData - columns can be retrieved in any order\n");
    else
      printf("SQLGetData - columns must be retrieved in order\n");
    if (getdata_support & SQL_GD_ANY_COLUMN)
      printf("SQLGetData - can retrieve columns before last bound one\n");
    else
      printf("SQLGetData - columns must be retrieved after last bound one\n");

    SQLDisconnect(dbc);               /* disconnect from driver */
  } else {
    fprintf(stderr, "Failed to connect\n");

  }
  /* free up allocated handles */
  SQLFreeHandle(SQL_HANDLE_DBC, dbc);
  SQLFreeHandle(SQL_HANDLE_ENV, env);
}

which, for an ODBC-ODBC Bridge connection to SQL Server, produces output like this:

Connected
DBMS Name: Microsoft SQL Server
DBMS Version: 08.00.0760
SQL_MAX_CONCURRENT_ACTIVITIES = 1
SQLGetData - columns must be retrieved in order
SQLGetData - columns must be retrieved after last bound one

Basic result set generating functions

Result set generating APIs are the workforce of ODBC since they allow you to obtain metadata and query your database with SQL. A result set is a list of rows and columns in a statement, which you can retrieve. For example, when you call SQLTables to get a list of tables you will get a result set with 0 or more rows (one per table or view). Each row contains columns describing the table.

For the metadata APIs that generate result sets, the ODBC specification states what the result set looks like. For example, for SQLTables you get rows of 5 columns called TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, and REMARKS.

The basic metadata APIs that generate result sets are:

• SQLTables Returns the list of table, catalog, or schema names, and table types, stored in the data source. The most basic call returning a result set containing all tables and views is:

  SQLTables(stmt_handle,
              NULL, 0, /* no specific catalog */
              NULL, 0, /* no specific schema */
              NULL, 0, /* no specific table */
              NULL, 0) /* no specific type - table or view */

  To restrict the result set to only tables (no views):

  SQLTables(stmt_handle,
              NULL, 0, /* no specific catalog */
              NULL, 0, /* no specific schema */
              NULL, 0, /* no specific table */
              "TABLE", SQL_NTS) /* only tables, no views */

  If you're using ODBC 3.0, catalogname allows search patterns. You also need to check and set the statement attribute SQL_ATTR_METADATA_ID as this affects whether the arguments are treated as case-insensitive identifiers or case-sensitive patterns or literals.

  To retrieve a list of all catalogs:

  SQLTables(stmt_handle,
              SQL_ALL_CATALOGS, SQL_NTS,
              "", 0,
              "", 0,
              NULL, 0);

  Use similar calls to retrieve a list of all schemas or tables.

• SQLColumns Returns the list of column names in the specified table. You call it just like SQLTables above.

Other metadata APIs returning result sets are: SQLTablePrivileges, SQLStatistics, SQLSpecialColumns, SQLProcedures, SQLProcedureColumns, SQLPrimaryKeys, SQLForeignKeys, SQLColumnPrivileges, and SQLGetTypeInfo.

To issue queries against your database, use SQLPrepare followed by SQLExecute or call SQLExecDirect. Here are some examples:

• To select all the columns in the table mytable:

  SQLExecDirect(stmt_handle, "select * from mytable", SQL_NTS);

• You would generally use SQLExecDirect for one-off queries but if you were wanting to reissue the same query repeatedly with say different search criteria, you can use SQLPrepare or SQLExecute as follows:

  SQLPrepare(stmt_handle,
             "select * from mytable where mycol = ?", SQL_NTS);
  /* bind a parameter with SQLBindParam */
  /* set parameter's value */
  loop {
    SQLExecute(stmt_handle);
    /* get results */
    /* change parameter's value */
  }
  
  

  Binding parameters is left for later tutorials but is mentioned here because it is the major reason for using SQLPrepare or SQLExecute instead of SQLExecDirect.

Basic fetching results

Now we've covered creating handles, connecting, and basic result set generating functions, we can actually write a complete program that does something useful. Our sample program retrieves some information from a database.

The general form for fetching results is:

1. Call some result set generating ODBC API.
2. Call SQLNumResultCols to find out how many columns are in the result set.
3. Optionally, call SQLDescribeCol or SQLColAttribute to find out metadata for the columns.
4. Call SQLFetch to retrieve a row. If SQLFetch returns SQL_NO_DATA, there are no rows in the result set.
5. Loop through columns calling SQLGetData to retrieve the column data.
6. Go back to step 4 until SQLFetch returns SQL_NO_DATA.

The following example illustrates the most basic operations: connect to the database, issue a request, and retrieve the results. This example does not attempt to cover all the ODBC APIs and does not include proper error checking, but it is a working program that we can also use to cover compilation and linking.

#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 */
int row = 0;

/* 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 */
while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
    SQLUSMALLINT i;
    printf("Row %d\n", row++);
    /* Loop through the columns */
    for (i = 1; i <= columns; i++) {
        SQLINTEGER indicator;
        char buf[512];
        /* retrieve column data as a string */
        ret = SQLGetData(stmt, i, SQL_C_CHAR,
                         buf, sizeof(buf), &indicator);
        if (SQL_SUCCEEDED(ret)) {
            /* Handle null columns */
            if (indicator == SQL_NULL_DATA) strcpy(buf, "NULL");
            printf("  Column %u : %s\n", i, buf);
        }
    }
  }
}

To compile this code using a unixODBC Driver Manager installed in /usr/local:

cc -I/usr/local/include exam1.c -o exam1 \
   -L/usr/local/lib -lodbc

When run, it produces output like:

Row 0
  Column 1 : easysoft
  Column 2 : dbo
  Column 3 : activeproductcodes
  Column 4 : TABLE
  Column 5 : NULL
Row 1
  Column 1 : easysoft
  Column 2 : dbo
  Column 3 : products
  Column 4 : TABLE
  Column 5 : NULL

For more about fetching results, refer to ODBC from C Tutorial (part 2) — fetching results

Handling and reporting ODBC errors and diagnostics

Return statuses

All ODBC APIs return a status value, which let you check whether the function succeeded or not.

In C, you test the return value from an ODBC function by using the macro SQL_SUCCEEDED. For example:

SQLRETURN fsts;
/* Assume for this example the environment has already been allocated */
SQLHENV   envh;
SQLHDBC   dbch;

fsts = SQLAllocHandle(SQL_HANDLE_DBC, envh, &dbch);
if (!SQL_SUCCEEDED(fsts))
{
  /* an error occurred allocating the database handle */
}
else
{
  /* Database handle allocated OK */
}

The SQL_SUCCEEDED macro definition is:

#define SQL_SUCCEEDED(rc) (((rc)&(~1))==0)

Virtually all ODBC functions can return two values that indicate success:

• SQL_SUCCESS
• SQL_SUCCESS_WITH_INFO

Both of these returns cause the SQL_SUCCEEDED macro to result in 1. If a function returns SQL_SUCCESS_WITH_INFO, it means that the call succeeded but an informational message was produced.

For example, with some ODBC drivers you might set the cursor type, prepare a statement and then execute it. When you call SQLExecute, the ODBC driver uses the statement but might change the cursor type to something else. In this case, SQLExecute returns SQL_SUCCESS_WITH_INFO and the ODBC driver adds a diagnostic indicating the cursor type has changed.

Another example is SQLGetData, which can return SQL_SUCCESS_WITH_INFO to indicate the buffer you supplied for the column data was not big enough and the data returned has been truncated.

Note that a few ODBC functions return a status that fails the SQL_SUCCEEDED macro, but does not indicate an error as such. For example, SQLFetch can return SQL_NO_DATA indicating that there are no further rows in the result set, this is not necessarily an error.

Retrieving ODBC diagnostics

When an ODBC function returns an error or SQL_SUCCESS_WITH_INFO, the ODBC driver associates a diagnostic with the handle used in the ODBC call. You can obtain the diagnostic to find out what failed by calling SQLGetDiagRec with the handle you used in the ODBC call that failed.

The ODBC driver may associate multiple diagnostic records with a handle. You can call SQLGetDiagField and request the SQL_DIAG_NUMBER attribute to find out how many diagnostics exist. Alternatively, as diagnostic records start at 1, you can repeatedly call SQLGetDiagRec asking for record 1, then 2 (and so on) until SQLGetDiagRec returns SQL_NO_DATA.

As an example, the following C function takes a function name string, handle type and handle and retrieves all the diagnostics associated with that handle.

void extract_error(
    char *fn,
    SQLHANDLE handle,
    SQLSMALLINT type)
{
    SQLINTEGER   i = 0;
    SQLINTEGER   native;
    SQLCHAR      state[ 7 ];
    SQLCHAR      text[256];
    SQLSMALLINT  len;
    SQLRETURN    ret;

    fprintf(stderr,
            "\n"
            "The driver reported the following diagnostics whilst running "
            "%s\n\n",
            fn);

    do
    {
        ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
                            sizeof(text), &len );
        if (SQL_SUCCEEDED(ret))
            printf("%s:%ld:%ld:%s\n", state, i, native, text);
    }
    while( ret == SQL_SUCCESS );
}

Use extract_error as follows:

SQLRETURN fsts;
/* Assume for this example the environment has already been allocated */
SQLHENV   envh;
SQLHDBC   dbch;

fsts = SQLAllocHandle(SQL_HANDLE_DBC, envh, &dbch);
if (!SQL_SUCCEEDED(fsts))
{
  extract_error("SQLAllocHandle for dbc", envh, SQL_HANDLE_ENV);
  exit(1);
}
else
{
  /* Database handle allocated OK */
}

Note ODBC 2.0 applications use SQLError instead of SQLGetDiagRec.

Diagnostic fields

When you call SQLGetDiagRec, you can retrieve 3 diagnostic fields:

• State.
• Native error code.
• Message text.

The state is a five character SQLSTATE code. The first two characters indicate the class and the next three indicate the subclass. SQLSTATEs provide detailed information about the cause of a warning or error. You can look states up in the ODBC specification.

The native error code is a code specific to the data source. This number is often extremely useful to ODBC driver developers for locating an internal error or state. If you're reporting a bug in the ODBC-ODBC Bridge ODBC driver, always supply the ODBC function called, the error text, and this native number.

The message text is the text of the diagnostic. For errors and warnings that do not occur in a data source the format is:

[vendor-identifier][ODBC-component-identifier]component-supplied-text

Otherwise, the format is:

[vendor-identifier][ODBC-component-identifier][data-source-identifer]
data-source-supplied-text

• 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.

• 
• 
•