A result of this operator overloading is the translation of the SQL-oriented syntax into calls to the regular interface in Listing One. This also explains why Examples 1(b) and 1(c) were separated. Gathering the binding information is executed while the temporary object is "traveling" through the expression, swallowing IntoTypePtr and UseTypePtr objects, whereas the actual binding of all variables is performed at the end of the expression, when the temporary object is destroyed.
The type of temporary object also has overloaded inserter operator<<. Thanks to this, the full expression can contain many insertions and commas. Every insertion is delegated to the underlying string stream object (this also means that you can build the query from the objects of your own types, if they are IOStreams aware) and every comma accumulates the binding info for the consecutive placeholders.
What I've described covers the case when the query is to be executed exactly once. Of course, database access needs can be rarely satisfied by the possibility of only executing one-time queries, so there is also a facility to just prepare statements for execution, leaving it up to you to execute the query and fetch the consecutive rows (if it is a "select" query).
The important thing is that the implementation of into and use functions (Listing Two) do not do much—they only create objects of special types, which need to follow a given interface. Depending on the type of the variable that is bound to the SQL placeholder, a specific specialization of the IntoType<> or UseType<> templates is used. This makes it an extremely extensible mechanism, which can be used to plug in user-defined types into the library. The only thing that is needed for every supported variable type is to write a specialization for the IntoType<> and UseType<> templates (the library itself is a good example of how to do this). Of course, the library contains specializations for commonly used types.
Examples
Listing Three presents examples that put the library to work. Of course, to run this code you need to provide true database credentials (service name, username, and user password) and prepare the database tables that make the example SQL statements valid.
Listing Three
// example program
#include "soci.h"
#include <iostream>
using namespace std;
using namespace SOCI;
int main()
{
try
{
Session sql("DBNAME", "user", "password");
// example 1. - basic query with one variable used
int count;
sql << "select count(*) from some_table", into(count);
// example 2. - basic query with parameter
int id = 7;
string name;
sql << "select name from person where id = " << id, into(name);
// example 3. - the same, but with input variable
sql << "select name from person where id = :id", into(name), use(id);
// example 4. - statement with no output
id = 8;
name = "John";
sql << "insert into person(id, name) values(:id, :name)",
use(id), use(name);
// example 5. - statement used multiple (three) times
Statement st1 = (sql.prepare <<
"insert into country(id, name) values(:id, :name)",
use(id), use(name));
id = 1; name = "France"; st1.execute(1);
id = 2; name = "Germany"; st1.execute(1);
id = 3; name = "Poland"; st1.execute(1);
// example 6. - statement used for fetching many rows
Statement st2 = (sql.prepare <<
"select name from country", into(name));
st2.execute();
while (st2.fetch())
{
cout << name << '\n';
}
}
catch (exception const &e)
{
cerr << "Error: " << e.what() << '\n';
}
}
A test driver accompanies the library (available electronically), which is self-contained in that it prepares the required database structures by itself. You may find this test driver to be a valuable source of information about what can be really done with the library.
Afterthought: Syntax-First Library Development
One of the biggest contributions of the eXtreme Programming (XP) method is its focus on test-driven development. As a rule of thumb, in XP the test unit is written before the code that is supposed to make the test pass. The result is code that exactly meets its requirements. It can be beneficial to apply a similar concept on another level of code design.
When implementing a library that is meant to provide some particular functionality, the key design problem is to choose the interface of the library. Sadly, most libraries seem to be developed in the "back-end to front-end" direction, where some low-level concepts (network connectivity, database access, filesystem operations, GUI, and the like) are simply wrapped into high-level language structures, hiding some of the underlying complexity but still revealing the fundamental low-level conventions. Listing One presents two classes that together can be considered a poor man's database library. Such libraries have little added value and in some extreme cases can even be a disservice to the entire language community by suggesting that the ability of the high-level language is limited to only provide simple wrappers for what is always available to the C programmers via the low-level APIs. I have heard such claims about the C++ language made by C programmers. The library I present here is based on an approach I call "syntax-first library development," which is exactly the reverse of this scenario.
The way I built the library was to set up the intended syntax before writing a single line of code. After that, I went through a head-scratching and pen-biting phase to come up with the implementation that makes this syntax possible. Granted, the library employs tricks that some programmers may consider to be obscure, but those tricks are meant to be hidden from library users.
The thrust of the library design is similar to the test-first development proposed by the XP method and, by analogy, the syntax selected before implementing the library itself can be considered to be documentation for the library interface in the same way that test units are documentation for the code requirements in XP. Interestingly, syntax-first library development and test-first development can be used together in a library design and development, leading to libraries that are both expressive and well tested.
Maciej is a Ph.D. student at the Institute of Computer Science, Warsaw University of Technology. You can contact him at http://www.msobczak.com/.
