Sharing code between database projects: a look at queries

by Willem Semmelink

Have you ever had to modify code because the project had to be ported from BDE objects to Interbase or ADO objects? Almost each new client-server data base I work on has a report engine, lookup table maintenance forms, and user validation code. Much of this code could have been shared between projects if I planned the usage of my queries a bit better. Developers using C++Builder 5 Professional can choose between BDE and IBExpress components, and C++Builder 5 Enterprise developers have the additional option to use ADO components. Peeping over the shoulder of a Delphi 6 developer, I foresee that new C++Builder releases may probably also include a dbExpress query as Delphi 6 does.

The problem is this: the three existing queries use different property names and even different data types for similar tasks. Since TDataSet is their first common parent, polymorphic operations on query-specific properties are difficult. TDataSet for example, lacks the SQL property. Therefore it is difficult to generalize abstract behavior for a query in a true O-O manner. When a source code file is ported from BDE to IBExpress, or to ADO (or dbExpress, or whatever else), we don’t want to manually search-and-replace our source code to change each TQuery to TADOQuery or to TIBQuery, resolve property names and their data types, and so on.

By manually changing code, we run the risk of introducing errors. My first attempt at solving this was to define C-style #define macros and typedef’s that would determine which of the three environments we are using. Thereafter, I will mention a few things about my most recent idea to solve the problem with an object-oriented solution.

For the C-style macros, I created the following definitions in a file that I called DBDefsU.h:

#ifdef USING_BDE    // BDE Definition
  #include <dbtables.hpp>
  //Query
  typedef TQuery TFischerQuery;
  #define DB_LINK_PROPERTY DatabaseName
  #define DB_LINK_VALUE    
    DataModule1->Database1->DatabaseName
#elif USING_ADO    // ADO Definition
  #include <ADOdb.hpp>
  //Query
  typedef TADOQuery TFischerQuery;
  #define DB_LINK_PROPERTY Connection
  #define DB_LINK_VALUE    
    DataModule1->ADOConnection1
#elif USING_INTERB // IB Definition
  #include <Ibquery.hpp>
  //Query
  typedef TIBQuery TFischerQuery;
  #define DB_LINK_PROPERTY Database
  #define DB_LINK_VALUE    
    DataModule1->IBDatabase1
#else #error DBDefsU.h Missing USING_BDE
  or USING_ADO or USING_INTERB
#endif //end DB check

In a new project, I add a data module and to that, I add either a BDE TDatabase component, TADOConnection or an Interbase TIBDatabase component (remember also to set up the parameters to connect to the database). I assume that only one connection object is present per project, and that a project exclusively uses either ADO, IB, or BDE. In the header file of the data module, I specify either one of the following three definitions:

#define USING_BDE
#define USING_ADO
#define USING_INTERB

Thereafter follows an #include to add the definitions file:

#include "DBDefsU.h"

Wherever the data module is included, the definitions of DBDEFS.H will be available. Then, I instantiate a TFischerQuery, which is typedefed in DBDEFS.H as a TQuery, a TADOQuery, or a TIBQuery. I called it FischerQuery since that is the name of the company I work for. I then set the database connection property, and the query can be used.

An implementation of the macros in a program is illustrated below:

TFischerQuery* qry = 
  new TFischerQuery(NULL);
qry->DB_LINK_PROPERTY = DB_LINK_VALUE;
qry->SQL->Clear();
qry->SQL->Add("select * from customer");
qry->Open();
for (int i=0;i<=qry->RecordCount;i++) {
  ListBox1->Items->Add( 
    Qry->FieldByName(
      "CUSTNO")->AsString + " " + 
      Qry->FieldByName(
        "COMPANY")->AsString);
  qry->Next();
}

If we used the USING_BDE directive in the data module, the compiler will replace the above code to the following:

TQuery* qry = new TQuery (NULL);
qry->DatabaseName = 
  DataModule1->Database1->DatabaseName;
// etc

Suppose we need to re-use the above code in an InterbaseExpress project, and then replace the TDatabase in the data module with a TIBDatabase and a TIBTransaction object. Set the DefaultTransaction property of the database object to point to the IBTransaction. Check that the TIBDatabase name is the same as the name the previous listing and change the lines in the data module of the new project to:

#define  USING_INTERB
#include "DBDefsU.h"

When we re-compile, the macros will be replaced as if we had written the following code:

TIBQuery* qry = new TIBQuery(NULL);
qry->Database = DataModule1->IBDatabase;
// etc

Developers that have the C++Builder Enterprise version may replace the Database object in the data module with a TADOConnection object. Ensure that the name of the connection object is the same as the definition in the earlier macro and change the data module to:

#define  USING_ADO
#include "DBDefsU.h"

The ADO macro definitions will then be replaced during compiling as:

TADOQuery * qry = new TADOQuery(NULL);
qry->Connection = 
  DataModule1->ADOConnection1;
// etc

Transaction management

The macro definitions also provide for transaction management, so you can use code like the following:

q_SomeQuery->SQL->Add(
  String("UPDATE USER SET password = ’") + 
    Encrypt(strNewPassword) + 
    "’ WHERE userid = ‘" + 
    strCurrentUser + "’");
try {
  DB_BEGIN_TRANSACTION;
  q_SomeQuery->ExecSQL();
  DB_COMMIT_TRANSACTION;
} catch (exception &E) {
  DB_ROLLBACK_TRANSACTION; 
  ShowMessage(E.Message);
}

