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

http://www.atlasindia.com/sql.htm

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 -p -u user mysql
&nbsp or create new user from root login
mysql -u root -p
GRANT ALL ON cpp_data TO 'user1'@'localhost' IDENTIFIED BY 'password';
exit
mysql -p -u user cpp_data
Next, simple SQL can be used to the database and tables for the database:

Read more: "Using a MySQL Database with C++: How to Access MySQL Stored Functions from a C++ Program"

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 ;
select user_count ();
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 &lt iostream &gt
#include &lt mysql.h &gt
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);
//mysql_real_connect(&mysql,"localhost","User","Password","databaseName",0,0,0);
connection = mysql_real_connect(&mysql,"localhost","user1","password","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

Read more: "Using a MySQL Database with C++: How to Access MySQL Stored Functions from a C++ Program" - http://c-programming.suite101.com/article.cfm/using_a_mysql_databases_with_c#ixzz0DVt7ShLb&A