3. Tutorial

This tutorial is meant to give you a jump start in using MySQL++. While it is a very complicated and powerful library, it's possible to make quite functional programs without tapping but a fraction of its power. This section will introduce you to the most useful fraction.

This tutorial assumes you know C++ fairly well, in particuler the Standard Template Library (STL) and exceptions.

3.1. Running the Examples

All of the examples are complete running programs. They may or may not be built for you already, depending on how you installed the library.

If you installed MySQL++ from the source tarball on a Unixy system, the examples should have been built along with the library. If not, simply go into the examples directory and type make.

If you installed the library via RPM, the examples are in the mysql++-devel RPM. After installing that, the examples are in /usr/src/mysql++/examples. To build them, go into that directory and type make -f Makefile.simple. See the file /usr/share/doc/mysql++-devel*/README.examples for more details.

If you are on a Windows system, the build process for the library should have built the examples as well. Where the programs are depends on which compiler you're using. There should be a README.* file in the distribution specific to your compiler with further instructions.

Once you have the examples building, you need to initialize the sample database by running the resetdb example. The usage of resetdb is as follows:

	resetdb [host [user [password [port]]]]

If you leave off host, localhost is assumed. If you leave off user, your current username is assumed. If you leave of the password, it is assumed that you don't need one. And if you leave off the port, it will use the standard MySQL port number.

The user you give resetdb needs to be an account with permission to create databases. Once the database is created you can use any account that has full permission to 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.

3.2. A Simple Example

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

#include "util.h"

#include <mysql++.h>

#include <iostream>
#include <iomanip>

using namespace std;

int
main(int argc, char *argv[])
{
    // Connect to the sample database.
    mysqlpp::Connection con(false);
    if (!connect_to_db(argc, argv, con)) {
        return 1;
    }

    // Retrieve a subset of the sample stock table set up by resetdb
    mysqlpp::Query query = con.query();
    query << "select item from stock";
    mysqlpp::Result res = query.store();

    // Display the result set
    cout << "We have:" << endl;
    if (res) {
        char buf[100];
        mysqlpp::Row row;
        mysqlpp::Row::size_type i;
        for (i = 0; row = res.at(i); ++i) {
            cout << '\t' << utf8trans(row.at(0), buf, sizeof(buf)) << endl;
        }
    }
    else {
        cerr << "Failed to get item list: " << query.error() << endl;
        return 1;
    }

    return 0;
}

		

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

Notice that MySQL++ lets you store result sets in STL containers, such as std::vector. We iterate through the result set just as you would with any other STL container. The only tricky bit is the it->at(0) idiom. This dereferences the iterator, which yields a Row object, on which we call the at() method to retrieve the first field.

The only thing that isn't explicitly handled in the code block above is that we delegate connection establishment to connect_to_db() in the util module. We do this only because that function also handles the command line parsing for the examples, so they have a consistent interface.

3.3. Examples' Utility Module

I referred to the util module above. Following is the source for that module, which also contains other functions used by other examples. It isn't important to understand this module in detail, but understanding its outlines will make the following examples more clear.

#include "util.h"

#include <iostream>
#include <iomanip>
#include <stdlib.h>

// This include isn't needed by util module.  It's just a test of the
// new SSQLS feature allowing the structure to be defined in many
// modules without having a multiply-defined static variable error.
#define MYSQLPP_SSQLS_NO_STATICS
#include "stock.h"      

using namespace std;

const char* kpcSampleDatabase = "mysql_cpp_data";


//// utf8trans /////////////////////////////////////////////////////////
// Converts a Unicode string encoded in UTF-8 form (which the MySQL
// database uses) to a form suitable for outputting to the standard C++
// cout stream.  Functionality is platform-specific.

