Improved Versions of CCommand and CDynamicAccessor

• Download source files - 12 Kb
• Download demo project - 31 Kb

Introduction

For a C++ developer there are a couple of technologies to choose from when it comes to database access. There's ODBC, ADO, DAO, OLEDB, and most certainly a couple more. Odds are that most C++ developers have used either ADO or OLEDB or both. Both are cumbersome to use in C++. If you choose ADO then you better have a love for VARIANTs. If you choose OLEDB you better have a thing going on for static and unflexible accessors - or - dynamic accessors with a void*-complex.

This is my humble attempt at making OLEDB easier to use, like ADO is in VB, but without the cost of using variants. Of course, if you like variants, you can always hack the code and include them as you see fit.

I have been using Java quite a lot at work lately. The work I've been doing is database centric, so I've been using jdbc as my main tool. I've found that the interface jdbc exposes for the programmer is quite nice. I've modeled my solution after the jdbc interfaces Connection.prepareStatement() and ResultSet.getXXX() (where XXX is a type name).

I expect you to know a little about OLEDB in C++. Java knowledge won't hurt, but it's not a requirement.

JDBC Introduction

The main classes you need to be aware of as a Java database programmer are

Connection

Equivalent to OLEDB CSession

Statement

Equivalent to OLEDB CCommand

PreparedStatement

There is no equivalent in OLEDB, this is where my code comes into the picture

ResultSet

Equivalent to my derivation of the OLEDB class CDynamicAccessor

Assuming you already know OLEDB, I won't explain Connection and Statement. It's fairly obvious what they do.

PreparedStatement

PreparedStatement is a command which supports parameterization of SQL queries. It allows you to write queries like SELECT attr FROM table WHERE key1 < ? AND key2 LIKE ?. This query as it is, as you can see, is not complete. In Java, you fill in the values for the question marks aftwards by function calls. The Java classes also make sure that values are appropriately quoted and escaped. This means you don't have to worry about single qoutes in your strings - it's all taken care of.

Here's an example how to create a complete query using PreparedStatement:

Connection conn = ...;
PreparedStatement pstmt = 
    conn.prepareStatement(
        "SELECT attr FROM table WHERE key1 < ? AND key2 LIKE ?"
    );
pstmt.setInt(1, 1234);
pstmt.setString(2, "Code%");

This piece of Java code will generate the SQL query SELECT attr FROM table WHERE key1 < 1234 AND key2 LIKE 'Code%'.

The setInt() and setString() functions takes two arguments. The first argument is a parameter index, thus telling which parameter should be substituted for a value. The second argument is the value it self - fairly straightforward. My classes works just like this, except for one thing - I overload a single method name instead of using one name for each available type.

ResultSet

When a query statement is executed in Java, you get a result set back. The result set is basically a row scroller which supports navigation such as "move to beginning, move next, move previous, move last" - very much like OLEDB.

To access the column data, you use the functions getXXX(ordinal) where ordinal is either an integer index or a string containing the column name.

Here's a simple example showing how to fetch data from a query (continued from earlier java example):

ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
   System.out.println("attr = " + rs.getString(1));
}

Simple eh? One goodie is that ResultSet.getXXX may coerce values for you. getString() for instance works with most types since most types are expressible as strings.

Accessors in OLEDB

In OLEDB you must either create a static accessor where you bind member variables to each column using macro magic. Or you can use the CDynamicAccessor to access data.

Static Accessors

Static accessors are the fastest way to access data, but as always - there are trade-offs involved. Static accessors are problematic if your table attributes change. Especially problematic are changes to string attributes. A string attribute in a static accessor is represented as a character array with a fixed size where the size is the size specified by the database plus one for termination. Imagine what happens if you increase the string size in the database and forgets to update the accessor.

Dynamic Accessors

