[DB-SIG] db module wrapper

[Chris Cogdon]

On Aug 19, 2004, at 08:57, M.-A. Lemburg wrote:

> I don't understand why you'd want to access the quoted
> SQL string with embedded parameters. After all binding
> parameters exist to make all this quoting unnecessary and
> indeed, many drivers pass the SQL string and the parameters
> well separated to the database. The database then builds
> an access path using the SQL string (with binding parameters)
> and runs the access path against the parameters that are
> specified as separate part of the request.

I can certainly understand why, because I've asked for this kind of 
thing in the past.

If I was writing a program that can, programatically piece together 
'where' criteria based on multiple, highly-varying external sources 
(such as tens of checkboxes a user on the other side of a web browser 
might select), I would want to have a structure that could return 
'where snippets' (which would be later joined by ANDs, and stuck onto 
the query).

The EASIEST way to do this is to be able to get access to the quoting 
mechanism (and different databases have different ways of quoting, for 
example, postgresql uses single quotes, and mysql uses double quotes 
(unless you put it into compatability mode)) and then pass plain old 
strings back into the section of code that actually issues the query.

One program that does exactly this is ViewCVS. in query.cgi there's a 
bunch of inputs and checkboxes. Each input, when used, generates a 
where snippet (i made up that name just now), plus a list of tables 
that needs to participate in the query. Once each input component is 
polled, all the where snippets and table requirements are collected, 
duplicates removed, and the query is pieced together.

The only reason it works as simply as it does is because ViewCVS has 
access to the quoting mechanism. Unfortunately, because there is no 
'standard' for getting access to the quoting mechanism, I cannot drop 
in another database, or even another database connector, unless it 
happens to have the same, non-standard, function for quoting attributes 
or I made modifications to the program.

One thing I learned when I pushed to have such a function included as 
part of the spec (even if it was OPTIONAL) is that many databases do 
NOT quote variable objects, and do NOT pass the entire query as a 
string to the backend, but instead pass a mixture of a string, and 
various binary objects, as a 'tuple'. Since the binary objects are 
passed 'raw', there is no need to conver them to a string only to have 
them converted back to binary again in the parser. Overall, saving 
time. (unfortunately, this doesn't take into account that some binary 
types need to be converted ANYWAY, such as handling endianness)

(In fact, the alternative to passing where snippets around is to 
emulate this behaviour... passing a format string and a list of 
parameters as a tuples around - which is awfully more complicated)

So... to Marc... that's why I think the DB-API 'community' is reluctant 
to standardise a way of getting access to the quoting mechanism: some 
databases (eg, oracle) don't actually 'do' quoting.

But... to the DB-API community, I still hope that you can see that, at 
least with databases that *DO* do quoting, that there is a good 
argument for making access to the quoting mechanism an optional but at 
least STANDARDISED part of the specification.


-- 
    ("`-/")_.-'"``-._        Chris Cogdon <chris at cogdon.org>
     . . `; -._    )-;-,_`)
    (v_,)'  _  )`-.\  ``-'
   _.- _..-_/ / ((.'
((,.-'   ((,/   fL