Skip navigation

Category Archives: mysql

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

/////
// Connects to MySQL database server from
// C or C++ program.
/////

/////////////////////////////////////////////
//                                         //
// Connecting C++ Win32 App To MySQL       //
//                                         //
// You found this at bobobobo's weblog,    //
// https://bobobobo.wordpress.com           //
//                                         //
// Creation date:  Apr 5/09                //
// Last modified:  Apr 5/09                //
//                                         //
/////////////////////////////////////////////



// !! Note!  I've included copies of the MySQL
// include headers and library files.. I've done this
// so that the project will compile and run
// more easily for you, but this DOES NOT mean
// that you should skip the SETUP-Y stuff section
// below!

// If you want MySQL to work easily for you in new
// projects, you really should set up Visual Studio
// as explained below.


#pragma region all the setup-y stuff
/////////
// MAKINET WORK: (any prince of persia 2 the shadow and the flame fans out there?)
//
// 1)  First, you OBVIOUSLY must install MySQL.
//     Be sure to download the 32-BIT VERSION (NOT THE x64)
//     of MySQL 5.1 - "Windows MSI Installer"
//     Located at http://dev.mysql.com/downloads/mysql/5.1.html#win32
//     DO NOT GET THE "WINDOWS ESSENTIALS" PACKAGE
//     DO NOT GET __ANY__ x64 crap EVEN IF you are on
//     windows 64 bit, ALWAYS use the NORMAL (non-64 bit)
//     stuff (see Ancient Dragon's comments for why:
//     http://www.programmingforums.org/thread16958.html)

//     Done that?  GOOD!!  Onto step 2.

// 2)  ENSURE THAT mysql.h is in your VISUAL STUDIO
//     PROJECT PATH SETTINGS.  If you don't do this,
//     then you'll may an error of the form of:

// Error	1	fatal error C1083: Cannot open include file: 'mysql.h':
//          No such file or directory


// For me, mysql.h is in :
// C:\Program Files\MySQL\MySQL Server 5.1\include

// But you have to tell VISUAL STUDIO THAT!!  IT doesn't know.

// Need to edit Visual Studio INCLUDE Directory settings.  here's how.

// SO I, click
//   1  TOOLS -> OPTIONS
//   2  PROJECTS AND SOLUTIONS -> VC++ DIRECTORIES (left hand panel)

//   3  (now in right hand panel),
//    from the two dropdowns there
//      (hanging like two microsoft eyes..), uh,

//   4  you just make sure under PLATFORM,
//      it says Win32,

//   5  and under SHOW DIRECTORIES FOR,
//      it says INCLUDE FILES.

//   6  THEN, click the SHINY YELLOW FOLDER,
//      and then click the '...'
//   7  and navigate to your MySQL INSTALL DIR\INCLUDE
//      add that folder there.

//    For me, I end up with an extra entry in that list that says:

//       C:\Program Files\MySQL\MySQL Server 5.1\include

//    yours may be slightly different, but not too different,
//    hey, don't try and be cool here by being REALLY different please.
//   

// OK?  So now the #include below should work for you.
// If it doesn't, INSTALL MYSQL FIRST!

// YOU MUST #INCLUDE WINDOWS.H FIRST IF YOU WANT MYSQL
// TO WORK ON WINDOWS!!
#include <windows.h>
#include <stdio.h>

// To get autocomplete to work, you may have to right-click
// your Header Files -> Add Existing Item... and pick
// mysql.h and add it to your project.  I've copy and pasted
// the file into this project, though.



// !!
// You may only use the form below if you have followed the
// installation instructions above.
/////#include <mysql.h>

#include "mysql_include/mysql.h"  // use this form if you haven't "installed"
// MySQL header files into visual studio as explained above.



// You must also add C:\Program Files\MySQL\MySQL Server 5.1\lib\debug
// to your Visual Studio LIBRARY directory settings (follow steps above again, except
// at step 5, you choose "SHOW DIRECTORIES FOR -> LIBRARY FILES"
// and at step 7, you add C:\Program Files\MySQL\MySQL Server 5.1\lib\debug
// (or whatever yours really is)


