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

Chris Cogdon chris at cogdon.org
Tue Jun 3 21:20:14 EDT 2003

On Tuesday, Jun 3, 2003, at 19:40 US/Pacific, Magnus Lyckå wrote:

> The adodbapi *doesn't* know what's behind that ODBC data source. It
> might be PostgreSQL, MySQL, DB2, Sybase, Oracle, SAP DB or Jet etc.
> They can all behave differently.
> The Python driver has no clue, and even if it might be able to detect
> it, you can't honestly expect it to contain the code to handle all
> possible databases? I don't think there is a single SQL database out
> there that can run on Windows but lacks an ODBC driver. An ADO or ODBC
> driver would need to know every single database syntax out there, and
> it would only use that knowledge in this particular case, since it
> passes the parameters as is on execute.

Point conceded.

>> That's EXACTLY what all of the PostgreSQL APIs do right now. It's 
>> their JOB to figure out how to get those parameters into the SQL 
>> string to send them to the DBMS.
> Only because PostrgreSQL fails to support Dynamic SQL and CLI.
> All the PostgreSQL drivers have to cover for this shortcoming,
> just as drivers for MySQL. That is not a feature, it's a
> shortcoming in those servers. (At least PostgreSQL supports
> embedded SQL, right?)

Clue me in here. What's Dynamic SQL, CLI, and Embedded SQL ?

> It's always possible to build up entire SQL strings without
> the involvement of the DB-API. At least the application programmer
> can decide what backends and features he wants, and learn them.

Granted. However, the application programmer would also need to know 
how to correctly represent particular datatypes as a string, and that's 
something that 'string-only' APIs do right now. In those cases, it 
'makes sense' to be able to call exactly the same function the API uses 
to convert those values to strings. Even now, that's driver-dependant 
(pg uses quote, PgSQL uses _quote, and it's not available at all on 
PoPy nor psycopg).

But... I DO understand the difficulty in exposing a function that 
doesn't currently exist (such as the ODBC driver). And it may NOT be 
worth the difficulty in adding that functionality as a requirement for 
a later API. (Perhaps optional? At least if it's doable, we could get a 
consistent interface).

> The db-api coder for an ODBC driver will have a hell of a job
> trying to predict what the application coder might want to do...

Point conceded.

> I doubt your statement about efficiency. In what way?

I've cited examples before, but... if they're not clear enough, I think 
I'd need to cite a complete working model. Can we take it as read that 
there may be SOME applications where this ability would be useful ?

> One could obviously imagine a quote function only for PostgreSQL
> and MySQL drivers, but does that have to be in the DB-API?

My argument is yes, because the API knows more about the database it is 
connecting to than anything else... in some circumstances. In the 
others, perhaps it could remain unimplemented, and the application 
writer is on their own.

>  It
> seems to me that this is what you are asking for. Naturally, one
> could put such logic in the drivers for Oracle or DB2 for instance,
> but why? It doesn't really belong there. In MySQL and PostgreSQL
> it's already there: It's part of the execute call today.

Yes, but the interface is non-standard, or not reachable. If it was in 
a consistent place (as I'm suggesting), then I could swap between 
drivers or databases at will.

I concede that, given that some DBMSes do not need to quote internally 
at all, that perhaps the solution to the application, to convert to 
strings before reaching the execute method, is the wrong solution.

How about I try and reduce my application down to a simple example 
using quote, and then rewrite it using my somewhat more complicated 
alternative, and you can all pick over that.

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

More information about the DB-SIG mailing list