Parameters

The greatest challenge in solving the problem of porting lies in handling the Parameters property, because the ADO implementation differs significantly from the BDE and IBExpress interfaces.

For simple queries, it is sufficient to insert parameters in the query, preceded by colon. The difference in the parameter property name is handled by the following definition in the DBDefsU file:

#define DB_PARAM_PROPERTY params

For ADO, the property name is Parameters. It can then be used as follows:

qry ->SQL->Add("select Company from "
  "Customer where Custno = :cno")
qry->DB_PARM_PROPERTY->ParamByName(
  "cno")->Value = "15";

The parameter challenge comes in when we use stored procedures and we want to create parameters dynamically using the CreateParam method. For BDE or IBExpress, we require two lines of code:

pr->Params->CreateParam(
  ftInteger,"USER_ID",ptInput);
pr->ParamByName("USER_ID")->AsInteger = 
  fUserID;

Porting to a TADOStoredProc, we find that the above is handled by one ADO method, and a change was made to the data type of the direction parameter:

Pr->Parameters->CreateParameter(
 "USER_ID",ftInteger,pdInput,0,fUserID);

Perhaps we can solve the above with a parameterized #define, but let’s leave the macros here, and look at an object-oriented solution that I thought about after solving our commercial software problems with the preceding solution.

An object-oriented solution

I experimented using an adaptor class pattern. This pattern is described in Design Patterns: Elements of Reusable Object-Oriented Software (Addison Wesley Longman Inc. ISBN 0-201-63361-2). It is also known as a wrapper class.

TTargetQuery is defined as a pure virtual class. There, we define all public properties and methods that will be needed. The implementation of the class is found in one of three classes that inherit from TTargetQuery. Each of these hides the VCL classes in a private data member, so that it is not visible from the outside. The member functions of the adapter classes map the function calls to the correct functions of their respective adaptee classes.

This configuration is complicated significantly by the fact that we will most probably inherit TTargetQuery itself from TDataSet, so that standard data-aware components can use the new query classes. Since many of TDataSet’s functions also need to be mapped to the adaptee classes, we will end up writing a hefty bunch of code before we can do anything useful with the class. This ended up in so much code that I abandoned the idea and implemented TTargetQuery without inheriting it from anything. I have not yet tested this in a real-life application, so I use my attached example code only to illustrate the concept. I briefly tested TABDEQuery on the BCDEMOS database. The other two adapter classes are not yet tested.

The code is still too much to discuss in detail in an article, but the following comments should be sufficient. The class hierarchy allows us to implement a common interface, and then we can swap between the three classes rather easily:

In a new project, I add a data module and to that, add either a BDE TDatabase component, or an ADO TADOConnection or an Interbase TIBDatabase component. Remember to set up the connection to the database as well. In the header file of the data module, specify either one of the following three definitions (and comment out the other two):

typedef TABDEQuery TFischerQuery;
// typedef TAIBQuery  TFischerQuery;
// typedef TAADOQuery TFischerQuery;

Using the query is very simple:

TTargetQuery* qry = 
  new TFischerQuery(this,0);
qry->SQL->Clear();
qry->SQL->Add("select * from customer");
qry->Open();
for (int i=0;i<=qry->RecordCount;I++) {
  ListBox1->Items->Add(
    qry->FieldByName("CUSTNO")->AsString
    + " " + qry->FieldByName("COMPANY")
      ->AsString);
  qry->Next();
}

I define all new types as TTargetQuery, so that I have a generic type, but I create them as TFischerQuery, so that the typedef can be used to switch all my code to another sub-type of TTargetQuery.

If we suppose that the BDE is used, the compiler will render code as if we typed:

TTargetQuery* qry = 
  new TABDEQuery(this,0);

A simple change in the typedef will change the query to be compiled as either a TAIBQuery or a TAADOQuery. No further change in the source code should be necessary, besides creating the correct DB connection object, and setting the connection parameters for the DB.

Since we are on that topic, you will notice from the previous listing that the constructor takes two arguments, and that I removed the line that assigns the database connection. I implement the query’s connection through the second parameter of the constructor. Each adaptor class has a static TList that contains connection pointers. Each class also has a static function to add new connections. We set the connection once only in the application (e.g. in the constructor of the data module):

TABDEQuery::AddConnection(Database1);

If we had an IB database or ADO, we would have used one of the following:

TAIBQuery::AddConnection(IBDatabase1); 
TAADOQuery::
  AddConnection(ADOConnection1);

Since these connections are added to a static list, they are available to all instances of the class. To allow the user to choose which connection to implement, I pass an integer to the constructor of the query. This integer is the ItemIndex of the static list, and it uniquely identifies the connection to be used. The integer will not pose any type conversion problems when we change from one query type to another, as the connection property itself did.

Again, my greatest obstacle was the ADO Parameters property. I implemented a TParams to operate in parallel with the Parameters property; so all classes work with the same type. Each time the Param is written, I write the Parameter, and each time a Param is read, I get its value from the Parameter property. The performance of my ADO example can be improved, though.

Conclusion

The preceding article illustrates that it is possible to port code between projects that implement different database connection strategies, without having to change more than three or four lines of code in the data module. This is of course only valid if you can refrain from the temptation to use the visual designer to create queries—this article’s info will be rather worthless if you have queries pasted on VCL forms.

C++Builder 5 users that wish to use ADO should download a patch called BCB5ADOUpgrade1.exe from Borland’s web site to fix an error in ADO queries.