Object-oriented Database Access in C++ (MySQL & mSQL)

13.2.1. The Database Connection

Database access in any environment starts with the connection. As you saw in the first two examples, MySQL and mSQL have two different ways of representing the same concept -- a connection to the database. We will start our object-oriented library by abstracting on that concept and creating a Connection object. A Connection object should be able to establish a connection to the server, select the appropriate database, send queries, and return results. Example 13-1 is the header file that declares the interface for the Connection object.

Example 13-1. The Connection Class Header

#ifndef l_connection_h
#define l_connection_h

#include <sys/time.h>

#if defined(HAS_MSQL)
#include <msql.h>
#elif defined(HAS_MYSQL)
#include <mysql.h>
#endif

#include "result.h"

class Connection {
private:
    int affected_rows;
#if defined(HAS_MSQL)    
    int connection;
#elif defined(HAS_MYSQL)
    MYSQL mysql;
    MYSQL *connection;
#else
    #error No database defined.
#endif

public:
    Connection(char *, char *);
    Connection(char *, char *, char *, char *);
    ~Connection();
    
    void Close();
    void Connect(char *host, char *db, char *uid, char *pw);
    int GetAffectedRows();
    char *GetError();
    int IsConnected();
    Result *Query(char *);
};

#endif // l_connection_h

The methods the Connection class will expose to the world are uniform no matter which database engine you use. Underneath the covers, however, the class will have private data members specific to the library you compile it against. For making a connection, the only distinct data members are those that represent a database connection. As we noted earlier, mSQL uses an int to represent a connection and MySQL uses a MYSQL pointer with an additional MYSQL value to handle establishing the connection.

13.2.1.1. Connecting to the database

Any applications we write against this API now need only to create a new Connection instance using one of the associated constructors in order to connect to the database. Similarly, an application can disconnect by deleting the Connection instance. It can even reuse a Connection instance by making direct calls to Close() and Connect(). Example 13-1 shows the implementation for the constructors and the Connect() method.

Example 13-1. Connecting to MySQL and mSQL Inside the Connection Class

#include "connection.h"

Connection::Connection(char *host, char *db) {
#if defined(HAS_MSQL)
    connection = -1;
#elif defined(HAS_MYSQL)
    connection = (MYSQL *)NULL;
#else
    #error No database linked.
#endif    
    Connect(host, db, (char *)NULL, (char *)NULL);
}

Connection::Connection(char *host, char *db, char *uid, char *pw) {
#if defined(HAS_MSQL)
    connection = -1;
#elif defined(HAS_MYSQL)
    connection = (MYSQL *)NULL;
#else
    #error No database linked.
#endif    
    Connect(host, db, uid, pw);
}

void Connection::Connect(char *host, char *db, char *uid, char *pw) {
    int state;

    if( IsConnected() ) {
        throw "Connection has already been established.";
    }
#if defined(HAS_MSQL)
    connection = msqlConnect(host);
    state = msqlSelectDB(connection, db);
#elif defined (HAS_MYSQL)
    mysql_init(&mysql);
    connection = mysql_real_connect(&mysql, host, 
                                    uid, pw,
                                    db, 0, 0);
#else
    #error No database linked.
#endif  
    if( !IsConnected() ) {
        throw GetError();
    }
    if( state < 0 ) {
        throw GetError();
    }
}

The two constructors are clearly designed to support the different parameters required by MySQL and mSQL connections. The API, nevertheless, should allow for both constructors to work against either database. The API accomplishes this by ignoring the user ID and password when an application using a mSQL calls the 4-argument constructor. Similarly, null values are passed to MySQL for the user ID and password when the 2-argument constructor is called. The actual database connectivity occurs in the Connect() method.

The Connect() method encapsulates all steps required for a connection. For MySQL, it calls mysql_real_connect() . For mSQL, it instead calls msqlConnect() followed by msqlSelectDB() . If either step fails, Connect() throws an exception.

13.2.1.2. Disconnecting from the database

A Connection's other logic function is to disconnect from the database and free up the resources it has hidden from the application. This functionality occurs in the Close() method. Example 13-1 provides all of the functionality for disconnecting from MySQL and mSQL.

Example 13-1. Freeing up Database Resources

Connection::~Connection() {
    if( IsConnected() ) {
        Close();
    }
}

void Connection::Close() {
    if( !IsConnected() ) {
        return;
    }
#if defined(HAS_MSQL)    
    msqlClose(connection);
    connection = -1;
#elif defined(HAS_MYSQL)    
    mysql_close(connection);
    connection = (MYSQL *)NULL;
#else
    #error No database linked.
#endif    
}

The mysql_close() and msqlClose() methods respectively free up the resources associated with connections to MySQL and mSQL.

13.2.1.3. Making Calls to the database

In between opening a connection and closing it, you generally want to send statements to the database. The Connection class accomplishes this via a Query() method that takes a SQL statement as an argument. If the statement was a query, it returns an instance of the Result class from the object model in Figure 13-1. If, on the other hand, the statement was an update, the method will return NULL and set the affected_rows value to the number of rows affected by the update. Example 13-1 shows how the Connection class handles queries against MySQL and mSQL databases.

Example 13-1. Querying the Database

