20.5 Mysql PROGRAMMING: INVOKING SQL THROUGH C | Programming with Objects: A Comparative Presentation of Object Oriented Programming with C++ and Java

20.5 Mysql++ PROGRAMMING: INVOKING SQL THROUGH C++

The open source Mysql++ is a C++-based programming interface for communicating with a MySQL database.^[06] Just like JDBC, Mysql++ can communicate your command to a database and can retrieve, analyze, and display the results.

Basic to Mysql++ programming are the Connection, Query, and Result classes. They play the same roles in Mysql++ that Connection, Statement, and ResultSet classes play in JDBC. The Connection class gives you a connection with a database. Its constructor can be invoked directly with the database name as the sole argument, as in

      Connection con("myDatabase");

where "myDatabase" is the name of a MySQL database on the local machine and if the database user name is the same as the login name. For accessing a database on a different machine or when the database user name is not the same as the login name and if a password is required for accessing the database, you'd need to invoke the Connection constructor with additional arguments. The prototype of the constructor is

      Connection(cchar* db, cchar* host="",                     cchar* user="", cchar* passwd="");

Another way to establish a connection with a database is to first carry out a partial construction of a Connection object with the use_exceptions option turned on and to then separately establish a connection with the designated database:

      Connection con(use_exceptions);      con.connect("myDatabase");

There is also available a 9-argument constructor for Connection that allows specification of a port, a socket, connection timeout, and so on. When a connection is established with the two-call invocation shown above, the connect method can also be invoked with up to four arguments, the additional arguments allowing specification of a hostname, a user name, and password.

Invoking the function query on a Connection object returns a Query object that can then be used to communicate SQL statements to the database:

      Connection con(….);      Query query = con.query();

A Query object behaves much like an output stream object in C++, such as cout. You can use the insertion operator ‘<<' to insert strings in a Query object. These strings will usually be SQL statements. How you get those SQL statements to execute depends upon whether or not their execution is supposed to return something. When an SQL statement fed into a Query object is not supposed to return anything-such as when inserting a new row into a table-the inserted statement can be executed by invoking the function execute on the Query object, as in

      Query query = ….      query << … SQL statement …      query.execute();

If it is desired to reset the state of the Query object, the last call can also be of the form that includes the flag RESET_QUERY:

      query.execute(RESET_QUERY);

On the other hand, when the SQL statement is supposed to return a result set, you need to invoke store, which causes execution of the SQL statement and retrieval of a result-set object of type Result:

      Query query = ….      query << … SQL SELECT statement …      Result res = query.store();

A Result object is an STL-like container, supporting a random-access read-only iterator and array-like indexing. Each element stored in a Result object corresponds to a row of the result set and is again an STL-like container that also supports array-like indexing. Both the Result object and the rows stored therein as elements can be accessed via iterators. Since both the row elements of a result set and the elements inside each row can be accessed by array-like indexing, the result set can be thought of as a two-dimensional table directly addressable by a pair of indices, as in

      Result res = query.store();      cout << res[2] [5];

which would display the datum in the column indexed 5 of the result set row indexed 2.^[7]

The example below, DBFriends1.cc, is a C++ version of the Java example DBFriends1.java shown earlier. The reader should note the parallels between the two. Note how, in the C++ version, we first construct an iterator to the result set. As we step through the allowed values for the iterator, we gain access to each row of the result set. We determine the number of column entries in each row, and then we invoke a loop to print all the entries out. Before printing out the contents of the result set, we also print out the column headings for the columns in the result set by invoking the names function on the Result object, as in

      Result res = query.store();      …      cout << res.names(j);      …

for displaying the name of the jth column.

 
 //DBFriends1.cc #include <iostream> #include <sqlplus.hh> #include <iomanip> int main() {      try {           Connection con(use_exceptions); con.connect("test"); Query query = con.query(); query << "SET AUTOCOMMIT=1"; query.execute(); query << "DROP TABLE IF EXISTS Friends"; query.execute(); query << "DROP TABLE IF EXISTS Rovers"; query.execute(); // Friends table: query << "CREATE TABLE Friends (Name CHAR (30)"      << "PRIMARY KEY, Phone INT, Email CHAR(30))"; query.execute(); query << "INSERT INTO Friends VALUES ('Ziggy Zaphod',"      << "4569876, 'ziggy@sirius')"; query.execute(); query << "INSERT INTO Friends VALUES ('Yo Yo Ma',"      << "3472828, 'yoyo@yippy')"; query.execute(); query << "INSERT INTO Friends VALUES ('Gogo Gaga',"      << "27278927, 'gogo@garish')"; query.execute(); // Rovers table: query << "CREATE TABLE Rovers (Name CHAR (30) NOT NULL," << "RovingTime CHAR(10))"; query.execute(); query << "INSERT INTO Rovers VALUES ('Dusty Dodo', '2 pm')"; query.execute(); query << "INSERT INTO Rovers VALUES ('Yo Yo Ma', '8 pm')"; query.execute(); query << "INSERT INTO Rovers VALUES ('BeBe Beaut', '6 pm')"; query.execute(); query << "SELECT Friends.Name, Rovers.RovingTime"      << "FROM Friends, Rovers WHERE Friends.Name=Rovers.Name"; // The result set: Result res = query.store(); cout << "Query:" << query.preview() << endl; cout << "Records Found:" << res.size() << endl << endl; Row row_rs; cout.setf(ios::left); Result::iterator i; for (i = res.begin(); i != res.end(); i++) {      row_rs = *i;      int numFields = row_rs.size();      if (i == res.begin()) {           for (int j = 0; j < numFields; j++)                cout << setw(17) << res.names(j) << "\t\t";           cout << endl << endl;         }         for (int j = 0; j < numFields; j++)              cout << setw(17) << row_rs[ j ] << "\t";         cout << endl;      } } catch (BadQuery& er) {      cerr << "Query Error:" << er.error << endl;      return -1; } catch(BadConversion& er) {      cerr << "Conversion Error: Tried to convert \1""           << er.data <<      "\" to a \""           << er.type_name           << "\"." << endl;      return -1;    } }

