Working with ODBC from C++

Setting up MySQL for use through ODBC.

First, recognize that THERE ARE several ways you can connect to a MySQL database from an application.

MDAC

click for larger

The 3 main ways are:

• Native C api – compile and link-in code that can talk to MySQL WITHIN your app. This is ALMOST like making MySQL “part of your program”

  

• ODBC (this article). ODBC stands for Ol’ Dirty Bastard Connector.. :) Just kidding. But it should. ODBC was first released in 1992. ODBC is short for Open Database Connectivity.

  Basically your application TALKS TO the ODBC driver THROUGH A COMMON SET OF API FUNCTIONS – the ODBC API library. The ODBC driver in turn, talks to the actual database for you. So you achieve a certain level of database API independence: you use ODBC in the same way whether programming to a MySQL database, or an MS-SQL Server database, or an Oracle database – you use the same functions and it works, as long as you have an ODBC driver for that database. So using ODBC is just ONE LEVEL of abstraction above the native C API. ODBC is still alive and kicking today and works fine.

  

  Aside:

  If you’re familiar with how the HAL works in Direct3D, ODBC is a little bit like the HAL.

  Direct3D function calls	ODBC function calls
  HAL (hardware abstraction layer)	ODBC driver
  GPU hardware itself	Database software itself

  In this case, the “database” is like the GPU, and ODBC driver is that layer which transforms standard ODBC function calls into function calls that the actual database being used underneath can understand.

  If that just didn’t make any sense, ignore this whole block

  The advantage of using ODBC above the MySQL functions directly is .. well, just that you don’t have to program to the MySQL function set directly anymore. So, say you already had experience with setting up a database connection under C++/Oracle, and you want to quickly get up and running on a C++/MySQL program. Instead of going and figuring out how the MySQL native api works, you actually wouldn’t have to know any of that at all, you’d just install the ODBC driver and program “to” ODBC – this is what we’ll show how to do in this article.

• OLE DB

  Just ANOTHER, newer, more recent library of functions that is supposed to replace ODBC. OLE DB is a low-level, high-performance interface to a variety of data stores. OLE DB, like ODBC as shown in this article, has a C-style API.

  OLE DB’s advantage over ODBC is that the underlying data provider when using OLE DB does NOT have to be a relational database.

  In fact, according to page 8 of this book

  … ODBC as we have just seen, is an excellent technology for accessing SQL-based data. OLE DB incorporates this proven technology with a particular component that allows OLE DB consumers to communicate directly with ODBC providers. In other words, use OLE DB to access SQL-based data, and you gain the advantage of being able to access both relational and other forms of data with the same code.

  So it looks like this:

  

• Finally, ADO, (also, ADO.NET).

  ADO is a high-level, easy-to-use interface to OLE DB.

  ADO is the NEWEST and is probably the most commonly used technology for accessing databases from MS apps. Ever heard of hibernate? Well, ADO, (which stands for ActiveX Data Objects) is BASICALLY the same idea as Hibernate. You can interact with the database through a series of functions WITHOUT EVER WRITING A LINE OF SQL. You interact with the database instead through the functionset provided by ADO.

  

  Can you see the layers? You can see that you should expect ADO to be somewhat slower than using ODBC directly.

  ADO is accessible in two flavors: ADO “regular” and ADO.NET. ADO.NET, clearly, is part of the .NET framework and so if you’re using a .NET application, then data access through ADO.NET is the natural standard for you.

  If you’re programming a native C++ app on the other hand, the choice whether to use native MySQL function calls, ODBC, OLE DB (directly), ADO, or ADO.NET kind of looms before you.

So, ok, on with it.

Accessing a MySQL database through ODBC

1. The first step is to install a MySQL database and create a table or two. Do it do it do it!!

