[DB-SIG] Other extensions

[M.-A. Lemburg]

On 2007-05-15 18:06, Art Protin wrote:
> Dear folks,
>     I have lots more questions about ways that the API could and possibly
> should be enriched.

There have been some discussions about this, but since no standard
API could be found, no additions to the DB-API were made.

ODBC has a very complete set of catalog functions for querying
meta-data of a database. It works by creating result sets that you
can then fetch using the standard DB-API tools, so it's fairly
straight forward to use.

Internally, most ODBC drivers map these function calls to standard
SQL SELECTs which work against the internal system tables or call
special stored procedures in the database to create the result sets.

I suppose the same could be done at a Python interface level (which
could be the DB-API level or a level above the DB-API).

> A. Stored Procedures:
> 1.    The API provides a method for calling a stored procedure.  Has there
>     been any discussion about how a user/application might discover the
>     names of such store procedures?
> 2.   Has there been any discussion of how a user/application might create
>     a stored procedure?

This can normally be done using standard .execute() calls.

> *   My implementation has made some attempt to address this.  All of our
>     queries are "named" and "stored" but they are either stored with the
>     session (connection) or with the user account (as provided in 
> connect()).
>     Everything stored with the session vanishes when the connection closes
>     and everything stored with user account is visible by all connections
>     using that account.  Thus I made visible objects of the class Server 
> (via
>     an attribute of connection objects), keep all the account info there and
>     provided some methods on server objects to create persistent named
>     queries and to control access to them by other accounts.  I have no
>     method to destroy a persistent query yet.

Like everything that deals with stored procedures, this is highly
database specific.

> B. Metadata
>     Not all DBMSs provide SQL access to the system tables.  In fact, the
>     DBMS I work with most is one that doesn't.
> 1. Has there been a discussion yet about how a user/application might do
>     discovery of the table names?
> 2. and the column names with a table?
> 3. and the types of the columns?
> *    My implementation has done naught to address this limitation.

See our mxODBC interface for how this can be done via catalog
methods:

	http://www.egenix.com/products/python/mxODBC/

> C. Non-SQL Queries
> 1. Has there been any discussion of how a user/application should present
>     queries that are in some other query language?

No. The DB-API is about relational databases and SQL as query
language. The interfaces may also be suitable for other query
languages, but that's out of scope for the DB-API.

> 2. Has there been any discussion of the representation of query language
>     names?
> *    My implementation had to address this because our DBMS has its own
>     preferred query language and management requires that I provide access
>     to it (which I accept as perfectly reasonable).  To avoid confusion 
> that might
>     arise when trying to recognize the difference between it and SQL, I 
> simply
>     added extension methods like Cursor.exec_alt(prog, parm, keys) where
>     prog is just the (non-SQL) program in a string, parm is just 
> parameters for
>     the query (just like for .execute()) and keys is a list of keys to 
> use when
>     parm is a dictionary (to linearize the parameters for handing off to 
> the DBMS).
>     But this does not address how a third party application might 
> discover that
>     an alternative language is available nor how it would know how to pass
>     such a query from a sophisticated user to this alternative method.

Unless the .execute() method signature doesn't provide the
necessary detail, I'd generally do this by passing an additional
(keyword) parameter to .execute().

I don't think that the DB-API should require a mechanism for
querying the query language as this is normally always SQL (in
some dialect).

>     I doubt this is a complete list, but my mind has gotten empty while 
> writing
> this so I will send it as is.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, May 15 2007)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611