[DB-SIG] cx_Oracle 2.5

[M.-A. Lemburg]

Kevin Jacobs wrote:
> On Wed, 17 Jul 2002, M.-A. Lemburg wrote:
> 
>>Interesting. Porting such an application to a sequence based
>>DB API module must be a nightmare though...
>>
>>I think we really need some sort of standard support for this:
>>a function which takes an SQL string, a parameter object (or
>>sequence of such object) and a paramstyle
>>and converts it to whichever other paramstyle format
>>is needed.
>>
>>Any volunteers ?
> 
> 
> Got one -- except that it is really slow, and needs to be taught all the
> details of the various SQL dialects.  (i.e., it is a full SQL parser)
> A much simpler verion could easily be written that only knows how to
> tokenize SQL and about a few syntactic landmarks.

I don't think you need to tokenize the SQL. The API
should take the paramstyle used in the SQL as parameter and
then you can extract the positions of the parameters
easily using e.g. re. You will only need to watch out for
quoting.

Here are the defined paramstyles:

                 'qmark'         Question mark style,
                                 e.g. '...WHERE name=?'
                 'numeric'       Numeric, positional style,
                                 e.g. '...WHERE name=:1'
                 'named'         Named style,
                                 e.g. '...WHERE name=:name'
                 'format'        ANSI C printf format codes,
                                 e.g. '...WHERE name=%s'
                 'pyformat'      Python extended format codes,
                                 e.g. '...WHERE name=%(name)s'

> Before I start, can people tell me all the wacky things that one can do with
> parameters in SQL statements?  I have a sneaky feeling that I don't know the
> whole story.
> 
> i.e., I'm sure some users attempt the following abuse of bound parameters:
> 
>   paramstyle = 'format'
> 
>   sql = '''SELECT foo_%s from bar;'''
> 
>   Where binding for, e.g. dbcon.execute(sql, 'a'), is implemented as 
>   sql % 'a'.

This is possible, even "select ... where x < %5.2f" would be.

-- 
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
_______________________________________________________________________
eGenix.com -- Makers of the Python mx Extensions: mxDateTime,mxODBC,...
Python Consulting:                               http://www.egenix.com/
Python Software:                    http://www.egenix.com/files/python/