3. Tutorial

The previous chapter introduced
the major top-level mechanisms in MySQL++. Now we’ll dig down a
little deeper and get into real examples. We start off with the basics
that every MySQL++ program will have to deal with, then work up to
more complex topics that are still widely interesting. You can stop
reading the manual after this chapter and still get a lot out of
MySQL++, ignoring the more advanced parts we present in later
chapters.

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 use RPMs instead, the example programs’ source
code and a simplified Makefile are in the
mysql++-devel package. They are typically
installed in
/usr/share/doc/mysql++-devel-*/examples, but it
can vary on different Linuxes.

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

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

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

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

MySQL++ comes with the exrun shell script
for Unixy systems, and the exrun.bat batch file
for Windows. You pass the example program and its arguments to the
exrun helper, which sets up the library search
path so that it will use the as-yet uninstalled version of the
MySQL++ library in preference to any other on your system:

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

That’s the typical form for a Unixy system. You leave
off the ./ bit on Windows. You can leave it
off on a Unixy system, too, if you have .
in your PATH. (Not a recommendation, just
an observation.)

All of the program arguments are optional.

If you don’t give -s,
the underlying MySQL C API (a.k.a. Connector/C)
assumes the server is on the local machine. It chooses
one of several different IPC options based on the platform
configuration. There are many different forms you can give
as server_addr with -s to
override this default behavior:

localhost — this is the
default; it doesn’t buy you anything

On Windows, a simple period tells the underlying MySQL C
API to use named pipes, if it’s available.

172.20.0.252:12345 — this would
connect to IP address
172.20.0.252 on TCP port
12345.

my.server.name:svc_name — this
would first look up TCP service name
svc_name in your system’s
network services database (/etc/services on
Unixy systems, and something like
c:\windows\system32\drivers\etc\services on
modern Windows variants). If it finds an entry for the service,
it then tries to connect to that port on the domain name
given.

For the TCP forms, you can mix names and numbers for the host
and port/service parts in any combination. If the server name
doesn’t contain a colon, it uses the default port,
3306.

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

If you don’t give -p, it will assume
the MySQL user doesn’t have a password. (One hopes this
isn’t the case...)

When running resetdb, the user name needs
to be for an account with permission to create the test database.
Once the database is created, you can use any account when running
the other examples that has DELETE, INSERT, SELECT and UPDATE
permissions for the test database. The MySQL root user can do all
this, of course, but you might want to set up a separate user,
having only the permissions necessary to work with the test
database:

CREATE USER mysqlpp_test@'%' IDENTIFIED BY ’nunyabinness';
GRANT ALL PRIVILEGES ON mysql_cpp_data.* TO mysqlpp_test@'%';

You could then create the sample database with the following
command:

./exrun resetdb -u mysqlpp_test -p nunyabinness

(Again, leave off the ./ bit on
Windows.)

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

See README-examples.txt for more
details on running the examples.

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:

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

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

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

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

3.3. A More Complicated Example

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

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.
We’ve been suppressing this in all the examples so far by
passing false to
Connection’s constructor. This kept
these early examples simple at the cost of some flexibility and
power in error handling. In a real program, we recommend that you
leave exceptions enabled. You do this by either using the default
Connection constructor, or by using the
create-and-connect constructor.

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

When exceptions are suppressed, MySQL++ signals errors
by returning either an error code or an object that tests
as false, or by setting an error flag on the object. Classes
that allow you to suppress exceptions derive from the OptionalExceptions interface. When
an OptionalExceptions derivative
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. This is why passing false for the
Connection constructor’s “throw
exceptions” parameter suppresses all optional exceptions
in the simple[1-3] examples. It keeps them,
well, simple.

This exception suppression mechanism is quite granular.
It’s possible to leave exceptions enabled most of the time,
but suppress them in sections of the code where they aren’t
helpful. To do this, put the section of code that you want to not
throw exceptions inside a block, and create a NoExceptions object at the top of that block. When created,
it saves the exception flag of the
OptionalExceptions derivative you pass to it,
and then disables exceptions on it. When the
NoExceptions object goes out of scope at the
end of the block, it restores the exceptions flag to its previous
state:

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

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

MySQL++ throws some exceptions unconditionally:

