Click here to Skip to main content
15,860,972 members
Articles / Database Development / MySQL

Accessing: MySQL Database using MySQL C API

Rate me:
Please Sign up or sign in to vote.
4.78/5 (22 votes)
1 Nov 2011CPOL6 min read 99.8K   7.2K   81   20
A set of class to access and manipulate MySQL database using MySQL C API

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:

  1. Windows
  2. Windows x64
  3. Linux (non RPM packages)
  4. Linux (non RPM, Intel C/C++ compiled, glibc-2.3)
  5. Red Hat Enterprise Linux 3 RPM (x86)
  6. Red Hat Enterprise Linux 3 RPM (AMD64 / Intel EM64T)
  7. Red Hat Enterprise Linux 3 RPM (Intel IA64)
  8. Red Hat Enterprise Linux 4 RPM (x86)
  9. Red Hat Enterprise Linux 4 RPM (AMD64 / Intel EM64T)
  10. Red Hat Enterprise Linux 4 RPM (Intel IA64)
  11. Red Hat Enterprise Linux 5 RPM (x86)
  12. Red Hat Enterprise Linux 5 RPM (AMD64 / Intel EM64T)
  13. SuSE Linux Enterprise Server 9 RPM (x86)
  14. SuSE Linux Enterprise Server 9 RPM (AMD64 / Intel EM64T)
  15. SuSE Linux Enterprise Server 9 RPM (Intel IA64)
  16. SuSE Linux Enterprise Server 10 RPM (x86)
  17. SuSE Linux Enterprise Server 10 RPM (AMD64 / Intel EM64T)
  18. SuSE Linux Enterprise Server 10 RPM (Intel IA64)
  19. Ubuntu 6.06 LTS (Dapper Drake)
  20. Linux x86 generic RPM (statically linked against glibc 2.2.5)
  21. Linux x86 generic RPM (dynamically linked)
  22. Linux AMD64 / Intel EM64T generic RPM
  23. Linux Intel IA64 generic RPM
  24. Solaris (pkgadd packages)
  25. Solaris (TAR packages)
  26. FreeBSD (TAR packages)
  27. Mac OS X (package format)
  28. Mac OS X (TAR packages)
  29. HP-UX (depot packages)
  30. HP-UX (TAR packages)
  31. IBM AIX
  32. IBM i5/OS (SAVF packages)
  33. IBM i5/OS (TAR packages)
  34. QNX
  35. Novell NetWare
  36. 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

C++
class DataBaseError : public std::exception 
/// DataBaseError class 
{ 
   public: 
      DataBaseError(const std::string& what) 
      : exception(what.c_str()) 
      { 
      } 
}; // DataBaseError

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

C++
template<class T> 
class DataBase 
/// DataBase class 
{ 
   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; 


}; // DataBase

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

C++
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; 
 
}; // ResultSet

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

C++
#include "Exception.h" 
#include "ResultSet.h" 
#include "DataBase.h" 

#include <mysql.h> 

