All of the examples are complete running programs. If you built the library from source, the examples should have been built as well. If you use RPMs instead, the example programs’ source code and a simplified Makefile are in the mysql++-devel package. They are typically installed in /usr/share/doc/mysql++-devel-*/examples, but it can vary on different Linuxes.

Before you get started, please read through any of the README* files included with the MySQL++ distribution that are relevant to your platform. We won’t repeat all of that here.

Most of the examples require a test database, created by resetdb. You can run it like so:

resetdb [-s server_addr] [-u user] [-p password]

Actually, there’s a problem with that. It assumes that the MySQL++ library is already installed in a directory that the operating system’s dynamic linker can find. (MySQL++ is almost never built statically.) Unless you’re installing from RPMs, you’ve had to build the library from source, and you should run at least a few of the examples before installing the library to be sure it’s working correctly. Since your operating system’s dynamic linkage system can’t find the MySQL++ libraries without help until they’re installed, we’ve created a few helper scripts to help run the examples.

MySQL++ comes with the exrun shell script for Unixy systems, and the exrun.bat batch file for Windows. You pass the example program and its arguments to the exrun helper, which sets up the library search path so that it will find the as-yet uninstalled version of the MySQL++ library first. So on a Unixy system, the above command becomes:

./exrun resetdb [-s server_addr] [-u user] [-p password]

See README.examples for more details.

All of the program arguments are optional.

If you don’t give -s, the underlying MySQL C API assumes the server is on the local machine. Depending on how the C API library and the server are configured, it can use any of several different IPC methods to contact the server. You can instead specify how to contact the server yourself, with the method depending on the value you give for the server address:

You can mix symbolic host and service names in any combination. If the name doesn’t contain a colon, it uses the default port, 3306.

If you don’t give -u, it assumes your user name on the local machine is the same as your user name on the database server.

If you don’t give -p, it will assume the MySQL user doesn’t have a password, which had better not be the case. It’s a wild world out there; play safe, kids.

A typical invocation is:

exrun.bat resetdb -u mydbuser -p nunyabinness

For resetdb, the user name needs to be for an account with permission to create databases. Once the database is created, you can use any account that has read and write permissions for the sample database, mysql_cpp_data.

You may also have to re-run resetdb after running some of the other examples, as they change the database.

The following example demonstrates how to open a connection, execute a simple query, and display the results. This is examples/simple1.cpp:

#include "cmdline.h"
#include "printdata.h"

#include <mysql++.h>

#include <iostream>
#include <iomanip>

using namespace std;

int
main(int argc, char *argv[])
{
    // Get database access parameters from command line
    const char* db = 0, *server = 0, *user = 0, *pass = "";
    if (!parse_command_line(argc, argv, &db, &server, &user, &pass)) {
        return 1;
    }

    // Connect to the sample database.
    mysqlpp::Connection conn(false);
    if (conn.connect(db, server, user, pass)) {
        // Retrieve a subset of the sample stock table set up by resetdb
        // and display it.
        mysqlpp::Query query = conn.query("select item from stock");
        if (mysqlpp::StoreQueryResult res = query.store()) {
            cout << "We have:" << endl;
            for (size_t i = 0; i < res.num_rows(); ++i) {
                cout << '\t' << res[i][0] << endl;
            }
        }
        else {
            cerr << "Failed to get item list: " << query.error() << endl;
            return 1;
        }

        return 0;
    }
    else {
        cerr << "DB connection failed: " << conn.error() << endl;
        return 1;
    }
}

This example simply gets the entire "item" column from the example table, and prints those values out.

Notice that MySQL++’s StoreQueryResult derives from std::vector, and Row provides an interface that makes it a vector work-alike. This means you can access elements with subscript notation, walk through them with iterators, run STL algorithms on them, etc.

Row provides a little more in this area than a plain old vector: you can also access fields by name using subscript notation.

The only thing that isn’t explicit in the code above is that we delegate command line argument parsing to parse_command_line() in the excommon module. This function exists to give the examples a consistent interface, not to hide important details. You can treat it like a black box: it takes argc and argv as inputs and sends back database connection parameters.

The simple1 example above was pretty trivial. Let’s get a little deeper. Here is examples/simple2.cpp:

#include "cmdline.h"
#include "printdata.h"

#include <mysql++.h>

#include <iostream>
#include <iomanip>

using namespace std;

int
main(int argc, char *argv[])
{
    // Get database access parameters from command line
    const char* db = 0, *server = 0, *user = 0, *pass = "";
    if (!parse_command_line(argc, argv, &db, &server, &user, &pass)) {
        return 1;
    }

    // Connect to the sample database.
    mysqlpp::Connection conn(false);
    if (conn.connect(db, server, user, pass)) {
        // Retrieve the sample stock table set up by resetdb
        mysqlpp::Query query = conn.query("select * from stock");
        mysqlpp::StoreQueryResult res = query.store();

        // Display results
        if (res) {
            // Display header
            cout.setf(ios::left);
            cout << setw(31) << "Item" <<
                    setw(10) << "Num" <<
                    setw(10) << "Weight" <<
                    setw(10) << "Price" <<
                    "Date" << endl << endl;

            // Get each row in result set, and print its contents
            for (size_t i = 0; i < res.num_rows(); ++i) {
                cout << setw(30) << res[i]["item"] << ' ' <<
                        setw(9) << res[i]["num"] << ' ' <<
                        setw(9) << res[i]["weight"] << ' ' <<
                        setw(9) << res[i]["price"] << ' ' <<
                        setw(9) << res[i]["sdate"] <<
                        endl;
            }
        }
        else {
            cerr << "Failed to get stock table: " << query.error() << endl;
            return 1;
        }

        return 0;
    }
    else {
        cerr << "DB connection failed: " << conn.error() << endl;
        return 1;
    }
}

The main point of this example is that we’re accessing fields in the row objects by name, instead of index. This is slower, but obviously clearer. We’re also printing out the entire table, not just one column.

You’ll notice above that we’re including mysql++.h in the examples. There are many headers in MySQL++, but this brings in all but one of them for you. MySQL++ has a pretty cohesive design: it doesn’t have very many pieces that are truly independent of the others. So, there’s not much advantage in including the few headers you think you need individually: you’re likely to also drag in all the rest indirectly.

