1. Audience
This article was written for beginners, developers that do not know how to access MySql database using MySQL C API and would like to have a small introduction.
2. Introduction
Today MySQL database is the world's most popular open source database because of its fast performance, high reliability, ease of use, and dramatic cost savings.
It is very popular in UNIX hosting packages available in all world, then in this article, I will show how you can access this very powerful relational database system.
We will encapsulate the provided MySQL C API in a set of C++ classes, and in the next article, I will use these same classes to do the same thing in PostgreSQL.
Well, by now let’s start with MySQL.
3. The Available APIs and Libraries
The MySql Database provides a rich set of APIs and Libraries to access and manipulating data and database, see:
The important thing to note is that the C API is the most extensively covered and documented, because it was developed by the MySQL team, and we will use it to make our set of C++ classes.
4. Get MySQL
Recently MySQL Labs was acquired by Sun Microsystems, then the easy way to get it and to obtain the last news is go to:
or:
MySQL is available on many platforms, when I wrote this article the version 5 was available to:
- Windows
- Windows x64
- Linux (non RPM packages)
- Linux (non RPM, Intel C/C++ compiled, glibc-2.3)
- Red Hat Enterprise Linux 3 RPM (x86)
- Red Hat Enterprise Linux 3 RPM (AMD64 / Intel EM64T)
- Red Hat Enterprise Linux 3 RPM (Intel IA64)
- Red Hat Enterprise Linux 4 RPM (x86)
- Red Hat Enterprise Linux 4 RPM (AMD64 / Intel EM64T)
- Red Hat Enterprise Linux 4 RPM (Intel IA64)
- Red Hat Enterprise Linux 5 RPM (x86)
- Red Hat Enterprise Linux 5 RPM (AMD64 / Intel EM64T)
- SuSE Linux Enterprise Server 9 RPM (x86)
- SuSE Linux Enterprise Server 9 RPM (AMD64 / Intel EM64T)
- SuSE Linux Enterprise Server 9 RPM (Intel IA64)
- SuSE Linux Enterprise Server 10 RPM (x86)
- SuSE Linux Enterprise Server 10 RPM (AMD64 / Intel EM64T)
- SuSE Linux Enterprise Server 10 RPM (Intel IA64)
- Ubuntu 6.06 LTS (Dapper Drake)
- Linux x86 generic RPM (statically linked against glibc 2.2.5)
- Linux x86 generic RPM (dynamically linked)
- Linux AMD64 / Intel EM64T generic RPM
- Linux Intel IA64 generic RPM
- Solaris (pkgadd packages)
- Solaris (TAR packages)
- FreeBSD (TAR packages)
- Mac OS X (package format)
- Mac OS X (TAR packages)
- HP-UX (depot packages)
- HP-UX (TAR packages)
- IBM AIX
- IBM i5/OS (SAVF packages)
- IBM i5/OS (TAR packages)
- QNX
- Novell NetWare
- SCO OpenServer 6
To get the updated list and direct download, please go to http://dev.mysql.com/downloads/mysql/5.0.html#downloads
Attention: When you will install the MySQL in your preferred platform, it is very important that you install the developer components (C Include Files / Lib Files) that include needed link libs and includes files, to follow this tutorial.
5. C++ Interface
The interface set is composed of 4 classes as follows:
| Physical file
| Class
|
1
| Exception.h
| DataBaseError
|
2
| DataBase.h
| DataBase
|
3
| ResultSet.h
| ResultSet
|
4
| MySql.h
| MySql
|
5.1 DataBaseError Class
class DataBaseError : public std::exception
{
public:
DataBaseError(const std::string& what)
: exception(what.c_str())
{
}
};
This simple class is used when we need throw an error in your interface, it inherits from ‘std::exception’ and takes advantage of your provided error handler.
5.2 DataBase
template<class T>
class DataBase
{
public:
DataBase()
: _connected(false)
{
}
virtual ~DataBase()
{
if(_connected)
_dataBaseEngine.close();
}
void connect(const std::string& server, const std::string& user,
const std::string& password, const std::string& database)
{
_dataBaseEngine.connect(server, user, password, database);
_connected = true;
}
DataBase& operator<< (const std::string& sql)
{
_dataBaseEngine.execute(sql);
return *this;
}
DataBase& operator, (ResultSet& rs)
{
_dataBaseEngine.populate(rs);
return *this;
}
private:
T _dataBaseEngine;
bool _connected;
};
This class is implemented with strategy pattern with template implementation, whereby algorithms can be selected at compile time, and then we can use it like this:
DataBase<MySQL> mySQLdb;
DataBase<PostgreSQL> postgreSQLdb;
It overloads the operators: ‘<<’ and ‘,’, the first is used to send SQL command to database, and the second is used if the first command is a SELECT that will generate one response in the ‘ResultSet’.
5.3 ResultSet
class ResultSet
{
public:
ResultSet()
: _current(0)
{
}
void addRow(const std::vector<std::string>& row)
{
_resultSet.push_back(row);
}
bool fetch(size_t field, std::string& fieldValue)
{
size_t sz = _resultSet.size();
if(sz)
{
if(sz > _current)
{
fieldValue = _resultSet[_current++][field];
return true;
}
}
_current = 0;
return false;
}
bool fetch(std::vector<std::string>& rowValue)
{
size_t sz = _resultSet.size();
if(sz)
{
if(sz > _current)
{
rowValue = _resultSet[_current++];
return true;
}
}
_current = 0;
return false;
}
std::string get(size_t row, size_t field)
{
return _resultSet[row][field];
}
std::vector<std::string> get(size_t row)
{
return _resultSet[row];
}
size_t countRows(void)
{
if (_resultSet.empty()) return 0;
return _resultSet.size();
}
size_t countFields(void)
{
if (_resultSet[0].empty()) return 0;
return _resultSet[0].size();
}
private:
std::vector<std::vector<std::string> > _resultSet;
size_t _current;
};
This receives result of an SELECT command when it is sent to database.
To maintain things simple, this class has the same bad points that can need change, these points are:
ResultSet only handle strings. ResultSet puts all data in one std::vector, it does not fetch row by row, this can be a problem for large results.
5.4 MySql
#include "Exception.h"
#include "ResultSet.h"
#include "DataBase.h"
#include <mysql.h>
class MySql
{
friend class DataBase<MySql>;
public:
MySql()
{
}
virtual ~MySql()
{
}
void connect(const std::string& server, const std::string& user,
const std::string& password, const std::string& database)
{
_connectionHandlerPtr = mysql_init(NULL);
if (NULL == mysql_real_connect(_connectionHandlerPtr, server.c_str(),
user.c_str(), password.c_str(), database.c_str(), 0, NULL, 0))
{
throw DataBaseError("Failed to connect to database: Error: " +
std::string(mysql_error(_connectionHandlerPtr)));
}
}
void execute(const std::string& sql)
{
std::cout << sql << std::endl;
if(!(mysql_query(_connectionHandlerPtr, sql.c_str()) == 0) )
{
throw DataBaseError("Failed to execute sql: Error: " +
std::string(mysql_error(_connectionHandlerPtr)));
}
}
void populate(ResultSet& rs)
{
MYSQL_RES *result = NULL;
result = mysql_use_result(_connectionHandlerPtr);
MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;
num_fields = mysql_num_fields(result);
while ((row = mysql_fetch_row(result)))
{
std::vector<std::string> myRow;
for(i = 0; i < num_fields; i++)
{
if (row[i] == NULL)
{
myRow.push_back("NULL"); }
}
else
{
myRow.push_back(row[i]);
}
}
rs.addRow(myRow);
}
mysql_free_result(result);
}
protected:
void close(void)
{
mysql_close(_connectionHandlerPtr);
}
private:
MYSQL* _connectionHandlerPtr;
};
This class is MySQL implementation, in it that we use C API to manipulate/access data.
I will start be: MYSQL* _connectionHandlerPtr; this is handle to database connection and it is used for almost all MySQL functions, remember that you should not try to make a copy of a MYSQL structure.
In void connect(...), we use these API functions:
The first allocates/initializes a MYSQL object for subsequent call of mysql_real_connect(...) that attempts to establish a connection to a MySQL database engine.
In case of insufficient memory, NULL is returned from <code><code>mysql_init(...);
If the connection was unsuccessful, NULL is returned from mysql_real_connect(...);
And to get error description, we use mysql_error(...) that returns a null-terminated string containing the error message for the most recently invoked API function that failed.
In execute(..), we use these API functions:
-
int mysql_query(MYSQL *mysql, const char *stmt_str)
(http://dev.mysql.com/doc/refman/5.0/en/mysql-query.html)
mysql_query(...) executes the SQL statement pointed to by the null-terminated string and returns zero if the statement was successful and non-zero if an error occurred.
In populate(...) that use these API functions:
The mysql_use_result(...) must be called for every query that successfully retrieves data (SELECT, SHOW, DESCRIBE, EXPLAIN). If error occurs, NULL is returned else MYSQL_RES structure.
mysql_num_fields(...) returns the number of columns in a result set.
mysql_fetch_row(...) retrieves the next row of a result set.
mysql_free_result (...) frees the memory allocated.
Finally we have close() that uses:
That closes a previously opened connection and deallocates the connection handle pointed to by mysql if the handle was allocated automatically by mysql_init() or mysql_connect().
Well, this is all, we use 10 functions of MySQL C API to build this set of classes that provide simple access/manipulation to MySQL database.
6. Using Interface
Here I will show how to use our set of classes, see:
#include "MySql.h"
int main()
{
try
{
ResultSet rs1, rs2;
DataBase<MySql> dataBase;
dataBase.connect("205.XXX.XXX.XX", "user", "pass", "db");
dataBase << "CREATE TABLE if not exists tblTest1(test char(15) NOT NULL,
testInt INT NULL, Constraint PK Primary Key(test))";
dataBase << "INSERT INTO tblTest1(test, testInt) VALUES('00', 1)" ;
dataBase << "INSERT INTO tblTest1(test) VALUES('01')" ;
dataBase << "INSERT INTO tblTest1(test) VALUES('02')" ;
dataBase << "INSERT INTO tblTest1(test) VALUES('03')" ;
dataBase << "INSERT INTO tblTest1(test) VALUES('04')" ;
dataBase << "INSERT INTO tblTest1(test) VALUES('05')" ;
dataBase << "INSERT INTO tblTest1(test) VALUES('06')" ;
dataBase << "INSERT INTO tblTest1(test) VALUES('07')" ;
dataBase << "INSERT INTO tblTest1(test) VALUES('08')" ;
dataBase << "INSERT INTO tblTest1(test) VALUES('09')" ;
dataBase << "CREATE TABLE if not exists tblTest2(test char(15) NOT NULL,
dt DATE NULL, Constraint PK Primary Key(test))";
dataBase << "INSERT INTO tblTest2(test, dt) VALUES('01', '1979/11/14')" ;
dataBase << "INSERT INTO tblTest2(test) VALUES('02')" ;
dataBase << "INSERT INTO tblTest2(test) VALUES('03')" ;
dataBase << "INSERT INTO tblTest2(test) VALUES('04')" ;
dataBase << "INSERT INTO tblTest2(test) VALUES('05')" ;
dataBase << "INSERT INTO tblTest2(test) VALUES('06')" ;
dataBase << "INSERT INTO tblTest2(test) VALUES('07')" ;
dataBase << "INSERT INTO tblTest2(test) VALUES('08')" ;
dataBase << "INSERT INTO tblTest2(test) VALUES('09')" ;
dataBase << "INSERT INTO tblTest2(test) VALUES('10')" ;
dataBase << "SELECT * FROM tblTest1, tblTest2 WHERE tblTest1.test = tblTest2.test",
rs1;
std::string value;
std::vector<std::string> row;
while(rs1.fetch(0, value))
{
std::cout << value << std::endl;
}
while(rs1.fetch(row))
{
for (size_t i = 0; i < row.size(); i++)
{
std::cout << row[i] << " | ";
}
std::cout << std::endl;
}
std::cout << rs1.get(0)[0] << " | " << rs1.get(0)[1] << std::endl;
dataBase << "SELECT * FROM tblTest2 WHERE test = '01'", rs2;
while(rs2.fetch(1, value))
{
std::cout << value << std::endl;
}
while(rs2.fetch(row))
{
for (size_t i = 0; i < row.size(); i++)
{
std::cout << row[i] << " | ";
}
std::cout << std::endl;
}
std::cout << rs2.get(0, 1) << std::endl;
}
catch (const DataBaseError& e)
{
std::cout << e.what() << std::endl;
}
return 0;
}
7. Conclusion
Today MySQL is powerful open source relational database alternative, it provides fast performance, high reliability, ease of use, cost savings and many access API to many languages is widely available and easy to use.
Bibliography
C++ Books (list)
The Web links listed here may not be valid in the future.
