Introduction to C++ API for mySQL

Introduction to C++ API for mySQL

Murat Balaban wrote this article.

We will try to make a brief
introduction to the `complex` C++ API for Mysql on FreeBSD. We are going to explain the
installation procedure and introduce the API in light of three samples…Recent version of
this document can be found on ; http://www.enderunix.org/documents/eng/mysql++.html
.

Installation for mySQL++-1.7

Let me state in the very beginning that, mysql++ assumes you have mysql
client libraries installed. If not, go and install them first. If you have a standard
mysql-version-client.tgz/ports installation, you don’t need to worry about this, they are
all in place…

We are going to install our API from FreeBSD ports collection:

[root@pathfinder examples]# cd /usr/ports/databases/mysql++
[root@pathfinder mysql++]# make install

FreeBSD will download the source for the API from one of the predefined sites, will
compile and install it all for us. Installation will copy the header files to
/usr/local/include , libsqlplus.{a|so|so.1} libraries to /usr/local/lib directory.
However, the port in FreeBSD seems somehow broken. Two header files, which are quite
necessary for clear compilation are missing. In fact, they are not copied to their usual
places, so we need to copy them manually:

cd /usr/ports/databases/mysql++
cp work/mysql++-1.7/sqlplusint/define_short /usr/local/include/
cp work/mysql++-1.7/sqlplusint/defs /usr/local/include/

Sample 1: create_table.cpp

Now that we have completed the installation, we can start accessing and
playing with mysql via our c++ codes. Now let’s have a look at our sample code.  (You
can grab this example source code along with others from http://www.enderunix.org/documents/mysql++-samples.tgz
):

Figure #1 ( create_table.cpp )

//----------------------------starts here---------------------------
#include <iostream>
#include <sqlplus.hh> // We need to supply this header file.
#define HOST "localhost" // so, where's your mysql server?
#define DB "enderunix" // and database name?
#define USERNAME "root" // a user granted access to the above database?
#define PASSWORD "" // enter the password for the above user. 
                    // If there's no password, leave it as it is...

int main () { // Here we go...
Connection connection (use_exceptions); // create an object `connection`
                                        // from the Connection class.

// Our API can handle exceptions, so let's utilize this

try { // All the way main() is nothing but a try block
  connection.connect("", HOST, USERNAME, PASSWORD); // connecting....

try { // we try to select database, if some exception is
      // returned, we'll catch it and create the database.
  connection.select_db(DB); // select database

    } catch (BadQuery er ) { // if returned an exception, catch it
    connection.create_db(DB); // so, no database? create it first then.
    connection.select_db(DB);
    }

// an object from Query class which 
// is in fact bound to connection object.
Query query = connection.query(); 
    // That is the query. see the overloaded << operator. We can do many 
    // things with it.
    query << "CREATE TABLE fihrist (id INT not null auto_increment, "
    << "name TEXT not null , surname TEXT not null , phone "
    << "TEXT not null , email TEXT not null , web "
    << "TEXT not null , PRIMARY KEY (id), INDEX (id), UNIQUE (id))";
    try {

    query.execute(); // execute it!
    } catch (BadQuery er) { // catch the exception
    cerr << "Error: " << er.error << endl; // Print the error 
    return -1;
    }

    } catch (BadQuery er) { // Print the error on the screen
    cerr << "Error: " << er.error << endl;
    return -1;
    }
    return 0;
    }
    //----------------------ends here----------------------
    

Ok, but how to compile?:

[root@pathfinder examples]# c++ -D_FIX_FOR_BSD_ -I/usr/local/include/mysql
-L/usr/local/lib
-lsqlplus create_table.cpp -o create_table
/usr/local/lib/mysql/libmysqlclient.so.6: warning: tempnam() possibly used unsafely;
consider using mkstemp()
[root@pathfinder examples]#

Here, via -I flag, we expand our PATH to /usr/local/include/mysql where mysql header
files are located, and via -L flag, we specify the location of the library, link
-lsqlplus. There comes a binary called create_table. And, when we run:

[root@pathfinder examples]# ./create_table 
[root@pathfinder examples]#

We have created our database and table without any error, but let’s check:

[root@pathfinder examples]# mysql 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 33 to server version: 3.22.32
Type 'help' for help.
mysql> show databases; 
+-----------+
| Database  | 
+-----------+ 
| enderunix | 
| murat     |
| mysql     |
| test      |
+-----------+
4 rows in set (0.01 sec) 
mysql> use enderunix; 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 
Database changed mysql> describe fihrist;
+---------+---------+------+-----+---------+----------------+ 
| Field   | Type    | Null | Key | Default | Extra          | 
+---------+---------+------+-----+---------+----------------+
| id      |int(11)  |      | PRI | 0       | auto_increment | 
| name    | text    |      |     | NULL    |                |              
|surname  | text    |      |     | NULL    |                | 
| phone   | text    |      |     | NULL    |                |
| email   | text    |      |     | NULL    |                |
| web     | text    |      |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+ 
6 rows in set (0.01 sec) 
mysql>

As you can see, our database and table have been created successfully. Lets write some
code to insert data to the table.

Sample #2: insert_data.cpp

Figure #2 ( insert_data.cpp )

//----------------starts here-------------------------------------------
#include <iostream>
#include <sqlplus.hh>
#include <string>
#define HOST "localhost" // so, where's your mysql server?
#define DB "enderunix" // and database name?
#define USERNAME "root" // a user granted access to the above database?
#define PASSWORD "" // enter the password for the above user. 
                   // If there's no password, leave it as it is...
   