Dynamic Accessors do not require macro magic, nor are they vulnerable to database changes. Since they use dynamic memory allocation to handle the data, they're a bit slower than static accessors. However, if you can accept this small overhead, you'll that accessing data in OLEDB can be as simple as in Java (although it won't be as slow as Java).

The standard implementation of CDynamicAccessor is quite restrictive actually. It has a templated GetValue() function which may seem excellent at a first glance. However, if you for example try to get a 2 byte integer and store it in a 4 byte long variable, then it will assert. The templated version thus requires that the size of your argument matches the size of the data. No attempts are made to coerce values.

Getting strings using the templated GetValue() function is virtually impossible - string pointers seldome have the same size as the string data (sum of all character sizes). For strings you have to use the void* version of GetValue(). Also worth noting is that there may be three types of strings: wchar_t*, char* and BSTR, so you must know in advance what kind of string it is - you can't assume one or the other!

I'll try to address these issues with my code. Getting strings should be easy - and it should perform necessary conversions to suite you, not the other way around. Same thing goes for other types. A short fits in a long! A short can also be expressed as a string.

CDynamicCommand

CDynamicCommand derives publically from CCommand. However, it should not be viewed upon as a polymorphic type - no methods are virtual. Functions worth noting are as follows:

• SetCommandTemplate()
• SetParamValue()
• Open()

SetCommandTemplate

Equivalent to Javas Connection.prepareStatement(). The function takes a single string containing your parameterized query. Please don't quote string parameters, the SetParamValue functions will do it for you!

SetParamValue

SetParamValue is equivalent to Javas PreparedStatement.setXXX(). The function takes an index telling which parameter you are setting, and a value.

In this function I don't just record the value, I also mark the value as "used". I use these marks later to determine if the user have missed to set a parameter or not.

These functions also escape single qoutes in strings, as well as quoting strings. Types such as datetime are converted into their string representations.

Open

Open() is equivalent to Javas PreparedStatement.executeQuery(). It'll execute the query, and let you scroll through the results. For more information about the parameters, please see the MSDN documentation as I just pass them along to CCommand::Open().

In this function I round up all parameters which you've set, and create the complete SQL query. If you've forgot to set a parameter, E_FAIL is returned. If all is well, CCommand::Open() is called with the complete SQL query.

CDynamicAccessorEx

Functions of interest are template <typename T> GetValue(ordinal, T* ptr) and its specializations. The generic version of GetValue passes everything into CDynamicAccessor::GetValue(), thus acting as a fallback function. This means that if you try to get the value for a non-specialized type, CDynamicAccessorEx will act just like CDynamicAccessor. Specialized versions of GetValue will perform conversions if necessary. Please note that the string versions (except CString) of GetValue() dynamically allocates a string for you. You have to free the strings later by using delete[]. I will add fixed size buffer versions of GetValue() for strings later to reduce memory allocations and the need to delete.

An Example Using CDynamicCommand and CDynamicAccessorEx

This is how these classes can be used:

CDataSource ds;
ds.Open(...); // Setup data source

CSession session;
session.Open(ds);
 
CDynamicCommand<CDynamicAccessorEx> cmd;
cmd.SetCommandTemplate(
    _T("SELECT Telephone, Mobile, Fax, Email FROM Person "
       "WHERE FirstName LIKE ?"));
cmd.SetParamValue(0, "A%");
cmd.Open(session);

HRESULT hr = cmd.MoveFirst(); 
while(S_OK == hr) {
    CString strTel, strMob, strFax, strEmail;
 
    cmd.GetValue(_T("Telephone"), &strTel);
    cmd.GetValue(1, &strMob);
    cmd.GetValue(_T("Fax"), &strFax);
    cmd.GetValue(4, &strEmail);

    DoSomething(strTel, strMob, strFace, strEmail);
    hr = cmd.MoveNext();
}
cmd.Close();

As you can see, it doesn't get much simpler - it's almost as trivial as ADO in VB or java.sql.* in Java. Download the demo project yourself and try it out. The demo project is a very simple dialog implemented using WTL7. See MainDlg::OnInitDialog(). Included in the ZIP is an Access MDB-file (not large). You may want to change the connection string so that the driver will find the MDB-file.

Caveat Emptor

• This code is not complete - consider it alpha for the time being.
• I have not done anything to support BLOBs.
• I've not tested this extensively enough to guarantee its fitness in a production environment.
• Dynamic accessors may become major bottlnecks in a threaded environment due to dynamic memory allocation.
• Strings (char*, wchar_t* and BSTR) must be freed after a call to CDynamicAccessor::GetValue()
• I'm not sure if CDynamicCommand should inherit publically from CCommand because the relationship is not a strict "IS-A". I'll see what I can do to let clients use CDynamicCommand as a CCommand. If I find that it is quite possible, the inheritance stays and I'll add a using CCommand::Open statement to CDynamicCommand.

However, my goal is to use this code in a project which will handle sort of sensitive data. I will update the code continually as I work on that project. As I find and fix bugs I'll be sure to update this article as well. Bug reports, fixes and/or suggestions, are of course, welcome.

License Grant

You are granted a license to use the code for whatever purpose, if and only if the following conditions are met:

• You may expect no warranties from my part. If you break something, you fix it.
• If we meet some day in the flesh, you may buy me a beer of my choice. This is not a requirement, but it would be a very nice thing to do and I would appreciate it. If your religion prohibits you from buying any alcohol, even if not for yourself, a coke is fine.

Revision

2002-11-22

Initial version

2002-11-23

Fairly large update including:

• GetValue() which can handle fixed size string buffers
• GetValue(ordinal, CString*) is more intelligent when the column data is string data. It uses CString::Get/ReleaseBuffer() in those cases. If time permits other coerced types, which have a defined length in characters, will use the Get/ReleaseBuffer() functions as well.
• More specializations of GetValue<>(). All native integers and bool.
• The string version can now coerce most values to strings. (Not all.. yet)

25 Nov 2002

Optimizations and fixes

• SetParamValue(size_t, const DATE&) is now correct.
• SetParamValue()'s are optimized overall.
• More string coercions in GetValue<char/wchar_t>()

Oct 26 2004

• Various bug fixes
• Decimal class added - wrapper for OLE type DECIMAL