MySQL++ checks array indices,
always. For instance, if your code said
“row[21]” on a
row containing only 5 fields, you’d get a
BadIndex exception. If you
say “row["fred"]”
on a row without a “fred” field, you get
a BadFieldName exception. In
the past, MySQL++ delegated some of its index checking
to the STL containers underpinning it, so you could get
std::range_error instead. As of MySQL++
v3.0.7, this should no longer happen, but there may be instances
where it still does.

String 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 use a C++ data type in a query
that MySQL++ doesn’t know to convert to SQL, MySQL++
will throw a TypeLookupFailed
exception. It typically happens with Section 5, “Specialized SQL Structures”,
especially when using data types other than the ones defined
in lib/sql_types.h.

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

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:

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

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

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

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

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

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

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

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

3.6. C++ vs. SQL Data Types

The C++ and SQL data type systems have several differences
that can cause problems when using MySQL++, or any other SQL
based system, for that matter.

Most of the data types you can store in a SQL database are
either numbers or text strings. If you’re only looking at
the data going between the database server and your application,
there aren’t even numbers: SQL is a textual language, so
numbers and everything else gets transferred between the client
and the database server in text string form.[6] Consequently, MySQL++
has a lot of special support
for text strings, and can translate to several C++ numeric data
types transparently.

Some people worry that this translation via an intermediate
string form will cause data loss. Obviously the text string data
types are immune from problems in this regard. We’re also
confident that MySQL++ translates BLOB
and integer data types losslessly.

The biggest worry is with floating-point numbers. (The FLOAT
and DOUBLE SQL data types.) We did have a problem with this in
older versions of MySQL++, but we believe we fixed it completely
in v3.0.2. No one has since proven data loss via this path. There
is still a known problem
[7]
with the SQL DECIMAL type, which is somewhat related to the
floating-point issue, but it’s apparently rarely encountered,
which is why it hasn’t been fixed yet.

The best way to avoid problems with data translation
is to always use the special MySQL++ data types defined in
lib/sql_types.h corresponding to your
SQL schema. These typedefs begin with sql_ and
end with a lowercase version of the standard SQL type name,
with spaces replaced by underscores. There are variants
ending in _null that wrap these base types
so they’re compatible with
SQL null. For instance, the SQL type TINYINT
UNSIGNED NOT NULL is represented in MySQL++ by
mysqlpp::sql_tinyint_unsigned. If you drop
the NOT NULL part, the corresponding C++ type is
mysqlpp::sql_tinyint_unsigned_null.

MySQL++ doesn’t force you to use these typedefs. It
tries to be flexible with regard to data conversions,
so you could probably use int anywhere you
use mysqlpp::sql_tinyint_unsigned,
for example. That said, the MySQL++ typedefs give several
advantages:

Space efficiency: the MySQL++ types are no
larger than necessary to hold the MySQL data.

Portability: if your program has to run on
multiple different system types (even just 32- and 64-bit
versions of the same operating system and processor type)
using the MySQL++ typedefs insulates your code from platform
changes.

Clarity: using C++ types named similarly to the
SQL types reduces the risk of confusion when working with code in
both languages at the same time.

Compatibility: using the MySQL++ types ensures
that data conversions between SQL and C++ forms are compatible.
Naïve use of plain old C++ types can result in data
truncation, TypeLookupFailed
exceptions, and worse.

Type compatibility is important not just at the time
you write your program, it also helps forward compatibility:
we occasionally change the definitions of the MySQL++
typedefs to reduce the differences between the C++
and SQL type systems. We’ll be fixing the DECIMAL issue
brought up above this way, for instance; if your program
uses sql_decimal instead of the
current underlying type, double, your program
will pick up this improvement automatically with just a
recompile.

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.

MySQL++ doesn’t have typedefs for the most exotic data
types, like those for the geospatial types. Patches to correct
this will be thoughtfully considered.

3.7. Handling SQL Nulls

Both C++ and SQL have things in them called NULL, but they
differ in several ways. Consequently, MySQL++ has to provide
special support for this, rather than just wrap native C++
facilities as it can with most data type issues.

SQL NULL is a type modifier

The primary distinction is one of type. In SQL,
“NULL” is a type modifier, which affects whether
you can legally store a null value in that column. There’s
simply nothing like it in C++.

