[DB-SIG] .prepare()

[M.-A. Lemburg]

Bill Tutt wrote:
> 
> > From: Andy Dustman [mailto:adustman@comstar.net]
> >
> >
> > Some general digression/spewing:
> >
> > Why prepare()? Because, queries take time to parse. Complex
> > queries take
> > longer. In most databases, queries are parsed in the server.
> > If you have a
> > commonly used query, one that properly factored out the actual query
> > parameters, having a "pre-compiled" query in the server is a
> > big win. Even
> > better is if these queries could have some persistence in the
> > server so
> > they could be reused by many clients, which may each supply a
> > different
> > parameter set.
> >
> 
> Well, duh... However, there's no reason you need to expose this concept to
> the user in an explicit API.
> example implmentation:
> 
> # Dictionary mapping query text -> opqaue prepared query reference (sproc
> name, underlying db access
> # mechanism or something entirely different)
> preparedQuery = {}
> 
> def execute(self, cmd):
>         if not preparedQuery.has_key(cmd):
>                 preparedQuery[cmd] = conn.prepare(cmd)
>         else:
>                 conn.execute(preparedQuery[cmd])

Don't know about other databases, but ODBC can only prepare
one statement on one cursor. Once you prepare another statement
on the cursor, the previous one is lost, so making the currently
prepared command available via an attribute looked like the
right way for mxODBC.
 
> > I have direct experience with two different native database APIs. The
> > first is ODBC, which I think most people would justifiably consider a
> > bloated monstrosity.
> 
> ODBC certainly is annoying, but then most flexible APIs into databases from
> the C level are. Databases are  complex enough that life starts to suck
> bigtime.
> 
> OLE DB is better than ODBC by a long shot, but its also a very complex
> beast.

But it's nowhere near as portable as ODBC... on NT or Win2k
OLE DB may be the way to go, but on other platforms such as
Unix or Mac you'd have to revert to other means.

> [Andy's CORBA DB API]
> 
> You really should read the OLE DB specs. They specify COM interfaces for
> everything you've specified above and mroe...

AFAIK, COM is MS centric and I think Andy is targetting Unix
platforms here as well.
 
> SQL 7 is completly built using a superset of these OLE DB interfaces. OLE DB
> lets SQL 7 easily perform and optimize joins between different databases.
> 
> Utterly cheesy example:
> Joe wants to join table A in an Oracle database against table B in a SQL 7
> database.
> Depending on the index statistics information (on table B and on table A)
> the SQL 7 query optimizer can decide how much of the query can be pushed
> into the Oracle database, and how much of it should be done locally.
> 
> The OLE DB interfaces exposes all the necessary information that allows SQL
> 7 do perform this cool task.
> (Yes, people really do use this feature in real life.)

FYI, in ODBC you would do the same using an ODBC DB engine like the
one sold by EasySoft.

-- 
Marc-Andre Lemburg
______________________________________________________________________
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/