Using a MySQL Database with C++

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

Access a MySQL database using C++ code - Mark Alexander Bain
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:

  1. log on to MySQL
  2. use SQL to create the MySQL database and any tables
  3. 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.

Popular topics

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.

Further Reading

MySQL Stored Procedures and Functions

Mark Alexander Bain - Mark Alexander Bain is a writer, Mo Bro and consultant for all aspects of software development at dsquared. He has also written regularly ...

rss
Advertisement
11 Comments

Comments

Dec 20, 2008 6:00 PM
Guest :
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
Nov 25, 2009 3:55 PM
Guest :
An AWESOME tutorial! Thank you so much for the easy to understand walkthrough. One question though, I read your tutorial on writing functions in the mySQL database, how would I go about calling that function in a C++ app?
Dec 4, 2009 2:11 AM
Guest :
Thank you very much for this straight forward and well explained tutorial!
Jan 13, 2010 5:00 AM
Guest :
thank you very much...best article
Feb 17, 2010 7:18 PM
Guest :
Nice Tutorial,
I was wondering is it possible to keep Running Query on SQL server.
Your example cover just one basic step One query and One result
is there any way to make it more Interactive
Feb 22, 2010 3:37 PM
Guest :
Very nice introduction. Short and sweet and to the point :)

Many thanks!
Apr 26, 2010 12:43 AM
Guest :
You saved my day!
First on windows I had some troube, with the mysql_com.h.
But include WinSock.h before mysql.h will solve the problem.
The my_global.h in the MySQL Server include folder can solve the problems too on a windows system.

I use it on Win7, MySql server 5.1 and MS VC++ 2010 Express

Thanks Again!
Jul 13, 2010 1:06 PM
Guest :
A brilliant article that got me a working mysql app in les than 5 minutes altogether. When it comes down to it, the designers of mysql.h made it pretty much child's play to setup a connection get the data and display the result.
11 Comments
Advertisement

Popular topics

more in c programming »

Related Topics

Advertisement