To emulate SQL NULL, MySQL++ provides the Null template to allow
the creation of distinct “nullable” versions of
existing C++ types. So for example, if you have a TINYINT
UNSIGNED column that can have nulls, the proper
declaration for MySQL++ would be:

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

As of MySQL++ 3.1, we also provide shorter aliases for
such types:

mysqlpp::sql_tinyint_unsigned_null myfield;

These types are declared in
lib/sql_types.h. You might want to scan
through that to see what all is available.

Template instantiations are first-class types in the C++
language, so there’s no possible confusion between this
feature of MySQL++ and C++’s native NULL concept.

SQL NULL is a unique value

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;

If you insert a MySQL++ field holding a SQL null into a
C++ IOstream, you get “(NULL)”, something fairly
unlikely to be in a normal output string, thus reasonably
preserving the uniqueness of the SQL null value.

MySQL++ also tries to enforce the uniqueness of the
SQL null value at compile time in assignments and data
conversions. If you try to store a SQL null in a field type
that isn’t wrapped by Null
or try to assign a Null-wrapped
field value to a variable of the inner non-wrapped type,
the compiler will emit some ugly error message, yelling about
CannotConvertNullToAnyOtherDataType. (The exact
message is compiler-dependent.)

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

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

3.8. MySQL++’s Special String Types

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

SQLTypeAdapter

As its name suggests, its only purpose is to adapt other
data types to be used with SQL. It has a whole bunch of conversion
constructors, one for all data types we expect to be used with
MySQL++ for values in queries. SQL queries are strings, so
constructors that take stringish types just make a copy of that
string, and all the others “stringize” the value in
the format needed by
SQL.[9] The conversion
constructors preserve type information, so this stringization
process doesn’t throw away any essential information.

STA is used anywhere MySQL++ needs to
be able to accept any of several data types for use in a SQL
query. Major users are Query’s
template query mechanism and the Query
stream quoting and escaping mechanism. You care about
STA because any time you pass a data value
to MySQL++ to be used in building a SQL query, it goes through
STA. STA is one of
the key pieces in MySQL++ that makes it easy to generate
syntactically-correct SQL queries.

String

If MySQL++ can be said to have its own generic string type,
it’s String, but it’s not
really functional enough for general use. It’s possible that
in future versions of MySQL++ we’ll expand its interface to
include everything std::string does, so
that’s why it’s called that.[10]

The key thing String provides over
std::string is conversion of strings in SQL
value formats to their plain old C++ data types. For example, if you
initialize it with the string “2007-11-19”, you can
assign the String to a Date, not because
Date knows how to initialize itself from
String, but the reverse:
String has a bunch of implicit conversion
operators defined for it, so you can use it in any type context
that makes sense in your application.

Because Row::operator[] returns
String, you can say things like
this:

int x = row["x"];

In a very real sense, String is the
inverse of STA:
String converts SQL value strings to C++
data types, and STA converts C++ data types
to SQL value strings.[11]

String has two main uses.

By far the most common use is as the field value type of
Row, as exemplified above. It’s not
just the return type of Row::operator[],
though: it’s actually the value type used within
Row’s internal array. As a result,
any time MySQL++ pulls data from the database, it goes through
String when converting it from the string
form used in SQL result sets to the C++ data type you actually
want the data in. It’s the core of the structure population
mechanism in the SSQLS feature, for
example.

Because String is the last pristine
form of data in a result set before it gets out of MySQL++’s
internals where end-user code can see it, MySQL++’s
sql_blob and related typedefs are
aliases for String. Using anything else
would require copies; while the whole “networked database
server” thing means most of MySQL++ can be quite inefficient
and still not affect benchmark results meaningfully, BLOBs tend to
be big, so making unnecessary copies can really make a difference.
Which brings us to...

Reference Counting

To avoid unnecessary buffer copies, both
STA and String
are implemented in terms of a reference-counted copy-on-write
buffer scheme. Both classes share the same underlying mechanism,
and so are interoperable. This means that if you construct
one of these objects from another, it doesn’t actually
copy the string data, it only copies a pointer to the data
buffer, and increments its reference count. If the object
has new data assigned to it or it’s otherwise modified,
it decrements its reference count and creates its own copy of
the buffer. This has a lot of practical import, such as the
fact that even though Row::operator[]
returns Strings by value, it’s
still efficient.

