[DB-SIG] API suggestion: expose 'quote' method

[Chris Cogdon]

On Tuesday, Jun 3, 2003, at 00:23 US/Pacific, M.-A. Lemburg wrote:

> I don't buy that. By "quoting" you actually mean "binding" and you
> thus lose all the benefits of having the SQL separated from the
> data.
>
> The good thing about binding parameters is that the user
> does *not* have to worry about quoting (plus it usually buys
> a performance gain for next to nothing).
>
> Of course, if some APIs think it's worth exposing such a quote
> function, they are free to do so. It shouldn't be required, though.

Okay... let me paraphrase your argument to see if I got things straight.

Exposing the quoting mechanism would make sense on APIs for DBMSes that 
require a single SQL string be passed. However, some DMBSes use a 
different query passing mechanism, where the SQL query with embedded 
'?' marks (or whatever) and parameters in binary format are passed 
separately. While somewhat more complicated, this is a performance gain 
as the DMBS query passer does not need to parse the parameters, nor 
does the application need to convert them into a string.

For these types of DMBSs, quoting and binding are different operations. 
For things like PostgreSQL, they're the same.

Is that correct?

Now, my counter-argument :)

I would suspect that these DMBSes still have a way of representing any 
literal value inside the SQL string itself. For example:

cur.execute ( "select * from employee where name like ? and 
type='manager'", name_criteria )

If not, I would then have to break out 'manager' into a parameter just 
to be passed to the DMBS. Now, if this is true, then the DMBS has a 
quoting convention that I believe should be exposed. There are many 
applications, such as the one I described previously, where this would 
be useful. Yes, it lowers performance somewhat, but the alternative is 
to jump through a lot of hoops to get the separated string and 
parameters to the 'execute' method, or use a quoting function external 
to the API (which, being separate from the API layer, is error-prone).

I'm suggesting that we make this part of the API (2.1, perhaps) so that 
there's something there my application can depend on. I believe it's an 
important part of dealing with the DMBS, and should be part of the 
specs/requirements.


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