Re: Writing oracle/postgress generic SQL

On Fri, 23 Feb 2007, David Fetter wrote:
> On Fri, Feb 23, 2007 at 08:28:06AM -0800, Richard Troy wrote:
> > 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.
>
> With all due respect, the presentation was if anything an
> understatement.

Yes; it didn't say very much. I'm sure Josh, as speaker, articulated what
wasn't in those slides, but we didn't get the benefit of that on the web.

> Unless, as with rare beasties like Science Tools, the
> major purpose of the application is to support multiple DBMS
> back-ends, it's just too expensive. Even in those rare cases, it's
> expensive.

I guess anything you have to pay for is too expensive. (Sounds like dogma
to me. And you know what dogma makes - just don't step in it.)

> > 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.
>
> That says something about the applications you've seen, and not about
> the adequacy of such a library.

That remark is uninformed and arrogantly presumptuous about both me and
the library, and uninsightful regarding the implementation of
applications. It's also needlessly offensive, if you'll forgive the pun.

> What point is there in using a
> powerful tool like an RDBMS and then hobbling yourself by only using
> 10% of the available features? It's certainly a bad thing to do by
> default.

10%? Whatever. I never said anything of the kind - and I'm reminded that
an unsupported argument can be dismissed without support. But there ARE
good reasons. We read on this very list about two weeks ago a long
treatise on the subject by an obviously long-in-the-tooth DBA type who
articulately took at least four pages to tell us why it was his practice
and advice to always be able to move to another RDBMS. Perhaps read the
archives and become informed...

> > It has pass-through capability so you can still get at engine-specific
> > features, though it does completely side-step stored procedures
>
> Oops! There went 60% of the code in some of the databases I've seen
> in production. 80% in at least one case I've seen in the past year.

Lots of people use stored procedures and some people over-use them while
some others under-utilize them in their architectures. It should be no
surprise that some people follow dogma while others consider every arrow
in their quiver. Yet I detect a certain flippant bigottry in your response
- Oops! Perhaps a more considered argument would be effective than just an
attack - that is, presuming there's a considered argument to be made.

The short of it is that Science Tools is surely not alone in having
developed an SQL dialect translator, though we may be the only ones to
offer it to customers. Either way, automated dialect translation, whether
by us otherwise, is another useful choice whether _you_ like it or not.

Ciao,
Richard

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