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

[Magnus Lyckå]

I don't see a need for this quote function for building SQL
statements, and I don't see how it could work cross platform
as presented, but I think it could be a good way to see what
is happening under the hood. I would like to have that for
pure debugging and logging purposes.

Chris Cogdon wrote:
>I believe my suggestion was to expose the quoting mechanism in a way that 
>EXACTLY REPLICATES the mechanism inside the API. Reiterating my first 
>email on the subject, the following two examples would be equivalent:
>
>cur.execute ( "some SQL statement", parameters )
>cur.execute ( db.quote ( "some SQL statement", parameters ) )
>
>In the first example, the SQL and the parameters are passed to execute, in 
>the second, only the string, but the SQL statement and parameters are 
>passed to the API's 'quote' method in **exactly the same way** as it's 
>passed to 'execute' in the first example.

I don't think your example can work reliably for all platforms
quite like that. But this might work:

cur.execute( *db.quote( "some SQL statement", parameters) )

Not that it would be of much use though...

Let's take a concrete example. Let's assume that we use either
mxODBC or adodbapi on Windows, and connect to a System data source
called "databaseX". It's just an ODBC source, and we don't know
what kind of database it is. What do we care--it speaks ODBC.

Our SQL is "SELECT * FROM T WHERE D = ?" and we send in a single
mxDateTime object as a parameter.

What these interfaces will actually do is to send the SQL string
just as it is, ? and all, down to the ODBC level at least. It will
convert the mxDateTime object to some suitable format for the
platform, but it will never worry about how to get it into the SQL
string, because it never will. After all, both Dynamic SQL and SQL
CLI mandates that parameter passing with ? should work, so as far
as I know, there will never be an SQL string with the date value
stuffed in. The database server will read directly from the parameter
list when is finds a ? in the SQL, instead of doing the labour of
extracting a string literal that it perhaps needs to unquote and
unescape. Seems like less work.

For every server, there *is* at least one potential SQL string
which would work in an equivalent way to the SQL with the parameters
we sent, but there is no reason for any part of the chain between
the application and the db server to know what that might look like.

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.

If the DB-API would try to pass in the parameter into the SQL
string, it could just guess how to do that, and it is competely
beyond its "duties".

So, I don't see that db.quote on adodbapi could actually return
anything more intelligent than the input you gave it. A string
and a tuple with parameters. Actually, what would be different
here is the types of the parameters. A passed in mxDateTime
object might be converted to a COM Date object, or to a string
with a date in ISO 8601 format etc. That could be useful I guess.

Not even this is trivial though. ADO doesn't seem to be so easy
to play with, so adodbapi tries different strategies in case of
error. It will for instance try both COM Date objects and ISO 8601
strings for dates.

If an interface is implemented in C or C++, it's not certain that
the actual data type being passed to the next step can be described
in Python at all? M-A? What about mxODBC for instance?

In other words, I would like such a quote function to return
what it would actually send to the next step in the chain, be
that ADO or the MySQL client libs. Either just a string, or a
tuple containing a string and a converted parameter tuple---but
I'm not sure it's a reasonable thing to ask for in all interfaces.


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