Accessing Your Database with C++ Is as Easy as DTL, Page 2

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.