[DB-SIG] Why the varying paramstyles in DB-API?

Greg Stein gstein@lyra.org
Mon, 20 Nov 2000 23:40:16 -0800

On Mon, Nov 20, 2000 at 11:04:11PM -0600, Paul DuBois wrote:
> Why are there so many different paramstyles for DB-API?
> Given that one benefit of the API is to provide a uniform
> access method across database engines, doesn't the non-uniformity
> of parameter styles diminish the extent to which that benefit
> can be realized?

The idea is to provide a *similar* access method across engines. If you use
DBAPI against one database, then you know how to use it against others. But
that doesn't mean it is used exactly the same.

There are way too many differences between databases to create any kind of
system that can encompass all of them. ODBC attempts to do so, but even that
has a bazillion runtime query mechanisms to find out *how* to run against
the database. And even then, you're probably *still* not portable. For
example: cursors aren't portable, certain SQL functions, some formatting
stuff, or even SQL itself.

The DBAPI helps you transition, but it doesn't create a totally safe "plug
in whatever you want" system.

Specifically, in this case, the formatting codes are database-specific. The
DBAPI does not attempt to enforce a particular form, which would simply
serve to make every DBAPI developer have to implement a lot of glue if that
form did not correspond to their database's form. Some databases use '?',
others use %1, others something else. Those changes are reflected up to the
DBAPI client.

Marc-Andre started some work to try to collect all the differences between
databases, then to use that information to write a higher-level API that
hides the differences. More power to him :-), but I've been down that road
before. The result is the DBAPI: similar across databases, but it isn't
going to break a leg for you because it simply can't get rid of all


Greg Stein, http://www.lyra.org/