SQL Database Support


Detailed Description

While looking around at other API's implementing SQL Database Supprt, especially Qt's (as we already have a fair few Qt API's), I was struck at how few bothered to leverage C++ metaprogramming to help ease query parameter binding. After all, most of one's time when working with databases is reading result rows and thus any help here would surely be wonderful! Therefore, via metaprogramming, TnFOX's SQL Database Support automatically maps SQL92 data types to TnFOX ones and vice versa, with further support for serialising and deserialising arbitrary C++ types into and out from SQL BLOB types via FX::FXStream. Indeed, unless you are working with weird databases, for the most part you can forget about binding.

The Drivers:

The core of the TnFOX SQL Database Support is FX::TnFXSQLDB which is the abstract base class of SQL Database drivers in TnFOX. To use it, simply instantiate an implementation via FX::TnFXSQLDBRegistry which is the future-proof method (which in the future may load in a driver DLL).

The drivers currently provided are:

If you want a driver for your particular database, it's very easy to implement your own - just see the sources. Please do consider donating your new driver back to the TnFOX project so your hard work need not be repeated by others. Probably at some point an ODBC driver will be implemented.

Quick Usage Example:

FXAutoPtr<TnFXSQLDB> mydb=TnFXSQLDBRegistry::make("SQLite3", dbname);
mydb->open();
mydb->immediate("CREATE TABLE test(id INTEGER PRIMARY KEY, 'value' INTEGER, 'text' VARCHAR(256), 'when' TIMESTAMP DEFAULT CURRENT_TIMESTAMP);");

TnFXSQLDBStatementRef s=db->prepare("SELECT :field FROM 'test' WHERE :field=:value;");
s->bind(":field", "test");
s->bind(":value", (FXint) 5);
for(TnFXSQLDBCursorRef c=s->execute(); !c->atEnd(); c->next())
{
  fxmessage("Entry %d: %d\n", c->at(), c->data(0)->get<FXint>(v));
}
Obviously, the binding of arguments is pretty pointless in this example as the prepared statement is used only once, so it would be quicker to use db->execute() directly. However, there is an important caveat - if you place values directly into a string, you must escape them in a SQL92 compatible way which isn't required if you simply bind in the values.

To give you some idea of the power of TnFOX, this is from the AllTests application in the Test suite:

// No other C++ toolkit can bz2 decompress from a SQL query BLOB in so little code!
QByteArray blob;
cur->data(7)->get<>(blob);
QBuffer buff(blob);
QBZip2Device bz2(&buff);
bz2.open(IO_ReadOnly);
FXStream s(&bz2);
s >> output;
Yes, that tiny amount of code fetches a BLOB from column 7 into a FX::QByteArray (by reference, thus not copying any memory), points a FX::QBuffer at it, a FX::QBZip2Device filter on that, then uses a FX::FXStream to read a FX::FXString!

TODO:


Classes

class  FX::TnFXSQLDB
 The abstract base class of a SQL database driver. More...
class  FX::TnFXSQLDBTransaction
 Manages a SQL database transaction. More...
class  FX::TnFXSQLDBCursor
 Abstract base class for a cursor which can iterate through the results of executing a statement. More...
class  FX::TnFXSQLDBStatement
 The abstract base class of a prepared SQL statement. More...
class  FX::TnFXSQLDBColumn
 Represents information about a column in a row. More...
class  FX::TnFXSQLDBRegistry
 Knows of all currently available FX::TnFXSQLDB's. More...
class  FX::TnFXSQLDB_ipc
 A SQL database driver for a FX::FXIPCChannel. More...
class  FX::TnFXSQLDB_sqlite3
 A SQL database driver for SQLite3. More...


(C) 2002-2008 Niall Douglas. Some parts (C) to assorted authors.
Generated on Fri Jun 13 21:55:23 2008 for TnFOX by doxygen v1.5.6