char*
utf8trans(const char* utf8_str, char* out_buf, int buf_len)
{
#ifdef MYSQLPP_PLATFORM_WINDOWS
    // It's Win32, so assume console output, where output needs to be in
    // local ANSI code page by default.
    wchar_t ucs2_buf[100];
    static const int ub_chars = sizeof(ucs2_buf) / sizeof(ucs2_buf[0]);

    // First, convert UTF-8 string to UCS-2
    if (MultiByteToWideChar(CP_UTF8, 0, utf8_str, -1,
            ucs2_buf, ub_chars) > 0) {
        // Next, convert UCS-2 to local code page.
        CPINFOEX cpi;
        GetCPInfoEx(CP_OEMCP, 0, &cpi);
        WideCharToMultiByte(cpi.CodePage, 0, ucs2_buf, -1,
                out_buf, buf_len, 0, 0);
        return out_buf;
    }
    else {
        int err = GetLastError();
        if (err == ERROR_NO_UNICODE_TRANSLATION) {
            cerr << "Bad data in UTF-8 string" << endl;
        }
        else {
            cerr << "Unknown error in Unicode translation: " <<
                    GetLastError() << endl;
        }
        return 0;
    }
#else
    // Assume a modern Unixy platform, where the system's terminal I/O
    // code handles UTF-8 directly.  (e.g. common Linux distributions
    // since 2001 or so, recent versions of Mac OS X, etc.)
    strncpy(out_buf, utf8_str, buf_len);
    return out_buf;
#endif
}


//// print_stock_header ////////////////////////////////////////////////
// Display a header suitable for use with print_stock_rows().

void
print_stock_header(int rows)
{
    cout << "Records found: " << rows << endl << endl;
    cout.setf(ios::left);
    cout << setw(21) << "Item" <<
            setw(10) << "Num" <<
            setw(10) << "Weight" <<
            setw(10) << "Price" <<
            "Date" << endl << endl;
}


//// print_stock_row ///////////////////////////////////////////////////
// Print out a row of data from the stock table, in a format compatible
// with the header printed out in the previous function.

void
print_stock_row(const mysqlpp::sql_char& item, mysqlpp::sql_bigint num,
        mysqlpp::sql_double weight, mysqlpp::sql_double price,
        const mysqlpp::sql_date& date)
{
    // We do UTF-8 translation on item field because there is Unicode
    // data in this field in the sample database, and some systems
    // cannot handle UTF-8 sent directly to cout.
    char buf[100];
    cout << setw(20) << utf8trans(item.c_str(), buf, sizeof(buf)) << ' ' <<
            setw(9) << num << ' ' <<
            setw(9) << weight << ' ' <<
            setw(9) << price << ' ' <<
            date << endl;
}


//// print_stock_row ///////////////////////////////////////////////////
// Take a Row from the example 'stock' table, break it up into fields,
// and call the above version of this function.

void
print_stock_row(const mysqlpp::Row& row)
{
    // The brief code below illustrates several aspects of the library
    // worth noting:
    //
    // 1. You can subscript a row by integer (position of the field in
    // the row) or by string (name of field in the row).  The former is
    // more efficient, while the latter trades some efficiency for
    // robustness in the face of schema changes.  (Consider using SSQLS
    // if you need a tradeoff in between these two positions.)
    // 
    // 2. You can also get at a row's field's with Row::at(), which is
    // much like Row::operator[](int).  Besides the syntax difference,
    // the only practical difference is that only at() can access field
    // 0: this is because '0' can be converted to both int and to const
    // char*, so the compiler rightly complains that it can't decide
    // which overload to call.
    //
    // 3. Notice that we make an explicit temporary copy of the first
    // field, which is the only string field.  We must tolerate the
    // inefficiency of this copy, because Row::operator[] returns a
    // ColData object, which goes away after it is converted to some
    // other form.  So, while we could have made print_stock_row()
    // take a const char* argument (as past versions mistakenly did!)
    // this would result in a dangling pointer, since it points into the
    // ColData object, which is dead by the time the pointer is
    // evaluated in print_stock_row().  It will probably even work this
    // way, but like any memory bug, it can wreak subtle havoc.
    std::string item(row.at(0));
    print_stock_row(item, row["num"], row[2], row[3], row[4]);
}


//// print_stock_rows //////////////////////////////////////////////////
// Print out a number of rows from the example 'stock' table.

