How To: Use MySQL Connector/C++ to Connect to a MySQL Database in Windows

r3dux | November 27, 2010

I got this working in Linux in about ten minutes, while the same thing in Windows took me closer to three hours… The difficulty I had was that I need to get this working on multiple machines which all get wiped and reset on each boot, so it had to be entirely stand-alone with everything necessary to build in the same project. And the difficulty with that is that Connector/C++ needs the boost library to compile, which I wasn’t too keen on involving. In the end I couldn’t find a way around it and just threw the entire boost library into the same directory as the project as well as the libmysql.lib/dll and mysqlcppconn.lib libraries.

So now it works. It’s a little ugly, and the project folder comes in at 62MB, but it works. Really it’s not all that bad, if you just wanted to build and deploy something you’d end up with a couple of hundred KB executable plus a few MB of dlls. I guess I could go in and strip out some parts of the boost library which aren’t being used. But to be honest it’s been a long day and I’m sick of fighting with it, so I’ll leave that as an exercise for the reader.

Reminder / Info

  • This code is tested and working on Windows XP 32-Bit.
  • The project (i.e. download, at bottom of post) is for Microsoft Visual Studio 2008 Express
  • The version of the libs/headers included are:
    • libmysql.lib from MySQL server v1.5.36 32-Bit
    • boost library v1.45.0
    • mysqlcppconn.lib/dll from Connector/C++ v1.1.0 32-Bit
  • I really, really hate having to work with Windows.

Anyhoo – here’s the junk…

// Standard C++ includes
#include <iostream>
#include <cstdlib>
#include <string>
using namespace std;
 
// Include the Connector/C++ headers
#include "cppconn/driver.h"
#include "cppconn/exception.h"
#include "cppconn/resultset.h"
#include "cppconn/statement.h"
 
// Link to the Connector/C++ library
#pragma comment(lib, "mysqlcppconn.lib")
 
// Specify our connection target and credentials
const string server   = "tcp://127.0.0.1:3306";
const string username = "root";
const string password = ""; // No password - thanks, WAMP Server!
 
int main()
{
	sql::Driver     *driver; // Create a pointer to a MySQL driver object
        sql::Connection *dbConn; // Create a pointer to a database connection object
        sql::Statement  *stmt;   // Create a pointer to a Statement object to hold our SQL commands
        sql::ResultSet  *res;    // Create a pointer to a ResultSet object to hold the results of any queries we run
 
	// Try to get a driver to use to connect to our DBMS
	try
	{
		driver = get_driver_instance();
	}
	catch (sql::SQLException e)
	{
		cout << "Could not get a database driver. Error message: " << e.what() << endl;
		system("pause");
		exit(1);
	}
 
	// Try to connect to the DBMS server
	try
	{
		dbConn = driver->connect(server, username, password);
	}
	catch (sql::SQLException e)
	{
		cout << "Could not connect to database. Error message: " << e.what() << endl;
		system("pause");
		exit(1);
	}
 
	stmt = dbConn->createStatement(); // Specify which connection our SQL statement should be executed on
 
	// Try to query the database
	try
	{
		stmt->execute("USE mysql");              // Select which database to use. Notice that we use "execute" to perform a command.
 
		res = stmt->executeQuery("show tables"); // Perform a query and get the results. Notice that we use "executeQuery" to get results back
	}
	catch (sql::SQLException e)
	{
		cout << "SQL error. Error message: " << e.what() << endl;
		system("pause");
		exit(1);
	}
 
	// While there are still results (i.e. rows/records) in our result set...
	while (res->next())
	{
		// ...get each field we want and output it to the screen
		// Note: The first field/column in our result-set is field 1 (one) and -NOT- field 0 (zero)
		// Also, if we know the name of the field then we can also get it directly by name by using:
		// res->getString("TheNameOfTheField");
		cout << res->getString(1) << endl;                
	}
 
	// Clean up after ourselves
	delete res;
	delete stmt;
	delete dbConn;
 
	system("pause");
	return 0;
}

