A class to use MYSQL

<After installing and setting up the MySql server ,we need to access the MySql database from code::blocks>

To achieve the objective I'm going to make a class to connect and launch SQL queries to the MySql server , and of course a test project to use the class.

So the steps to get started are:

1. Open code::blocks
2. Create an empty project
3. Add a file and save it "main.cpp"
4. Add a new class with the wizard and name it "csql"

Now we edit the header for class csql (csql.h):

#ifndef CSQL_H
#define CSQL_H
#include <mysql.h>//Needed to use MySql
#include <string.h>
#include <sstream>

using namespace std;

class csql
{
    public:
        MYSQL_ROW row; //Here we store query results in rows
        csql(); //constructor
        virtual ~csql();//destructor
        bool connect(const char * serverip, const char * username, const char * password, const char * database);//connecto to a server , with username and password and select database
        void executequery(const char *query);//execute SQL query
        bool getrow();//we get a row from the results from a query

    private:
        MYSQL mysql;//mysql class
        MYSQL_RES *res ;//mysql results for queries
};

#endif // CSQL_H

Basically there are 5 functions: the creator and destructor, a function to connect to the mysql server, a function to execute queries and finally a function to get the result from SELECT queries.

Now the implementation (csql.cpp):

#include "csql.h"

using namespace std;

csql::csql()
{
    //ctor
        mysql_init(&mysql);//start mysql
}

csql::~csql()
{
    //dtor
        mysql_close(&mysql);//close mysql
}

//We use connect() to connect to the database needed
bool csql::connect( const char * serverip, const char * username, const char * password, const char * database)
{
    if (mysql_real_connect(&mysql
  ,serverip
  ,username
  ,password
  ,database,0,NULL,0))
    {

        return true;//everything ok
    }else{
        return false;//connection failed
    }
}

void csql::executequery(const char *query)//Execute SQL query
{
mysql_real_query(&mysql,query,strlen(query));
res = mysql_store_result(&mysql);
}

bool csql::getrow()//We extract a row from the result from an SQL query (or get false if empty)
{
    if ((row = mysql_fetch_row(res)))
    {
        return true;//we have extracted a row
    }else{
        return false; // no rows to extract
    }
}

It is quite simple to follow the code:

• csql() (constructor) just initializes the mysql class used to work with the DB
•  ~csql() (destructor) frees the class, closing any active connection
• connect() connect to the server, using 4 parameters: the server IP, the username, the password and the database to connect (it returns true if the it connects ok, false when there is a failure)
• executequery() used to launch SQL queries, just plain SQL commands like "select * from table"
• getrow() get a data row from the results of a select query, if there are rows it will return true and we can access the data columns with csql_instance.row[index], other way it returns false

And finally, to use the class I wrote a pair of examples (main.cpp):

#include <iostream>
#include "csql.h"

using namespace std;

int main(int argc, char *argv[])
{

csql sql;//we use the sql class

cout << "Connecting to database.." << endl;

if (!sql.connect("localhost","root","rpgdata","rpgdata"))//we connect using the serverip, username, password and database
{
    cout << "Error connecting to database" << endl;
    return -1;
}

cout << "DELETING.." << endl;

sql.executequery("delete from accounts");//executing delete query

sql.executequery("select * from accounts");//executing select query
while (sql.getrow())
{
    cout << sql.row[0] << " " << sql.row[1] << " " << sql.row[2] << " " << sql.row[3] << " " << sql.row[4] << endl;
}

cout << "INSERTING Jorge.." << endl;

sql.executequery("insert into accounts values (NULL, 'Jorge','jorge@hotmail.com','pass',NULL)");//executing insert query

sql.executequery("select * from accounts");//executing select query
while (sql.getrow())
{
    cout << sql.row[0] << " " << sql.row[1] << " " << sql.row[2] << " " << sql.row[3] << " " << sql.row[4] << endl;
}

cout << "INSERTING Paul.." << endl;

sql.executequery("insert into accounts values (NULL, 'Paul','paul@gmail.com','pass',NULL)");//executing insert query

sql.executequery("select * from accounts");//executing select query
while (sql.getrow())
{
    cout << sql.row[0] << " " << sql.row[1] << " " << sql.row[2] << " " << sql.row[3] << " " << sql.row[4] << endl;
}

    return 0;
}

I make an instance from the mysql class, then I connect to the server, clean the table "accounts", and then I insert a pair of data rows, I display the results from select queries too, there are other SQL commands like UPDATE which I have not used, but I think that it is quite enough to show the use from the class.

<Notice that I am using the password "rpgdata", the database "rpgdata", the table "accounts".. if you don't use the same database or table names, the example wont work(I explained how to set it up in the previous two posts)>
 
Finally there is one final thing you need to set up to make the project compile:

• Go to "project" > "build options"

• Then in "compiler" > "other options"

`mysql_config --cflags`

• And in "linker" > "other linker options"

`mysql_config --libs`

Other way the compiler wont find mysql and it will refuse to work...

<So basically we have a simple class to use the freshly new installed MySql server, now we can use it in any project, but obviously I'm going to add it to the socket server, in the next post I will show another new I'm going to use to host the project development...>

See you!