void
print_stock_rows(mysqlpp::Result& res)
{
    print_stock_header(res.size());

    // Use the Result class's read-only random access iterator to walk
    // through the query results.
    mysqlpp::Result::iterator i;
    for (i = res.begin(); i != res.end(); ++i) {
        // Notice that a dereferenced result iterator can be converted
        // to a Row object, which makes for easier element access.
        print_stock_row(*i);
    }
}


//// get_stock_table ///////////////////////////////////////////////////
// Retreive the entire contents of the example 'stock' table.

void
get_stock_table(mysqlpp::Query& query, mysqlpp::Result& res)
{
    // Reset the query object, in case we're re-using it.
    query.reset();

    // You can write to the query object like you would any ostream.
    query << "select * from stock";

    // Show the query string.  If you call preview(), it must be before
    // you call execute() or store() or use().
    cout << "Query: " << query.preview() << endl;

    // Execute the query, storing the results in memory.
    res = query.store();
}


//// print_stock_table /////////////////////////////////////////////////
// Simply retrieve and print the entire contents of the stock table.

void
print_stock_table(mysqlpp::Query& query)
{
    mysqlpp::Result res;
    get_stock_table(query, res);
    print_stock_rows(res);
}


//// connect_to_db /////////////////////////////////////////////////////
// Establishes a connection to a MySQL database server, optionally
// attaching to database kdb.  This is basically a command-line parser
// for the examples, since the example programs' arguments give us the
// information we need to establish the server connection.

bool
connect_to_db(int argc, char *argv[], mysqlpp::Connection& con,
        const char *kdb)
{
    if (argc < 1) {
        cerr << "Bad argument count: " << argc << '!' << endl;
        return false;
    }

    if (!kdb) {
        kdb = kpcSampleDatabase;
    }

    if ((argc > 1) && (argv[1][0] == '-')) {
        cout << "usage: " << argv[0] <<
                " [host] [user] [password] [port]" << endl;
        cout << endl << "\tConnects to database ";
        if (strlen(kdb) > 0) {
            cout << '"' << kdb << '"';
        }
        else {
            cout << "server";
        }
        cout << " on localhost using your user" << endl;
        cout << "\tname and no password by default." << endl << endl;
        return false;
    }

    if (argc == 1) {
        con.connect(kdb);
    }
    else if (argc == 2) {
        con.connect(kdb, argv[1]);
    }
    else if (argc == 3) {
        con.connect(kdb, argv[1], argv[2]);
    }
    else if (argc == 4) {
        con.connect(kdb, argv[1], argv[2], argv[3]);
    }
    else if (argc >= 5) {
        con.connect(kdb, argv[1], argv[2], argv[3], atoi(argv[4]));
    }

    if (con) {
        return true;
    }
    else {
        cerr << "Database connection failed: " << con.error() << endl;
        return false;
    }
}

		

This is actually an abridged version of util.cpp, with the Unicode stuff removed. The interaction between MySQL, MySQL++ and Unicode is covered in a later chapter, Using Unicode with MySQL++.

3.4. A More Complicated Example

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

#include "util.h"

#include <mysql++.h>

#include <iostream>
#include <iomanip>

using namespace std;

int
main(int argc, char *argv[])
{
    // Connect to the sample database.
    mysqlpp::Connection con(false);
    if (!connect_to_db(argc, argv, con)) {
        return 1;
    }

    // Retrieve the sample stock table set up by resetdb
    mysqlpp::Query query = con.query();
    query << "select * from stock";
    mysqlpp::Result res = query.store();

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

        // Get each row in result set, and print its contents
        char buf[100];
        mysqlpp::Row row;
        mysqlpp::Row::size_type i;
        for (i = 0; row = res.at(i); ++i) {
            cout << setw(20) <<
                    utf8trans(row["item"], buf, sizeof(buf)) << ' ' <<
                    setw(9) << row["num"] << ' ' <<
                    setw(9) << row["weight"] << ' ' <<
                    setw(9) << row["price"] << ' ' <<
                    setw(9) << row["sdate"] <<
                    endl;
        }
    }
    else {
        cerr << "Failed to get stock table: " << query.error() << endl;
        return 1;
    }

    return 0;
}

		

This example illustrates several new concepts.

