[DB-SIG] Towards a single parameter style

[Magnus Lycka]

I'm curious about something...

I've never used Perl-DBD or whatever it's called, but my impression
is that it enables perl scripts to be written so that they can
communicate with different database brands without rewriting any
code as long as we stick to that common subset of SQL dialects
which is called ... SQL. (Well, roughly.)

My first question:
Does this really work well in practice? I realize that SQL dialects
vary, with different string concatentation syntax, data types,
functions etc, but if we stick to Entry Level SQL or whatever is the
common denominator, which would be fine for me almost all the time,
does Perl-DBD work as expected? (Not that I want to code in Perl, but
for those who can live with that... ;)

It seems from the discussion here, that supporting the kind of
features that Perl-DBD has, in Python the database drivers, would
make them slow and much harder to build. Still, there are more than
30 Perl DBD drivers at CPAN. I'm sure they are *all* harder to
maintain than the Python drivers, they are after all written in
Perl ;), but it doesn't seem to be impossible...

But what I wonder is:
Are they really much slower than their python counterparts?

A long tim e ago, I have written code that ran against at least
Oracle, Informix and Gupta SQLBase without any major problems.
It needed some tweaks, and testing on all platformas, but apart
from connect strings, you could typically use a common SQL syntax
for all platforms. In that case our development tool (JYACC App-
lication Manager (JAM), now Prolifics Panther) handled parameter
parsing from an extended version of :named parameters, but other-
wise we wrote SQL as we would for either of the products, just
that we would have to cater for three sets of bugs at once, like
Oracle 7 needing ... = " " instead of ... = "" to compare correctly
with an empty varchar field, slightly different views on isolation
level interpretation etc.

The thing was, that we had one application running, with a single
code base for roughly 70 different customers, and they had several
different databases. Some had 200 users, others had 5. Some had
Oracle as a requirement, others wanted the cheapest possible and
so on. Being able to cater for a variety of backends with a single
code base was a big benefit. Of course!

It is certainly possible to add such a layer of uniformity above
the DB-API, as those who don't want to change the DB-API suggests
now and then, but then it's not a standard solution. This uniformity
layer won't be shared between a lot of modules from different sources.
We don't get plug and pray...

The point with the Perl-DBD is that if you have x different front-ends
that support the Perl-DBD without relying on vendor specific SQL
extensions, and you have y different DBD back-ends, you have x * y
different possible configurations. If you plan to use several different
front-end applications in the system, you can do that without loosing
back-ends. And vice versa, you can add/swap back-ends without loosing
front-end functionality. Ok, life isn't always as simple as the theory,
and worst of all, this would be a Perl system, but it's a good concept
with a single, uniform mediator between front-end and back-end. There
would not be a commercial mxODBC package if this wasn't very useful.

With the Python DB-API it's very different. Sure, it's typically possible
to add support for back-end X without having to rewrite everything, but
it's usually not going to work out of the box for more than one particular
back-end. We don't get anything close to the multiplicity of choice that
the Perl system offers.


-- 
Magnus Lycka, Thinkware AB
Alvans vag 99, SE-907 50 UMEA, SWEDEN
phone: int+46 70 582 80 65, fax: int+46 70 612 80 65
http://www.thinkware.se/  mailto:magnus@thinkware.se