[DB-SIG] Towards a single parameter style

[Matthew T. Kromer]

Kevin Jacobs wrote:

>On Mon, 17 Feb 2003, Anthony Tuininga wrote:
>  
>
>>Since you seem to have experience in this matter, I'll ask you.... :-)
>>
>>I take it that simply parsing for ? (or whatever is equivalent for the
>>different styles) and igorning quoting is insufficient? Could you give
>>some examples?
>>    
>>

Correct me if I'm wrong -- I dont use qmark notation ;)  but I count 
only 5 bindings here:

>Here is a "simple" example:
>
>  SELECT cost AS "?"
>  FROM   sales
>  WHERE  quantity  > CAST(? AS double precision)
>
	
	One


>    AND  shipdate  < ?::DATE
>  
>

    Two

>    AND  "shipped ?" IS NOT NULL
>    AND  comment NOT LIKE ? | '?'
>

    Three


>    AND  descr = '\'?\''
>    AND  orderdate NOT BETWEEN ? - INTERVAL '2 days' AND ?;
>

    Four and Five

>
>It is not intrinsically hard to parse out the parameters from the above
>statement, but it is hard to see how it can be done without significant
>performance penalty.  And this is a relatively simple example -- there are
>many other corner cases to consider (e.g., nested queries, literal escaping
>variations, runtime configurable SQL dialect support, etc..etc..etc...).
>
>-Kevin
>
>  
>
I'm fairly sure the RDBMS names don't get substituted into, e.g. 
"shipped ?" and I'm confident that string literals don't.

I'm also not sure how valid ?::DATE is -- I've never seen that before so 
I'm not sure what SQL dialect it is (could be my ignorance, too).

In any case, I would contend that MOST drivers tell the adapter what 
Python says the type is for bound arguments, and let the database do 
type conversion.

I could rip off a C based string parser that handled the quoted vs. 
unquoted characters with reasonable speed such that I could convert ? ? 
? into :1 :2 :3 or whatever in reasonable time.  (And :2::DATE is 
probably just as valid or invalid as ?::DATE <grin>)

A better question is if there are tighter rules to follow, e.g. 
<alphanumeric>?<alphanumeric>  being non-quoted ie WHERE FOO =?a -- 
ignore it or not?  I dont think I've ever seen a full lexer for this (a 
parser is irrelevant, so long as it can be tokenized).  The deal for me 
as a driver author is to be able to do string conversion on the SQL 
statement to a format that the underlying RDBMS supports; I let the 
RDBMS raise an error if it doesnt like a parameter rather than trying to 
out guess it.  Of secondary importance is to not use Python itself to do 
the string conversion, since Python is very slow at character-at-a-time 
operations.

An unrelated quibble has to do with how to cope with instances that are 
passed as binding variables.  I have DCOracle2 puke on them rather than 
attempt to string-ify them and bind the string equivalent (except in one 
special case ;)  This has never been an issue, so I'm not worried about 
it, but it would be easy enough to do.