[DB-SIG] Database Independent SQL

[Bob Kline]

On Mon, 9 Sep 2002, Gerhard H=E4ring wrote:

> * Huy Do <huy@tramada.com.au> [2002-09-09 11:44 +1000]:
> > Hi,
> >
> > I'm about to develop a major Python application and was wondering wha=
t the
> > most database independent way of going about accessing a database in =
my
> > application. i.e so I can switch between databases almost instantly (=
a
> > little bit of work is also okay :-)).
>
> My recommendation: Put as much as possible into stored
> procedures/functions and views. Then you'll likely be able to use a
> very minimal subset of SQL.

I should think that this recommendation would push him in the opposite
of the direction in he wants to go.  The more of your application you
put into stored procedures, the more tightly you will be bound to a
single DBMS product.  That's why, for example, an Oracle consultant will
try so hard to get you to write most of your application in PL*SQL
stored procedures and functions (and tell you how important it is for
you to use VARCHAR2 throughout all your tables).

>
> > I know the DB API 2 handles the connection, querying etc. in an
> > independent manner but what I am also looking for is some sort of
> > library which provided a standard SQL syntax as well.

The ANSI SQL standard provides the common syntax, and a number of the
DBMS products implement enough of it to support building a portable
application.  A good SQL book (try Joe Celko's books, for example) will
give you guidance about which parts of the standard are the dark corners
which have traps to avoid in non-conforming implementations, though as
time goes on the available DBMS choices get closer and closer to the
goal.

You've got the two major ingredients for portability already:  standard
SQL and Python.  Take advantage of the portability of Python and use it
to implement the procedural parts of the application, rather than
proprietary stored procedures.  Where you do have to fall back on a
stored procedure or two (to achieve acceptable performance, for example)
be sure to encapsulate properly in order to minimize the job of swapping
them out if you do swap out your DBMS.

Good luck!

--=20
Bob Kline
mailto:bkline@rksystems.com
http://www.rksystems.com