*** REALLY IMPORTANT: Build the following project in Release mode if you want it to work!! No, really. The libs and dll’s that the project uses have been built in Release mode and you can’t mix n’ match. ***

Download of Visual Studio 2008 Express project including all necessary headers/libs/dlls (i.e. it’s ready to go – and 12MB): MySQL-Connector-Test.zip

Credits: Thanks to TidyTutorials for the guide that got me started.

Related posts:

  1. How To: Disable All WordPress Plugins from the Database
  2. How To: Fix Wireless Connection Drops on the Intel 4965 Chipset in Jaunty 9.04
  3. How To: Switch a VirtualBox Windows Guest Hard Drive from IDE to SATA Mode
  4. How To: Stop Windows Vista/Windows 7 from Automatically Restarting Your PC after Updates
  5. How To: Perform Real-Time OpenCV Edge Detection on a WebCam Stream
Categories
Coding
Tags
, , , , , ,
Comments rss
Comments rss
Trackback
Trackback
Print This Post Print This Post

« »

26 Responses to “How To: Use MySQL Connector/C++ to Connect to a MySQL Database in Windows”

  1. Reilly says:
    December 18, 2010 at 1:31 pm

    yeah windows is a right pain to do anything even if we had access to cygwin or vmware at uni it would be easier i hate how something works perfectly on linux and you get it on windows and its like argh my head hurts already

    Reply
  2. chloe says:
    April 14, 2011 at 1:02 am

    hi, I am stuck at the try to query the database step. The message error is unknown exception. Can you help me to solve it? Thank you very much.

    Reply
    • r3dux says:
      April 14, 2011 at 9:35 am

      Hi Chloe,

      I assume the project compiles. If you have WAMP server installed, it’s probably worth doing some or all of the following:

      - Confirm the mysql service is running from the WAMP indicator in the bottom-right system tray,

      - Confirm (via phpmyadmin) that a database called “mysql” exists. Phpmyadmin is accessible by going to http://localhost in a web browser while all WAMP server services are running,

      - Confirm that you can log into mysql using mysqladmin -u root (the mysqladmin.exe utility will be in C:\wamp\bin\mysqlx.y.z\mysql or such) so that you know it’s not a username/password issue,

      - Try turning your firewall off to make sure it’s not blocking traffic on port 3306

      Put some debug output in the code if you’re not sure where it’s failing. But if I understand you correctly, the query database block is running, but generating an exception so you see output of: “SQL error. Error message: unknown exception”, right? As long as you can use mysql.exe command line util (found in the same place as the mysqladmin on mentioned above), and the commands use mysql; followed by show tables; actually show you a list of tables in the database then we can rule out that MySQL isn’t functioning at all.

      Have a go and let me know how you go on – and remember, the more info you give me to work with the better I’m likely to be able to help you! If there are error messages, please provide the exact and complete error messages.

      Also, some further reading: Introduction to MySQL

      Cheers!

      Reply
  3. chloe says:
    April 14, 2011 at 12:39 pm

    Thank you very much. =D
    It works now. May I know how to select and print out the information inside one table? Sorry for the trouble.

    Reply
  4. chloe says:
    April 14, 2011 at 4:37 pm

    Its ok… i already solve it! =D thank you!!!!!!!!

    Reply
  5. chloe says:
    April 14, 2011 at 5:19 pm

    Hi,
    Sorry to bother you again. I cant seem to add the insert data into database part into my code.

    // Lots of code to connect to the database etc...
 
