The FreeBSD Diary

The FreeBSD Diary (TM)

Providing practical examples since 1998

If you buy from Amazon USA, please support us by using this link.
[ HOME | TOPICS | INDEX | WEB RESOURCES | BOOKS | CONTRIBUTE | SEARCH | FEEDBACK | FAQ | FORUMS ]

Things look quiet here. But I've been doing a lot of blogging at dan.langille.org because I prefer WordPress now. Not all my posts there are FreeBSD related. I am in the midst of migrating The FreeBSD Diary over to WordPress (and you can read about that here). Once the migration is completed, I'll move the FreeBSD posts into the new FreeBSD Diary website.

Introduction to C++ API for mySQL --- by Murat Balaban 25 March 2001
Need more help on this topic? Click here
This article has 4 comments
Show me similar articles
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

Share
Need more help on this topic? Click here
This article has 4 comments
Show me similar articles