Getting started with MySQL in C++

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