template <class T>
std::string toString(const T&amp; t)
{
     std::ostringstream stream;
     stream &lt;execute("USE sp"); // Select our database
 
	// Variables to hold our input data
	string cardID;
	string staffID;
 
	// Get user input
	cout &lt;&gt; cardID;
	cout &lt;&gt; staffID;
 
	// Construct our SQL statement as a string	
	string sqlStr = " INSERT INTO staff (staffID, cardID) VALUES ('";                // Notice the opening apostrophe!
	sqlStr += staffID;
	sqlStr += "',";            // Notice the closing apostrophe!
	sqlStr += cardID; // Convert our int to a string and append it. No apostrophes because it's a numerical type!!
	sqlStr += ")";
 
 
	int affectedRows = stmt-&gt;executeUpdate(sqlStr); 
 
	cout &lt;&lt; &quot;Done! Num. rows affected: &quot; &lt;executeQuery(sqlStr); 
}
catch (sql::SQLException e)
{
	cout &lt;&lt; &quot;SQL error. Error message: &quot; &lt;&lt; e.what() &lt;&lt; endl;
	system(&quot;pause&quot;);
	exit(1);
}
 
	// Clean up after ourselves
	delete res;
	delete stmt;
	delete dbConn;
 
	system(&quot;pause&quot;);
	return 0;
}

    There is always a red color line under template regardless of where I tried to put #include . Can you help me solve this? Thank you.

    Edited for brevity by r3dux – 2011-04-14

    Reply
    • r3dux says:
      April 14, 2011 at 6:46 pm

      That code looks awfully familiar ;)

      Ah, I see what you’re getting at – you’ve got the templated toString function all messed up. Also, the way I did it in the slides (i.e. the way you did it) isn’t a particularly nice way of creating SQL queries.

      I’ve done a better way of inserting data using prepared queries and other such stuff in another slide set, I just haven’t posted it – I’ll clean up the slides and post tomorrow & you’ll be all set.

      If you want to have a go yourself before then, the templated toString function should look like this:

      template<class T>
std::string toString(const T& t)
{
   std::ostringstream stream;
   stream << t;
   return stream.str();
}

      And to use it, you’ll need to include the following:

      #include <iostream>
#include <string>
#include <sstream>

      Here’s some example code to demonstrate how the toString function works:

      #include <iostream>
#include <string>
#include <sstream>
using namespace std;
 
// Templated function to convert any type of data to a string
template<class T>
std::string toString(const T& t)
{
   std::ostringstream stream;
   stream << t;
   return stream.str();
}
 
int main()
{
	int firstNumber = 5;
	string intAsString = toString(firstNumber);
	cout << "The integer as a string is: " << intAsString << endl;
 
	float secondNumber = 1.234f;
	string floatAsString = toString(secondNumber);
	cout << "The floating point number as a string is: " << floatAsString << endl;
 
    return 0;
}
      Reply
    • r3dux says:
      April 15, 2011 at 10:17 pm

      As promised, second part is now up here and shows how to use prepared statements to insert data – I think you’ll find it a much nicer method =D

      Cheers!
      -r3dux

      Reply
  6. chloe says:
    April 16, 2011 at 6:10 pm

    Thank you sooo much… everything can run smoothly now.. =D

    Reply
  7. michael says:
    May 26, 2011 at 5:02 pm

    hi,

    may i know how to link c++ window form application using button with mysql?

    like when i press the button in the form, it will link to mysql.

    so i can proceed with add/delete/update to mysql.

    thanks in advance.

    Reply
    • r3dux says:
      May 26, 2011 at 6:12 pm

      Hi Michael,

      Sorry, but I have no idea (I don’t really code GUIs in Windows) but this looks like it’ll show you how to trigger functions from buttons in a Windows form: http://msdn.microsoft.com/en-us/library/s0scsyfb.aspx

      It sounds like a pretty common thing people would want to do, so I’m sure there’s plenty of tutorials/video guides out there. Have a look around & give it a go. If you really can’t get it I’ll have a look and try to knock a simple “call-a-function-when-you-press-a-button” example together for you, but Windows programming is definitely not my forte, k?

      Best wishes,
      r3dux

      Reply
  8. Jan says:
    June 3, 2011 at 3:29 pm

    Hi, Im currently having some probs with mysql. im using vs2010 and wamp, yet i have problem whenever i debug:
    Could not connect to database. Error message: Unknown exception.

    Do you know the solution to it?
    :)

    Reply
    • r3dux says:
      June 3, 2011 at 5:26 pm

      Um, maybe. You cannot build the provided project in Debug mode, you have to build it in Release mode, because there are no debug symbols in the provided libraries, as mentioned in the article. If you want to build it in debug mode, you need to obtain and link to debug versions of the the libraries (libmysql, Connector/C++ etc).

      If you are building in Release mode and it’s still not working, then I’m not so sure. The connect to server catch block is triggering, i.e. this bit:

      // Try to connect to the DBMS server