3.9. Dealing with Binary Data

Historically, there was no way to hold arbitrary-sized blocks
of raw binary data in an SQL database. There was resistance to
adding such a feature to SQL for a long time because it’s
better, where possible, to decompose blocks of raw binary data into
a series of numbers and text strings that can
be stored in the database. This lets you query, address and
manipulate elements of the data block individually.

A classic SQL newbie mistake is trying to treat the database
server as a file system. Some embedded platforms use a database
engine as a file system, but MySQL doesn’t typically live
in that world. When your platform already has a perfectly good
file system, you should use it for big, nondecomposable blocks
of binary data in most cases.

A common example people use when discussing this is images
in database-backed web applications. If you store the image in the
database, you have to write code to retrieve the image from the
database and send it to the client; there’s more overhead,
and less efficient use of the system’s I/O caching system. If
you store the image in the filesystem, all you have to do is
point the web server to the directory where the images live,
and put a URL for that image in your generated HTML. Because
you’re giving the web server a direct path to a file on
disk, operation is far more efficient. Web servers are very
good at slurping whole files off of disk and sending them out
to the network, and operating systems are very good at caching
file accesses. Plus, you avoid the overhead of pushing the data
through the high-level language your web app is written in, which
is typically an interpreted language, not C++. Some people still
hold out on this, claiming that database engines have superior
security features, but I call bunk on that, too. Operating systems
and web servers are capable of building access control systems
every bit as granular and secure as a database system.

Occasionally you really do need to store a nondecomposable
block of binary data in the database. For such cases, modern
SQL database servers support BLOB data types, for Binary Large
OBject. This is often just called binary data, though of course
all data in a modern computer is binary at some level.

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. We’ve made a lot of improvements
to the way MySQL++ handles string
data to avoid this problem, but it’s still possible
to bypass these features, wrecking your BLOBs. These examples
demonstrate correct techniques.

Loading a binary file into a BLOB column

Above, I opined that it’s usually incorrect to
store image data in a database, particularly with web apps,
of which CGI is a primitive form. Still, it makes a nice,
simple example.

Instead of a single example program, we have here a
matched pair. The first example takes the name of a JPEG
file on the command line along with all the other common example program parameters,
loads that file into memory, and stores it in a BLOB column in
the database.

This example also demonstrates how to retrieve the
value assigned to an auto-increment column in the previous
insertion. This example uses that feature in the typical way,
to create unique IDs for rows as they’re inserted.

Notice that we used the escape manipulator
when building the INSERT query above. This is because
mysqlpp::sql_blob is just an alias for one of the
special MySQL++ string types,
which don’t do automatic quoting
and escaping. They can’t, because MySQL++ also
uses these data types to hold raw SQL query strings, which
would break due to doubled quoting and/or escaping if it were
automatic.

Serving images from BLOB column via CGI

The other example in this pair is rather short,
considering how much it does. It parses a CGI query string
giving the image ID, uses that to retreive data loaded into
the database by load_jpeg, and writes
it out in the form a web server wants when processing a CGI
call, all with adequate real-world error handling. This is
examples/cgi_jpeg.cpp:

While you can run it by hand, it’s
best to install this in a web server’s
CGI program directory, 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 database and
returns it to the web server, which will send it on to the
browser.

We’ve included an image with MySQL++
that you can use with this example pair,
examples/logo.jpg.

3.10. Using Transactions

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

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

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

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

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

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

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 SimpleResult object. In addition to the obvious — a
flag stating whether the query succeeded or not — this object
also contains things like the number of rows that the query
affected. If you only need the success status, it’s a little
more efficient to call Query::exec()
instead, as it simply returns bool.

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

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

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

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

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

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

3.12. Conditional Result Row Handling

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

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

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

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

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.14. Connection Options

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

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

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

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

3.15. Dealing with Connection Timeouts

By default, current MySQL servers have an 8 hour idle
timeout on connections. This is not a problem if your program
never has to run for more than 8 hours or reliably queries the
database more often than that. And, it’s a good thing for
the database server, because even an idle connection takes up
server resources.

