TECH: Database independence

From: Emlyn O'regan (
Date: Mon Oct 08 2001 - 21:17:46 MDT

Hi all,

I've got a technical question, so I hope there are some technically minded
people out there (erm...). Warning to non-IT types... you will likely find
this entire post entirely pointless, so switch off now!

I'm doing YADA work at the moment (Yet Another Database App), and I've run
into an old problem for which there never seems to be a really good
solution. This particular app is targetted at RDBMS Brand X (actually it's
Sybase), and now there is a need to retarget it to another RDBMS. I am
positive that more databases will creep into the story over time, so this is
the old database-independent-app problem.

The big drama with retargetting the app is the mighty mountain of SQL which
is database specific. RDBMSs are supposed to follow standards with their SQL
- yeah, right. While they do look superficially similar, SQL dialects differ
in fundamental join syntax, in built in functions, in the kinds of primitive
data types they provide operators for, etc. This can be a really big hassle,
and from an ongoing maintenance point of view it is big enough to be a
project killer, especially if it forces a codebase to be forked into one
copy for each database to be targetted.

In the past I've worked on systems using various approaches to solving this
problem, from parallel code bases for each database (yes, really!
urrgghh....), through database tables full of alternate forms of queries for
each desired database (still nasty to maintain), even apps which try to
stick to a common simple subset of SQL which works across all dbs (a noble
but doomed approach). The nicest approach I've used so far was a system I
built for translating the SQL from one database's dialect to another at run
time, so that I could stick to one brand of SQL with impunity (within some

I'm really truly sick of this stupid problem popping up all the time, and I
think it's time to nail it. I've got some ideas for the Grand Solution to
proprietary database evilness, and I'm ready to go and build these solutions
myself. However, before I go off half cocked, I was hoping others could
share their experiences with this common problem, solutions they've
used/know of. I don't want to reinvent the wheel, but the best that google
has been able to tell me is that yes, this really is a problem without a
decent solution, and for which one may buy all manner of half baked partial
fixes. If people can help me get a decent feel for what is available to
address this problem, I can move forward in some sensible manner.


Confidentiality: The contents of this email are confidential and are
intended only for the named recipient. If the reader of this e-mail is not
the intended recipient you are hereby notified that any use, reproduction,
disclosure or distribution of the information contained in the e-mail is
prohibited. If you have received this e-mail in error, please reply to us
immediately and delete the document.
Viruses: Any loss/damage incurred by using this material is not the sender's
responsibility. Our entire liability will be limited to resupplying the
material. No warranty is made that this material is free from computer virus
or other defect.

This archive was generated by hypermail 2b30 : Sat May 11 2002 - 17:44:12 MDT