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

[Magnus Lyckå]

At 17:24 2003-06-03 -0700, Chris Cogdon wrote:
>>In this particular case it will vary depending on the data source.
>>If the database happens to use the MS Jet engine, (i.e. MS Access)
>>the SQL could look like "SELECT * FROM T WHERE D = #1999-12-31#",
>>but for most other backends it should look like
>>"SELECT * FROM T WHERE D = '1999-12-31'". The #-version would only
>>work on Jet, and it's the only one that works on Jet. Depending on
>>national seetings, what kind of database you have etc, the date
>>string might look different, regardless of quote-style. Passed as
>>a separate parameter, it might work to send it as a COM Date object
>>if we're in Windows.
>
>Which is why I'm asking for it to be a part of the python driver. The 
>driver knows what type of database it is talking to, so it is perfectly 
>capable of generating the correct 'string-equivalent SQL' for the database.

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.

>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?)

>I appreciate that other DBMSes do not need to worry about quoting, since 
>they can use a different mechanism to get the parameters to the DBMS. 
>However, I claim that there are applications where 'pre-quoting' is 
>considerably more efficient for the application, and I'm asking that the 
>next revision of the API include a method to make a 'embed into SQL 
>string' function available to the application writer.

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.
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...

I doubt your statement about efficiency. In what way?

One could obviously imagine a quote function only for PostgreSQL
and MySQL drivers, but does that have to be in the DB-API? 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.

 From a debugging perspective, "quote" is certainly more useful in
the cases where the Python driver does the quoting, but in general,
I do feel it could be useful to see exactly what I would have sent
to the next step in the message chain if I did an execute.


--
Magnus Lycka (It's really Lyckå), magnus at thinkware.se
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The Agile Programming Language