Using a MySQL Database with C++

How to Access MySQL Stored Functions from a C++ Program

Sep 23, 2008

Access a MySQL database using C++ code, Mark Alexander Bain

C++ and MySQL are both very powerful, but when combined they can make a killer application.

One of the most powerful combinations that any programmer can use is the combination of C++ and MySQL - a flexible programming language with a multi-platform and stable database; but this may seem an intimidating task to the new software developer.

It's not. This article will show just how easy it is for a programmer to use C++ to:

set up a connection to a MySQL database
use the C++ code to access an MySQL stored function
display the results returned by the MySQL stored function
and (perhaps most importantly) handle any errors

Setting up Test Data in a MySQL Database

Before a programmer can use a database that database must, of course, exist; or, at very least, a test database must exist. Fortunately creating a database in MySQL is very simple and consists of three steps:

log on to MySQL
use SQL to create the MySQL database and any tables
populate the tables with appropriate data

The first step (logging on to MySQL) can be done from the command line:

mysql -u<user> -p<password> mysql

Next, simple SQL can be used to the database and tables for the database:

create database cpp_data;

use cpp_data;

create table users(id int, fname varchar(25), sname varchar(25), active bool);

insert into users values (1, 'Fred', 'Smith', True);

insert into users values (2, 'Jane', 'Jones', True);

With this done, it's time to start thinking about doing some actual programming.

Creating a Stored Procedure in a MySQL Database

One of the new additions to MySQL is one that Oracle users will already know - the stored function. The great advantage to using stored functions is that programming code can be built into the database rather than into an application - meaning that multiple applications can use the same piece of code:

delimiter //

create function user_count () returns int

deterministic

begin

declare c int;

select count(*) into c from users where active = True;

return c;

end

delimiter ;

This code simply returns the number of active users (from the table users).

Loading the MySQL Header File into C++

When using MySQL with C++ the programmer needs to know absolutely nothing about the actual mechanics of the process - all the programmer has to do is to load the MySQL header file:

#include <iostream>

#include <mysql.h>

using namespace std;

MYSQL *connection, mysql;

MYSQL_RES *result;

MYSQL_ROW row;

int query_state;

int main() {

return 0;

}

C++ Code for Connecting to a Database

This example code above will compile and run, but doesn't actually do anything - first the C++ code must make a connection to the MySQL database:

mysql_init(&mysql);

//connection = mysql_real_connect(&mysql,"host","user","password","database",0,0,0);

connection = mysql_real_connect(&mysql,"localhost","bainm","not_telling","cpp_data",0,0,0);

if (connection == NULL) {

cout << mysql_error(&mysql) << endl;

return 1;

}

The above code:

initialises the MySQL connection
makes the connection to the MySQL database (for which the programmer needs to define the host, user name, password and database)
displays an error message if the connection is rejected for any reason

C++ Code for Running a Query on a MySQL Database

Having made a successful connection to the MySQL database the C++ code may be used to send s SQL query - in this case to run the stored procedure created earlier:

query_state = mysql_query(connection, "select user_count()");

if (query_state !=0) {

cout << mysql_error(connection) << endl;

return 1;

}

This time the C++ code sends the SQL and then displays another error message if any problem is encountered.

C++ Code for Processing the Results of a MySQL Query

If the connection is successful and the query returns a result (otherwise known as a recordset) then the next step is to display those results:

result = mysql_store_result(connection);

while ( ( row = mysql_fetch_row(result)) != NULL ) {

cout << row[0] << endl;

}

C++ Code for Disconnecting from a MySQL Database

The final step is to free up any memory used by the recordset and to close the connection:

mysql_free_result(result);

mysql_close(connection);

Compiling and Running the C++ Code

How the code is compiled will depend on the operating system being used and the local set up - in the case of Debian Linux the code would be compiled by using the command:

g++ -o db db.cc -L/usr/include/mysql -lmysqlclient -I/usr/include/mysql

Assuming, of course, that the code is stored in a file named db.cc.

Conclusion

Both the MySQL database and the C++ programming language are powerful tools in their own right; and combined they are an incredibly important tool for the software developer - an important tool and one which is very easy to use, and very, very effective.

Thank you for a very straight-to-the-point tutorial! I have no desire to create a Win32 application or use wrapper libraries. I want just the functions I need to open a connection, run a query, and close a connection.

Windows users note that you need to include <my_global.h> before <mysql.h> in order to compile without errors.

So the code will need to be:
#include <iostream>
#include <my_global.h>
#include <mysql.h>

my_global.h includes some windows related header files such as windows.h. You need to do this even if you aren't making a Win32 project. My experience is compiling under Microsoft Visual C++ 2008 Express Edition on Vista. The program compiled after I included my_global.h.

For further information, see:
http://dev.mysql.com/doc/refman/5.1/en/windows-client-compiling.html

Feb 22, 2009 9:34 PM

Guest :

Great guide.

Working well for me under ubuntu 8.10

Apr 29, 2009 6:01 AM

Guest :

Bonjour
I thank you for this valuable tuto it works very well on xubuntu.
Merci beaucoup

Aug 16, 2009 7:03 AM

Guest :

This is a really good example!!! Thanks
This help me to figure out why Qt class was not working.
Just a simple and effective example
I ussed it with Debian Lenny and MySQL 5 and compiled in Qt using g++
Big Thanks

4 Comments

Using a MySQL Database with C++

How to Access MySQL Stored Functions from a C++ Program

Setting up Test Data in a MySQL Database

Creating a Stored Procedure in a MySQL Database

Loading the MySQL Header File into C++

C++ Code for Connecting to a Database

C++ Code for Running a Query on a MySQL Database

C++ Code for Processing the Results of a MySQL Query

C++ Code for Disconnecting from a MySQL Database

Compiling and Running the C++ Code

Conclusion

Further Reading