The one header that mysql++.h doesn’t bring in for you is ssqls.h, which is only useful if you use the optional Specialized SQL Structures feature.

By default on Unixy systems, MySQL++ installs its headers into a mysql++ subdirectory of one of the main system include directories, either /usr/include or /usr/local/include. Since it’s typical for either or both of these directories to be in your program’s include path already, you might be wondering if you can include the main MySQL++ header like this:

#include <mysql++/mysql++.h>

The answer is, yes you can. You don’t need to do anything special to make it work.

Since MySQL is usually installed in much the same way (/usr/include/mysql is common, for example), you might then ask if you can get away without having the MySQL C API header directory to your program’s include path. You can, but mysql++.h requires a little help from your program to find the C API headers when you do this:

#define MYSQLPP_MYSQL_HEADERS_BURIED
#include <mysql++/mysql++.h>

This tells it to prefix all includes for C API headers with mysql/.

By default, MySQL++ uses exceptions to signal errors. Most of the examples have a full set of exception handlers. This is worthy of emulation.

All of MySQL++’s custom exceptions derive from a common base class, Exception. That in turn derives from Standard C++’s std::exception class. Since the library can indirectly cause exceptions to come from the Standard C++ Library, it’s possible to catch all exceptions from MySQL++ by just catching std::exception. However, it’s better to have individual catch blocks for each of the concrete exception types that you expect, and add a handler for either Exception or std::exception to act as a “catch-all” for unexpected exceptions.

Most of these exceptions are optional. When exceptions are disabled on a MySQL++ object, it signals errors in some other way, typically by returning an error code or setting an error flag. Classes that support this feature derive from OptionalExceptions. Moreover, when such an object creates another object that also derives from this interface, it passes on its exception flag. Since everything flows from the Connection object, disabling exceptions on it at the start of the program disables all optional exceptions. You can see this technique at work in the simple[1-3] examples, which keeps them, well, simple.

Real-world code typically can’t afford to lose out on the additional information and control offered by exceptions. But at the same time, it is still sometimes useful to disable exceptions temporarily. To do this, put the section of code that you want to not throw exceptions inside a block, and create a NoExceptions object at the top of that block. When created, it saves the exception flag of the OptionalExceptions derivative you pass to it, and then disables exceptions on it. When the NoExceptions object goes out of scope at the end of the block, it restores the exceptions flag to its previous state:

mysqlpp::Connection con(...); // exceptions enabled

{
  mysqlpp::NoExceptions ne(con);
  if (!con.select_db("a_db_that_might_not_exist_yet")) {
    // Our DB doesn't exist yet, so create and select it here; no need
    // to push handling of this case way off in an exception handler.
  }
}

When one OptionalExceptions derivative passes its exceptions flag to another such object, it is only passing a copy; the two objects’ flags operate independently. There’s no way to globally enable or disable this flag on existing objects in a single call. If you’re using the NoExceptions feature and you’re still seeing optional exceptions thrown, you disabled exceptions on the wrong object. The exception thrower could be unrelated to the object you disabled exceptions on, it could be its parent, or it could be a child created before you disabled optional exceptions.

MySQL++ throws some exceptions unconditionally:

It’s educational to modify the examples to force exceptions. For instance, misspell a field name, use an out-of-range index, or change a type to force a String conversion error.

SQL syntax often requires certain data to be quoted. Consider this query:

SELECT * FROM stock WHERE item = 'Hotdog Buns' 

Because the string “Hotdog Buns” contains a space, it must be quoted. With MySQL++, you don’t have to add these quote marks manually:

string s = "Hotdog Buns";
query << "SELECT * FROM stock WHERE item = " << quote_only << s; 

That code produces the same query string as in the previous example. We used the MySQL++ quote_only manipulator, which causes single quotes to be added around the next item inserted into the stream. This works for any type of data that can be converted to MySQL++’s SQLTypeAdapter type, plus the Set template. Specialized SQL Structures also use these manipulators internally.

Quoting is pretty simple, but SQL syntax also often requires that certain characters be “escaped”. Imagine if the string in the previous example was “Frank's Brand Hotdog Buns” instead. The resulting query would be:

SELECT * FROM stock WHERE item = 'Frank's Brand Hotdog Buns' 

That’s not valid SQL syntax. The correct syntax is:

SELECT * FROM stock WHERE item = 'Frank''s Brand Hotdog Buns' 

As you might expect, MySQL++ provides that feature, too, through its escape manipulator. But here, we want both quoting and escaping. That brings us to the most widely useful manipulator:

string s = "Frank's Brand Hotdog Buns";
query << "SELECT * FROM stock WHERE item = " << quote << s; 

The quote manipulator both quotes strings and escapes any characters that are special in SQL.

MySQL++ provides other manipulators as well. See the manip.h page in the reference manual.

It’s important to realize that MySQL++’s quoting and escaping mechanism is type-aware. Manipulators have no effect unless you insert the manipulator into a Query or SQLQueryParms stream. ^[1] Also, values are only quoted and/or escaped if they are of a data type that may need it. For example, Date must be quoted but never needs to be escaped, and integer types need neither quoting nor escaping. Manipulators are suggestions to the library, not commands: MySQL++ will ignore these suggestions if it knows it won’t result in syntactically-incorrect SQL.

It’s also important to realize that quoting and escaping in Query streams and template queries is never implicit.^[2] You must use manipulators and template query flags as necessary to tell MySQL++ where quoting and escaping is necessary. It would be nice if MySQL++ could do quoting and escaping implicitly based on data type, but this isn’t possible in all cases.^[3] Since MySQL++ can’t reliably guess when quoting and escaping is appropriate, and the programmer doesn’t need to^[4], MySQL++ makes you tell it.

The sql_types.h header declares typedefs for all MySQL column types. These typedefs all begin with sql_ and end with a lowercase version of the standard SQL type name. For instance, the MySQL++ typedef corresponding to TINYINT UNSIGNED is mysqlpp::sql_tinyint_unsigned. You do not have to use these typedefs; in this particular case, you might get away with using something as loose as int.

The most obivious reason to use these typedefs is clarity. Someone reading code that uses these typedefs can’t be confused about what the corresponding SQL type is.

