Techno News

Database Template Library (DTL)

The goal of DTL is to make Open Database Connectivity (ODBC) query results look just like an STL container. ODBC is an ISO standard that identifies a specific set of APIs to access SQL databases efficiently while hiding the databases' backend proprietary (a.k.a. "native") interfaces. Essentially, this allows you to write one set of source code that can pull data equally well from a Microsoft Excel spreadsheet on your PC or an IBM mainframe in another continent. You've likely used the ODBC Data Source Administrator applet in the Control Panel (ODBCCPL32.CPL) without even knowing it.
DTL provides all this in a portable abstraction, which can run on an impressive array of platforms and C++ compilers (see Table 1).

Compiler	OS	ODBC
Microsoft Visual C++ 6.0 and 7.x, Borland C++ 5.5 and 6.0	Windows NT, 2000, XP	Microsoft
GCC 3.2	Red Hat 7	unixODBC 2.x
GCC 3.3	Cygwin	Microsoft
GCC 2.95 and STLPort	FreeBSD	unixODBC 2.x
Sun Workshop 6	Solaris	Merant ODBC
aCC A.03.35	HP-UX	Merant ODBC

Table 1. Platforms and C++ Compilers on Which You Can Run DTL
DTL works with any ODBC 3.0, Level 1-compliant driver, including Oracle 8 and 9, SQL Server 2000, Access 2000, MySQL, Postgres, Sybase, and DB2.

STL Iterators and Containers: A New Face for Your Old Database

Now comes the best part: Programmatically, you can move through DTL containers using standard STL iterators. That's right, you don't need any proprietary, native, or unfamiliar API or syntax. If you insert(), erase(), or replace() records inside a DTL container, changes can be automatically migrated to the database for you. The library's compliance with the STL iterator and container standards means you can plug your abstractions into a wide variety of STL algorithms for data storage, searching, and manipulation. In addition, the C++ reflection mechanism DTL uses to bind to database tables enables generic indexing and lookup properties quite easily. Because DTL takes full advantage of the template mechanism, it adds minimal overhead compared with raw ODBC calls to access a database.

Hello DTL

So far, the discussion has been fairly abstract. Now, dive into a simple "hello world" program in DTL that prints the contents of a table. The program's function is (1) selecting all records from the SALES_Q3 table, (2) iterating through the entire recordset, and (3) pushing the data through cout:

 1 #include "DTL.h"
 2 #include <iostream>
 3 using namespace dtl;
 4 using namespace std;
 5
 6 int main()
 7 {
 8    try
 9    {
10    // Connect to the database
11    DBConnection::GetDefaultConnection().Connect(
        "UID=johndoe;PWD=secret;DSN=sales2005;");
12
13    // Create a container to hold records from a query.
14    // In this case, the query will be "SELECT * FROM sales_Q3".
15    DynamicDBView<> view("sales_Q3", "*");
16
17    // Read all rows from the database and send to cout
18    copy(view.begin(), view.end(),
           ostream_iterator<variant_row>(cout, "\n"));
19    }
20
21    catch (std::exception &ex)
22    {
23    // Show any database or other standard errors
24    cerr << ex.what() << endl;
25    }
26
27    return 0;
28 } 

The first thing to notice is that all the DTL routines can throw exceptions, so you'll need to enclose all DTL code in try/catch blocks. To select all records from the SALES_Q3 table, the DBConnection global object uses a standard ODBC syntax to specify user, password, and Data Source Name (DSN) in line #11. To iterate through the entire recordset, the code creates a DynamicDBView object (on line #15), which binds the query fields to a class called variant_row. Last, to push the data through cout, it uses the STL copy algorithm to cycle between two input iterators, view.begin() and view.end(), and in each case performs the assignment to cout. It's quite cool what just three lines of code can do!