Result *Connection::Query(char *sql) {
    T_RESULT *res;
    int state;

    // if not connectioned, there is nothing we can do
    if( !IsConnected() ) {
        throw "Not connected.";
    }
    // execute the query
#if defined(HAS_MSQL)    
    state = msqlQuery(connection, sql);
#elif defined(HAS_MYSQL)    
    state = mysql_query(connection, sql);
#else
    #error No database linked.
#endif
    // an error occurred
    if( state < 0 ) {
        throw GetError();
    }
    // grab the result, if there was any
#if defined(HAS_MSQL)
    res = msqlStoreResult();
#elif defined(HAS_MYSQL)
    res = mysql_store_result(connection);
#else
    #error No database linked.
#endif
    // if the result was null, it was an update or an error occurred
    // NOTE: mSQL does not throw errors on msqlStoreResult()
    if( res == (T_RESULT *)NULL ) {
        // just set affected_rows to the return value from msqlQuery()
#if defined(HAS_MSQL)
        affected_rows = state;
#elif defined(HAS_MYSQL)
        // field_count != 0 means an error occurred
        int field_count = mysql_num_fields(connection);

        if( field_count != 0 ) {
            throw GetError();
        }
        else {
            // store the affected_rows
            affected_rows = mysql_affected_rows(connection);
        }
#else
        #error No database linked.
#endif
        // return NULL for updates
        return (Result *)NULL;
    }
    // return a Result instance for queries
    return new Result(res);
}

The first part of a making-a-database call is calling either mysql_query() or msqlQuery() with the SQL to be executed. Both APIs return a nonzero on error. The next step is to call mysql_store_result() or msqlStoreResult() to check if results were generated and make those results usable by your application. At this point, the two database engines differ a bit on the processing details.

Under the mSQL API, msqlStoreResult() will not generate an error. This function is used by an application to move a newly generated result set into storage to be managed by the application instead of by the mSQL API. In other words, when you call msqlQuery(), it stores any results in a temporary area in memory managed by the API. Any subsequent call to msqlQuery() will wipe out that storage area. In order to store that result in an area of memory managed by your application, you need to call msqlStoreResult().

Because msqlStoreResult() does not generate an error, you need to worry about only two possibilities when you make an msqlStoreResult() call. If the call to the database was a query that generated a result set, msqlStoreResult() returns a pointer to an m_result structure to be managed by the application. For any other kind of call (an update, insert, delete, or create), msqlStoreResult() returns NULL. You can then find out how many rows were affected by a non-query through the return value from the original msqlQuery() call.

Like the msqlStoreResult() call, mysql_store_result() is used to place the results generated by a query into storage managed by the application. Unlike the mSQL version, you need to wrapper mysql_store_result() with some exception handling. Specifically, a NULL return value from mysql_store_result() can mean either the call was a nonquery or an error occurred in storing the results. A call to mysql_num_fields() will tell you which is in fact the case. A field count not equal to zero means an error occurred. The number of affected rows, on the other hand, may be determined by a call to mysql_affected_rows() .[21]

[21]One particular situation behaves differently. MySQL is optimized for cases where you delete all records in a table. This optimization incorrectly causes some versions of MySQL to return for a mysql_affected_rows() call.

13.2.1.4. Other Connection behaviors

Throughout the Connection class are calls to two support methods, IsConnected() and GetError() . Testing for connection status is simple -- you just check the value of the connection attribute. It should be non-NULL for MySQL and something other than -1 for mSQL. Error messages, on the other hand, require some explanation.

Retrieving error messages under mSQL is very simple and straightforward. You just use the value of the msqlErrMsg global variable. This value is exactly what our GetError() method returns for mSQL. MySQL, however, is a little more complicated. Being multithreaded, it needs to provide threadsafe access to any error messages. It manages to make error handling work in a multithreaded environment by hiding error messages behind the mysql_error() function. Example 13-1 shows MySQL and mSQL error handling in the GetError() method as well as connection testing in IsConnected().

Example 13-1. Reading Errors and Other Support Tasks of the Connection Class

int Connection::GetAffectedRows() {
    return affected_rows;
}

char *Connection::GetError() {
#if defined(HAS_MSQL)
    return msqlErrMsg;
#elif defined(HAS_MYSQL)
    if( IsConnected() ) {
        return mysql_error(connection);
    }
    else {
        return mysql_error(&mysql);
    }
#else
    #error No database linked.
#endif
}

int Connection::IsConnected() {
#if defined(HAS_MSQL)
    return !(connection < 0);
#elif defined(HAS_MYSQL)
    return !(!connection);
#else
    #error No database linked.
#endif  
)

13.2. Object-oriented Database Access in C++

Figure 13-1. Object-oriented database access library

13.2.1. The Database Connection

Example 13-1. The Connection Class Header

13.2.1.1. Connecting to the database

Example 13-1. Connecting to MySQL and mSQL Inside the Connection Class

13.2.1.2. Disconnecting from the database

Example 13-1. Freeing up Database Resources

13.2.1.3. Making Calls to the database

Example 13-1. Querying the Database

13.2.1.4. Other Connection behaviors

Example 13-1. Reading Errors and Other Support Tasks of the Connection Class

13.2.2. Error Handling Issues

13.2.3. Result Sets

Example 13-1. The Interface for a Result Class in result.h

13.2.3.1. Navigating results

Example 13-1. Result Set Navigation

13.2.3.2. Cleaning up and row count

Example 13-1. Clean up and Row Count

13.2.4. Rows

Example 13-1. The Row Class from row.h

Example 13-2. The Implementation of the Row Class