// !!
// You may only use the form below if you have followed the
// installation instructions above.
/////#pragma comment( lib, "libmysql.lib" )

#pragma comment( lib, "mysql_lib/debug/libmysql.lib" )  // use this form if you haven't "installed"
// MySQL header files into visual studio as explained above.

// libmysql.lib is what is termed an "import library" - basically
// it is what wires up function calls in this program to MYSQL
// functionality to the libmysql.dll file.

// So what about libmysql.dll?

// FINALLY, ENSURE TO COPY libmysql.dll to EITHER:  the \Debug folder
// of this project (WHERE THE FINAL .EXE RESIDES), __OR__, to
// C:\WINDOWS\System32 (if that doesn't work, then copy it to C:\WINDOWS\System).

// For me, libmysql.dll lives in
// C:\Program Files\MySQL\MySQL Server 5.1\lib\debug
// Its also included in the mysql_lib/debug folder of
// this project.

// To quote Charlie Charlie Petzold, Programming windows 5th ed:
// You can put a DLL file:

// A dynamic library must be present on the disk
// when a program is run that uses the library.
// When Windows needs to load a DLL module before running
// a program that requires it, the librar must be stored:
//   1.  In the directory containing the .exe program
//   2.  the current directory
//   3.  the Windows system directory
//   4.  the windows directory
//   5.  or a directory accessibel through the PATH string in the MS-DOC environment

// The directories are searched in the above order.


#pragma endregion





#pragma region CODING WITH MYSQL


////
// Globals
MYSQL mysql;    // the MYSQL global object.  Passed to mysql_init,
                // and there's only one of this.

MYSQL * conn ;  // represents connection to database
//
////

int main()
{
  // OK!!  Now that you've got all the setup-y stuff out of the way, its
  // time to connect to MySQL!!

  // here are some great references:
  // 1)  http://c-programming.suite101.com/article.cfm/using_a_mysql_databases_with_c
  // 2)  http://dev.mysql.com/doc/refman/5.1/en/windows-client-compiling.html
  mysql_init( &mysql ) ;
  
  // PLEASE SEE DOCS PAGE FOR MORE DETAILS
  // ABOUT mysql_real_connect():
  //   http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html
  conn = mysql_real_connect(  &mysql,
                              "localhost",// synonymous with 127.0.0.1
                              "root",     // connect as user="root".  Uh, bad security here..
                              "",         // my root password is blank.  REALLY bad security :)
                              "mysql",    // connect to the 'mysql' _database_ within MySQL itself.
                                          // if you create another database, then you can
                                          // specify you'd like to connect to that one here.

                              0,          // port.  Mine is on 3306, but you can leave it as 0
                                          // and it seems to work automatically.

                              0,          // unix_socket (not in use)

                              0 ) ;       // client flag:  usually 0, unless you want special features (see docs page)
  
  // At this point you may be wondering, ok,
  // so if this is real_connect, what's fake_connect?
  // There is a mysql_connect() function!  Which is
  // basically the "fake" connect... see docs, but
  // the "fake" functions (mysql_query(), mysql_connect()),
  // are like watered down version of their _real_ counterparts.
  // Sometimes useful, sometimes not.  Again, see docs pages.


  // Check if connection succeeded.
  if( conn == NULL )
  {
    printf("Couldn't connect to MySQL database server!\n");
    printf("Error: %s\n", mysql_error( &mysql ) ) ;
    return 1 ;
  }
  else
  {
    printf("Connect success\n") ;
  }
  


  #pragma region running an actual query
  // Here, we are connected.
  // form a sql string to execute.
  
  char * query = "select * from user" ;
  int queryState;

  queryState = mysql_query( conn, query ) ;

  if( queryState != 0 )
  {
    printf("Whoops!  The query failed.  Error:  %s\n", mysql_error( conn ) );
    return 1 ;
  }

  // here, query succeeded, so we can proceed
  // to pull out results.
  MYSQL_RES * resultset ;
  MYSQL_ROW row;  // MYSQL_ROW is #defined as (char **)
  // Data ALWAYS comes back from MySQL as
  // an array of strings.  To convert it
  // to ints or whatever is YOUR JOB as the programmer.

  // mysql_store_result basically fetches
  // the entire array of results and dumps them
  // into our local program memory space (all
  // in the resultset variable.
  resultset = mysql_store_result( conn );

  // How many rows will there be?
  int numRows = mysql_num_rows( resultset ) ;
  printf( "There are %d ROWS (records) of data\n", numRows ) ;

  
  
  // Now tell me what columns there are
  // in the result set.
  int numFields = mysql_num_fields( resultset ) ;
  printf( "There are %d FIELDS (columns) of data\n", numFields ) ;

  // Print all those column by name
  MYSQL_FIELD * fields = mysql_fetch_fields( resultset ) ;
  for( int i = 0 ; i < numFields ; i++ )
  {
    printf( "%25.25s", fields[i].name ) ;
  }

  printf( "\n" ) ;
  // print all results
  while( row = mysql_fetch_row( resultset ) )
  {
    // row is 2d array of char
    // underlying type is char **
    for ( int i = 0; i < numFields ; i++ )
    {
      printf( "%25.25s", row[ i ] ) ;
    }

    // next row
    printf( "\n" ) ;
  }

  #pragma endregion
  
  // Now free the result
  mysql_free_result( resultset );

  // And close down.
  mysql_close( conn ) ;
  return 0;
}


