OODBC

Introduction

OODBC is object interface built above ODBC API for C++ language. The primary goal of OODBC is to provide a flexible and convenient interface to relational databases for C++ language. Anyone who has to use ODBC or similar SQL interfaces will understand what I am speaking about. In OODBC a query can be written in C++ in the following way:

    dbQuery q; 
    dbCursor<Contract> contracts;
    dbCursor<Supplier> suppliers;
    int price, quantity;
    SQL_DATE_STRUCT delivery;
    q = "(price >=",price,"or quantity >=",quantity,
        ") and delivery <=", delivery;

    // input price, quantity and delivery values

    if (contracts.select(q) != 0) { 
        while (contracts.fetch());
        do { 
            contract.print();
        }
    } 
So binding of variables is performed exactly in place where they are used in the query. Programmer should not worry about specifying types and sizes of query parameters, binding buffers to retrieve results, allocation and deallocation of resources. As a result programming of interaction with database becomes significantly less error prone. What is more important, this interface allows programmer to abstract from relational database table and deal only with application objects. OODBC also provides flexible mapping of application classes on relational database tables, making application code less dependent of the database structure.

C++ interface

Table

OODBC associate C++ class with the table of relational database. Each table row is considered as object instance. Unfortunately C++ has no reflection mechanism so a programmer has to provide information about class fields himself. OODBC provides a set of macros to make this work as simple as possible and reduce possibility of making an error.

MacroParameter 1Parameter 2Description
KEY_COLField nameDatabase table attribute nameDescribes primary key field (names of class field and table column are different)
KEY_COLField name-Describes primary key field (names of class field and table column are different)
FIELD_COLField nameDatabase table attribute nameDescribes non-primary key field (names of class field and table column are different)
KEY_COLField name-Describes non-primary key field (names of class field and table column are different)
SUPERCLASSBase class name-Specify descriptor of base class
TYPE_DESCRIPTORList of fields (in extra parentheses)-Specify class descriptor
REGISTER_ASDatabase table nameApplication class nameAssociate class descriptor with database table
REGISTERDatabase table name-Associate class descriptor with database table (with the same name as the name of the application class)

Primary keys specified in class descriptor (one or more) are the set of fields (columns) which can be used to uniquely identify any record in the table. They should not match with primary keys declared at table creation time. OODBC needs to have information about primary keys to implement update/delete operations.

It is not necessary to declare all variables in TYPE_DESCRIPTOR - you should only describe those variables which are mapped on the database table columns. You should specify type descriptors for all application classes and structures which either are mapped to the database tables or used for declaring components of such classes. All mapped on the tables classes should be registered using REGISTER macro. For example:

class Employee {
  public:
    int      code;
    char     name[100];
    int      age;
    SQL_DATE_STRUCT hired;
    long     salary;
    Activity project;    

    TYPE_DESCRIPTOR((KEY(code), 
		     FIELD(name),
		     FIELD(age),
		     FIELD(hired),
		     FIELD(salary),
		     FIELD(project)));
};

REGISTER(Employee);
The following field types are supported by OODBC:

C typeODBC SQL typeOODBC parameter type
char[]SQL_VARCHARSQL_C_CHAR
boolSQL_BITSQL_C_BIT
charSQL_TINYINTSQL_C_STINYINT
signed charSQL_TINYINTSQL_C_STINYINT
signed shortSQL_SMALLINTSQL_C_SSHORT
signed intSQL_INTEGERSQL_C_SLONG
signed longSQL_INTEGERSQL_C_SLONG
unsigned charSQL_TINYINTSQL_C_UTINUINT
unsigned shortSQL_SMALLINTSQL_C_USHORT
unsigned intSQL_INTEGERSQL_C_ULONG
unsigned longSQL_INTEGERSQL_C_ULONG
floatSQL_FLOATSQL_C_FLOAT
doubleSQL_DOUBLESQL_C_DOUBLE
SQL_DATE_STRUCTSQL_TIMESTAMPSQL_C_DATA
SQL_TIME_STRUCTSQL_TIMESQL_C_TIME
SQL_TIMESTAMP_STRUCTSQL_TIMESTAMPSQL_C_TIMESTAMP
SQL_NUMERIC_STRUCTSQL_NUMERICSQL_C_NUMERIC

