DBI cursor behaviour with multiple statements?

[Paul Boddie]

Henrik.Weber at sys.aok.de (Henrik Weber) wrote in message news:<7e964d1d.0210090210.724589c5 at posting.google.com>...
> 
> In the last few days I have made some experiments with ADO and the Jet
> OLE DB provider. They seem to understand several syntax styles for
> parameters more or less directly. For the rest I think it is possible
> to reformat the query string. Alright, I'll make the paramstyle user
> configurable.

It was just a suggestion, but the pyformat paramstyle is misleading,
in my opinion, and should not have been proposed by the DB-API
specification.

[...]

> No reason to apologise. Basically I was under that illusion. I hadn't
> given much thought to parameters when I posted my first message. Well,
> I got answers for two questions by posting only one. That's pretty
> efficient ;-)

Usually I give no answers and people somehow work it out by
themselves, so you've made me work harder than usual. ;-)

> >   SELECT * FROM table WHERE column IN :1
> 
> Would you also expect the database module to know to place parentheses
> around the placeholder? The Jet Provider complains about them missing,
> so the user at least has to write:
> 
> SELECT * FROM table WHERE column IN (:1)

That's an interesting point, but it could well be an artifact of the
SQL parser used in the Jet stuff. I would expect parentheses to be
used to indicate an expression or a sequence, where the sequence would
possibly have more than one comma-separated value. Perhaps, the Jet
implementation specifically treats the content of the parentheses as a
sequence in this particular case, but the binding mechanism only
supports single values. There are certainly many possible
explanations, but only one of them is correct. ;-)

> The Jet OLE DB provider does not like lists or tuples as parameters
> directly. What might work is to try to bind the parameter. When the
> attempt fails determine if the parameter is a sequence and if so
> replace the placeholder with a number of new placeholders depending on
> the length of the sequence. Then the elements of the sequence could be
> bound one by one to the new placeholders.

Yes, this would be a good fallback technique that could be useful in
other database modules, too. (Weak hint to other module maintainers!)

> Thanks for your input. It will help me address some issues I'm just
> now starting to come across.

I've never actually written a proper database module, but I have
modified and debugged one or two, and I did once start an iODBC
wrapper. In these days of Pyrex, it's tempting to give it another shot
instead of using SWIG. Still, it's good that you find this discussion
useful.

Paul