First, notice that we store the result set in a Result object. Like the a std::vector we used in the simple1 example, Result is a container type, so iterating through it is straightforward. The main difference is that Result is less distanced from the way the underlying MySQL C API works, so it is somewhat more efficient.

Second, we access each row's data indirectly through a Row object. This affords several nice features, such as the ability to access a field by name. You can also access fields by position, as we did in the simple1 example, which is more efficient, but makes your code less flexible.

3.5. Exceptions

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 the Standard C++ exception base class, std::exception. 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 by reference. However, it's usually better to catch the all of the concret eexception types that you expect, and add a handler for Exception or std::exception to act as a "catch-all" for unexpected exceptions.

Some of these exceptions are optional. When disabled, the object 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" 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. See examples/resetdb.cpp to see this technique at work.

When one OptionalExceptions derivative creates another such object and passes on its exception flag, it passes a copy of the flag. Therefore, the two objects' flags operate independently after the new one is created. There's no way to globally enable or disable this flag on existing objects in a single call.

There are a few classes of exceptions MySQL++ can throw that are not optional:

  • The largest set of non-optional exceptions are those from the Standard C++ Library. For instance, if your code said "row[21]" on a row containing only 5 fields, the std::vector underlying the row object will throw an exception. (It will, that is, if it conforms to the standard.) You might consider wrapping your program's main loop in a try block catching std::exceptions, just in case you trigger one of these exceptions.

  • ColData will always throw BadConversion when you ask it to do an improper type conversion. For example, you'll get an exception if you try to convert "1.25" to int, but not when you convert "1.00" to int. In the latter case, MySQL++ knows that it can safely throw away the fractional part.

  • If you use template queries and don't pass enough parameters when instantiating the template, Query will throw a BadParamCount exception.

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 ColData conversion error.

3.6. Quoting and Escaping

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 q = conn.query();
q << "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 various string types, for any type of data that can be converted to MySQL++'s ColData type, and for Specialized SQL Structures. (The next section introduces the SSQLS feature.)

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 q = conn.query();
q << "SELECT * FROM stock WHERE item = " << quote << s; 

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

3.7. Specialized SQL Structures

Retrieving data

The next example introduces one of the most powerful features of MySQL++: Specialized SQL Structures (SSQLS). This is examples/custom1.cpp:

#include "stock.h"
#include "util.h"

#include <iostream>
#include <vector>

using namespace std;

int
main(int argc, char *argv[])
{
    // Wrap all MySQL++ interactions in one big try block, so any
    // errors are handled gracefully.
    try {                       
        // Establish the connection to the database server.
        mysqlpp::Connection con(mysqlpp::use_exceptions);
        if (!connect_to_db(argc, argv, con)) {
            return 1;
        }

        // Retrieve the entire contents of the stock table, and store
        // the data in a vector of 'stock' SSQLS structures.
        mysqlpp::Query query = con.query();
        query << "select * from stock";
        vector<stock> res;
        query.storein(res);

        // Display the result set
        print_stock_header(res.size());
        vector<stock>::iterator it;
        for (it = res.begin(); it != res.end(); ++it) {
            print_stock_row(it->item, it->num, it->weight, it->price,
                    it->sdate);
        }
    }
    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; e.g. type mismatch populating 'stock'
        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;
}

			

Here is the stock.h header used by that example, and many others:

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

#include <string>

// The following is calling a very complex macro which will create
// "struct stock", which has the member variables:
//
//   sql_char item;
//   ...
//   sql_date sdate;
//
// plus methods to help populate the class from a MySQL row.  See the
// SSQLS sections in the user manual for further details.
sql_create_5(stock,
    1, 5, // The meaning of these values is covered in the user manual
    mysqlpp::sql_char, item,
    mysqlpp::sql_bigint, num,
    mysqlpp::sql_double, weight,
    mysqlpp::sql_double, price,
    mysqlpp::sql_date, sdate)


			

As you can see, SSQLS is very powerful. It allows you to have a C++ structure paralleling your SQL table structure and use it easily with STL code.

Adding data

SSQLS can also be used to add data to a table. This is examples/custom2.cpp:

#include "stock.h"
#include "util.h"