2. Ok, now the SECOND step is to GET THE MySQL ODBC 5.1 DRIVER. GET THE 32-BIT DRIVER ONLY, PLEASE, EVEN IF YOU ARE ON A 64-bit MACHINE. Unless you know EXACTLY what you’re doing FOR SURE and you KNOW your compiler pushes out 64-bit applications (Visual Studio 2005/2008 pushes out 32-bit applications by default ONLY!! EVEN IF you are on a 64-bit platform!)

3. Once you’ve got that installed, OPEN UP Start -> Administrative Tools -> Data Sources (ODBC).

Note: If you DO NOT SEE “Administrative Tools” on your start menu RIGHT CLICK TASKBAR > PROPERTIES > START MENU TAB > CUSTOMIZE > FIND AND SELECT THE “DISPLAY ADMINISTRATIVE TOOLS” checkbox.

4. Ok, now in the Ol’ Dirty Bastard Connections window (ODBC Window) that you just opened in step 3, select the System DSN tab

IMPORTANT NOTE: IF YOU ARE ON A 64-BIT MACHINE, __DO NOT__, I REPEAT, __DO NOT__ USE THE ODBC Window that is accessible from the taskbar. Instead, go to START > RUN > C:\Windows\SysWOW64\odbcad32.exe.

The reason is when developing in Visual Studio 2005/2008, under normal circumstances you in fact cannot publish 64-bit applications, so you CANNOT USE the 64-bit ODBC drivers from your 32-bit application. So you must set up and use the __32-bit__ ODBC drivers instead. This is a REAL assbiter. See jlgdeveloper’s master-genius answer at http://forums.devarticles.com/microsoft-sql-server-5/data-source-name-not-found-and-no-default-driver-specified-8346.html#postmenu_203344 (repeated at bottom of this page for permanence)

5. Click ADD. You see a menu

IF YOU DO NOT SEE “MySQL ODBC 5.1 DRIVER” there, CALL 911. Or, try installing the correct version of the ODBC driver.

6. Now, pick the ODBC 5.1 item and click “FINISH” (you’re not done yet though..)

7. Fill out the connection params. This is how I filled out mine.

When you’re done click TEST. You should see the box that I see “Connection successful”

If you see this box

CALL 911, or double check your parameters and make sure your MySQL daemon is running.

8. Now that you’re done that, you should see this:

REMEMBER THAT NAME, “mysqldata” or whatever name you gave the connection in the top box. This is the name you’ll refer to from your program.

9. Now run this program. Once again I remind you IF YOU ARE ON A 64-BIT MACHINE, BE SURE TO HAVE SET UP YOUR ODBC CONNECTIONS IN THE C:\Windows\SysWOW64\odbcad32.exe WINDOW, AND NOT THE ODBC WINDOW ACCESSIBLE FROM THE START MENU. YOU HAVE BEEN WARNED.

///////////////////////////////////////////
//                                       //
// WORKING WITH OL' DIRTY BASTARD (ODBC) //
//                                       //
// You found this at bobobobo's weblog,  //
// https://bobobobo.wordpress.com         //
//                                       //
// Creation date:  July 10/09            //
// Last modified:  July 10/09            //
//                                       //
///////////////////////////////////////////
// Note also nice sample comes with
// WinSDK in C:\Program Files\Microsoft SDKs\Windows\v6.1\Samples\dataaccess\odbc\odbcsql

#include <stdio.h>
#include <stdlib.h>
#include <windows.h>

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

// Here is the complete Ol' Dirty Bastard function reference:
// http://msdn.microsoft.com/en-us/library/ms714562(VS.85).aspx

// This article says you need
// to link with odbc32.lib, but taking the next line of code OUT
// doesn't seem to harm anything
#pragma comment( lib, "odbc32.lib" )

bool CHECK( SQLRETURN rc, char * msg, bool printSucceededMsg=false, bool quit=true )
{
  if( SQL_SUCCEEDED( rc ) )
  {
    if( printSucceededMsg )  printf( "%s succeeded\n", msg ) ;
    
    return true ;
  }
  else
  {
    printf( "NO!!!  %s has FAILED!!\n", msg ) ;
    
    if( quit )  FatalAppExitA( 0, msg ) ;

    return false ;
  }
}

