Setting up MySQL for use through ODBC.

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.

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

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

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.

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

Excellent article, thanks for sharing this. The code is great, just a couple of comments:
* Don’t forget to call SQLDisconnect before you call SQLFreeHandle or you might get a “Function call sequence error”
* For me SQLRowCount always returns -1, apparently it is not intended for Select statements? Strange.

What a joy when sample code is clearly written and works, ruff. Just two amendments: On Windows 7/2008 with Visual Studio 2010 the x64 ODBC drive worked fine, no need to fear it. And SQL Server doesn’t return the row count, so you have to take the sample’s second approach with a while( SQL_SUCCEEDED( SQLFetch( hStmt ) ) ) { } loop. And be careful not to call SQLFetch once before that loop for your column info, because that “eats” your first row of data.