[DB-SIG] db module wrapper
chris at cogdon.org
Thu Aug 19 18:33:21 CEST 2004
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 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
More information about the DB-SIG