This program can be compiled by the following command line that can be conveniently placed in a shell script:

      g++ -o DBFriends1 DBFriends1.cc -I/usr/include/mysql \                                -lsqlplus -Wl, --rpath -Wl,/usr/local/lib

This assumes a standard installation of Mysql++ on a Linux machine. The output of the program is the same as for the Java example DBFriends1.java.

Our next example, DBFriends2.cc, parallels the JDBC example DBFriends2.java. This example, like the earlier Java example, creates two database tables by reading in the data from two flat files and then executes a more complex query compared to our previous Mysql++ example. The result set returned by the query is displayed in a manner that is exactly the same as before.

 
 //DBFriends2.cc #include <iostream> #include <sqlplus.hh> #include <iomanip> int main() {      try {           Connection con(use_exceptions);           con.connect("test");           Query query = con.query(); query << "SET AUTOCOMMIT=1"; query.execute(); query << "DROP TABLE IF EXISTS Friends"; query.execute(); query << "DROP TABLE IF EXISTS SportsClub"; query.execute(); query << "CREATE TABLE Friends (Name CHAR (30) PRIMARY KEY,"      << "Phone CHAR (15), Email CHAR(30),"      << "Age TINYINT (3), Married BOOL,"      << "NumKids TINYINT (3), Sport CHAR(20))"; query.execute(); query << "CREATE TABLE SportsClub (Name CHAR (30) PRIMARY KEY,"      << "Age TINYINT (3), Sport CHAR(20),"      << "Level Char(20))"; query.execute(); query << "LOAD DATA LOCAL INFILE 'Friends.txt' "      << "INTO TABLE Friends"; query.execute(); query << "LOAD DATA LOCAL INFILE 'SportsClub.txt' INTO"      << "TABLE SportsClub"; query.execute(); // which of the Friends also play tennis at the club: query << "SELECT Friends.Name,      SportsClub.Level FROM Friends,"      << "SportsClub WHERE"      << "Friends.Name = SportsClub.Name AND"      << "Friends.Sport = SportsClub.Sport AND"      << "Friends.Sport = 'tennis"'; Result res = query.store(); cout << "Query:" << query.preview() << endl; cout << "Records Found:" << res.size() << endl << endl; Row row_rs; cout.setf(ios::left); Result::iterator i; for (i = res.begin(); i != res.end(); i++) {      row_rs = *i;      int numFields = row_rs.size();      if (i == res.begin()) {           for (int j = 0; j < numFields; j++)           cout << setw(17) << res.names(j) << "\t\t";           cout << endl << endl;      }      for (int j = 0; j < numFields; j++)                cout << setw(17)           << row_rs[ j ] <<      "\t";           cout << endl;      }    } catch (BadQuery& er) {        cerr << "Query Error:" << er.error << endl;        return -1;    } catch(BadConversion& er) {        cerr << "Conversion Error: Tried to convert \""           << er.data << "\" to a \""           << er.type_name << "\"." << endl;       return -1;     } }

This program can be compiled by the following command line that can be conveniently placed in a shell script:

      g++ -o DBFriends2 DBFriends2.cc -I/usr/include/mysql \                     -lsqlplus -Wl,--rpath -Wl,/usr/local/lib

As before, this assumes a standard installation of Mysql++ on a Linux machine. The output of the program is the same as for the Java example DBFriends2.java.

^[06]Its future releases are expected to work with SQL databases in general. Mysql++ is available from http://www.mysql.com/download+mysql++.html.

^[7]There are two different kinds of result sets in Mysql++: dramatic result set and static result set. The two-dimensional indexing shown can only be used for the former type.