2. Overview

Libtabula has a lot of complexity and power to cope with the variety of DBMSes and uses for them, but at bottom it doesn’t work all that differently than other database access APIs. The usage pattern looks like this:

  1. Open the connection

  2. Form and execute the query

  3. If successful, iterate through the result set

  4. Else, deal with errors

Each of these steps corresponds to a libtabula class or class hierarchy. An overview of each follows.

2.1. The Connection Object

A Connection object manages the connection to the MySQL server. You need at least one of these objects to do anything. Because the other libtabula objects your program will use often depend (at least indirectly) on the Connection instance, the Connection object needs to live at least as long as all other libtabula objects in your program.

MySQL supports many different types of data connection between the client and the server: TCP/IP, Unix domain sockets, and Windows named pipes. The generic Connection class supports all of these, figuring out which one you mean based on the parameters you pass to Connection::connect(). But if you know in advance that your program only needs one particular connection type, there are subclasses with simpler interfaces. For example, there’s TCPConnection if you know your program will always use a networked database server.

2.2. The Query Object

Most often, you create SQL queries using a Query object created by the Connection object.

Query acts as a standard C++ output stream, so you can write data to it like you would to std::cout or std::ostringstream. This is the most C++ish way libtabula provides for building up a query string. The library includes stream manipulators that are type-aware so it’s easy to build up syntactically-correct SQL.

Query also has a feature called Template Queries which work something like C’s printf() function: you set up a fixed query string with tags inside that indicate where to insert the variable parts. If you have multiple queries that are structurally similar, you simply set up one template query, and use that in the various locations of your program.

A third method for building queries is to use Query with SSQLS. This feature lets you create C++ structures that mirror your database schemas. These in turn give Query the information it needs to build many common SQL queries for you. It can INSERT, REPLACE and UPDATE rows in a table given the data in SSQLS form. It can also generate SELECT * FROM SomeTable queries and store the results as an STL collection of SSQLSes.

2.3. Result Sets

The field data in a result set are stored in a special std::string-like class called String. This class has conversion operators that let you automatically convert these objects to any of the basic C data types. Additionally, libtabula defines classes like DateTime, which you can initialize from a MySQL DATETIME string. These automatic conversions are protected against bad conversions, and can either set a warning flag or throw an exception, depending on how you set the library up.

As for the result sets as a whole, libtabula has a number of different ways of representing them:

Queries That Do Not Return Data

Not all SQL queries return data. An example is CREATE TABLE. For these types of queries, there is a special result type (SimpleResult) that simply reports the state resulting from the query: whether the query was successful, how many rows it impacted (if any), etc.

Queries That Return Data: Libtabula Data Structures

The most direct way to retrieve a result set is to use Query::store(). This returns a StoreQueryResult object, which derives from std::vector<libtabula::Row>, making it a random-access container of Rows. In turn, each Row object is like a std::vector of String objects, one for each field in the result set. Therefore, you can treat StoreQueryResult as a two-dimensional array: you can get the 5th field on the 2nd row by simply saying result[1][4]. You can also access row elements by field name, like this: result[2]["price"].

A less direct way of working with query results is to use Query::use(), which returns a UseQueryResult object. This class acts like an STL input iterator rather than a std::vector: you walk through your result set processing one row at a time, always going forward. You can’t seek around in the result set, and you can’t know how many results are in the set until you find the end. In payment for that inconvenience, you get better memory efficiency, because the entire result set doesn’t need to be stored in RAM. This is very useful when you need large result sets.

Queries That Return Data: Specialized SQL Structures

Accessing results through libtabula’s data structures is a pretty low level of abstraction. It’s better than using the MySQL C API, but not by much. You can elevate things a little closer to the level of the problem space by using the SSQLS feature. This lets you define C++ structures that match the table structures in your database schema. In addition, it’s easy to use SSQLSes with regular STL containers (and thus, algorithms) so you don’t have to deal with the quirks of libtabula’s data structures.

The advantage of this method is that your program will require very little embedded SQL code. You can simply execute a query, and receive your results as C++ data structures, which can be accessed just as you would any other structure. The results can be accessed through the Row object, or you can ask the library to dump the results into an STL container — sequential or set-associative, it doesn’t matter — for you. Consider this:

vector<stock> v;
query << "SELECT * FROM stock";
for (vector<stock>::iterator it = v.begin(); it != v.end(); ++it) {
  cout << "Price: " << it->price << endl;

Isn’t that slick?

If you don’t want to create SSQLSes to match your table structures, libtabula lets you use Row here instead:

vector<libtabula::Row> v;
query << "SELECT * FROM stock";
for (vector<libtabula::Row>::iterator it = v.begin(); it != v.end(); ++it) {
  cout << "Price: " << it->at("price") << endl;

It lacks a certain syntactic elegance, but it has its uses.

2.4. Exceptions

By default, the library throws exceptions whenever it encounters an error. You can ask the library to set an error flag instead, if you like, but the exceptions carry more information. Not only do they include a string member telling you why the exception was thrown, there are several exception types, so you can distinguish between different error types within a single try block.