Many programs must run continually, however, and may
experience long idle periods, such as nights and weekends
when no one is around to make the program issue database
queries. It’s therefore common for people writing such
programs to get a bug report from the field complaining that the
program died overnight or over a long weekend, usually with some
error message about the database server going away. They then check
the DB server, find that it’s still running and never did
restart and scratch their heads wondering what happened. What
happened is that the server’s connection idle timeout
expired, so it closed the connection to the client.

You cannot detect this condition by calling
Connection::connected(). When
that returns true, it just means
that either the connect-on-create constructor or the
connect() call succeeded and that we
haven’t observed the connection to be down since then.
When the database server closes an idle connection, you won’t
know it until after you try to issue a query. This is simply due
to the nature of network programming.

One way around this problem is to configure
MySQL to have a longer idle timeout. This timeout is
in seconds, so the default of 8 hours is 28,800 seconds. You
would want to figure out the longest possible time that your
program could be left idle, then pick a value somewhat longer
than that. For instance, you might decide that the longest
reasonable idle time is a long 4-day weekend — 345,600
seconds — which you could round up to 350,000 or 400,000
to allow for a little bit of additional idle time on either end
of that period.

Another way around this, on a per-connection basis from
the client side, would be to set the ReconnectOptionconnection
option. This will cause MySQL++ to reconnect to the server
automatically if it drops the connection. Beware that unless
you’re using MySQL 5.1.6 or higher, you have to set this
only after the connection is established, or it won’t take
effect. This means there’s a potential race condition:
it’s possible the connection could drop shortly enough
after being established that you don’t have time to apply
the option, so it won’t come back up automatically. MySQL
5.1.6+ fixes this by allowing this option to be set before the
connection is established.

A completely different way to tackle this, if your program
doesn’t block forever waiting on I/O while idle, is to
periodically call Connection::ping().
[12]
This sends the smallest possible amount of data to the
database server, which will reset its idle timer and cause
it to respond, so ping() returns
true. If it returns false
instead, you know you need to reconnect to the server. Periodic
pinging is easiest to do if your program uses asynchronous I/O,
threads, or some kind of event
loop to ensure that you can call something periodically even
while the rest of the program has nothing to do.

An interesting variant on this strategy is to ping the server
before each query, or, better, before each group of queries within
a larger operation. It has an advantage over pinging during idle
time in that the client is about to use far more server resources
to handle the query than it will take to handle the ping, so the
ping time gets lost in the overhead. On the other hand, if the
client issues queries frequently when not idle, it can result
in a lot more pings than would happen if you just pinged every
N hours while idle.

Finally, some programmers prefer to wrap the querying
mechanism in an error handler that catches the “server has
gone away” error and tries to reestablish the connection and
reissue the query. This adds some complexity, but it makes your
program more robust without taking up unnecessary resources. If you
did this, you could even change the server to drop idle connections
more often, thus tying up fewer TCP/IP stack resources.

3.16. Concurrent Queries on a Connection

An important limitation of the MySQL C API library —
which MySQL++ is built atop, so it shares this limitation —
is that you can only have one query in progress on each connection
to the database server. If you try to issue a second query while
one is still in progress, you get an obscure error message about
“Commands out of sync” from the underlying C API
library. (You normally get this message in a MySQL++ exception
unless you have exceptions disabled, in which case you get a
failure code and Connection::error()
returns this message.)

There are lots of ways to run into this limitation:

The easiest way is to try to use a single Connection object in a multithreaded
program, with more than one thread attempting to use it to
issue queries. Unless you put in a lot of work to synchronize
access, this is almost guaranteed to fail at some point, giving
the dread “Commands out of sync” error.

You might then think to give each thread that issues
queries its own Connection object.
You can still run into trouble if you pass the data you get
from queries around to other threads. What can happen is
that one of these child objects indirectly calls back to the
Connection at a time where it’s
involved with another query. This is properly covered
elsewhere, in Section 7.4, “Sharing MySQL++ Data Structures”.)

One way to run into this problem without using
threads is with “use” queries, discussed above. If you don’t
consume all rows from a query before you issue another on
that connection, you are effectively trying to have multiple
concurrent queries on a single connection. Here’s a
recipie for this particular disaster:

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

Still another way to run into this limitation
is if you use MySQL’s multi-query feature. This
lets you give multiple queries in a single call,
separated by semicolons, and get back the results for
each query separately. If you issue three queries using
Query::store(), you only get
back the first query’s results with that call, and
then have to call store_next()
to get the subsequent query results. MySQL++ provides
Query::more_results() so
you know whether you’re done, or need to call
store_next() again. Until you reach
the last result set, you can’t issue another query on
that connection.