try
{
	dbConn = driver->connect(server, username, password);
}
catch (sql::SQLException e)
{
	cout << "Could not connect to database. Error message: " << e.what() << endl;
	system("pause");
	exit(1);
}

      But, I’ve just tested the code with Visual Studio 2008 and I get:

      Could not connect to database. Error message: Can’t connect to MySQL server on ’127.0.0.1′ (10061) – if the MySQL server isn’t running or is pointed at an bad address,
      SQL error. Error message: Access denied for user ”@’localhost’ to database ‘mysql’ – if MySQL is running and we connect to it, but provide an invalid username, and
      Could not connect to database. Error message: Access denied for user ‘root’@'localhost’ (using password: YES) – if MySQL is running and we connect but provide an invalid password.

      Again, I think it’ll be a library thing and either an incompatibility with VS2010 (in which case perhaps you’ll need to get libraries compiled for VS2010), or you’re trying to run 32-bit libs on a 64-bit OS and it’s not liking it.

      Best of luck & hope you get it sorted,
      r3dux

      Reply
  9. james says:
    October 12, 2011 at 4:46 am

    I’m getting the same error as Chloe. Did you find out what was causing her problem?

    I followed your suggestions to her:
    – Confirm the mysql service is running from the WAMP indicator in the bottom-right system tray,
    – Confirm (via phpmyadmin) that a database called “mysql” exists.
    – Can log in using “msql.exe -u root”, & issue “use mysql” & “show tables”
    – Turned firewall off

    I put some debug messages and it looks like the error happens when doing:
    stmt->execute(“USE mysql”);

    Everything compiled ok under MS VC++ 2010 Express (Release build).
    I was going to try w/ 2008, but having trouble downloading it at the moment.
    Would appreciate any help.
    Thanks.

    Reply
    • r3dux says:
      October 12, 2011 at 8:59 am

      Hi James,

      It sounds like you’re doing everything right – service is running, can see & connect to database from MySQL tools etc….

      If you’re getting an unknown exception error (which I think is the error you’re referring to) then I’d have to guess that it’s got to be some kind of library mismatch – try getting a new version of Connector/C++ for Visual Studio 2010 and substitute in those dlls for any in the existing project. Also, the libs I provided with the precompiled project are 32-bit, if your system is 64-bit it’s not going to work.

      Also if you separate out each line of “risky” code into its own separate try/catch block then you’ll know exactly which statement is failing, like this:

      try
{
	cout << "About to try to USE mysql..." << endl;
	stmt->execute("USE mysql");
	cout << "USEd mysql without dying!" << endl;
}
catch (sql::SQLException e)
{
	cout << "SQLException on USE: " << e.what() <<endl;
	exit(-1);
}
catch (exception e)
{
	cout << "Generic exception on USE: " << e.what() << endl;
	exit(-1);
}
 
