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

[M.-A. Lemburg]

Chris Cogdon wrote:
> Was tempted to that this off-list, but... I think it's useful for anyone 
> that's not that familiar with ODBC databases.

I believe it's similar to what other DB interfaces do one
way or another...

> On Thursday, Jun 5, 2003, at 03:16 US/Pacific, M.-A. Lemburg wrote:
> 
>> Chris Cogdon wrote:
>>
>>> I believe the python driver ALREADY has access to this information, 
>>> since it needs it to determine how to correctly pass this information 
>>> to the DBMS, be it in a string or binary format.
>>
>>
>> Not necessarily. In ODBC the database can tell the driver to send
>> the data using a certain data type, but that information is only
>> known for SQL statements that you actually plan to actually execute
>> (and not even in all cases).
> 
> So, in other words, it might work like this:
> 
> 1. application sends a string and some parameters through the 'execute' 
> method.
> 2. driver sends the string (only) to the backend through a function that 
> asks 'how should I send you the parameters?'

Not quite: the driver/database prepares the statement, ie. it parses
the SQL, finds out which data types the bound parameters needs and
then passes this information on to the application.

Whether this happens in the driver and/or in part in the database
is up to the driver/database combo. Many drivers cache data type
information locally to avoid the network traffic and do at
least part of the parsing themselves.

OTOH, the database server can already start setting up the
right access paths to the data while the driver on the client
side still waits for the application to pass in the data.

> 3. backend responds.
> 4. driver sends the string (perhaps), plus parameters (encoded according 
> to the previous response) to the backend for execution.

Not "encoded"; converted to whatever the backend requires.

> Is that right?
> 
> If so, that seems to be a particularly bad performance hit, almost not 
> worth the savings in sending binary data. (Unless i'm missing someting, 
> of course :)

Huh ? Where's the performance hit ? The more meta-data you have
on the client side the better and faster the whole process can
be made. That's also why drivers usually keep a cache of
schema information on the client side.

Side note: database wire protocols often have limits to the
size of the SQL statement you can send, e.g. SAP DB won't
accept anything much longer than 16kB worth of SQL.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Jun 05 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
EuroPython 2003, Charleroi, Belgium:                        19 days left