executemany ('SELECT ...') (was: [DB-SIG] DBAPI-2.0 clarifica tions)

[Dittmar, Daniel]

> can you explain what an "array select" is
> and why choosing (a) would "hide" it on sap and oracle?  

The list of input parameter sets is sent to the database with one request,
resulting in one result set (the union of the result sets for each
individual parameter set).

'SELECT * from sometable where a = ? and b = ?' called with [(1, 2), (3, 4),
(5, 6)]

amounts to

SELECT * from sometable where a = 1 and b = 2
UNION
SELECT * from sometable where a = 3 and b = 4
UNION
SELECT * from sometable where a = 5 and b = 6

Another example that was given is 'WHERE a in (?)', but allowing a variable
number of elements of the list.

This is of course more efficient than sending three SELECTs and fetching and
closing three result sets.

If the DB API standard would require to produce a separate result set for
each input parameter set, then there would be no way to specify the
behaviour described above, thus 'hiding' this feature.

I must admit that it can rarely be used in real life because it's
implemented as a UNION.

'SELECT * from sometable where a in (?) and b in (?)' called with [(1, 2),
(3, 4), (5, 6)]
is equivalent to the example above, not to
SELECT * from sometable where a in (1, 3, 5) and b in (2, 4, 6)

Daniel

--
Daniel Dittmar
daniel.dittmar@sap.com
SAP DB, SAP Labs Berlin
http://www.sapdb.org/