There’s also a correctness aspect to using these typedefs. The definitions of these types have changed over time as new, improved types have become available in MySQL++. For a past example, sql_tinyint used to just be an alias for signed char, but when MySQL++ began treating char as a single-character string instead of an integer, we changed the sql_tinyint typedef to be an alias for mysqlpp::tiny_int<signed char>. Code using the type aliases changed over transparently, while code using what used to be the correct corresponding C++ type usually broke. This is likely to happen again in the future, too. One example that comes to mind is that sql_decimal is currently an alias for double, but SQL’s DECIMAL type is a fixed-point data type, while double is floating-point. Thus, you lose accuracy when converting DECIMAL column data to sql_decimal right now. In the future, we may add a fixed-point data type to MySQL++; if we do, we’ll certainly change the tyepdef alias to use it instead of double.

Most of these typedefs use standard C++ data types, but a few are aliases for a MySQL++ specific type. For instance, the SQL type DATETIME is mirrored in MySQL++ by mysqlpp::DateTime. For consistency, sql_types.h includes a typedef alias for DateTime called mysqlpp::sql_datetime.

There is no equivalent of SQL’s null in the standard C++ type system.

The primary distinction is one of type: in SQL, null is a column attribute, which affects whether that column can hold a SQL null. Just like the const keyword in the C++ type system, this effectively doubles the number of SQL data types. To emulate this, MySQL++ provides the Null template to allow the creation of distinct “nullable” versions of existing C++ types. So for example, if you have a TINYINT UNSIGNED column that can have nulls, the proper declaration for MySQL++ would be:

mysqlpp::Null<mysqlpp::sql_tinyint_unsigned> myfield;

Template instantiations are first-class types in the C++ language, on par with any other type. You can use Null template instantiations anywhere you’d use the plain version of that type. (You can see a complete list of Null template instantiations for all column types that MySQL understands at the top of lib/type_info.cpp.)

There’s a secondary distinction between SQL null and anything available in the standard C++ type system: SQL null is a distinct value, equal to nothing else. We can’t use C++’s NULL for this because it is ambiguous, being equal to 0 in integer context. MySQL++ provides the global null object, which you can assign to a Null template instance to make it equal to SQL null:

myfield = mysqlpp::null;

By default, MySQL++ enforces the uniqueness of SQL null at compile time. If you try to convert a SQL null to any other data type, the compiler will emit an error message saying something about CannotConvertNullToAnyOtherDataType. It’s safe to insert a SQL null into a C++ stream, though: you get “(NULL)”.

If you don’t like this behavior, you can change it by passing a different value for the second parameter to template Null. By default, this parameter is NullIsNull, meaning that we should enforce the uniqueness of SQL null. To relax this distinction, you can instantiate the Null template with a different behavior type: NullIsZero or NullIsBlank. Consider this code:

mysqlpp::Null<unsigned char, mysqlpp::NullIsZero> myfield(mysqlpp::null);
cout << myfield << endl;
cout << int(myfield) << endl;

This will print “0” twice. If you had used the default for the second Null template parameter, the first output statement would have printed “(NULL)”, and the second wouldn’t even compile.

The Transaction class makes it easier to use SQL transactions in an exception-safe manner. Normally you create the Transaction object on the stack before you issue the queries in your transaction set. Then, when all the queries in the transaction set have been issued, you call Transaction::commit(), which commits the transaction set. If the Transaction object goes out of scope before you call commit(), the transaction set is rolled back. This ensures that if some code throws an exception after the transaction is started but before it is committed, the transaction isn’t left unresolved.

examples/transaction.cpp illustrates this:

#include "cmdline.h"
#include "printdata.h"
#include "stock.h"

#include <iostream>

using namespace std;

int
main(int argc, char *argv[])
{
    // Get database access parameters from command line
    const char* db = 0, *server = 0, *user = 0, *pass = "";
    if (!parse_command_line(argc, argv, &db, &server, &user, &pass)) {
        return 1;
    }

    try {
        // Establish the connection to the database server.
        mysqlpp::Connection con(db, server, user, pass);

        // Show initial state
        mysqlpp::Query query = con.query();
        cout << "Initial state of stock table:" << endl;
        print_stock_table(query);

        // Insert a few rows in a single transaction set
        {
            mysqlpp::Transaction trans(con);

            stock row("Sauerkraut", 42, 1.2, 0.75,
                    mysqlpp::sql_date("2006-03-06"), mysqlpp::null);
            query.insert(row);
            query.execute();

            cout << "\nRow inserted, but not committed." << endl;
            cout << "Verify this with another program (e.g. simple1), "
                    "then hit Enter." << endl;
            getchar();

            cout << "\nCommitting transaction gives us:" << endl;
            trans.commit();
            print_stock_table(query);
        }
            
        // Now let's test auto-rollback
        {
            mysqlpp::Transaction trans(con);
            cout << "\nNow adding catsup to the database..." << endl;

            stock row("Catsup", 3, 3.9, 2.99,
                    mysqlpp::sql_date("2006-03-06"), mysqlpp::null);
            query.insert(row);
            query.execute();
        }
        cout << "\nNo, yuck! We don't like catsup. Rolling it back:" <<
                endl;
        print_stock_table(query);
            
    }
    catch (const mysqlpp::BadQuery& er) {
        // Handle any query errors
        cerr << "Query error: " << er.what() << endl;
        return -1;
    }
    catch (const mysqlpp::BadConversion& er) {  
        // Handle bad conversions
        cerr << "Conversion error: " << er.what() << endl <<
                "\tretrieved data size: " << er.retrieved <<
                ", actual size: " << er.actual_size << endl;
        return -1;
    }
    catch (const mysqlpp::Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        cerr << "Error: " << er.what() << endl;
        return -1;
    }

    return 0;
}

One of the downsides of transactions is that the locking it requires in the database server is prone to deadlocks. The classic case where this happens is when two programs both want access to the same two rows within a single transaction each, but they modify them in opposite orders. If the timing is such that the programs interleave their lock acquisitions, the two come to an impasse: neither can get access to the other row they want to modify until the other program commits its transaction and thus release the row locks, but neither can finish the transaction because they’re waiting on row locks the database server is holding on behalf of the other program.

The MySQL server is smart enough to detect this condition, but the best it can do is abort the second transaction. This breaks the impasse, allowing the first program to complete its transaction.