#include <iostream>

using namespace std;

int
main(int argc, char *argv[])
{
    try {
        // Establish the connection to the database server.
        mysqlpp::Connection con(mysqlpp::use_exceptions);
        if (!connect_to_db(argc, argv, con)) {
            return 1;
        }

        // Create and populate a stock object.  We could also have used
        // the set() member, which takes the same parameters as this
        // constructor.
        stock row("Hot Dogs", 100, 1.5, 1.75, "1998-09-25");

        // Form the query to insert the row into the stock table.
        mysqlpp::Query query = con.query();
        query.insert(row);

        // Show the query about to be executed.
        cout << "Query: " << query.preview() << endl;

        // Execute the query.  We use execute() because INSERT doesn't
        // return a result set.
        query.execute();

        // Print the new table.
        mysqlpp::Result res;
        get_stock_table(query, res);
        print_stock_rows(res);
    }
    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;
}

			

That's all there is to it!

There is one subtlety: MySQL++ automatically quotes and escapes the data when building SQL queries using SSQLS structures. It's efficient, too: MySQL++ is smart enough to apply quoting and escaping only for those data types that actually require it.

Because this example modifies the sample database, you may want to run resetdb after running this program.

Modifying data

It almost as easy to modify data with SSQLS. This is examples/custom3.cpp:

#include "stock.h"
#include "util.h"

#include <iostream>

using namespace std;

int
main(int argc, char *argv[])
{
    try {
        // Establish the connection to the database server.
        mysqlpp::Connection con(mysqlpp::use_exceptions);
        if (!connect_to_db(argc, argv, con)) {
            return 1;
        }

        // Build a query to retrieve the stock item that has Unicode
        // characters encoded in UTF-8 form.
        mysqlpp::Query query = con.query();
        query << "select * from stock where item = \"Nürnberger Brats\"";

        // Retrieve the row, throwing an exception if it fails.
        mysqlpp::Result res = query.store();
        if (res.empty()) {
            throw mysqlpp::BadQuery("UTF-8 bratwurst item not found in "
                    "table, run resetdb");
        }

        // Because there should only be one row in the result set,
        // there's no point in storing the result in an STL container.
        // We can store the first row directly into a stock structure
        // because one of an SSQLS's constructors takes a Row object.
        stock row = res.at(0);

        // Create a copy so that the replace query knows what the
        // original values are.
        stock orig_row = row;

        // Change the stock object's item to use only 7-bit ASCII, and
        // to deliberately be wider than normal column widths printed
        // by print_stock_table().
        row.item = "Nuerenberger Bratwurst";

        // Form the query to replace the row in the stock table.
        query.update(orig_row, row);

        // Show the query about to be executed.
        cout << "Query: " << query.preview() << endl;

        // Run the query with execute(), since UPDATE doesn't return a
        // result set.
        query.execute();

        // Print the new table contents.
        get_stock_table(query, res);
        print_stock_rows(res);
    }
    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;
}

			

When you run the example you will notice that in the WHERE clause only the 'item' field is checked for. This is because SSQLS also also less-than-comparable.

Don't forget to run resetdb after running the example.

Less-than-comparable

SSQLS structures can be sorted and stored in STL associative containers as demonstrated in the next example. This is examples/custom4.cpp:

#include "stock.h"
#include "util.h"

#include <iostream>

using namespace std;

int
main(int argc, char *argv[])
{
    try {
        // Establish the connection to the database server.
        mysqlpp::Connection con(mysqlpp::use_exceptions);
        if (!connect_to_db(argc, argv, con)) {
            return 1;
        }

        // Retrieve all rows from the stock table and put them in an
        // STL set.  Notice that this works just as well as storing them
        // in a vector, which we did in custom1.cpp.  It works because
        // SSQLS objects are less-than comparable.
        mysqlpp::Query query = con.query();
        query << "select * from stock";
        set<stock> res;
        query.storein(res);

        // Display the result set.  Since it is an STL set and we set up
        // the SSQLS to compare based on the item column, the rows will
        // be sorted by item.
        print_stock_header(res.size());
        set<stock>::iterator it;
        cout.precision(3);
        for (it = res.begin(); it != res.end(); ++it) {
            print_stock_row(it->item.c_str(), it->num, it->weight,
                    it->price, it->sdate);
        }

        // Use set's find method to look up a stock item by item name.
        // This also uses the SSQLS comparison setup.
        it = res.find(stock("Hotdog Buns"));
        if (it != res.end()) {
            cout << endl << "Currently " << it->num <<
                    " hotdog buns in stock." << endl;
        }
        else {
            cout << endl << "Sorry, no hotdog buns in stock." << endl;
        }
    }
    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;
}

			