Finally, there’s a way to run into this
that surprises almost everyone sooner or later: stored
procedures. MySQL normally returns at least
two result sets for a stored procedure call. The
simple case is that the stored procedure contains a single
SQL query, and it succeeds: you get two results, first the
results of the embedded SQL query, and then the result
of the call itself. If there are multiple SQL queries
within the stored procedure, you get more than two result
sets. Until you consume them all, you can’t start a
new query on the connection. As above, you want to have
a loop calling more_results()
and store_next() to work your
way through all of the result sets produced by the stored
procedure call.

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

[2] SQLQueryParms is used as a
stream only as an implementation detail within the library. End user
code simply sees it as a std::vector
derivative.

[3] By contrast, the
Query methods that take an SSQLSdo add quotes and
escape strings implicitly. It can do this because SSQLS knows all
the SQL code and data types, so it never has to guess whether
quoting or escaping is appropriate.

[4] Unless you’re smarter than I am, you
don’t immediately see why explicit manipulators are necessary.
We can tell when quoting and escaping is not
appropriate based on type, so doesn’t that mean we know when
it is appropriate? Alas, no. For most data
types, it is possible to know, or at least make an awfully good
guess, but it’s a complete toss-up for C strings, const
char*. A C string could be either a literal string of SQL
code, or it can be a value used in a query. Since there’s no
easy way to know and it would damage the library’s usability
to mandate that C strings only be used for one purpose or the other,
the library requires you to be explicit.

[6] Yes,
we’re aware that there is a feature in MySQL that lets you
transfer row data in a binary form, but we don’t support
this yet. We may, someday, probably as an extension to SSQLS. The only real reason to do so
is to shave off some of the data translation overhead, which
is typically neglibible in practice, swamped by the far greater
disk and network I/O overheads inherent in use of a client-server
database system like MySQL.

[7] SQL’s DECIMAL data type is a configurable-precision
fixed-point number format. MySQL++ currently translates these to
double, a floating-point data format, the closest
thing available in the C++ type system. Since the main reason
to use DECIMAL is to get away from the weird roundoff behavior
of floating-point numbers, this could be viewed as a serious
problem. The thing is, though, in all the years MySQL++ has
been around, I don’t remember anyone actually complaining
about it. Apparently there’s either no one using DECIMAL
with MySQL++, or they’re ignoring any roundoff errors
they get as a result. Until this wheel squeaks, it’s not
likely to be greased. To fix this, we’ll have to create
a new custom data type to hold such column values, which will
be a lot of work for apparently little return.

[8] In version 2
of MySQL++ and earlier, SQLTypeAdapter was
called SQLString, but it was confusing
because its name and the fact that it derived from
std::string suggested that it was a
general-purpose string type. MySQL++ even used it this way in a
few places internally. In v3, we made it a simple base class and
renamed it to reflect its proper limited
function.

[9] SQLTypeAdapter
doesn’t do quoting and escaping itself. That happens
elsewhere, right at the point that the STA
gets used to build a query.

[10] If you
used MySQL++ before v3, String used to be
called ColData. It was renamed because
starting in v2.3, we began using it for holding more than just
column data. I considered renaming it
SQLString instead, but that would have
confused old MySQL++ users to no end. Instead, I followed the
example of Set, MySQL++’s specialized
std::set variant.

[11] During the development of
MySQL++ v3.0, I tried merging
SQLTypeAdapter and
String into a single class to take
advantage of this. The resulting class gave the C++ compiler the
freedom to tie itself up in knots, because it was then allowed to
convert almost any data type to almost any other. You’d get
a tangle of ambiguous data type conversion errors from the most
innocent code.

[12] Don’t ping the server too often! It takes a tiny
amount of processing capability to handle a ping, which can add
up to a significant amount if done often enough by a client, or
even just rarely by enough clients. Also, a lower ping frequency
can let your program ride through some types of network faults
— a switch reboot, for instance — without needing
a reconnect. I like to ping the DB server no more often than
half the connection timeout. With the default of 8 hours, then,
I’d ping between every 4 and 7 hours.