The second program now has to deal with the fact that its transaction just got aborted. There’s a subtlety in detecting this situation when using MySQL++. By default, MySQL++ signals errors like these with exceptions. In the exception handler, you might expect to get ER_LOCK_DEADLOCK from Query::errnum() (or Connection::errnum(), same thing), but what you’ll almost certainly get instead is 0, meaning “no error.” Why? It’s because you’re probably using a Transaction object to get automatic roll-backs in the face of exceptions. In this case, the roll-back happens before your exception handler is called by issuing a ROLLBACK query to the database server. Thus, Query::errnum() returns the error code associated with this roll-back query, not the deadlocked transaction that caused the exception.

To avoid this problem, a few of the exception objects as of MySQL++ v3.0 include this last error number in the exception object itself. It’s populated at the point of the exception, so it can differ from the value you would get from Query::errnum() later on when the exception handler runs.

The example examples/deadlock.cpp demonstrates the problem:

#include "cmdline.h"

#include <mysql++.h>
#include <mysqld_error.h>

#include <iostream>

using namespace std;

// Bring in global holding the value given to the -m switch
extern int run_mode;


int
main(int argc, char *argv[])
{
    // Get database access parameters from command line
    const char* db = 0, *server = 0, *user = 0, *pass = "";
    if (!parse_command_line(argc, argv, &db, &server, &user, &pass)) {
        return 1;
    }

    // Check that the mode parameter was also given and it makes sense
    if ((run_mode != 1) && (run_mode != 2)) {
        cerr << argv[0] << " must be run with -m1 or -m2 as one of "
                "its command-line arguments." << endl;
        return 1;
    }

    mysqlpp::Connection con;
    try {
        // Establish the connection to the database server
        con.connect(db, server, user, pass);

        // Start a transaction set.  Transactions create mutex locks on
        // modified rows, so if two programs both touch the same pair of
        // rows but in opposite orders at the wrong time, one of the two
        // programs will deadlock.  The MySQL server knows how to detect
        // this situation, and its error return causes MySQL++ to throw
        // a BadQuery exception.  The point of this example is that if
        // you want to detect this problem, you would check the value of
        // BadQuery::errnum(), not Connection::errnum(), because the
        // transaction rollback process executes a query which succeeds,
        // setting the MySQL C API's "last error number" value to 0.
        // The exception object carries its own copy of the error number
        // at the point the exception was thrown for this very reason.
        mysqlpp::Query query = con.query();
        mysqlpp::Transaction trans(con);

        // Build and run the queries, with the order depending on the -m
        // flag, so that a second copy of the program will deadlock if
        // run while the first is waiting for Enter.
        char dummy[100];
        for (int i = 0; i < 2; ++i) {
            int lock = run_mode + (run_mode == 1 ? i : -i);
            cout << "Trying lock " << lock << "..." << endl;

            query << "select * from deadlock_test" << lock << 
                    " where x = " << lock << " for update";
            query.store();

            cout << "Acquired lock " << lock << ".  Press Enter to ";
            cout << (i == 0 ? "try next lock" : "exit");
            cout << ": " << flush;
            cin.getline(dummy, sizeof(dummy));
        }
    }
    catch (mysqlpp::BadQuery e) {
        if (e.errnum() == ER_LOCK_DEADLOCK) {
            cerr << "Transaction deadlock detected!" << endl;
            cerr << "Connection::errnum = " << con.errnum() <<
                    ", BadQuery::errnum = " << e.errnum() << endl;
        }
        else {
            cerr << "Unexpected query error: " << e.what() << endl;
        }
        return 1;
    }
    catch (mysqlpp::Exception e) {
        cerr << "General error: " << e.what() << endl;      
        return 1;
    }

    return 0;
}

This example works a little differently than the others. You run one copy of the example, then when it pauses waiting for you to press Enter, you run another copy. Then, depending on which one you press Enter in, one of the two will abort with the deadlock exception. You can see from the error message you get that it matters which method you call to get the error number. What you do about it is up to you as it depends on your program’s design and system architecture.

There are three major ways to execute a query in MySQL++: Query::execute(), Query::store(), and Query::use(). Which should you use, and why?

execute() is for queries that do not return data per se. For instance, CREATE INDEX. You do get back some information from the MySQL server, which execute() returns to its caller in a SimpleResult object. In addition to the obvious — a flag stating whether the query succeeded or not — this object also contains things like the number of rows that the query affected. If you only need the success status, it’s a little more efficient to call Query::exec() instead, as it simply returns bool.

If your query does pull data from the database, the simplest option is store(). (All of the examples up to this point have used this method.) This returns a StoreQueryResult object, which contains the entire result set. It’s especially convenient because StoreQueryResult derives from std::vector<mysqlpp::Row>, so it opens the whole panoply of STL operations for accessing the rows in the result set. Access rows randomly with subscript notation, iterate forwards and backwards over the result set, run STL algorithms on the set...it all works naturally.

If you like the idea of storing your results in an STL container but don’t want to use std::vector, you can call Query::storein() instead. It lets you store the results in any standard STL container (yes, both sequential and set-associative types) instead of using StoreQueryResult. You do miss out on some of the additional database information held by StoreQueryResult’s other base class, ResultBase, however.

store*() queries are convenient, but the cost of keeping the entire result set in main memory can sometimes be too high. It can be surprisingly costly, in fact. A MySQL database server stores data compactly on disk, but it returns query data to the client in a textual form. This results in a kind of data bloat that affects numeric and BLOB types the most. MySQL++ and the underlying C API library also have their own memory overheads in addition to this. So, if you happen to know that the database server stores every record of a particular table in 1 KB, pulling a million records from that table could easily take several GB of memory with a store() query, depending on what’s actually stored in that table.

For these large result sets, the superior option is a use() query. This returns a UseQueryResult object, which is similar to StoreQueryResult, but without all of the random-access features. This is because a “use” query tells the database server to send the results back one row at a time, to be processed linearly. It’s analogous to a C++ stream’s input iterator, as opposed to a random-access iterator that a container like vector offers. By accepting this limitation, you can process arbitrarily large result sets. This technique is demonstrated in examples/simple3.cpp:

#include "cmdline.h"
#include "printdata.h"

#include <mysql++.h>

#include <iostream>
#include <iomanip>

using namespace std;

