[DB-SIG] paramstyle specification

[Michael Bayer]

On May 21, 2013, at 3:24 PM, M.-A. Lemburg <mal at egenix.com> wrote:

> On 21.05.2013 20:55, Michael Bayer wrote:
>> 
>> On May 21, 2013, at 2:30 PM, M.-A. Lemburg <mal at egenix.com> wrote:
>> 
>>> 
>>> Something we do need to address in a paramstyle spec for
>>> qmark and named is SQL comments. Perhaps easiest would be
>>> to disallow them in SQL statements passed to .execute*().
>> 
>> um, meaning no SQL comments allowed in a call to cursor.execute() ?   That would be a really big problem for folks who pass comments in their SQL as a means to help with database query log parsing.   There are definitely folks who do that, and it's very useful.
> 
> Well, I know it's useful, but it also makes parsing SQL for
> parameter markers much harder :-)

I'd definitely want DBAPIs to be willing to take that on, though, rather than disallowing comments.


> 
>>> PS: mxODBC 3.2 implements both styles and does conversion from
>>> named to qmark. When choosing named style, we explicitly disallow
>>> question marks to appear in the SQL statement to detect errors
>>> early and give proper error messages to the user, rather than
>>> some obscure "too few parameters for statement".
>> 
>> you used the word "underspecified" a moment ago which I found frightening, but then three paragraphs later it's just the word I need - I think KISS should be the rule here, the parser is only aware of one paramstyle at a time, and doesn't try to do any nannying of things that look like other paramstyles.   
> 
> I'm not sure I understand.
> 
> With "underspecified" I meant that we leave the other paramstyles
> in the same unspecified state as they are now (or rather leave them
> specified by the database module implementations).

Right, my vague idea here is that how to deal with ? or :names in comments would also be "underspecified".   I.e. let's not even get into it.

> For the two main paramstyles we're currently discussing, qmark
> and named, we will have to put more effort into specifying
> what they mean and how they work.
> 
> I agree that adding magic auto-selection of SQL parsers is
> not a good idea. If you setup a cursor to expect qmark style,
> it should complain loudly when you try to pass in named style
> parameters or perhaps even named style SQL.

I'm guessing a bit what we mean by "auto-selection", here are the two scenarios I can see:

1. the cursor knows upfront what paramstyle is expected.  This is achieved via the .paramstyle setting.   When execute() is called, it parses the string appropriate to that paramstyle, and expects the parameter argument to be either a sequence or a mapping depending on if the style is positional or named.  if the sequence/mapping is mismatched to the paramstyle, an error is raised.

or 

2. the cursor knows upfront that it will deal with a single "positional" or "named" parameter style.   When execute() is called, it examines the parameter argument to see if it is a sequence or a mapping.  Based on that, it determines to parse for the "positional" or "named" style.   

I'm guessing that #2 is the "magic auto-selection" here.  I don't have a problem with it as I think some DBAPIs already do that, but I don't have a huge problem if we go with #1 either.  

But I would like to get it specified what happens when there are *no* parameters sent, as the DBAPIs behave inconsistently in this regard (some parse the string, others don't).

As a total aside, I also have issues with some DBAPIs that accept *only* a list for the "sequence", others that accept *only* a tuple (this may be only when the parameter list is empty, however).   So specifying the significance of "empty" parameter lists as well as how to test for "sequence" would be nice too, so that I can hit the DBAPI authors with bug reports backed up by the spec.