class MySql 
    /// MySql data base class 
{ 
    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 of querying for all rows in table 

        // You must call mysql_store_result() or mysql_use_result() 
        // for every query that successfully retrieves data (SELECT, SHOW,
        // DESCRIBE, EXPLAIN). 
        result = mysql_use_result(_connectionHandlerPtr); 

        MYSQL_ROW row; 
        unsigned int num_fields; 
        unsigned int i; 
        num_fields = mysql_num_fields(result); 

        // get rows 
        while ((row = mysql_fetch_row(result))) 
        { 
            std::vector<std::string> myRow; 

            // get fields od row 
            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 structure represents a handle to one database connection.


}; // MySql

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:

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:

C++
#include "MySql.h" 
 
int main() 
{ 
    try 
   {  
      ResultSet rs1, rs2; 
      DataBase<MySql> dataBase; 
 
      dataBase.connect("205.XXX.XXX.XX", "user", "pass", "db"); 
 
     // CREATE FIRST TABLE 
     dataBase << "CREATE TABLE if not exists tblTest1(test char(15) NOT NULL,
         testInt INT NULL, Constraint PK Primary Key(test))"; 

     // INSERT SOME ITENS 
     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')" ; 
 
     // CREATE SECOND TABLE 
     dataBase << "CREATE TABLE if not exists tblTest2(test char(15) NOT NULL,
         dt DATE NULL, Constraint PK Primary Key(test))"; 
  
     // INSERT SOME ITENS 
     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')" ; 
 
     // GET (one column) SAME ITENS OF tblTest1 
     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; 
     } 
 
     // GET A SPECIFIC ITEN 
     std::cout << rs1.get(0)[0] << " | " << rs1.get(0)[1] << std::endl; 
 
     // GET (one column) SAME ITENS OF tblTest2 
     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; 
     } 
 
     // GET A SPECIFIC ITEN 
     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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Systems Engineer
Brazil Brazil
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questionwhat to install Pin
Member 1297031419-May-17 9:43
Member 1297031419-May-17 9:43 
Questiongood ecellent Pin
Member 82858587-Jan-14 23:34
Member 82858587-Jan-14 23:34 
GeneralGood work! Pin
true6431-Jul-13 23:04
true6431-Jul-13 23:04 
GeneralRe: Good work! Pin
Renato Tegon Forti17-Aug-13 13:20
Renato Tegon Forti17-Aug-13 13:20 
QuestionException help me :( Pin
marzeyeh31-Oct-11 21:18
marzeyeh31-Oct-11 21:18 
AnswerRe: Exception help me :( Pin
Renato Tegon Forti1-Nov-11 1:55
Renato Tegon Forti1-Nov-11 1:55 
GeneralRe: Exception help me :( Pin
marzeyeh1-Nov-11 1:59
marzeyeh1-Nov-11 1:59 
GeneralRe: Exception help me :( Pin
Renato Tegon Forti1-Nov-11 2:29
Renato Tegon Forti1-Nov-11 2:29 
GeneralRe: Exception help me :( Pin
marzeyeh1-Nov-11 19:57
marzeyeh1-Nov-11 19:57 
mnazaralipoor@gmail.com

tnx laot
Questionhow to keep the connection to mysql? Pin
gy_bison4-May-11 22:14
gy_bison4-May-11 22:14 
GeneralThanks a lot!!!!! Pin
agechamp19-Feb-11 15:14
agechamp19-Feb-11 15:14 
GeneralRe: Thanks a lot!!!!! Pin
Renato Tegon Forti19-Feb-11 15:19
Renato Tegon Forti19-Feb-11 15:19 
GeneralMy vote of 5 Pin
zhhxhero17-Feb-11 14:25
zhhxhero17-Feb-11 14:25 
GeneralExcellent Pin
loyal ginger2-Nov-09 10:35
loyal ginger2-Nov-09 10:35 
GeneralRe: Excellent Pin
Renato Tegon Forti2-Nov-09 11:10
Renato Tegon Forti2-Nov-09 11:10 
GeneralGood Pin
loyal ginger6-Oct-09 6:16
loyal ginger6-Oct-09 6:16 
GeneralRe: Good Pin
Renato Tegon Forti6-Oct-09 7:02
Renato Tegon Forti6-Oct-09 7:02 
GeneralThis seems to be an article about the C++ API not the C API Pin
paulmarley6-Apr-09 14:21
paulmarley6-Apr-09 14:21 
GeneralRe: This seems to be an article about the C++ API not the C API Pin
Renato Tegon Forti7-Apr-09 6:23
Renato Tegon Forti7-Apr-09 6:23 
GeneralRe: This seems to be an article about the C++ API not the C API Pin
Homero De la Garza16-Apr-09 6:26
Homero De la Garza16-Apr-09 6:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.