void status( SQLSMALLINT handleType, SQLHANDLE theHandle, int line )
{
  SQLCHAR sqlState[6];
  SQLINTEGER nativeError;
  SQLCHAR msgStr[256];
  SQLSMALLINT overBy ; // the number of characters that msgStr buffer was TOO SHORT..
  
  // http://msdn.microsoft.com/en-us/library/ms716256(VS.85).aspx
  // This must be the WEIRDEST ERROR REPORTING FUNCTION I've EVER seen.
  // It requires 8 parameters, and its actually pretty .. silly
  // about the amount of state information it expects YOU to keep track of.

  // It isn't so much a "GetLastError()" function
  // as it is a "GetMeStatus( something very, very specific )" function.
  
  SQLRETURN retCode ;
  
  for( int i = 1 ; i < 20 ; i++ )
  {
    retCode = SQLGetDiagRecA(
      
      handleType,  // the type of object you're checking the status of
      theHandle,   // handle to the actual object you want the status of
      
      i, // WHICH status message you want.  The "Comments" section at the 
      // bottom of http://msdn.microsoft.com/en-us/library/ms716256(VS.85).aspx
      // seems to explain this part well.

      sqlState,    // OUT:  gives back 5 characters (the HY*** style error code)
      &nativeError,// numerical error number
      msgStr,      // buffer to store the DESCRIPTION OF THE ERROR.
      // This is the MOST important one, I suppose

      255,         // the number of characters in msgStr, so that
      // the function doesn't do a buffer overrun in case it
      // has A LOT to tell you
      &overBy      // again in case the function has A LOT to tell you,
      // the 255 character size buffer we passed might not be large
      // enough to hold the entire error message.  If that happens
      // the error message will truncate and the 'overBy' variable
      // will have a value > 0 (it will measure number of characters
      // that you 'missed seeing' in the error message).
      
    ) ;

    if( CHECK( retCode, "SQLGetDiagRecA" ) )
    {
      printf( "LINE %d:  [%s][%d] %s\n", line, sqlState, nativeError, msgStr ) ;
    }
    else
    {
      // Stop looping when retCode comes back
      // as a failure, because it means there are
      // no more messages to tell you
      break ;
    }
  }
}