int
main(int argc, char *argv[])
{
    // Get database access parameters from command line
    const char* db = 0, *server = 0, *user = 0, *pass = "";
    if (!parse_command_line(argc, argv, &db, &server, &user, &pass)) {
        return 1;
    }

    // Connect to the sample database.
    mysqlpp::Connection conn(false);
    if (conn.connect(db, server, user, pass)) {
        // Ask for all rows from the sample stock table and display
        // them.  Unlike simple2 example, we retreive each row one at
        // a time instead of storing the entire result set in memory
        // and then iterating over it.
        mysqlpp::Query query = conn.query("select * from stock");
        if (mysqlpp::UseQueryResult res = query.use()) {
            // Display header
            cout.setf(ios::left);
            cout << setw(31) << "Item" <<
                    setw(10) << "Num" <<
                    setw(10) << "Weight" <<
                    setw(10) << "Price" <<
                    "Date" << endl << endl;

            // Get each row in result set, and print its contents
            while (mysqlpp::Row row = res.fetch_row()) {
                cout << setw(30) << row["item"] << ' ' <<
                        setw(9) << row["num"] << ' ' <<
                        setw(9) << row["weight"] << ' ' <<
                        setw(9) << row["price"] << ' ' <<
                        setw(9) << row["sdate"] <<
                        endl;
            }

            // Check for error: can't distinguish "end of results" and
            // error cases in return from fetch_row() otherwise.
            if (conn.errnum()) {
                cerr << "Error received in fetching a row: " <<
                        conn.error() << endl;
                return 1;
            }
            return 0;
        }
        else {
            cerr << "Failed to get stock item: " << query.error() << endl;
            return 1;
        }
    }
    else {
        cerr << "DB connection failed: " << conn.error() << endl;
        return 1;
    }
}

This example does the same thing as simple2, only with a “use” query instead of a “store” query.

Valuable as use() queries are, they should not be the first resort in solving problems of excessive memory use. It’s better if you can find a way to simply not pull as much data from the database in the first place. Maybe you’re saying SELECT * even though you don’t immedidately need all the columns from the table. Or, maybe you’re filtering the result set with C++ code after you get it from the database server. If you can do that filtering with a more restrictive WHERE clause on the SELECT, it’ll not only save memory, it’ll save bandwidth between the database server and client, and can even save CPU time. If the filtering criteria can’t be expressed in a WHERE clause, however, read on to the next section.

Sometimes you must pull more data from the database server than you actually need and filter it in memory. SQL’s WHERE clause is powerful, but not as powerful as C++. Instead of storing the full result set and then picking over it to find the rows you want to keep, use Query::store_if(). This is examples/store_if.cpp:

#include "cmdline.h"
#include "printdata.h"
#include "stock.h"

#include <mysql++.h>

#include <iostream>

#include <math.h>


// Define a functor for testing primality.
struct is_prime
{
    bool operator()(const stock& s)
    {
        if ((s.num == 2) || (s.num == 3)) {
            return true;    // 2 and 3 are trivial cases
        }
        else if ((s.num < 2) || ((s.num % 2) == 0)) {
            return false;   // can't be prime if < 2 or even
        }
        else {
            // The only possibility left is that it's divisible by an
            // odd number that's less than or equal to its square root.
            for (int i = 3; i <= sqrt(double(s.num)); i += 2) {
                if ((s.num % i) == 0) {
                    return false;
                }
            }
            return true;
        }
    }
};


int
main(int argc, char *argv[])
{
    // Get database access parameters from command line
    const char* db = 0, *server = 0, *user = 0, *pass = "";
    if (!parse_command_line(argc, argv, &db, &server, &user, &pass)) {
        return 1;
    }

    try {
        // Establish the connection to the database server.
        mysqlpp::Connection con(db, server, user, pass);

        // Collect the stock items with prime quantities
        std::vector<stock> results;
        mysqlpp::Query query = con.query();
        query.store_if(results, stock(), is_prime());

        // Show the results
        print_stock_header(results.size());
        std::vector<stock>::const_iterator it;
        for (it = results.begin(); it != results.end(); ++it) {
            print_stock_row(it->item.c_str(), it->num, it->weight,
                    it->price, it->sdate);
        }
    }
    catch (const mysqlpp::BadQuery& e) {
        // Something went wrong with the SQL query.
        std::cerr << "Query failed: " << e.what() << std::endl;
        return 1;
    }
    catch (const mysqlpp::Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        std::cerr << "Error: " << er.what() << std::endl;
        return 1;
    }

    return 0;
}

I doubt anyone really needs to select rows from a table that have a prime number in a given field. This example is meant to be just barely more complex than SQL can manage, to avoid obscuring the point. That point being, the Query::store_if() call here gives you a container full of results meeting a criterion that you probably can’t express in SQL. You will no doubt have much more useful criteria in your own programs.

If you need a more complex query than the one store_if() knows how to build when given an SSQLS examplar, there are two overloads that let you use your own query string. One overload takes the query string directly, and the other uses the query string built with Query’s stream interface.

SQL is more than just a database query language. Modern database engines can actually do some calculations on the data on the server side. But, this isn’t always the best way to get something done. When you need to mix code and a query, MySQL++’s Query::for_each() facility might be just what you need. This is examples/for_each.cpp:

#include "cmdline.h"
#include "printdata.h"
#include "stock.h"

#include <mysql++.h>

#include <iostream>

#include <math.h>


// Define a functor to collect statistics about the stock table
class gather_stock_stats
{
public:
    gather_stock_stats() :
    items_(0),
    weight_(0),
    cost_(0)
    {
    }

    void operator()(const stock& s)
    {
        items_  += s.num;
        weight_ += (s.num * s.weight);
        cost_   += (s.num * s.price);
    }
    
private:
    mysqlpp::sql_bigint items_;
    mysqlpp::sql_double weight_, cost_;

    friend std::ostream& operator<<(std::ostream& os,
            const gather_stock_stats& ss);
};


// Dump the contents of gather_stock_stats to a stream in human-readable
// form.
std::ostream&
operator<<(std::ostream& os, const gather_stock_stats& ss)
{
    os << ss.items_ << " items " <<
            "weighing " << ss.weight_ << " stone and " <<
            "costing " << ss.cost_ << " cowrie shells";
    return os;
}