#pragma endregion // CODING WITH MYSQL


// Your hands quiver with power.

// You are now a MySQL GURU.


// For the record, there's also:
// Connector C++:  http://forge.mysql.com/wiki/Connector_C%2B%2B
// MySQL++:  http://tangentsoft.net/mysql++/

// I chose not to use either library because.. well, I dislike
// additional layers.



// TROUBLESHOOTING:
//
// "This application has failed to start because LIBMYSQL.dll
// was not found.  Re-installing the application may fix this problem."

// So, like internet superhero says, you have 3 options:
// [ http://blog.ulf-wendel.de/?p=215 ]
// 1.  Copy LIBMYSQL.dll into you Windows system directory (C:\Windows\system and/or C:\windows\system32, whichever spins your wheels)
// 2.  You copy LIBMYSQL.dll into the current working directory 
// 3.  You add the location of the LIBMYSQL.dll to your path setting, for example using SET PATH=%PATH%;C:\path\to\LIBMYSQL.dll

// LIBMYSQL.dll is somewhere in your MYSQL INSTALLATION directory..
// so find it, and copy LIBYMSQL.dll to C:\Windows\System32 and/or C:\Windows\System
// OR copy it to the \Debug directory for this project (where the final .exe resides)



// Final note:  static lib compiling.
//#pragma comment( lib, "mysqlclient.lib" )   // doesn't work.. produces 76 errors of the form
// Error	1	error LNK2005: __aligned_malloc already defined in MSVCRTD.lib(MSVCR90D.dll)	       (from file LIBCMTD.lib)
// 
// root of problem seems to be in the warning:
//  Warning 44 warning LNK4098: defaultlib 'MSVCRTD' conflicts with use of other libs; use /NODEFAULTLIB:library

// Problem docs:
//     http://curl.haxx.se/mail/lib-2006-04/0101.html
//     http://www.codeguru.com/forum/archive/index.php/t-375084.html

// MikeAThon, at the bottom of the second post there, says
// LIBCMT is the static C-runtime library (and is multi-threaded),
// whereas MSVCRTD is the dynamic linking C-runtime. See
// "How to link with the correct C Run-Time (CRT) library" at
// http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q140584& .
// According to that KB article, "the linker will issue a warning
// (LNK4098) if a resulting module attempts to combine more than
// one copy of the CRT library"

// So go into linker -> input -> ignore all default libraries (/NODEFAULTLIB)
// See Internet Superhero again for more help http://blog.ulf-wendel.de/?p=215


Download Visual Studio 2008 project files on esnips (thanks esnips!)