[fpc-pascal]Common database access in Pascal

[Michael Van Canneyt]

On Thu, 25 Jul 2002, Fernando Lozano wrote:

> Hi Michael,
>
> > > [Common DB access]
> > >
> > Your remarks are to the point. I have started to code something like
> >
> > TDatabase
> > TTransaction
> > TDBDataset
> >
> > Which would cooperate together much like you would expect them to.
> >
> > >From TDatabase/TDBdataset etc, a specific descendent should be made for
> > each type of database. The Interbase components at this point do this.
> > unfortunately, the mysql components don't (not yet). It is my idea to
> > finish this work, but due to lack of time, I cannot start working on
> > this yet. (3 zillion other things requiring attention...)
> >
> > If you're looking for a unified approach in the sense of Borland's BDE
> > or even DBExpress, I have no plans for implementing this for the very
> > simple reason that I don't believe in this approach: If you want unified
> > database access, then I see more sense in making a set of
> > TODBCDatabase/TODBCTransaction/TODBCDataset
> > descendents which can then be used to access any needed database.
>
> Well, having a unified DB API like Perl DBI, PHP PEAR, Java JDBC and
> others saves much development time. It's not nice to change lots of
> lines of code just to move from one database to another.

Depending on how you would work, you'd normally have only one or two lines
of code to change:

Something like

DB:=TMYSQLDatabase.Create(Nil);
DS:=TMySQLDataset.Create(Nil);

Would become

DB:=TIBDatabase.Create(Nil);
DS:=TIBDataset.Create(Nil);

All the rest would remain 100% the same...

>
> I don't know how much work is involved for the designer of the API and
> the developer of the drivers, but I guess that with so many sucessfull
> free software DB APIs availabe it shouldn't be so hard to pick one of
> them and mimic its structure and implementation.

Sure. Note that I am not against a 'unified' approach - the only thing
is that I don't think we should repeat the BDE or DBExpress setup.

>
> Each Database class could have its own unique methods for its unique
> capabilites, but the standard connect/query/iterate should work the
> same way for any database, ideally using dynamic loading of libraries
> so the application don't need even to be recompiled to use a different DB.

The 'ideally using dynamic loading of libraries' is where I disagree.
I think that recompiling the application is not really a hassle.
See below for more argumentation.

For the rest I agree, but the OOP approach and the TDataset/TDBDataset/
TDatabase/TTransaction class tree already ensure that 99% of the code
will remain exactly the same when changing databases.

It is one of the reasons why I went one step further as Delphi and
introduced TDBDataset/TDatabase/TTransaction already in DB.pas: to
stimulate a uniform approach of databases.

>
> About using ODBC this does not look like a good idea except because
> it won't need a lot of custom drivers. No one uses ODBC to provide
> drivers although everyone can use ODBC when they have no driver
> availabe. It's much easier, for example, to use Perl DBD::Oracle
> or DBD::MySQL directly than to configure the unix ODBC (or Windows ODBC)
> support for these databases.

I understand. But I think a recompilation is usually not really a
problem. I work with DB apps since many years, and

1. It certainly isn't so that most apps are required to run on a variety
   of different databases. If they do, usually they DO use ODBC :-)
   The average application is designed to work with 1 fixed kind of
   database. As a consequence, many queries will be geared towards
   this particular database for performance reasons.

2. We've seldom had to switch databases. We did it only once
   (Access->Interbase).
   If you switch databases, I think the problem of different SQL
   syntaxes for many queries is more of a hassle than the fact that you
   would have to replace 2 lines of code as indicated above.

That is why I don't really see the need for this 'dynamic switching' and
would prefer to leave that kind of stuff to an ODBC layer implementation.

That said, everyone is free to implement a BDE or dbExpress type of
TDatabase/TDBDataset/etc descendent, which can handle dynamic switching
of databases. It's not high on *my* priority list however.

Michael.