int
main(int argc, char *argv[])
{
    // Get database access parameters from command line
    const char* db = 0, *server = 0, *user = 0, *pass = "";
    if (!parse_command_line(argc, argv, &db, &server, &user, &pass)) {
        return 1;
    }

    try {
        // Establish the connection to the database server.
        mysqlpp::Connection con(db, server, user, pass);

        // Gather and display the stats for the entire stock table
        mysqlpp::Query query = con.query();
        std::cout << "There are " << query.for_each(stock(),
                gather_stock_stats()) << '.' << std::endl;
    }
    catch (const mysqlpp::BadQuery& e) {
        // Something went wrong with the SQL query.
        std::cerr << "Query failed: " << e.what() << std::endl;
        return 1;
    }
    catch (const mysqlpp::Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        std::cerr << "Error: " << er.what() << std::endl;
        return 1;
    }

    return 0;
}

You only need to read the main() function to get a good idea of what the program does. The key line of code passes an SSQLS examplar and a functor to Query::for_each(). for_each() uses the SSQLS instance to build a select * from TABLE query, stock in this case. It runs that query internally, calling gather_stock_stats on each row. This is a pretty contrived example; you could actually do this in SQL, but we’re trying to prevent the complexity of the code from getting in the way of the demonstration here.

Just as with store_if(), described above, there are two other overloads for for_each() that let you use your own query string.

MySQL has a large number of options that control how it makes the connection to the database server, and how that connection behaves. The defaults are sufficient for most programs, so only one of the MySQL++ example programs make any connection option changes. Here is examples/multiquery.cpp:

#include "cmdline.h"
#include "printdata.h"

#include <mysql++.h>

#include <iostream>
#include <iomanip>
#include <vector>

using namespace std;
using namespace mysqlpp;


typedef vector<int> IntVectorType;


static void
print_header(IntVectorType& widths, StoreQueryResult& res)
{
    cout << "  |" << setfill(' ');
    for (size_t i = 0; i < res.field_names()->size(); i++) {
        cout << " " << setw(widths.at(i)) << res.field_name(i) << " |";
    }
    cout << endl;
}


static void
print_row(IntVectorType& widths, Row& row)
{
    cout << "  |" << setfill(' ');
    for (size_t i = 0; i < row.size(); ++i) {
        cout << " " << setw(widths.at(i)) << row[i] << " |";
    }
    cout << endl;
}


static void
print_row_separator(IntVectorType& widths)
{
    cout << "  +" << setfill('-');
    for (size_t i = 0; i < widths.size(); i++) {
        cout << "-" << setw(widths.at(i)) << '-' << "-+";
    }
    cout << endl;
}


static void
print_result(StoreQueryResult& res, int index)
{
    // Show how many rows are in result, if any
    StoreQueryResult::size_type num_results = res.size();
    if (res && (num_results > 0)) {
        cout << "Result set " << index << " has " << num_results <<
                " row" << (num_results == 1 ? "" : "s") << ':' << endl;
    }
    else {
        cout << "Result set " << index << " is empty." << endl;
        return;
    }

    // Figure out the widths of the result set's columns
    IntVectorType widths;
    int size = res.num_fields();
    for (int i = 0; i < size; i++) {
        widths.push_back(max(
                res.field(i).max_length(),
                res.field_name(i).size()));
    }

    // Print result set header
    print_row_separator(widths);
    print_header(widths, res);
    print_row_separator(widths);

    // Display the result set contents
    for (StoreQueryResult::size_type i = 0; i < num_results; ++i) {
        print_row(widths, res[i]);
    }

    // Print result set footer
    print_row_separator(widths);
}


static void
print_multiple_results(Query& query)
{
    // Execute query and print all result sets
    StoreQueryResult res = query.store();
    print_result(res, 0);
    for (int i = 1; query.more_results(); ++i) {
        res = query.store_next();
        print_result(res, i);
    }
}


int
main(int argc, char *argv[])
{
    // Get connection parameters from command line
    const char* db = 0, *server = 0, *user = 0, *pass = "";
    if (!parse_command_line(argc, argv, &db, &server, &user, &pass)) {
        return 1;
    }

    try {
        // Enable multi-queries.  Notice that you almost always set
        // MySQL++ connection options before establishing the server
        // connection, and options are always set using this one
        // interface.  If you're familiar with the underlying C API,
        // you know that there is poor consistency on these matters;
        // MySQL++ abstracts these differences away.
        Connection con;
        con.set_option(new MultiStatementsOption(true));

        // Connect to the database
        if (!con.connect(db, server, user, pass)) {
            return 1;
        }

        // Set up query with multiple queries.
        Query query = con.query();
        query << "DROP TABLE IF EXISTS test_table; " <<
                "CREATE TABLE test_table(id INT); " <<
                "INSERT INTO test_table VALUES(10); " <<
                "UPDATE test_table SET id=20 WHERE id=10; " <<
                "SELECT * FROM test_table; " <<
                "DROP TABLE test_table";
        cout << "Multi-query: " << endl << query << endl;

        // Execute statement and display all result sets.
        print_multiple_results(query);

#if MYSQL_VERSION_ID >= 50000
        // If it's MySQL v5.0 or higher, also test stored procedures, which
        // return their results the same way multi-queries do.
        query << "DROP PROCEDURE IF EXISTS get_stock; " <<
                "CREATE PROCEDURE get_stock" <<
                "( i_item varchar(20) ) " <<
                "BEGIN " <<
                "SET i_item = concat('%', i_item, '%'); " <<
                "SELECT * FROM stock WHERE lower(item) like lower(i_item); " <<
                "END;";
        cout << "Stored procedure query: " << endl << query << endl;

        // Create the stored procedure.
        print_multiple_results(query);

        // Call the stored procedure and display its results.
        query << "CALL get_stock('relish')";
        cout << "Query: " << query << endl;
        print_multiple_results(query);
#endif

        return 0;
    }
    catch (const BadOption& err) {
        cerr << err.what() << endl;
        cerr << "This example requires MySQL 4.1.1 or later." << endl;
        return 1;
    }
    catch (const ConnectionFailed& err) {
        cerr << "Failed to connect to database server: " <<
                err.what() << endl;
        return 1;
    }
    catch (const Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        cerr << "Error: " << er.what() << endl;
        return 1;
    }
}