For more details on the SSQLS feature, see the Specialized SQL Structures chapter.

3.8. C++ Equivalents of SQL Column Types

In MySQL++ version 2.1, the new 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; you could use an unsigned char here if you wanted to. For that matter, you could use a plain int in most cases; MySQL++ is quite tolerant of this sort of thing. The typedefs exist for style reasons, for those who want their C++ code to use the closest equivalent type for any given SQL type.

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.

3.9. Handling SQL Nulls

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 TINY INT UNSIGNED column that can have nulls, the proper declaration for MySQL++ would be:

mysqlpp::Null<unsigned char> 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;

The final aspect of MySQL++'s null handling is that, by default, it will enforce the uniqueness of the SQL null value. If you try to convert a SQL null to a plain C++ data type, MySQL++ will throw a BadNullConversion exception. If you insert a SQL null into a C++ stream, 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 the null type. 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;

myfield = mysqlpp::null;
cout << myfield << endl;

int x = myfield;
cout << x << 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 would have thrown a BadNullConversion exception.

3.10. Creating Transaction Sets

MySQL++ v2.1 added the Transaction class, which makes it easier to use 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/xaction.cpp illustrates this:

#include "stock.h"
#include "util.h"

#include <transaction.h>

#include <iostream>

using namespace std;

int
main(int argc, char *argv[])
{
    try {
        // Establish the connection to the database server.
        mysqlpp::Connection con(mysqlpp::use_exceptions);
        if (!connect_to_db(argc, argv, con)) {
            return 1;
        }

        // 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 row1("Sauerkraut", 42, 1.2, 0.75, "2006-03-06");
            query.insert(row1);
            query.execute();
            query.reset();

            stock row2("Bratwurst", 24, 3.0, 4.99, "2006-03-06");
            query.insert(row2);
            query.execute();
            query.reset();

            cout << "\nRows are 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, "2006-03-06");
            query.insert(row);
            query.execute();
            query.reset();
        }
        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;
}

		

3.11. Which Query Type to Use?

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 ResNSel 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, there's Query::exec(), which just returns bool.

If your query does pull data from the database, the simplest option is store(). This returns a Result object, which contains an in-memory copy of the result set. The nice thing about this is that Result is a sequential container, like std::vector, so you can iterate through it forwards and backwards, access elements with subscript notation, etc. There are also the storein() methods, which actually put the result set into an STL container of your choice. The downside of these methods is that a sufficiently large result set will give your program memory problems.

For these large result sets, the superior option is a use() query. This returns a ResUse object, which is similar to Result, 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 "util.h"

#include <mysql++.h>

#include <iostream>
#include <iomanip>

using namespace std;

int
main(int argc, char *argv[])
{
    // Connect to the sample database.
    mysqlpp::Connection con(false);
    if (!connect_to_db(argc, argv, con)) {
        return 1;
    }

    // Ask for all rows from the sample stock table set up by resetdb.
    // Unlike simple2 example, we don't store result set in memory.
    mysqlpp::Query query = con.query();
    query << "select * from stock";
    mysqlpp::ResUse res = query.use();

    // Retreive result rows one by one, and display them.
    if (res) {
        // Display header
        cout.setf(ios::left);
        cout << setw(21) << "Item" <<
                setw(10) << "Num" <<
                setw(10) << "Weight" <<
                setw(10) << "Price" <<
                "Date" << endl << endl;

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

        return 0;
    }
    else {
        cerr << "Failed to get stock item: " << query.error() << endl;
        return 1;
    }
}

		

