Hot File

Accessing: MySQL Database using MySQL C API

View: 2446    Dowload: 2   Comment: 0   Post by: naruto  
Author: none   Category: PostgreSQL   Fields: Other

9 point/6 review File has been tested

Download   accessing-mysql-database.rar (75.56 KB)

You need to Sign In to download the file accessing-mysql-database.rar
If you do not have an account then Sign up for free here

Download error   Report copyright violation

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.

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

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

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:

<strong>DataBase<MySQL> mySQLdb; 
DataBase<PostgreSQL> postgreSQLdb; </strong>

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; 
 
}; // 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

#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:

MYSQL *mysql_init(MYSQL *mysql)

(http://dev.mysql.com/doc/refman/5.0/en/mysql-init.html)

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 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:

#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;  
}

Accessing: MySQL Database using MySQL C API

Accessing: MySQL Database using MySQL C API Posted on 15-05-2014  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. 1.5/10 2446

Comment:

To comment you must be logged in members.

Files with category

  • Accessing: MySQL Database using MySQL C API

    View: 2447    Download: 2   Comment: 0   Author: none  

    Accessing: MySQL Database using MySQL C API

    Category: PostgreSQL
    Fields: Other

    0.75/6 review
    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.

  • Full Database Abstraction Layer Generator

    View: 2533    Download: 0   Comment: 0   Author: none  

    Full Database Abstraction Layer Generator

    Category: PostgreSQL
    Fields: Other

    0/23 review
    This article presents yet another Data Abstraction Layer generator (popular topic). The DAL structure is directly inspired from the DotNetNuke, DAL document and the SQL generation is done using SQLpp. The generator takes care of pretty much...

  • Code .NET 2.0; Build Java; Run Linux

    View: 2043    Download: 0   Comment: 0   Author: none  

    Code .NET 2.0; Build Java; Run Linux

    Category: PostgreSQL
    Fields: Other

    1.5/3 review
    This article is in the Product Showcase section for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers. This is a showcase review for...

  • Postgres Database Backup/Restore From C#

    View: 2951    Download: 2   Comment: 0   Author: none  

    Postgres Database Backup/Restore From C#

    Category: PostgreSQL
    Fields: Other

    0/10 review
    This article provides Postgres database backup/restore functionality. By using this application we can check whether a Postgres database is installed or not in a system. If a Postgres database is installed then the Postgres databases list is...

  • Installing Redmine on Windows in production

    View: 5443    Download: 0   Comment: 0   Author: none  

    Installing Redmine on Windows in production

    Category: PostgreSQL
    Fields: Other

    0/10 review
    This is a very simple step by step guide to install Redmine on Windows server in production. This guide is applicable to IIS 7+ based servers, which include Windows Server 2008, 2008 R2, Windows Vista and Windows 7. Latter two should not be used for...

  • A SQL Management Console for MSSQL 2000 & 2005, MySQL 5.0,...

    View: 3273    Download: 2   Comment: 0   Author: none  

    A SQL Management Console for MSSQL 2000 & 2005, MySQL 5.0,...

    Category: PostgreSQL
    Fields: Other

    0.5625/8 review
    Since MSSQL is not holding the lion share of the market yet, integration/interaction with other RDBMS like PL/Oracle, MySQL, FireBird, DB2, is inevitable. With the class System.Data.Common in ADO .NET 2.0, different data providers can write the...

  • Creating an Interactive Map in ASP.NET 2.0 Using SharpMap

    View: 4377    Download: 5   Comment: 0   Author: none  

    Creating an Interactive Map in ASP.NET 2.0 Using SharpMap

    Category: PostgreSQL
    Fields: Other

    0/2 review
    Showing maps on the web is becoming more and more common. The most well-known ones are Google Maps and Microsoft's Virtual Earth. Creating your own websites with interactive maps require you to get hold on the map-data and convert this data to...

  • DevShot - Database Snapshots for Developers

    View: 956    Download: 0   Comment: 0   Author: none  

    DevShot - Database Snapshots for Developers

    Category: PostgreSQL
    Fields: Other

    0/2 review
    A few weeks back, I got an idea of writing a simple tool for switching database states between coding cycles. I thought this would be a good tool for anyone who is working on database related projects and have to switch between database states since...

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd