ODBC handle classes

[Indiana Epilepsy and Child Neurology]

Before asking this questions I've spent literally _years_ reading
(Meyer, Stroustrup, Holub), googling, asking more general design
questions, and just plain thinking about it. I am truly unable to
figure out what would be a "proper" OO design (in C++) for this.

There may be alternatives to writing my own ODBC handle classes, and I
may be interested in them, but I'd like to pursue this particular
problem, if for no other reason than to understand the C++ OOD related
issues.

There are three types of ODBC handles I'm interested in: environment,
connection and statement. All have this in common:

* All three have the same data type: SQLHANDLE.
* They are created by the SQLAllocHandle function.
* They must be deallocated using the SQLFree function.
* They have "attributes" which can be set or retrieved by a function
which has the same signature, but a different name for each type of
handle: SQLSetEnvAttr, SQLGetEnvAttr, SQLSetConnectAttr, etc.
* Functions which take an ODBC handle as an argument (always?) return
a value whose type is SQLRETURN, which can have a half-dozen or so
possible values indicating success or failure for different reasons.
More details can be retrieved by passing the handle to the
SQLGetDiagField or SQLGetDiagRec ODBC API functions.

In addition to the handle, SQLAllocHandle, SQLGetDiagField and
SQLGetRec require a "handle type" enumeration argument. SQLFree does
not. I'm not sure if there some any other API functions common to all
the handle types, but most or all the rest are handle-specific; each
can only be used with one specific type of handle.

My first idea was simple, something along the lines of:

class Handle
{
public:
Handle(SQLHANDLE sqlHandle): sqlHandle_(sqlHandle) { }
~Handle() { SQLFreeHandle(sqlHandle_);
operator SQLHANDLE(void) { return sqlHandle); }
private:
SQLHANDLE sqlHandle_;
};

This made the handle "self-destruct" but provided no encapsulation.
Next, I considered removing the operator SQLHANDLE function and adding
"forwarding" member functions along the lines of:

SQLRETURN Handle::allocHandle(SQLSMALLINT handleType,SQLHANDLE
*pNewHandle)
{
return SQLAllocHandle(handleType,sqlHandle_, pNewHandle);
}

The handle-specific functions seemed to suggest separate classes (with
different member functions) for each type of handle. Since they are
all handles ("is a") deriving from Handle seemed reasonable.

Here's the problem: the "handle-specific" member functions of those
derived classes need to be able to pass the handle to the
corresponding API functions, but the handle is stored in the base
class, Handle.

Solutions I've considered, all of which seem to have problems:
1. Make sqlHandle_ protected.
2. Make sqlHandle_ available through an accessor function.
3. Make sqlHandle_ available through an operator SQLHANDLE function.
4. Pass functors to the base class from the derived classes.
5. Invert the hierarchy by using a kind of policy-based template
design: Handle<T> derives from T.
6. Derive from more than one class.
7. Putt the "self-destructing" code into a separate class which is not
part of the hierarchy, then use a data member of that type.
8. Duplicate the handle allocation and deallocation code in the class
for each handle type.
9. Put ALL functions for ALL handle types in as public functions in
the base class which pass the handle to private virtual functions
which just throw exceptions unless overridden. Override only those
functions appropriate to the handle type corresponding to the derived
class.
10. Quit programming altogether.

Briefly, here are the problems I see with each "solution":

1. Violates encapsulation.
2. and 3. If the goal of encapsulation is to prevent an "invalid
state", this only half solves the problem: derived classes can't
overwrite sqlHandle_, but they could still SQLFree it.
4. Derived class could pass a functor that SQLFrees the handle.
5. Doesn't solve the problem; the handle is still in a different class
from the classes that most often need to use it.
6. Strouvstrup's C++ FAQ, under "Why do my compiles take so long?" has
a part which begins "But what if there really is some information that
is common to all derived classes...". The classes use protected
derivation from a struct with the common data. The interface (in my
case, allocHandle and pure virtual setAttr and getAttr functions) get
moved to an abstract base class, and my three handle classes would
derive from both that abstract base and ("protectedly", if that's a
word) from my "self-destructing handle" class. Problem is, this is
complicated, seems contrived, and I don't see how it's any better. It
still seems to violate encapsulation as badly as #1 does. Maybe a
little less "brittle", but I don't see the ODBC handle mechanism
changing soon.
7. Still has the same encapsulation issues; now _containing_ classes
need access to the handle.
8. This seems worse even than a "type switch".
9. It seems like the base class would have to "know" too much about
derived classes. Besides, that's three (admittedly simple) functions
in two different classes for every one API function.
10 But I used to LIKE programming . . .

Your guidance is much appreciated.

 

[Bob Hairgrove]

On Tue, 02 Aug 2005 02:47:39 GMT, Indiana Epilepsy and Child Neurology

[big snip]

To me, a "handle" is too low-level to warrant having an OOP treatment.
It is a C-language kludge which C++ was meant to replace.

If you want objects, "Environment", "Connection" and "Statement" would
be more appropriate concepts. In my last line of work, I had the
opportunity to write a lean-and-mean C++ library to wrap the ODBC
functionality necessary to our applications. I made the base classes
abstract so that if I ever wanted to replace the ODBC API with, for
example, Oracle or DB2 native CLI, I woudn't have to change the
interface, but merely <g> the implementation classes which are not
visible to users of the code.

Forget handles except as an implementation detail. Users should not
have to worry about them.

 

[Don Stauffer]

While I was dwelling on the fundamental interconnectedness of all
things on Fri, 05 Aug 2005 23:22:37 +0200, Bob Hairgrove

On Tue, 02 Aug 2005 02:47:39 GMT, Indiana Epilepsy and Child Neurology

[big snip]

To me, a "handle" is too low-level to warrant having an OOP treatment.
It is a C-language kludge which C++ was meant to replace.

That's pretty much what I'm trying to do.

If you want objects, "Environment", "Connection" and "Statement" would
be more appropriate concepts.

The plan is that even these concepts would become implementation
details in a higher-level hierarchy. But, I have to start somewhere.

In my last line of work, I had the
opportunity to write a lean-and-mean C++ library to wrap the ODBC
functionality necessary to our applications. I made the base classes
abstract so that if I ever wanted to replace the ODBC API with, for
example,

That's the next step. I'm not there yet because I can't even "handle"
the handles.

Oracle or DB2 native CLI, I woudn't have to change the
interface, but merely <g> the implementation classes which are not
visible to users of the code.

That depends. This higher-level class is itself a "user" of the
handles. Another derivation might use an entirely different database
system, but right now I'm trying to get building blocks together to
implement the ODBC derivation.

Forget handles except as an implementation detail. Users should not
have to worry about them.

I still don't want the implementation details to be spaghetti; in
fact, I'd like to figure out how to do them "right" (or at least
"well"). Even if that's not "necessary", after a good 5 years of
study I still can't seem to get my mind around the practical
implementation of good object-oriented coding in C++.

It seems like the biggest liability object-oriented design has, at
least in C++ which is the only language I've explored it in, is that
even a smart person can have huge amounts of trouble understanding how
to do it right

So far, I'm concluding that preventing a handle from being SQLFreed by
a derived class is impossible while making the handle available for
other API calls. The best I can do is provide an accessor to prevent
the handle data member from being changed:

class Handle
{
public:
...
~Handle(void) { if (sqlHandle_ != SQL_NULL_HANDLE)
SQLFree(sqlHandle_); }
operator SQLHANDLE(void) { return sqlHandle_; }

private:

SQLHANDLE sqlHandle_;
};

class Environment: protected Handle
{
...
};