int main () {

struct Person { // perhaps useless, but just for future development.
  int id;
  string name;
  string surname;
  string phone;
  string email;
  string web;
  };

    
Person person;

cout << "Please enter name\n"; // we get the information from the user.
cin >> person.name;
cout << "Please enter surname\n";
cin >> person.surname;
cout << "Please enter phone number\n";
cin >> person.phone;
cout << "Please enter email address\n";
cin >> person.email;
cout << "Please enter web address\n";
cin >> person.web;

    Connection connection (use_exceptions);

    try {

    connection.connect("", HOST, USERNAME, PASSWORD);
    connection.select_db(DB);
    Query query = connection.query();
    //Difference from the first sample: none but the query itself.

    query << "INSERT INTO fihrist " << "(id, name, surname, phone,"
    << " email, web) VALUES (\"\",\"" << person.name << "\", \"" 
    << person.surname << "\", \"" << person.phone
    << "\", \"" << person.email << "\", \"" << person.web << "\" )";

     try {
     query.execute();
     } catch (BadQuery er) {
     cerr << "Error: " << er.error << endl;
     return -1;
    }
    } catch (BadQuery er) {
    cerr << "Error: " << er.error << endl;
    return -1;
    }
    return 0;
}
//----------------ends here-----------------------------------------

compile:

[root@pathfinder examples]# c++ -D_FIX_FOR_BSD_ -I/usr/local/include/mysql
-L/usr/local/lib -lsqlplus insert_data.cpp -o insert_data
/usr/local/lib/mysql/libmysqlclient.so.6: warning: tempnam() possibly
used unsafely; consider using mkstemp()
[root@pathfinder examples]#

run:

[root@pathfinder examples]# ./insert_data
Please enter name
A
Please enter surname
Black
Please enter phone number
66557
Please enter email address
murat@example.org
Please enter web address
http://example.org/~b/
[root@pathfinder examples]# ./insert_data
Please enter name
B
Please enter surname
White
Please enter phone number
45511
Please enter email address
ismail@example.org
Please enter web address
http://example.org/~b/
[root@pathfinder examples]#

All information is successfully inserted to mysql. Lets verify:

[root@pathfinder examples]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36 to server version: 3.22.32
Type 'help' for help.
mysql> use enderunix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from fihrist;
+----+------+---------+-------+----------------+------------------------+
| id | name | surname | phone | email          | web                    |
+----+------+---------+-------+----------------+------------------------+
| 1  | A    | Black   | 66557 | a@example.org  | http://example.org/~b  |
| 2  | B    | White   | 45511 | b@example.org  | http://example.org/~a/ |
+----+------+---------+-------+----------------+------------------------+
  
2 rows in set (0.00 sec)
mysql>

As we can see, the data is in the table. Now, lets make some more fun, and write code
to query some given information:

Sample #3: select_data.cpp

Figure #3 ( select_data.cpp )

//---------------------------starts here--------------------------------
#include <iostream>
#include <sqlplus.hh>
#include <iomanip>
#include <string>
#define HOST "localhost" // so, where's your mysql server?
#define DB "enderunix" // and database name?
#define USERNAME "root" // a user granted access to the above database?
#define PASSWORD "" // enter the password for the above user. If 
                   // there's no password, leave it as it is...

int main () {   

struct Person {
 int id;
 string name;
 string surname;
 string phone;
 string email;
 string web;
};
 Person person;
cout << "Please enter name\n"; // get the name to bu queried.
cin >> person.name;

Connection connection (use_exceptions);
    try {
    connection.connect("", HOST, USERNAME, PASSWORD);
    connection.select_db(DB);
    Query query = connection.query();
    // Querymiz yapiliyor:
    query << "SELECT * FROM fihrist WHERE name = \"" << person.name
    << "\"";
    
    try {

    Result result = query.store(); // query.store() executes query 
    // and stores it. and we create a result object of Result class
    // which is bound to query object.
    Row row; // this is for row[""]
    Result::iterator i;
    int count = 0;
    for ( i = result.begin(); i != result.end() ; i++ ) { 
    // loop till the end of result.
    row = *i;
    cout << "\nRecord #" << ++count << "\tID: " <<
    row["id"] << endl;
    cout.setf(ios::left);
    cout << setw(10) << "Name" << row["name"] << "\n"
    << setw(10) << "Surname" << row["surname"] << "\n"
    << setw(10) << "E-Mail" << row["email"] <<  "\n"
    << setw(10) << "Phone" << row["phone"] <<   "\n"
    << setw(10) << "Web" << row["web"] <<  "\n";
    }
    cout << "\nTotally, " << result.size() << " records listed.\n\n";
    } catch (BadQuery er) {
    cerr << "Error: " << er.error << endl;
    return -1;
    }
    } catch (BadQuery er) {
    cerr << "Error: " << er.error << endl;
    return -1;
    }
    catch (BadConversion er) {
    cerr << "Error: Tried to convert \"" << er.data <<  "\" to a \""
    << er.type_name << "\"." << endl;
    return -1;
    }
    return 0;
    }
//-----------------------ends here--------------------------------

Compile and run:

[root@pathfinder examples]# c++ -D_FIX_FOR_BSD_ -I/usr/local/include/mysql 
-L/usr/local/lib -lsqlplus select_data.cpp -o select_data
/usr/local/lib/mysql/libmysqlclient.so.6: warning: tempnam() possibly 
used unsafely; consider using mkstemp()
[root@pathfinder examples]#
[root@pathfinder examples]# ./select_data
Please enter name
murat
  
Record #1 ID: 1
Name A
Surname White
E-Mail a@example.org
Phone 66557
Web http://example.org/~a/
 

Totally, 1 records listed.
[root@pathfinder examples]#

That’s it. Good Luck:)

References

• mySQL manual – http://www.mysql.com/documentation/index.html
• mySQL++ manual –http://www.mysql.com/documentation/mysql++/index.html