OODBC provides special template class dbFieldWithIndicator for declaring variables with indicators. The indicators are needed to detect NULL values. You can substitute template parameter of this class with any type supported by OODBC. The method isNull of this class can be used after fetching object from the database to check attribute for NULL. Another way of associating indicator with variable is to use FIELD_IND_COL macro in type descriptor, but I do not recommend it because it.

Query

The class query is used to bind in safe way query parameters. traditional approach based on using placeholders is very inconvenient, complex and error prone. Using overloaded C++ operators it is possible to specify parameters exactly in the places of the query statement where they are used.

OODBC provides overloaded '=' and ',' C++ operators to construct query statements with parameters. Parameters can be specified directly in places where they are used, eliminating any mapping between parameter placeholders and C variables. In the following sample query, pointers to the parameters price and quantity are stored in the query, so that the query can be executed several times with different parameter values. C++ overloaded functions make it possible to automatically determine the type of the parameter, requiring no extra information to be supplied by the programmer (such reducing the possibility of a bug).

        dbQuery q;
        int price, quantity;
        q = "price >=",price,"or quantity >=",quantity;
Since the char* type can be used both for specifying a fraction of a query (such as "price >=") and for a parameter of string type, OODBC uses a special rule to resolve this ambiguity. This rule is based on the assumption that there is no reason for splitting a query text into two strings like ("price ",">=") or specifying more than one parameter sequentially ("color=",color,color). So OODBC assumes the first string to be a fraction of the query text and switches to operand mode after it. In operand mode, OODBC treats the char* argument as a query parameter and switches back to query text mode, and so on... It is also possible not to use this "syntax sugar" and construct query elements explicitly by the dbQuery::append(dbQueryElement::ElementType type, void const* ptr) method. Before appending elements to the query, it is necessary to reset the query by the dbQuery::reset() method ('operator=' does it automatically).

It is not possible to use C++ numeric constants as query parameters, because parameters are accessed by reference. String parameters should be always linked to the array of char not to pointer to chars. Values should be placed to the buffers before query execution:

     dbQuery q;
     char name[256];
     q = "name=",name;

     scanf("%s", name);
     cursor.select(q);
     ...
     scanf("%s", name);
     cursor.select(q);
     ...

In addition to builtin types, it is possible to use user-defined types in queries. Programmers should define methods, which will not do actual calculations, but instead return an expression (in terms of predefined database types), which performs the necessary calculation. It is better to describe it by example. For example we need to have Point type having x and y attributes. Declaration of this class can look something like this:

class Point { 
    int x, y;
  public:
    dbQueryExpression operator == (char const* field) { 
    	dbQueryExpression expr;
	expr = dbComponent(field,"x"),"=",x,"and",
	       dbComponent(field,"y"),"=",y;
	return expr;
    }	
    dbQueryExpression operator @= (char const* field) { 
    	dbQueryExpression expr;
	expr = dbComponent(field,"x"),"<>",x,"or",
	       dbComponent(field,"y"),"<>",y;
	return expr;
    }	
    ...
};	    
All these methods receive as their parameter a name of a field in the record. This name is used to contract the full name of the record's component. This can be done by class dbComponent, which constructor takes the name of the structure field and the name of the component of the structure and returns a compound name separated by a '.' symbol. The class dbQueryExpression is used to collect expression items. The expression is automatically enclosed in parentheses, eliminating conflicts with operator precedence.

So, assuming a record containing a field location of Point type, it is possible to construct queries like these:

        Point p;
	Query q;
        q = (p == "location");

Cursor

