3. Tutorial
Prev |  | Next

## 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 particular the Standard Template Library (STL) and exceptions.

### 3.1. Running the Examples

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 installed it via the RPM package, the example source code and a simplified Makefile is in the examples subdirectory of the mysql++-devel package's documentation directory. (This is usually `/usr/share/doc/mysql++-devel-*`, 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 run it like so:

            ./exrun resetdb [host [user [password [port]]]]

`exrun` is a shell script that ensures that the MySQL++ example program you give as its first argument finds the correct shared library version. If you run the example program directly, it will search the system directories for the MySQL++ shared library. That will only work correctly if you've installed the library before running the examples. You should run the examples before installing the library to ensure that the library is working correctly, thus `exrun`. See `README.examples` for more details. (We've been using POSIX file and path names for simplicity above, but there's a Windows version of `exrun`, called `exrun.bat`. It works the same way.)

As for the remaining program arguments, they are all optional, but they must be in the order listed. If you leave off the port number, it uses the default value, 3306. If you leave off the password, it assumes you don't need one to log in. If you leave off the user name, it uses the name you used when logging on to the computer. And if you leave off the host name, it assumes the MySQL server is running on the local host. A typical invocation is:

            ./exrun resetdb localhost root 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.

### 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) {
            mysqlpp::Row row;
            mysqlpp::Row::size_type i;
            for (i = 0; row = res.at(i); ++i) {
                cout << '\t' << row.at(0) << 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++'s `Result` objects work similarly to the STL `std::vector` container. The only trick is that you can't use subscripting notation if the argument is ever 0, because of the way we do overloading, so it's safer to call `at()` instead.

The only thing that isn't explicit in the code above is that we delegate command line argument parsing and connection establishment to `connect_to_db()` in the `util` module. This function exists to give the examples a consistent interface, not to hide important details.

### 3.3. 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
            mysqlpp::Row row;
            mysqlpp::Row::size_type i;
            for (i = 0; row = res.at(i); ++i) {
                cout << setw(20) << row["item"] << ' ' <<
                        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;
    }

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.

### 3.4. 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 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` by reference. 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.

Some 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. See `examples/resetdb.cpp` to see this technique at work.

When one `OptionalExceptions` derivative passes its exceptions flag to another such object, it is only passing a copy. This means that 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 changed the exception throwing flag.

Some of the exceptions MySQL++ can throw 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::exception`s, 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.

- If you pass a bad option value to `Connection::set_option`, it will throw a `BadOption` 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.5. 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.6. 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.7. 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.8. 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<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;

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.9. 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.10. 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 `simple3`, but with exception-awareness.

### 3.11. Conditional Result Row Handling

`Query::store()` is fine if you really need all the rows the query returns. It sometimes happens that you can't express the full selection criteria in a SQL query. Instead of storing the full result set, then picking over it to find the rows you want to keep, use `Query::store_if()`. This is `examples/store_if.cpp`:

    #include "util.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 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[])
    {
        try {
            // Connect to the sample database
            mysqlpp::Connection con;
            if (!connect_to_db(argc, argv, con)) {
                return 1;
            }

            // 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. 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.

### 3.12. Executing Code for Each Row In a Result Set

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 new `Query::for_each()` facility might be just what you need. This is `examples/for_each.cpp`:

    #include "util.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[])
    {
        try {
            // Connect to the sample database
            mysqlpp::Connection con;
            if (!connect_to_db(argc, argv, con)) {
                return 1;
            }

            // 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.

### 3.13. 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.14. Dealing with Binary Data

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.

#### Loading a binary file into a BLOB column

This example shows how to insert binary data into a MySQL table's BLOB column with MySQL++, and also how to get the value of the auto-increment column from the previous insert. (This MySQL feature is usually used to create unique IDs for rows as they're inserted.) The program requires one command line parameter over that required by the other examples you've seen so far, the path to a JPEG file. This is `examples/load_jpeg.cpp`:

    #include "util.h"

    #include <mysql++.h>

    #include <fstream>

    using namespace std;
    using namespace mysqlpp;

    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[])
    {
        // 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_data;
        if ((argc > 1) && (argv[1][0] != '-')) {
            ifstream img_file(argv[argc - 1], ios::ate);
            if (img_file) {
                size_t img_size = img_file.tellg();
                if (img_size > 10) {
                    img_file.seekg(0, ios::beg);
                    char* img_buffer = new char[img_size];
                    img_file.read(img_buffer, img_size);
                    if (is_jpeg((unsigned char*)img_buffer)) {
                        img_data.assign(img_buffer, img_size);
                    }
                    else {
                        cerr << "File does not appear to be 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;
        }
        --argc;     // pop filename argument off end of list

        try {
            // Establish the connection to the database server.
            mysqlpp::Connection con(mysqlpp::use_exceptions);
            if (!connect_to_db(argc, argv, con)) {
                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 << "\")";
            ResNSel res = query.execute();

            // If we get here, insertion succeeded
            cout << "Inserted \"" << argv[argc] <<
                    "\" 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;
    }

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.

#### Serving images from BLOB column via CGI

This example is also a very short one, considering the function that it performs. It retreives data loaded by `load_jpeg` and prints it out in the form a web server can accept for a CGI call. This is `examples/cgi_jpeg.cpp`:

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

    using namespace std;
    using namespace mysqlpp;

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

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

        return 0;
    }

You install this in a web server's CGI program directory (usually called `cgi-bin`), then call it with a URL like `http://my.server.com/cgi-bin/cgi_jpeg?id=1`. That retrieves the JPEG with ID 1 from the table and returns it to the web server, which will send it on to the browser.

Prev | Up | Next
2. Overview | Home | 4. Template Queries