Accessing Your Database with C++ Is as Easy as DTL

In the late 1980s, when I first learned to program in C, I cobbled together data structures and algorithms from books and pretended they were generic or they could be generalized, perhaps even by macros. Invariably, however, I would end up copying them or tweaking them for every new task that came along. A typical application in those days might have dozens of implementations of linked lists, each coded with varying degrees of usefulness.

Fortunately, the Standard Template Library (STL) came along in the mid-1990s, and the ANSI/ISO committee for C++ standards quickly embraced it. Thus, STL put an end to code cloning and by most accounts ushered in the era of generic programming. Other programming languages rarely match the power of the STL, even those that offer loads of built-in containers and iterators.

STL has been a major boon for C++ programmers. It gives them a ready-made set of common classes (such as containers and associative arrays) that they can use with any built-in type or any user-defined type that supports copying and assignment. STL includes sequence-based containers such as vector, string, and deque, as well as associative-based containers such as set, multiset, map, and multimap. To traverse these structures, you use input, output, forward, bidirectional, and random-access iterators as needed and appropriate to the container.

This article explores libraries and tools that go one step beyond the STL.

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!

Accessing Your Database with C++ Is as Easy as DTL

Associating Columns with Data Members

Go one step further and build a vector containing a user-defined object, a simple struct that you might use when creating a sales report. For a more concrete example, here's a little bit more about the sales_q3 table:

SQL> desc sales_q3;
Name                            Type
------------------------------- --------
VENDOR_ID                       INTEGER
ITEM_NAME                       VARCHAR
PRICE                           FLOAT
QTY_SOLD                        INTEGER
DATE_SOLD                       DATE

It's not hard to imagine a specific struct that will hold the bare essentials of your sales_q3 records:

struct sales_q3 {                // tablename.columnname:
   int vendorID;                 // sales_q3.VENDOR_ID
   string itemName;              // sales_q3.ITEM_NAME
   double price;                 // sales_q3.PRICE
   long qtySold;                 // sales_q3.QTY_SOLD
   TIMESTAMP_STRUCT dateSold;    // sales_q3.DATE_SOLD
};

DTL defines the mapping between columns and data members through a construct known as the Bind Column Addresses (BCA). The BCA specifically links an ODBC SQL query result field with a physical part of a struct or class. The following code snippet does this by declaring a DefaultBCA called sales_q3 with an () operator:

template<> class dtl::DefaultBCA<sales_q3>
{
public:
   void operator()(BoundIOs &cols, sales_q3 &rowbuf)
   {
      cols["INT_VALUE"] == rowbuf.vendorID;
      cols["STRING_VALUE"] == rowbuf.itemName;
      cols["DOUBLE_VALUE"] == rowbuf.price;
      cols["EXAMPLE_LONG"] == rowbuf.qtySold;
      cols["EXAMPLE_DATE"] == rowbuf.dateSold;
   }
};

Now, you can finally read the contents of the sales_q3 table and return a vector of the resulting rows:

vector<sales_q3> ReadData() {
   // Read the data
   vector<sales_q3> results;
   DBView<sales_q3> view("sales_q3");

   DBView<sales_q3>::select_iterator read_it = view.begin();
   for ( ; read_it != view.end(); ++read_it)
   {
      results.push_back(*read_it);
   }
   return results;
}

More DTL Iterators

This example uses the select_iterator to traverse a query result. If you were changing records in a "select for update" scenario, you would use the select_update_iterator (also an output iterator, of course). There are several other output ierators to choose from: insert_iterator, update_iterator, delete_iterator, and sql_iterator.

Unleashing STL Algorithms in DTL