try
{
	cout << "About to try to show tables..." << endl;
	res = stmt->executeQuery("show tables"); 
	cout << "Showed tables without dying!" << endl;
}
catch (sql::SQLException e)
{
	cout << "SQLException on show tables: " << e.what() <<endl;
	exit(-1);
}
catch (exception e)
{
	cout << "Generic exception on show tables: " << e.what() << endl;
	exit(-1);
}
      Reply
      • james says:
        October 13, 2011 at 12:48 am

        I think you’re right about needing to recompile Connector/C++ under VS 2010.

        Was able to obtain VS 2008 Express and it works fine now.
        Thanks for the help & the great sample code!

        Reply
  10. rr. says:
    November 3, 2011 at 1:28 am

    It’s a bit strange for me.
    In my case, it only works with DEBUG mode.
    In RELEASE mode, it spit out message like “Could not connect to database. Error message: Unknown exception”.

    I tested with: Windows 7 SP1 Pro x64 + MySQL x64 5.5.12 + VS 2010 SP1
    (I converted the original project into VS 2011 project)

    To run it in RELEASE model, I had to replace “mysqlcppconn.dll” and “mysqlcppconn.lib”.
    (I extracted the files from “mysql-connector-c++-noinstall-1.1.0-win32.zip” at http://www.mologie.de/blog/2010/10/mysql-connector-c-und-visual-studio-2010/)

    Did I do something wrong?

    Reply
  11. rr. says:
    November 3, 2011 at 1:34 am

    If I try to run it again in DEBUG mode after I replace the files (to run it in RELEASE mode and it works),

    then it spit out message like “Could not connect to database. Error message: Access denied for user ‘ODBC’@'localhost’ (using password: NO)”

    I don’t know what is wrong with me.

    Any advice will be appreciated.

    Thanks.

    Reply
  12. r3dux says:
    November 3, 2011 at 5:09 pm

    I originally wrote this code using XP and Visual Studio 2008 and it worked correctly when built in Release mode – if you’re going to use this with Windows 7 and/or Visual Studio I’d recommend creating a new project entirely and copying and pasting the code in then setting up the libraries of the VS project, as something’s obviously going wrong with the conversion.

    I recently had to cover this material in a class I’m teaching (like, last week) and it worked fine with Windows 7 and Visual Studio 2008 – so it must be a VS2010 thing.

    This might help you out (but remember to get rid of the password if you’re using WAMP Server as by default there is no password for the root account): http://blog.ulf-wendel.de/?p=215#hello

    Reply
  13. sathishkumar says:
    December 16, 2011 at 12:56 am

    Hi r3dux,

    i tried to connect mysql db using c++. but, compile time i get no input file from compiler.how to solve this problem?

    Reply
    • r3dux says:
      December 16, 2011 at 11:33 am

      I don’t know what you mean by “no input file from the compiler” – what error message do you get? (please quote the entire, exact error message)

      Reply
  14. paul says:
    May 24, 2012 at 1:09 am

    How can I specify connection parameters when creating a connection?
    For instance, I want to set the parameter “CharSet” to “utf8″.

    I’m used to appending all the key / value pairs after the port in the url, but that doesn’t seem to work.

    Thanks,
    Paul

    Reply
  15. adri says:
    November 17, 2012 at 7:57 am

    Hi. Thank you before nothing.

    I’m working with Windows 7 and Visual Studio 2010. I have the same problem that was writen by “rr.” user.

    I try connect to mysq data through c++ code. When I compile it in DEGUB mode, it works successfully, but I need compile my proyect with RELEASE mode. In this case always has an error to connect to dabase:

    ” dbConn = driver->connect(current_server, current_username, current_password); ”

    Error:
    “Could not connect to database. Error message: Unknown exception”.

    I have tried with:

    MySQL Connector/C++ 1.1.1 (and boost_1_52 for this case to be neccesary)
    MySQL Connector/C++ 1.0.5

    Thanks in advance.

    Reply
    • r3dux says:
      November 17, 2012 at 8:18 am

      I wish I knew how to help you – I’ve had nothing but headaches from trying to use MySQL Connector C++ with various versions of Visual Studio, Boost and compiling in Debug and Release mode. On one machine it’ll work fine, then on another it won’t work in one build mode but will in another, and on a different machine again it’ll work in the opposite mode (say, Debug) but not in Release!

      It (MySQL Connector C++) just seems to be problematic and poorly maintained piece of code all round.

      Maybe this guy can help?:
      http://pixelstech.net/article/1338576740_Using_MySQL_Connector_C%2B%2B_with_Visual_Studio_2010

      Best of luck with it and sorry I couldn’t be of more help.

      Reply

Leave a Reply