[DB-SIG] Towards a single parameter style

[Kevin Jacobs]

On Mon, 17 Feb 2003, Matthew T. Kromer wrote:
> 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:

That sounds right.

> 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).

It is PostgreSQL type coersion syntax.

> 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.

Sure, IF the driver and backend support real prepared queries with bound
arguments.

> 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>)

Sure, this is what I mean by an SQL tokenizer.  You need to be able to
lexically distinguish literals from expressions.

> 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?

Do you mean something like ''' WHERE FOO=?AND"BAR"=?OR't' '''?  It should be
valid under the SQL92/99 tokenizing rules, though that says nothing about
what vendors have implemented.

Here is another case: several RDMS allow runtime configurable SQL syntax
specification.  What happens when the dialect is changed at runtime, and
requires your tokenizer to know which mode is active?  What happens to
systems where these settings are local to the given active transaction and
the transaction is committed via pass-through SQL and the dialect reverts to
the default connection dialect?

> 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.

It is a significant issue for me.  We have a type-mapping layer implemented
over DB-API to handle any such conversions.

-Kevin

-- 
--
Kevin Jacobs
The OPAL Group - Enterprise Systems Architect
Voice: (216) 986-0710 x 19         E-mail: jacobs@theopalgroup.com
Fax:   (216) 986-0714              WWW:    http://www.theopalgroup.com