int main()
{
  // Following this example, just adding a bit more
  // color and making it work with our specific example.
  
  
  // 1.  Create a handle for the environment.
  SQLHANDLE hEnv ;
  SQLRETURN retCode ;
  
  retCode = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv ) ;
  
  CHECK( retCode, "allocate environment handle" ) ;
  

  // 2.  Next, set the version of ODBC to use to ODBC version 3.
  // Format of this command is a bit weird, we cast the value we're passing
  // to (void*) because the function requires it, but then we say that the
  // length of the "string" we've passed in is 0 characters long, so I assume
  // that means SQLSetEnvAttr should know to interpret the "pointer value" that
  // we passed in as actually an integer value (which is what it is).
  retCode = SQLSetEnvAttr( hEnv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0 ) ; 

  CHECK( retCode, "setting the environment attribute setting to ODBC version 3" ) ;


  // 3.  Allocate the connection handle.  Note this doesn't
  // connect us to the database YET.  We're still "ALLOCATING",
  // whatever that means :) (Hey i know what allocating is,
  // but this is an awful number of steps to follow if you
  // ask me, microsoft!!  Whatever happened to a simple init()
  // function?
  SQLHANDLE hConn ;

  CHECK( SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hConn ), "allocate handle" ) ;

  
  

  // HOOK IT UP!!  Actually connect to the database.
  SQLCHAR* dsnName = (SQLCHAR*)"mysqldata" ;  // MUST BE THE SAME
  // as the name of the ODBC data source you set
  // in the Microsoft ODBC Administrator window.

  SQLCHAR* userid = (SQLCHAR*)"root";
  SQLCHAR* password = (SQLCHAR*)"";  // using a BLANK
  // Above are my own correct userid and password credentials to
  // be used when logging into the MySQL database server.

  // 4.  Open database connection.
  retCode = SQLConnectA(
    
    hConn,
    
    dsnName,  // name of data source we are connecting to,
    // AS PER REGISTERED IN ODBC Data Source Administrator.

    // If you are on a 64-bit machine, and you
    // DO NOT USE THE 64-bit driver.  As long as
    // your compiler publishes a 32-bit .exe (which
    // Visual Studio does), you'll keep getting:

    // [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    // SO DON'T USE THE 64-BIT DRIVERS!  Instead, install
    // the 32-bit driver, and then managing/using
    // your 32-bit datasources in
    // c:\windows\syswow64\odbcad32.exe

    // Note that on a 64-bit windows machine, the 32-bit
    // drivers and the 64-bit drivers are managed
    // from COMPLETELY SEPARATE, BUT IDENTICAL-LOOKING
    // windows.  Its really weird.

    // On a 64-bit machine:
    // c:\windows\system32\odbcad32.exe    // 64-bit version [even though it SAYS system32_ in the path, this is the __64__ bit version on a 64-bit machine]
    // c:\windows\syswow64\odbcad32.exe    // 32-bit version [even though it SAYS syswow64_ in the path]

    // Call it stupid, scream, pull your hair out,
    // that's what it is.
    // http://stackoverflow.com/questions/949959/why-do-64bit-dlls-go-to-system32-and-32bit-dlls-to-syswow64-on-64bit-windows

    // and

    // http://blogs.sepago.de/helge/2008/04/20/windows-x64-all-the-same-yet-very-different-part-7/

    // Thanks again, Microsoft,
    // for making the 64-bit programming experience
    // such a pleasure.
    SQL_NTS,  // the DSN name is a NULL TERMINATED STRING, so "count it yourself"

    userid,
    SQL_NTS,  // userid is a null-terminated string
    
    password,
    SQL_NTS   // password is a null terminated string
    
  ) ;
  if( !CHECK( retCode, "SqlConnectA", false ) )
  {
    // if this fails, I want that extra status
    // information about WHY the failure happened.
    // status function is defined above.
    
    status( SQL_HANDLE_DBC, hConn, __LINE__ ) ;
  }


  // 6.  Create and allocate a statement
  SQLHANDLE hStmt ;
  CHECK( SQLAllocHandle( SQL_HANDLE_STMT, hConn, &hStmt ), "allocate handle for statement" ) ;

  
  // 7.  Form a query to run and attach it to the hStmt
  // this basically connects the hStmt up with
  // some results.
  SQLCHAR* query = (SQLCHAR*)"SELECT * from user" ;
  CHECK( SQLExecDirectA( hStmt, query, SQL_NTS ), "execute query" ) ;


  // 8.  Read data results that are now in the hStmt.
  retCode = SQLFetch( hStmt ) ;

  CHECK( retCode, "first sqlFetch" ) ;

  // How many rows got returned?
  SQLLEN numRows ;
  retCode = SQLRowCount( hStmt, &numRows ) ;
  printf( "%d rows were fetched, ruff.\n", numRows ) ;

  // With a query like the one we wrote (SELECT *),
  // we don't know how many columsn should be in
  // the result set at this point.
  // So we ask ODBC to tell us!
  SQLSMALLINT numCols ;
  retCode = SQLNumResultCols( hStmt, &numCols ); // SqlNumResultCols

  // Now print the column names.
  // SQLDescribeCol function
  SQLCHAR colName[ 256 ] ;
  
  SQLSMALLINT colNameLen, dataType, numDecimalDigits, allowsNullValues ;
  SQLUINTEGER columnSize ;

  for( int i = 1 ; i <= numCols ; i++ )
  {
    retCode = SQLDescribeColA( hStmt, i, colName, 255, &colNameLen, &dataType, &columnSize, &numDecimalDigits, &allowsNullValues ) ;
    if( CHECK( retCode, "SQLDescribeCol" ) )
    {
      printf( "Column #%d: '%s', datatype=%d size=%d decimaldigits=%d nullable=%d\n",
                       i,colName,   dataType, columnSize,  numDecimalDigits, allowsNullValues ) ;
    }
  }

  for( int i = 1 ; i <= numRows ; i++ )
  {
    // Datatypes
    // SQLGetData

    char buf[256];
    SQLINTEGER numBytes ;

    for( int j = 1 ;   // column counter starts at __1__, not 0.
      j <= numCols ;   // numCols retrieved above
      j++ )
    {
      retCode = SQLGetData(
      
        hStmt,
        j,           // COLUMN NUMBER of the data to get
        SQL_C_CHAR,  // the data type that you expect to receive
        buf,         // the place to put the data that you expect to receive
        255,         // the size in bytes of buf (-1 for null terminator)
        &numBytes    // size in bytes of data returned
      
      ) ;

      if( CHECK( retCode, "SqlGetData", false ) )
      {
        // Print the data we got.
        printf( "%10s", buf ) ;
      }
    
    } // end for.

    puts("");

    // Try and fetch the next result.
    // fall out of the loop if fetch fails
    // (meaning, no more data to get)
    retCode = SQLFetch( hStmt ) ;
    if( !SQL_SUCCEEDED( retCode ) )
    {
      // SQLFetch FAILS AS IT FETCHES the last row of data.
      printf( "And %d is the LAST row.. we're not getting any more after this one\n", i ) ;
    }
  }

  // So we used a FOR loop above to fetch
  // exactly numRows rows from the results.

  // The other (perhaps more common) 
  // way to do this is to use a loop like
  
  // while( SQL_SUCCEEDED( SQLFetch( hStmt ) ) )
  // {
  //   // Work with result data
  // }

  // When we do it that way, 
  // WE EXPECT/RELY ON SQLFetch TO TELL US
  // IT FAILED when we've reached the
  // LAST row of data.

  // free up resources.
  SQLFreeHandle( SQL_HANDLE_STMT, hStmt ) ;
  SQLFreeHandle( SQL_HANDLE_DBC, hConn ) ;
  SQLFreeHandle( SQL_HANDLE_ENV, hEnv ) ;
}