This example does the same thing as simple2, only with a "use" query instead of a "store" query. If your program uses exceptions, you should instead look at examples/usequery.cpp, which does the same thing as simple, but with exception-awareness.

3.12. Getting Field Meta-Information

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/fieldinf1.cpp:

#include "util.h"

#include <mysql++.h>

#include <iostream>
#include <iomanip>

using namespace std;
using namespace mysqlpp;

int
main(int argc, char *argv[])
{
    try {
        Connection con(use_exceptions);
        if (!connect_to_db(argc, argv, con)) {
            return 1;
        }

        Query query = con.query();
        query << "select * from stock";
        cout << "Query: " << query.preview() << endl;

        Result res = query.store();
        cout << "Records Found: " << res.size() << endl << endl;

        cout << "Query Info:\n";
        cout.setf(ios::left);

        for (unsigned int i = 0; i < res.names().size(); i++) {
            cout << setw(2) << i
                    // this is the name of the field
                    << setw(15) << res.names(i).c_str()
                    // this is the SQL identifier name
                    // Result::types(unsigned int) returns a mysql_type_info which in many
                    // ways is like type_info except that it has additional sql type
                    // information in it. (with one of the methods being sql_name())
                    << setw(15) << res.types(i).sql_name()
                    // this is the C++ identifier name which most closely resembles
                    // the sql name (its is implementation defined and often not very readable)
                    << setw(20) << res.types(i).name()
                    << endl;
        }

        cout << endl;

        if (res.types(0) == typeid(string)) {
            // this is demonstrating how a mysql_type_info can be 
            // compared with a C++ type_info.
            cout << "Field 'item' is of an SQL type which most "
                    "closely resembles\nthe C++ string type\n";
        }

        if (res.types(1) == typeid(longlong)) {
            cout << "Field 'num' is of an SQL type which most "
                    "closely resembles\nC++ long long int type\n";
        }
        else if (res.types(1).base_type() == typeid(longlong)) {
            // you have to be careful as if it can be null the actual
            // type is Null<TYPE> not TYPE.  So you should always use
            // the base_type method to get at the underlying type.
            // If the type is not null than this base type would be
            // the same as its type.
            cout << "Field 'num' base type is of an SQL type which "
                    "most closely\nresembles the C++ long long int type\n";
        }
    }
    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;
}

		

3.13. Let's Do Something Useful

These next few examples demonstrate just how powerful C++ can be, allowing you to do a lot of work in few lines of code without losing efficiency.

Since the code is meant to be re-used as-is, constants that can differ from one case to another have been grouped in order to simplify editing. Also, all of these examples have full error checking code, showing off the power of MySQL++'s exception handling features.

Loading binary file in a BLOB column

Since MySQL 3.23, BLOB columns have been available, but their use is sometimes not straightforward. Besides showing how easy it can be with MySQL++, this example demonstrates several features of MySQL++. The program requires one command line parameter, which is a full path to the binary file. This is examples/load_file.cpp:

#include <mysql++.h>

#include <sys/stat.h>

#include <fstream>

#include <stdlib.h>

using namespace std;
using namespace mysqlpp;

const char MY_DATABASE[] = "telcent";
const char MY_TABLE[] = "fax";
const char MY_HOST[] = "localhost";
const char MY_USER[] = "root";
const char MY_PASSWORD[] = "";
const char MY_FIELD[] = "fax";  // BLOB field