This is a fairly complex example demonstrating the multi-query and stored procedure features in newer versions of MySQL. Because these are new features, and they change the communication between the client and server, you have to enable these features in a connection option. The key line is right up at the top of main(), where it creates a MultiStatementsOption object and passes it to Connection::set_option(). That method will take a pointer to any derivative of Option: you just create such an object on the heap and pass it in, which gives Connection the data values it needs to set the option. You don’t need to worry about releasing the memory used by the Option objects; it’s done automatically.

The only tricky thing about setting options is that only a few of them can be set after the connection is up. Most need to be set just as shown in the example above: create an unconnected Connection object, set your connection options, and only then establish the connection. The option setting mechanism takes care of applying the options at the correct time in the connection establishment sequence.

If you’re familiar with setting connection options in the MySQL C API, you’ll have to get your head around the fact that MySQL++’s connection option mechanism is a much simpler, higher-level design that doesn’t resemble the C API in any way. The C API has something like half a dozen different mechanisms for setting options that control the connection. The flexibility of the C++ type system allows us to wrap all of these up into a single high-level mechanism while actually getting greater type safety than the C API allows.

The following example demonstrates how to get information about the fields in a result set, such as the name of the field and the SQL type. This is examples/fieldinf.cpp:

#include "cmdline.h"
#include "printdata.h"

#include <iostream>
#include <iomanip>

using namespace std;


// Access the flag that's set when running under the dtest framework, so
// we modify our output to be testable.
extern bool dtest_mode;


int
main(int argc, char *argv[])
{
    // Get database access parameters from command line
    const char* db = 0, *server = 0, *user = 0, *pass = "";
    if (!parse_command_line(argc, argv, &db, &server, &user, &pass)) {
        return 1;
    }

    try {
        // Establish the connection to the database server.
        mysqlpp::Connection con(db, server, user, pass);

        // Get contents of main example table
        mysqlpp::Query query = con.query("select * from stock");
        mysqlpp::StoreQueryResult res = query.store();

        // Show info about each field in that table
        char widths[] = { 12, 22, 46 };
        cout.setf(ios::left);
        cout << setw(widths[0]) << "Field" <<
                setw(widths[1]) << "SQL Type" <<
                setw(widths[2]) << "Equivalent C++ Type" <<
                endl;
        for (size_t i = 0; i < sizeof(widths) / sizeof(widths[0]); ++i) {
            cout << string(widths[i] - 1, '=') << ' ';
        }
        cout << endl;
        
        for (size_t i = 0; i < res.field_names()->size(); i++) {
            // Suppress C++ type name outputs when run under dtest,
            // as they're system-specific.
            const char* cname = dtest_mode ? "n/a" : res.field_type(i).name();
            mysqlpp::FieldTypes::value_type ft = res.field_type(i);
            ostringstream os;
            os << ft.sql_name() << " (" << ft.id() << ')';
            cout << setw(widths[0]) << res.field_name(i).c_str() <<
                    setw(widths[1]) << os.str() <<
                    setw(widths[2]) << cname <<
                    endl;
        }
        cout << endl;

        // Simple type check
        if (res.field_type(0) == typeid(string)) {
            cout << "SQL type of 'item' field most closely resembles "
                    "the C++ string type." << endl;
        }

        // Tricky type check: the 'if' path shouldn't happen because the
        // description field has the NULL attribute.  We need to dig a
        // little deeper if we want to ignore this in our type checks.
        if (res.field_type(5) == typeid(string)) {
            cout << "Should not happen! Type check failure." << endl;
        }
        else if (res.field_type(5) == typeid(mysqlpp::Null<mysqlpp::String>)) {
            cout << "SQL type of 'description' field resembles "
                    "a nullable variant of the C++ string type." << endl;
        }
        else {
            cout << "Weird: fifth field's type is now " <<
                    res.field_type(5).name() << endl;
            cout << "Did something recently change in resetdb?" << endl;
        }
    }
    catch (const mysqlpp::BadQuery& er) {
        // Handle any query errors
        cerr << "Query error: " << er.what() << endl;
        return -1;
    }
    catch (const mysqlpp::Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        cerr << "Error: " << er.what() << endl;
        return -1;
    }

    return 0;
}

MySQL++ has two classes that work like std::string to some degree: String and SQLTypeAdapter. These classes exist to provide functionality that std::string doesn’t provide, but they are neither derivatives of nor complete supersets of std::string. As a result, end-user code generally doesn’t deal with these classes directly, because std::string is a better general-purpose string type. In fact, MySQL++ itself uses std::string most of the time, too. But, the places these specialized stringish types do get used are so important to the way MySQL++ works that it’s well worth taking the time to understand them.

int x = row["x"];

The tricky part about dealing with binary data in MySQL++ is to ensure that you don’t ever treat the data as a C string, which is really easy to do accidentally. C strings treat zero bytes as special end-of-string characters, but they’re not special at all in binary data. Recent releases of MySQL++ do a better job of letting you keep data in forms that don’t have this problem, but it’s still possible to do it incorrectly. These examples demonstrate correct techniques.

#include "cmdline.h"
#include "printdata.h"

#include <mysql++.h>

#include <fstream>

using namespace std;
using namespace mysqlpp;


// Pull in a state variable used by att_getopt() implementation so we
// can pick up where standard command line processing leaves off.  Feel
// free to ignore this implementation detail.
extern int ag_optind;


static bool
is_jpeg(const unsigned char* img_data)
{
    return (img_data[0] == 0xFF) && (img_data[1] == 0xD8) &&
            ((memcmp(img_data + 6, "JFIF", 4) == 0) ||
             (memcmp(img_data + 6, "Exif", 4) == 0));
}