// Assbiters:
// 1. Be careful not to use the SUCCEEDED() macro
// instead of the SQL_SUCCEEDED() macro when
// checking your SQLRESULT values.
//
// 2. Do not use 64-bit ODBC drivers, even if on
// a 64-bit machine.  See information above.

/*
     ____   __   __      __   __  ___
    / _  \ /  / /  /    /  /  \ \/  /
   / _/ / /  / /  /    /  /    \   /
  / _/ \ /  / /  /__  /  /__   /  /
 /_____//__/ /______//______/ /__/

*/

Master genius John

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I had this error and want to let you know how it was resolved.

First, this was an ASP web application using a vb 6.0 dll to get data from a sql server 2005 database on a 64 bit windows server 2008 enterprise (vista like) server. I could only get the dll to work in component services as opposed to simply registering it.

It all worked fine upon setup, but after four windows updates one night, the error above was posted in the event viewer, and the web app crashed.

Here is the resolution:

In a 64 bit windows server operating system, there are TWO odbc managers. When you pull up the usual menu for the odbc / dsn system, it is for the 64 bit odbc manager, and 32 bit applications (vb 6.0) will not work using these dsn’s.

This is where the 32 bit odbc manager is:

C:\Windows\SysWOW64\odbcad32.exe

I hope you do not have to go through what I and three Microsoft Support engineers had to to figure this out.

Jonathan

Download the code package courtesy of esnips (thanks esnips!)