Cursors are used to access records returned by a select statement. OODBC provides typed cursors, i.e. cursors associated with concrete tables. Cursors in OODBC are represented by the C++ template class dbCursor<T>, where T is the name of a C++ class associated with the database table. When cursor is created, database connection is associated with the cursor by the constructor. Instance of dbConnection object should be passed to the cursor. To avoid access conflicts, the cursor should be used only by one thread each moment of time. A cursor can be also used for updating or removing selected records. In this case "for update" clause should be added to SQL selection statement.

A query is executed either by the cursor select(dbQuery& q) method or by the select() method, which can be used to iterate through all records in the table. To fetch the records, method fetch() should be called. It returns false if there are not more records in selection or in case of error. Fetched record is placed to the instance of the object located in cursor itself. Components of this object can be accessed by means of redefined -> operator.

To update the selected object instance, you should store new values in cursor object component and execute update() method of the cursor. Selected row can be removed from the table by means of remove() method.

A cursor for class T contains an instance of class T, used for fetching the current record. That is why table classes should have a default constructor (constructor without parameters), which has no side effects.

All cursor methods return boolean result code: true if operation succeeded and false if failed. By means of getLastResult() function it is possible to knew more about the reasons of failure. This function return code, which was in its turn returned by failed ODBC function (or SQL_SUCCESS if no error s took place). For example, when last record is fetched by the cursor, the following execution of fetch() method will return false and successive invocation of getlastError - SQL_NO_DATA.

Except construction queries using dbQuery class, it is possible to specify query by example. In this case fields of the object which should be matched with the correspondent columns of the table should be set in the cursor and queryByExample method will construct SQL statement which will match only these columns of the tables, which were specified in sample object. Field of the object considered to be specified if:

  1. It has indicator and this indicator is set to the value different from SQL_NULL_DATA;
  2. It is scalar field without indicator;
  3. It is string field and its value is not empty string.

Query by example semantic is especially convenient for interactive applications in which user fill the request form to specify selection criteria.

Connection

Class dbConnection encapsulates establishing connection with database server and handling session errors. Method open() of dbConnection class takes three parameters - data sources name, user name and password. It is possible to insert, update and remove record from the table using methods of dbConnection class. update and remove methods use set of primary keys specified in class descriptor to locate record within the table. All these methods accepts class descriptor and object reference parameters. Values of the primary key field of specified object are using in SQL query to locate target table record. It is also possible to use template functions insert, remove and update which takes only one parameter - pointer to the object.

By default SQL server implicitly commit transaction after execution of each ODBC request. It is possible to switch off autocommit mode and control fixation of transactions explicitly using commit() and rollback() methods of dbConnection class. Setting transaction mode can be done using standard ODBC mechanism.

It is possible to execute arbitrary SQL data manipulation or data definition statement using execute method of connection class. This method also accepts dbQuery parameter, making it possible to bind parameters with the query.

All methods of dbConnection class returns boolean value - true if operation is completed successfully and false otherwise. Also when ODBC function call is failed, virtual method dbConnection::handleError is invoked. This method is given three parameters - error code returned by ODBC function, string with description of place in sources where fault took place (in format "Error in function %s at %s:%d") and string with description of the error returned by SQLError function.

Building

OODBC is shipped with sources as library for Windows platform and ODBC v.3. It is possible to rebuild this library using Microsoft Visual C++ compiler - just run make utility (nmake if you are using Microsoft make).

There is also test example of application using OODBC for interaction with Microsoft SQL Server. It can be easily changed to work with other databases with ODBC driver, but login information and may be some type in table definition SQL statements should be changed.

Distribution terms

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the Software), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHOR OF THIS SOFTWARE BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

I will provide e-mail support and help you with development of OODBC applications.


Look for new version at my homepage | E-Mail me about bugs and problems

/B>

I will provide e-mail support and help you with development of OODBC applications.


Look for new version at my homepage | E-Mail me about bugs and problems