Let's get right to the code.
Example 2-1. Basic Example
dbstream<dbstatus> db(provider, username, password);
db.open( servername, catalog );
if( !db )
throw db.status();
query q( "create table T "
" ( t int NULL"
" , r real NULL"
" , s varchar(30) NULL"
" )" );
db << q;
if( !db )
throw db.status();
db.table( "T" );
db << 4 << 4.4 << "four" << endl
<< null << 5.5 << "five" << endl;
typedef struct { char *name; int number; } nvp;
static const nvp values[] =
{ { "four", 4 }
, { "six", 6 }
};
q = "select * from T ";
q << "where s = " << values[0].name
<< " and t = " << values[0].number;
db << q;
std::vector<npv> output(10);
for( int i(0); db && i < output.size(); db++, i++ ) {
db >> c("t") >> output[i].number
>> c("s") >> output[i].name;
}
Elements of a dbstreams program
dbstream<dbstatus> db(provider, username, password);
The dbstream
[1]
constructor's first argument is an enumerated type that indicates the kind of server we want to connect to or, more precisely, the native database library to use. It's called provider here because that's dbstreams's terminology: a dbstreams provider is simlilar to what's known as a driver in other contexts.
If the provider requested is not known to the dbstreams implementation, the constructor throws a std::runtime_error exception.
db.open( servername, catalog );
Just like an std::iostream: an open method. The catalog is optional because not all servers support one. This function connects to the server.
There is a companion close function. It is called automatically with the dbstream goes out of scope, just as you'd expect.
if( !db ) throw db.status();
The state of the stream can always be tested with operator!. if the native library produced an error message (most do), the default behavior is to write the message to standard error. (More detail can be had by writing the status object to standard error, too.)
db.table( "T" ); db << 4 << 4.4 << "four" << endl << null << 5.5 << "five" << endl;
Inserting data is as easy as setting the table and using the familiar operator<<. The dbstream constructs the appropriate insert statement, and some providers, such as the one for Sybase®, support faster, non-SQL features.
This example inserts in column order. A manipulator (seen later in reading results) can be used to control the column order.
q = "select * from T "; q << "where s = '" << values[0].name << "' and t = " << values[0].number;
The query object is basically a SQL-aware std::stringstream to facilitate building query strings.
Queries can also contain placeholders. The dbstream accepts the parameter descriptions and data. We'll get to that later.
db << q;
To execute a query, insert it into the stream.
for( int i(0); db && i < output.size(); db++, i++ ) {
db >> c("t") >> output[i].number
>> c("s") >> output[i].name;
} Data can be extracted in column order, but usually it's clearer if the columns are referenced by name. The manipulator c is used to indicate the column name.
Note the use of operator++ to advance to the next row, and the treatment of the dbstream as a boolean to test of end-of-data.
From this one example, you know all the basics of how to use a dbstream.
As with std::iostreams, there are very few methods that need to be called.
The SQL is passed verbatim to the provider, and thence to the server. The dbstream doesn't interpret it.
To distinguish between data and commands — setting/changing stream behavior or communicating with th server — this principle holds: Data are inserted and extracted literally, as built-in datatypes or std::string. Non-data are typically sent as dbstreams objects.
| [1] | Perhaps dbstream<dbstatus> should be basic_dbstream<dbstatus>. That would let us define dbstream as a typedef. The downside would be that the programmer would have no better choice (than presently) were he to derive his own class from dbstatus. That is, if you don't like typing "dbstream<dbstatus>" all the time, you're already free now to typedef it to, say, DbStream. |