Uncategorized

Creating database agnostic and database engine agnostic applications in Delphi

Two major dilemmas arise with database application development:

How to manage the different SQL dialects:

  • whilst sticking to ANSI-SQL 92 syntax will help, particularly with table joins, data conversion or calculation routines and simple data elements such as current date vary with each database vendor making code re-use a nightmare.
  • if you ever plan in supporting multiple vendors such as MS SQL server and Oracle, then you need to consider your application design to factor this in.

How to swap between different database engines:

  • DB Express may be fine for many functions, but it has limitations which are not present in Borland Database Engine (BDE), or you may wish to use ADO or other engine technology.
  • unfortunately, each engine technology has different programming syntax, and indeed different non-visual Delphi components for the database connection and the TQuery component.
  • furthermore, they tend to return different data types, for example DBX uses TSQLTimeStamp while BDE uses TDateTime, while BDE and ADO differ on how they determine which field is a numeric or integer field. This makes using persistent fields problematic.

On my research, there are 5 main Delphi approaches:

The traditional Delphi / MIDAS / Datasnap approach:

  • see here for details on how I use this approach
  • this requires maintaining separate datamodules for each database engine, and potentially separate datamodules for different vendors unless you write code to manually modify SQL statements dynamically.
  • easy to visualise and TClientDatasets work as designed with persistent fields, but not so easy to maintain as you need to remember to add your modifications to each version of datamodule.

The Dimeric Virtual Database approach:

  • in 2002, Dimeric Software created a database abstraction technology (VDB) for Delphi 6 to help address the above issues and add extra functionality such as database connection pooling, enhanced transaction capabilities, enhanced control over prepared queries, etc.
  • it uses interfaces to abstract the Dephi components such as TDatabase, TDataset and TDatasetProvider but leaves TClientDataset unchanged.
  • they support the following database engines BDE, ADO (dbGo), DBX (DataSnap), IBX (Interbase Express), ADS (Advantage database server), DBISAM, and Flash Filer 2.
  • it uses a set of “Function Escapes” to convert SQL statements to vendor specific statements on the fly – ie. in your SQL statement you would use [today()] and the VDB will convert this into the vendor specific SQL command to retrieve today’s date.
  • they have support for the following database vendors or dialects: MS SQL Server, Sybase, Oracle, Interbase, Informix, MySQL, Paradox, DB2, and MS Access.
  • this interface approach is powerful and inexpensive ($US99) but may not be the easiest to use if you wish to use persistent fields in TClientDatasets
  • see their website for more details.
  • Misha Charrett utilises this technology in his CSI Distributed Application Development Framework which makes developing multi-threaded applications easier – see here. This is freely available for use but does require Delphi 2005 or higher.

RemObjects Data Abstract:

  • a more expensive solution (~$US990) which will require you to replace TClientDatasets with their equivalent component as well as all the other non-visual database connection components.
  • you will not be able to use persistent fields but all fields on the client side are declared in the classes which means a change in the way you approach programming.
  • uses Schema to handle different database vendors
  • it does allow for data push functionality
  • there is no easy conversion from a legacy Delphi application.
  • see here for more details.

kbmMW Development Framework:

  • kbmMW is another option which may be considered and new licence for the Professional version is $US478
  • they do have a free, limited functionality version without source code but only for the current version of Delphi.
  • supports different database backends directly without having to write any code.
  • advanced connection pooling on both client to server and server to database, caching of result sets on both client to server and server to database
  • can mimic as a SOAP server, a WEB server, a AJAX server, a AMF3 server
  • only the Enterprise version supports multiple messaging topologies incl. Peer 2 Peer, Hub/Spoke and Broadcast using Publish/Subscribe based messaging via multiple types of fully developer accessible and configurable  message queues
  • supports client side controlled transactions, and supports transactions, even spanning multiple databases.
  • supports most database vendors
  • Automated datastore/database connectivity recovery on connection loss or database breakdown
  • compiled as an assembly, kbmMW can be used, feature complete, from within VS.Net to develop both client and server side code
  • a native PHP extension exists for both Win32 and Linux that allow direct access to a kbmMW based application server
  • an application server can also be accessed from portable devices via a fully portable native C client, a Compact Framework compatible native C# client, a SOAP interface, or an XML interface (only available in Enterprise version).
  • the free version “CodeGear” is available for Delphi versions 2007 and later.
  • unfortunately, attempting to install this D2007 version failed with the latest Indy version installed, it presumably needs the Indy version shipped with Delphi 2007 (ie. Indy 10.1). Design package could not load as procedure entry point @Idtcpclient@TIdTCPClientCustom@SetBoundPortMin$qqrxi could not be located in IndyCore100.bpl.  The difference in the Indy source seems to be that the constant for this procedure is now TIdPort:Word instead of integer;
  • this is an issue as the CSI framework requires the latest Indy version installed.

ASTA:

  • ASTA is another data abstraction technology from the 1990′s
  • in Nov 2010, they announced ASTA 4 with Unicode support for Delphi 2009, 2010 and XE.

Desiderata software and consultancy

Desiderata software® and consultancy is based in Melbourne, Australia and specialises in clinical database systems and clinical decision making algorithms.

Desiderata:

L. desiderata – something desired

in decision analysis – the desired characteristics of a decision making algorithm

and, of course, the famous poem by Max Ehrman – go placidly amid the noise and haste ...

some excerpts from the poem which reflect the ideals of Desiderata Software:

Speak your truth quietly and clearly; and listen to others,
even to the dull and ignorant; they too have their story.

Exercise caution in your business affairs,
for the world is full of trickery.
But let this not blind you to what virtue there is;

many persons strive for high ideals,
and everywhere life is full of heroism

And whatever your labors and aspirations,
in the noisy confusion of life, keep peace with your soul.
With all its sham, drudgery and broken dreams,
it is still a beautiful world.
Be cheerful. Strive to be happy.

Desiderata Software is a Registered Trademark ®