home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  

Book HomeMySQL and mSQLSearch this book

Chapter 13. C and C++

In this book, we examine several different programming languages, Python, Java, Perl, and C. Of these languages, C/C++ is by far the most challenging. With the other languages, your primary concern is the formulation of SQL, the passing of that SQL to a function call, and the manipulation of the resulting data. C adds the very complex issue of memory management into the mix.

Both MySQL and mSQL provide C libraries that enable the creation of MySQL and mSQL database applications. In fact, MySQL derives its API very heavily from mSQL, meaning that experience programming against one API translates well to the next. As we explored in the first section, however, MySQL is much more feature-rich than mSQL. These extra features naturally result in a few differences between the two APIs. In this chapter, we will examine these differences while coming to understand the details of each API by building an object-oriented C++ API that can be conditionally compiled to run with either API.

13.1. The Two APIs

Whether you are using C or C++, the MySQL and mSQL C APIs are your gateway into the database. How you use them, however, can be very different depending on whether you are using C or the object-oriented features of C++. C database programming must be attacked in a linear fashion, where you step through your application process to understand where the database calls are made and where clean up needs to occur. Object-oriented C++, on the other hand, requires an OO interface into the API of your choice. The objects of that API can then take on some of the responsibility for database resource management.

Table 13-1 shows the function calls of each API side by side. We will go into the details of how these functions are used later in the chapter. Right now, you should just take a minute to see how the two APIs compare and note what is available to you. Naturally, the reference section lists each of these methods with detailed prototype information, return values, and descriptions.

Table 13-1. The C APIs for MySQL and mSQL




See msqlQuery()


















































The MySQL API is much larger than the mSQL API in order to account for MySQL's extended feature set. In many cases, MySQL is actually only providing an API interface into database administration functions that are present in both database engines. By just reading the function names, you might have gathered that any database application you write might minimally look something like this:

  1. Connect

  2. Select DB

  3. Query

  4. Fetch row

  5. Fetch field

  6. Close

Example 13-1 shows a simple select statement that retrieves data from a MySQL database using the MySQL C API.

Example 13-1. A Simple Program that Selects All Data in a Test Database and Displays the Data

#include <sys/time.h>
#include <stdio.h>
#include <mysql.h>

int main(char **args) {
    MYSQL_RES *result;
    MYSQL_ROW row;
    MYSQL *connection, mysql;
    int state;
     /* connect to the mySQL database at athens.imaginary.com */
    connection = mysql_real_connect(&mysql,
                                    0, 0,
                                    "db_test", 0, 0);
    /* check for a connection error */
    if( connection == NULL ) {
        /* print the error message */
        return 1;
    state = mysql_query(connection, 
                        "SELECT test_id, test_val FROM test");
    if( state != 0 ) {
        return 1;
    /* must call mysql_store_result() before we can issue any
     * other query calls
    result = mysql_store_result(connection);
    printf("Rows: %d\n", mysql_num_rows(result));
    /* process each row in the result set */
    while( ( row = mysql_fetch_row(result)) != NULL ) {
        printf("id: %s, val: %s\n", 
               (row[0] ? row[0] : "NULL"), 
               (row[1] ? row[1] : "NULL"));
    /* free the result set */
    /* close the connection */

Of the #include files, both mysql.h and stdio.h should be obvious to you. The mysql.h header contains the prototypes and variables required for MySQL, and stdio.h the prototype for printf(). The sys/time.h header, on the other hand, is not actually used by this application. It is instead required by the mysql.h header as the MySQL file uses definitions from sys/time.h without actually including it. To compile this program using the GNU C compiler, use the command line:

gcc -L/usr/local/mysql/lib -I/usr/local/mysql/include -o select select.c\ -lmysql -lnsl -lsocket

You should of course substitute the directory where you have MySQL installed for /usr/local/mysql in the preceding code.

The main() function follows the steps we outlined earlier -- it connects to the server, selects a database, issues a query, processes the result sets, and cleans up the resources it used. We will cover each of these steps in detail as the chapter progresses. For now, you should just take the time to read the code and get a feel for what it is doing. In addition, compare it to the same program written for mSQL shown in Example 13-2.[18]

[18]MySQL comes with a utility called msql2mysql which ostensibly converts any application written against the mSQL API to the MySQL API. It does provide a start at converting mSQL applications, but it leaves a bit of work to be done since MySQL requires extra arguments to some functions.

The two programs are nearly identical. Other than name differences, there are only a few really dramatic distinguishing features. The most striking difference is the database connection. There are two main qualities of this difference:

As we discussed earlier in the book, MySQL supports a complex level of user authentication with user name and password combinations. mSQL, on the other hand, has a simple authentication scheme based on the user ID of the process connecting to the database. The more robust MySQL scheme is much more desirable in a client/server environment, but it is also much harder to manage as an administrator. For application developers, the result is the need to pass a user name and password to the mysql_real_connect() call when using MySQL in addition to the basic server name used in mSQL.

The first argument of the connection API for MySQL is peculiar at first inspection. It is basically a way to track all calls not otherwise associated with a connection. For example, if you try to connect and the attempt fails, you need to get the error message associated with that failure. The MySQL mysql_error() function, however, requires a pointer to a valid MySQL connection. The null connection you allocate early on provides that connection. You must, however, have a valid reference to that value for the lifetime of your application -- an issue of great importance in more structured environment than a straight "connect, query, close" application. The C++ examples later in the chapter will shed more light on this issue.

The other two major API distinctions lie in the way error handling is done and result set counting is done. The mSQL API creates a global variable that stores error messages. Because MySQL is multithreaded, such a global error variable would not function for its API. It, therefore, uses the mysql_error() function to retrieve error messages associated with the last error raised for the specified connection.

The connection API and error handling are two places where MySQL differs from mSQL in order to provide functionality not found in mSQL. Result set counting is done differently in mSQL in order to provide a better interface than MySQL provides. Specifically, when you send SQL to msqlQuery(), the number of affected rows is returned as the return value (or -1 if an error occurred). Counting affected rows for updates versus rows in a result set for queries thus uses the same paradigm. In MySQL, however, you have to use different paradigms for dealing with queries than with result sets. For queries, you pass the result set to mysql_num_rows() to get the number of rows in the result set. Updates, on the other hand, require you to call another API, mysql_affected_rows(). Where msqlQuery() provides the number of rows matched by the WHERE clause in an update, mysql_affected_rows() actually reports the number of changed rows. As a final note, mSQL does provide a msqlNumRows() method that provides the same interface for result set counting that MySQL provides. It does not provide a counterpart to mysql_affected_rows().

Library Navigation Links

Copyright © 2001 O'Reilly & Associates. All rights reserved.