Re: Writing oracle/postgress generic SQL - Mailing list pgsql-general

On Fri, 23 Feb 2007, David Fetter wrote:
> On Fri, Feb 23, 2007 at 10:23:56AM +0100, Ben Edwards wrote:
> > Anyone know of any guidelines for writing SQL which works under
> > Oracle witch will also work under postgress.  This is to ensure that
> > SQL written for an Oracle database can be migrated to postgress
> > later.
>
> You've just bumped into the problem that while standard SQL exists,
> only Mimer and possibly DB2 implement it.  The presentation below
> outlines your main choices for supporting more than one DB back-end,
> and they're all expensive and troublesome to maintain.
>
> http://www.powerpostgresql.com/Downloads/database_depends_public.swf


With all due respect to Josh's presentation, there's a lot more to the
story than those couple of slides. (They were meant to be given, I'm sure,
along with a talk in which the speaker provided most of the value.) And I
don't think launching an attack on MySql is helpful to this dialogue,
though I do understand the point Josh is making...

There are other choices. For example, Science Tools, back in 1997, faced
with the similar but slightly different problem of being a vendor
supporting multiple RDBMSes for client data, could have taken the typical
choice of managing different code branches for each of the RDBMSes it
supports.  Instead, we wrote an SQL dialect translator that presently
supports five (and soon six) RDBMS platforms - and could probably support
all the rest if only someone cared enough to configure them - and this
translator is available to customers, not just embeded for the exclusive
use of Science Tools' applications. You link your user-application code to
our library and you can send it any version of SQL, either statically or
dynamically, and it automatically translates into the correct dialect for
the database engine you're connected to. It does both DDL and DML and it
has command-line tools available, too, so you don't have to link your apps
if you don't want to. Presently supported are:  Postgres (of course!),
Informix, DB2, Sybase, and also Oracle - yes, of course, them, too.
(OpenIngres is undergoing testing right now for certification sometime
this spring.)

Are there things it misses? Yes, but not much. I'll take the wild guess
that more than 80% of applications are completely and adequately served.
It has pass-through capability so you can still get at engine-specific
features, though it does completely side-step stored procedures as these
are vastly harder to automate conversion of - we just do the SQL. When
calling a DBMS from our library, we handle error recovery, database
reconnection, optional DBMS independent journaling and even important
aspects of security. When parsing DDL, it (optionally) throws warnings of
incompatability, though, as a practical matter, most engines have now
removed most of their older limitations that made this vital in their
earlier versions. (We support versions of all five since about 1997 and,
as there were so many small changes along the way, we provide a
configuration mechanism where you can tell it the limitations of your
version such as attribute length, maximum length of varchar, etc.)

Regards,
Richard

--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
rtroy@ScienceTools.com, http://ScienceTools.com/