int
main(int argc, char *argv[])
{
    // Get database access parameters from command line
    const char* db = 0, *server = 0, *user = 0, *pass = "";
    if (!parse_command_line(argc, argv, &db, &server, &user, &pass,
            "[jpeg_file]")) {
        return 1;
    }

    try {
        // Establish the connection to the database server.
        mysqlpp::Connection con(db, server, user, pass);

        // Assume that the last command line argument is a file.  Try
        // to read that file's data into img_data, and check it to see
        // if it appears to be a JPEG file.  Bail otherwise.
        string img_name, img_data;
        if (argc - ag_optind >= 1) {
            img_name = argv[ag_optind];
            ifstream img_file(img_name.c_str(), ios::ate);
            if (img_file) {
                size_t img_size = img_file.tellg();
                if (img_size > 10) {
                    img_file.seekg(0, ios::beg);
                    unsigned char* img_buffer = new unsigned char[img_size];
                    img_file.read(reinterpret_cast<char*>(img_buffer),
                            img_size);
                    if (is_jpeg(img_buffer)) {
                        img_data.assign(
                                reinterpret_cast<char*>(img_buffer),
                                img_size);
                    }
                    else {
                        cerr << '"' << img_file <<
                                "\" isn't a JPEG!" << endl;
                    }
                    delete[] img_buffer;
                }
                else {
                    cerr << "File is too short to be a JPEG!" << endl;
                }
            }
        }
        if (img_data.empty()) {
            print_usage(argv[0], "[jpeg_file]");
            return 1;
        }

        // Insert image data into the BLOB column in the images table.
        // We're inserting it as an std::string instead of using the raw
        // data buffer allocated above because we don't want the data
        // treated as a C string, which would truncate the data at the
        // first null character.
        Query query = con.query();
        query << "INSERT INTO images (data) VALUES(\"" <<
                mysqlpp::escape << img_data << "\")";
        SimpleResult res = query.execute();

        // If we get here, insertion succeeded
        cout << "Inserted \"" << img_name <<
                "\" into images table, " << img_data.size() <<
                " bytes, ID " << res.insert_id() << endl;
    }
    catch (const BadQuery& er) {
        // Handle any query errors
        cerr << "Query error: " << er.what() << endl;
        return -1;
    }
    catch (const BadConversion& er) {
        // Handle bad conversions
        cerr << "Conversion error: " << er.what() << endl <<
                "\tretrieved data size: " << er.retrieved <<
                ", actual size: " << er.actual_size << endl;
        return -1;
    }
    catch (const Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        cerr << "Error: " << er.what() << endl;
        return -1;
    }

    return 0;
}

#include <mysql++.h>
#include <ssqls.h>

#define IMG_DATABASE    "mysql_cpp_data"
#define IMG_HOST        "localhost"
#define IMG_USER        "root"
#define IMG_PASSWORD    "nunyabinness"

sql_create_2(images,
    1, 2,
    mysqlpp::sql_int_unsigned, id,
    mysqlpp::sql_blob, data)

int main()
{
    unsigned int img_id = 0;
    char* cgi_query = getenv("QUERY_STRING");
    if (cgi_query) {
        if ((strlen(cgi_query) < 4) || memcmp(cgi_query, "id=", 3)) {
            std::cout << "Content-type: text/plain" << std::endl << std::endl;
            std::cout << "ERROR: Bad query string" << std::endl;
            return 1;
        }
        else {
            img_id = atoi(cgi_query + 3);
        }
    }
    else {
        std::cerr << "Put this program into a web server's cgi-bin "
                "directory, then" << std::endl;
        std::cerr << "invoke it with a URL like this:" << std::endl;
        std::cerr << std::endl;
        std::cerr << "    http://server.name.com/cgi-bin/cgi_jpeg?id=2" <<
                std::endl;
        std::cerr << std::endl;
        std::cerr << "This will retrieve the image with ID 2." << std::endl;
        std::cerr << std::endl;
        std::cerr << "You will probably have to change some of the #defines "
                "at the top of" << std::endl;
        std::cerr << "examples/cgi_jpeg.cpp to allow the lookup to work." <<
                std::endl;
        return 1;
    }

    try {
        mysqlpp::Connection con(IMG_DATABASE, IMG_HOST, IMG_USER,
                IMG_PASSWORD);
        mysqlpp::Query query = con.query();
        query << "SELECT * FROM images WHERE id = " << img_id;
        mysqlpp::UseQueryResult res = query.use();
        if (res) {
            images img = res.fetch_row();
            std::cout << "Content-type: image/jpeg" << std::endl;
            std::cout << "Content-length: " << img.data.length() << "\n\n";
            std::cout << img.data;
        }
        else {
            std::cout << "Content-type: text/plain" << std::endl << std::endl;
            std::cout << "ERROR: No such image with ID " << img_id << std::endl;
        }
    }
    catch (const mysqlpp::BadQuery& er) {
        // Handle any query errors
        std::cout << "Content-type: text/plain" << std::endl << std::endl;
        std::cout << "QUERY ERROR: " << er.what() << std::endl;
        return 1;
    }
    catch (const mysqlpp::Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        std::cout << "Content-type: text/plain" << std::endl << std::endl;
        std::cout << "GENERAL ERROR: " << er.what() << std::endl;
        return 1;
    }

    return 0;
}

An important limitation of the MySQL C API library — which MySQL++ is built atop, so it shares this limitation — is that you can’t have two concurrent queries running on a single connection. If you try, you get an obscure error message about “Commands out of sync” from the underlying C API library. (You get it in a MySQL++ exception unless you have exceptions disabled, in which case you get a failure code and Connection::error() returns this message.)

The easiest way to cause this error is in a multithreaded program where you have a single Connection object, but allow multiple threads to issue queries on it. Unless you put in a lot of work to synchronize access, this is almost guaranteed to fail.

If you give each thread that issues queries has its own Connection object, you can still run into trouble if you pass the data you get from queries around to other threads. What can happen is that one of these child objects indirectly calls back to the Connection at a time where it’s involved with another query. (There are other ways to run into trouble when sharing MySQL++ data structures among threads, but the whole topic is complex enough to deserve its own chapter, Section 7, “Using MySQL++ in a Multithreaded Program”.)

It’s possible to run into this problem in a single-threaded program as well. As discussed above (Section 3.11, “Which Query Type to Use?”), one of the options MySQL offers for executing a query lets you issue the query, then consume the rows one at a time, on demand: it’s the “use” query. If you don’t consume all rows from a query before you issue another on that connection, you are effectively trying to have multiple concurrent queries on a single connection, and you end up with the same problem. The simplest recipie for disaster is:

UseQueryResult r1 = query.use("select garbage from plink where foobie='tamagotchi'");
UseQueryResult r2 = query.use("select blah from bonk where bletch='smurf'");

The second use() call fails because the first result set hasn’t been consumed yet.