Now, put the native STL algorithms to the test by constructing a TableDiff() function to compare the contents of two tables. In any other database library you've seen, this would require either (1) iterating through both tables yourself and manually inspecting the records or (2) using a series of SQL commands to dump both tables into a temporary table, a SELECT UNIQUE from the temp table, and another temp table to do a SELECT with a "NOT IN" sub-query, etc. With DTL, you can just hand off the dirty work to set_difference (see lines #22 and #28) or set_symmetric_difference (line #32):

 1 // Table difference function.
 2 // Takes two containers and prints out the differences (via set
   // difference) between the containers.
 3 // container 1 = "original" values, container 2 = "new" values
 4
 5 template<class Container> void TableDiff(ostream &o,
                                            const Container &cont1,
                                            const Container &cont2)
 6 {
 7    typedef Container::value_type value_type;
 8
 9    // copy container data into sets as set_symmetric_difference
10    // needs a sorted list to do its work
11    multiset<value_type> set1;
12    multiset<value_type> set2;
13
14    // Slight workaround for VC++ 6.0 STL library: can only work
      // with pointers, not iterators.
15    // Therefore, we cannot do this at set construction time
16    // as recommended by the standard
17    copy(cont1.begin(), cont1.end(),
         inserter(set1, set1.begin()));
18    copy(cont2.begin(), cont2.end(),
         inserter(set2, set2.begin()));
19
20    // Show set1 - set2 = deleted / changed items
21    o << "deleted / changed items:" << endl;
22    set_difference(set1.begin(), set1.end(),
                     set2.begin(), set2.end(),
23                   ostream_iterator<value_type>(o, "\n"));
24
25    // Show set2 - set1 = inserted / changed items
26 #ifndef USE_SET_SYMMETERIC
27    o << "inserted / changed items:" << endl;
28    set_difference(set2.begin(), set2.end(),
                     set1.begin(), set1.end(),
29                   ostream_iterator<value_type>(o, "\n"));
30 #else
31    // Show all differences as single set
32    set_symmetric_difference(set1.begin(), set1.end(),
                               set2.begin(), set2.end(),
                               ostream_iterator<value_type>(o, "\n"));
33
34 }
35
36
37 // Show the difference between the rows in two tables
38 void TableDiff()

39 {
40    // Use two DBViews to directly difference the contents of two tables

41    DBView<Example> new_table("sales_q3");
42    DBView<Example> old_table("sales_q3_backup");
43    TableDiff(cout, old_table, new_table);
44
45    cout << "--- should be same for IndexedDBViews --- " << endl;
46
47    // now do the same thing for an IndexedDBView
48    IndexedDBView<DBView<Example> > new_idx_table(new_table,
                                                    "PrimaryIndex;
                                                    STRING_VALUE");
49    IndexedDBView<DBView<Example> > old_idx_table(old_table,
                                                       "PrimaryIndex;
                                                        STRING_VALUE");
50    TableDiff(cout, old_idx_table, new_idx_table);
51 }

Another construct the code brings into play is an advanced container for holding database tables called IndexedDBView. This container is a specialization of an STL Unique Associative Container. As any database developer will tell you, the most common operations performed on a set of table records are: read the records into a container, search the records by different key fields (in other words,, indexes), and delete, insert, or update records in the container. For these uses, IndexedDBView works admirably.

Specifically, IndexedDBView adds easy creation of indexes into rows and synchronization capabilities that can automatically propagate any changes back to the database. Of course, you pay a price: IndexedDBView incurs more overhead than the simple DBView, and because it works at a higher level, you lose a bit of the fine-grained control that you get with simple iterators.

The DTL Way

DTL has a lot to offer over the traditional procedural approach to databases, which requires you essentially to create all algorithms by hand. DTL also has a lot more under the hood that I haven't even begun to explore here, such as C++ reflection, access to metadata, parameterized queries, and dynamic SQL. If you've found yourself cloning a lot of database code lately, give DTL a spin.

About the Author

Victor Volkman has been writing for C/C++ Users Journal and other programming journals since the late 1980s. He is a graduate of Michigan Tech and a faculty advisor board member for Washtenaw Community College CIS department. Volkman is the editor of numerous books, including C/C++ Treasure Chest and is the owner of Loving Healing Press. He can help you in your quest for open source tools and libraries, just drop an e-mail to sysop@HAL9K.com.



About the Author

Victor Volkman

Victor Volkman has been writing for C/C++ Users Journal and other programming journals since the late 1980s. He is a graduate of Michigan Tech and a faculty advisor board member for Washtenaw Community College CIS department. Volkman is the editor of numerous books, including C/C++ Treasure Chest and is the owner of Loving Healing Press. He can help you in your quest for open source tools and libraries, just drop an e-mail to sysop@HAL9K.com.

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: November 20, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Are you wanting to target two or more platforms such as iOS, Android, and/or Windows? You are not alone. 90% of enterprises today are targeting two or more platforms. Attend this eSeminar to discover how mobile app developers can rely on one IDE to create applications across platforms and approaches (web, native, and/or hybrid), saving time, money, and effort and introducing apps to market faster. You'll learn the trade-offs for gaining long …

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds