High-level abstractions for database developers

Jeremy Woo-Sam and Tony Murphy

The Oracle Call Interface (OCI) allows C applications to access and manipulate data in an Oracle database. Unfortunately, using the C API functions requires extensive knowledge of your tables and their data types. You must also know how to access columns inside a table and efficiently retrieve database rows into an application. By creating abstractions for database access and manipulation, you can wrap the C API into C++ classes. This provides database access in an easy-to-use and easy-to-maintain form, promotes code reuse, and bridges RDBMS concepts to an object-oriented paradigm.

In this article, we will present DBObject, a C++ class library that provides database connectivity and query access to an Oracle RDBMS. In doing so, we will examine the interface classes that are used within a C++ application, and the implementation classes that support array fetching within the interface classes. We will use an example that supports some primary data types: variable character strings, fixed-length character strings, numerics, and date types. Our abstraction of a database connection allows both local and remote connectivity, while our abstraction of database columns provides the mechanism for accessing data in the query.

The object-oriented approach offers database developers several benefits. For instance, users not familiar with cursors, array fetching, and other OCI concepts often find retrieving data from Oracle a time-consuming task. Object orientation lets users focus on solving their domain problems rather than on retrieving data.

Oracle data types are substantially different from those supported in C. This causes problems when converting between the database and C data types. For example, the Oracle NUMBER data type can have a value of NULL or 0. In the Oracle world, these values are different. This means no primitive C data type can entirely represent an Oracle NUMBER, because float, double, and int cannot represent both NULL and 0. This problem is commonly referred to as the "impedance mismatch" between RDBMS and C data types.

The common solution is to map the Oracle NUMBER data type to a C struct. However, this creates a new problem because you cannot use the C arithmetic operators with user-defined structs. (Note that C++ classes do allow structs to have operators.)

Finally, using high-level database abstractions lets you extend a class library's functionality without impacting existing applications. For example, the first version of our class did not implement array fetching; this was added later to improve efficiency. Likewise, obsolete OCI calls can be replaced with their newer counterparts without changing any application code. And, since our abstractions are valid on most SQL databases, our class library can easily be ported to other relational databases.

The interface layer is comprised of DBConnection, DBQuery, DBCol, DBColNumber, DBColDate, and DBColString. Each class is a well-defined abstraction of some aspect of the Oracle interface.

DBConnection manages the database connection. You simply instantiate an instance of the class by passing an Oracle connect string to the constructor to establish a connection with the database. When the object is destroyed (either explicitly or when it goes out of scope), the destructor severs the connection with the database. Behind the scenes, the OCI functions ORLON and OLOGOF are used to log on to and off of the Oracle server.

DBQuery abstracts the concept of an SQL query. Its constructor accepts an SQL string as a parameter. To execute the query, invoke the Execute() member function. The rows of data returned by the query can be sequenced using the MoveNext() member function.

Internally, the DBQuery object uses an instance of DBSQLEngine to parse the SQL statement. It allocates DBColumnBuffers to hold the columns retrieved from the database by the SQL string. DBQuery uses the OCI function ODESCR to decide which type of column buffer to use: DBNumberBuff, DBDateBuff, or DBBufferInfo.

DBCol classes are used to interface and manipulate the actual data returned from the database. DBColNumber, DBColDate, and DBColString implement the C++ equivalents to the Oracle NUMBER, DATE, and CHAR data types, respectively.

DBCol is an abstract base class for the column classes that implements services required by DBColNumber, DBColDate, and DBColString. Such services include maintaining a pointer to the DBQuery object, identifying the corresponding DBColumnBuffer that contains the physical data, and implementing an IsNull() member function to tell if a column's value is set to NULL.

Implementing a cast to double in DBColNumber lets you use instances of this type with C arithmetic operators. For example, Total=A+B is legal in C++, where A and B are of type DBColNumber or double, and Total is of type double. During arithmetic operations, a NULL column is assumed to be of value 0. However, if you prefer a value other than 0, simply inherit from DBColNumber and redefine the cast.

DBColString implements a cast to char*, which allows its instances to interoperate with functions that require null-terminated strings. For example, strcmp(A,B) is acceptable in C++ if A and B are either of type DBColString or are pointers to null-terminated strings. During string function calls, a NULL value is converted to a null-terminated string of length 0. Users can implement their own representation of NULL by overloading the DBColString::GetString() member function.

Implementation classes include DBSQLEngine, DBColumnBuffer, DBStringBuffer, DBNumberBuffer, and DBDateBuffer. They represent the internal structure and implementation of handling the rows retrieved from the database.

The DBSQLEngine class encapsulates the OCI functions that handle database cursors. These OCI functions handle fetching rows from the database, parsing the SQL statement, describing columns from a SELECT statement, and defining the memory area to hold rows returned from a database fetch. DBSQLEngine can be easily extended to support DML and DDL statements.

DBColumnBuffer is an abstract base class for DBStringBuffer, DBNumberBuffer, and DBDateBuffer. It represents the static and dynamic information of a database column and is responsible for gathering and maintaining information about it, including column type, external type, width, position, name, scale, and precision. DBColumnBuffer also contains information about the return length and NULL status for each row of the column. The pure virtual member functions GetData() and GetBufferAddress() are defined in the derived classes. This allows the derived classes to manage, maintain, and index their rows.

The DBConnection, DBQuery, and DBCol classes work together to access data from the database. Separating these classes into complete, distinct abstractions allows greater flexibility than folding connectivity, query capabilities, and column handling into one class. Once a successful connection to the database is established, the DBConnection object can be used by multiple DBQuery objects. The DBQuery constructor takes the SELECT string and a reference to a DBConnection object as its arguments. When a DBQuery object has been successfully created, DBCol objects can be associated to the DBQuery internal columns. The DBCol objects take a reference to a DBQuery object and the column number that it wants to reference. Once "attached" to the DBQuery internal column, you can print and manipulate the DBCol object.

The DBQuery object constructs and maintains the DBColumnBuffer objects. It parses the SELECT statement and creates a linked list of DBBufferInfo objects for each column in the SELECT statement by calling the DBSQLEngine::Describe() function. A linked list is preferable to an array of DBColumnBuffer objects because Oracle does not provide any functions to determine the number of columns in a SELECT statement. To determine the number of columns in the query, the ODESCR function is called until it returns the end-of-select error status. While calling DBSQLEngine::Describe(), we gather information about the column to populate the DBQuery object's DBColumnBuffer array.

The DBQuery object now allocates an array of DBColumnBuffer pointers that reference a data-type-specific DBColumnBuffer object such as a DBStringBuffer or a DBDateBuffer. The DBQuery object walks the linked list, allocates a data-type-specific DBColumnBuffer object, copies the information from the linked list, and sets the proper width and external type.

After the array has been populated, the DBQuery object passes each DBColumnBuffer object in the array to the DBSQLEngine member function Define(). This is done to determine the destination address for the rows retrieved.

To extend the DBObject class library, you could, for example, add enhanced data types. The DBObjects database class library demonstrates how to abstract a limited number of Oracle data types in C++. The same techniques can be applied to simulate other Oracle data types such as LONG and LONG RAW.

The current implementation utilizes double-precision floating-point numbers to represent the numeric values of NUMBER columns. This is sufficient for applications that don't require more than 15 digits of precision. Oracle itself supports a maximum precision of 38 digits. For more than 15 digits of precision, a high-precision column class could be developed. Client-application writers could then manage the efficiency-versus-precision trade-offs using the column class that most closely corresponds with their needs.

Additional member functions could be added to DBColString, DBColNumber, and DBColDate, resulting in more complete data types. This might include more operators and perhaps more advanced date manipulation.

Since most member functions are declared virtual, new column classes can be inherited from existing implementations. This technique can be used to add or modify behavior. For example, to change the way a column type prints its output, override the PrintValue() member function.

The DBObject class currently contains member functions to let derived classes set and get errors. A possible extension would be to throw an exception after the error message is copied into the buffer. Another extension could have the SetError() function terminate the application if the error were severe. Because all errors are processed in the DBObject class, your error-handling strategy can be implemented in this class.

Class libraries like DBObjects will play a substantial role in reducing complexity and enhancing interoperability between the C++ language and relational-database technology. The benefits don't just end here, however. By-products of our approach are portability, efficient database access at low cost, and reduced maintenance. Who knows? This type of technology may even aid a migration from RDBMS to the great nirvana of OODBMS.

#include <iostream.h>
#include <dbobject.h>
#include <dbcol.h>
int main()
{
    DBConnection TestConn("scott/tiger@t:cire-ss2:oracle7");
    DBQuery TestQuery("SELECT ename,empno,job,hiredate from EMP",&TestConn);
    TestQuery.Execute();
    DBColString colEname(&TestQuery, 1);
    DBColNumber colEmpno(&TestQuery, 2);
    DBColString colJob(&TestQuery, 3);
    DBColDate colHiredate(&TestQuery, 4);
    while(TestQuery.MoveNext())
    {
    cout    << "EName:  " << colEname << endl
            << "Empno:  " << colEmpno  << endl
        << "Job:    " << colJob    << endl
        << "Hired   " << colHiredate << endl << endl;
    }
}

ENAME  EMPNO JOB       HIREDATE
------ ----- ----      ---------
SMITH  7369  CLERK     17-DEC-80
ALLEN  7499  SALESMAN  20-FEB-81
WARD   7521  SALESMAN  22-FEB-81
JONES  7566  MANAGER   02-APR-81
MARTIN 7654  SALESMAN  28-SEP-81
BLAKE  7698  MANAGER   01-MAY-81
CLARK  7782  MANAGER   09-JUN-81
SCOTT  7788  ANALYST   09-DEC-82
KING   7839  PRESIDENT 17-NOV-81
TURNER 7844  SALESMAN  08-SEP-81
ADAMS  7876  CLERK     12-JAN-83
JAMES  7900  CLERK     03-DEC-81

EName:  SMITH
Empno:  7369
Job:    CLERK
Hired   17-DEC-1980 00:00:00
EName:  ALLEN
Empno:  7499
Job:    SALESMAN
Hired   20-FEB-1981 00:00:00
EName:  WARD
Empno:  7521
Job:    SALESMAN
Hired   22-FEB-1981 00:00:00
EName:  JONES
Empno:  7566
Job:    MANAGER
Hired   02-APR-1981 00:00:00
EName:  MARTIN
Empno:  7654
Job:    SALESMAN
Hired   28-SEP-1981 00:00:00
EName:  BLAKE
Empno:  7698
Job:    MANAGER
Hired   01-MAY-1981 00:00:00
EName:  CLARK
Empno:  7782
Job:    MANAGER
Hired   09-JUN-1981 00:00:00
EName:  SCOTT
Empno:  7788
Job:    ANALYST
Hired   09-DEC-1982 00:00:00
EName:  KING
Empno:  7839
Job:    PRESIDENT
Hired   17-NOV-1981 00:00:00
EName:  TURNER
Empno:  7844
Job:    SALESMAN
Hired   08-SEP-1981 00:00:00
EName:  ADAMS
Empno:  7876
Job:    CLERK
Hired   12-JAN-1983 00:00:00
EName:  JAMES
Empno:  7900
Job:    CLERK
Hired   03-DEC-1981 00:00:00

Copyright © 1995, Dr. Dobb's Journal