int
main(int argc, char *argv[])
{
    if (argc < 2) {
        cerr << "Usage : load_file full_file_path" << endl << endl;
        return -1;
    }

    Connection con(use_exceptions);
    try {
        con.connect(MY_DATABASE, MY_HOST, MY_USER, MY_PASSWORD);
        Query query = con.query();
        ostringstream strbuf;
        ifstream In(argv[1], ios::in | ios::binary);
        struct stat for_len;
        if ((In.rdbuf())->is_open()) {
            if (stat(argv[1], &for_len) == -1)
                return -1;
            unsigned int blen = for_len.st_size;
            if (!blen)
                return -1;
            char *read_buffer = new char[blen];
            In.read(read_buffer, blen);
            string fill(read_buffer, blen);
            strbuf << "INSERT INTO " << MY_TABLE << " (" << MY_FIELD <<
                ") VALUES(\"" << mysqlpp::escape << fill << "\")" << ends;
            query.exec(strbuf.str());
            delete[]read_buffer;
        }
        else
            cerr << "Failed to open " << argv[1] <<
                    '.' << 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;
}

			

Notice that we used the escape manipulator when building the INSERT query above. This is because we're not using one of the MySQL++ types that does automatic escaping and quoting.

Displaying images in HTML from BLOB column

This example is also a very short one, considering the function that it performs. Although all modern versions of MySQL have a command that dumps data from a BLOB column to a binary file, this example shows how to do it in your code instead, without requiring an temporary file on disk. This is examples/cgi_image.cpp:

#include <mysql++.h>

using namespace std;
using namespace mysqlpp;

#define MY_DATABASE "telcent"
#define MY_TABLE    "fax"
#define MY_HOST     "localhost"
#define MY_USER     "root"
#define MY_PASSWORD ""
#define MY_FIELD    "fax"       // BLOB field
#define MY_KEY      "datet"     // PRIMARY KEY

int
main(int argc, char *argv[])
{
    if (argc < 2) {
        cerr << "Usage : cgi_image primary_key_value" << endl << endl;
        return -1;
    }

    cout << "Content-type: image/jpeg" << endl;
    Connection con(use_exceptions);
    try {
        con.connect(MY_DATABASE, MY_HOST, MY_USER, MY_PASSWORD);
        Query query = con.query();
        query << "SELECT " << MY_FIELD << " FROM " << MY_TABLE << " WHERE "
            << MY_KEY << " = " << argv[1];
        ResUse res = query.use();
        Row row = res.fetch_row();
        long unsigned int *jj = res.fetch_lengths();
        cout << "Content-length: " << *jj << endl << endl;
        fwrite(row.raw_data(0), 1, *jj, stdout);
    }
    catch (const BadQuery& er) {
        // Handle any query errors
        cerr << "Query error: " << er.what() << endl;
        return -1;
    }
    catch (const Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        cerr << "Error: " << er.what() << endl;
        return -1;
    }

    return 0;
}

			

DELETE or UPDATE from SELECT

MySQL's SELECT statement has more power to winnow out just the items of interest from the database than do DELETE or UPDATE queries. Therefore, many people have wanted the ability to execute a SELECT statement that in fact deletes or updates the rows matched, rather than returning them. This example implements that feature in just a few lines of code. It is examples/updel.cpp:

#include <mysql++.h>

#include <string>

using namespace std;
using namespace mysqlpp;

#define MY_DATABASE "telcent"
#define MY_TABLE    "nazivi"
#define MY_HOST     "localhost"
#define MY_USER     "root"
#define MY_PASSWORD ""
#define MY_FIELD    "naziv"
#define MY_QUERY    "SELECT URL from my_table as t1, my_table as t2 where t1.field = t2.field"

int
main()
{
    Connection con(use_exceptions);
    try {
        ostringstream strbuf;
        unsigned int i = 0;
        con.connect(MY_DATABASE, MY_HOST, MY_USER, MY_PASSWORD);
        Query query = con.query();
        query << MY_QUERY;
        ResUse res = query.use();
        Row row;
        strbuf << "delete from " << MY_TABLE << " where " << MY_FIELD <<
            " in (";
        //  for UPDATE just replace the above DELETE FROM with UPDATE statement
        for (; row = res.fetch_row(); i++)
            strbuf << row.at(0) << ",";
        if (!i)
            return 0;
        string output(strbuf.str());
        output.erase(output.size() - 1, 1);
        output += ")";
        query.exec(output);
        //cout << output << 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;
}

			

Notice that the row values used in the IN clause aren't escaped or quoted. This is because row elements are ColData types, so they have automatic escaping and quoting, as appropriate to the type being inserted. If you want to disable this feature, it's easily done: click the ColData link for the details.

Users of this example should beware that one more check is required in order to run this query safely: in some extreme cases, the size of the query might grow larger than MySQL's maximum allowed packet size. This check should be added.