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

Chris Cogdon chris at cogdon.org
Tue Jun 3 18:24:38 EDT 2003


On Tuesday, Jun 3, 2003, at 15:06 US/Pacific, Magnus Lyckå wrote:

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

Actually, I don't think it would work that well for logging, but... :)

I most certainly have an application where this would make things 
easier for me (in fact, I'm already using it, but that's only because 
the python drivers I'm using have a callable 'quote' function).

Can we take it as read that there are applications where this would be 
useful?

No? Oh, okay... here's one. I have an XML interface into my database so 
that XML queries on the information can be made. For any of three data 
types, a number of optional criteria can be specified to restrict the 
information that's queried and sent back to the client. My function 
turns XML elements into individual parts of a 'where' clause. For 
example.

<displayname relation="<=">CZZ</displayname>

gets turned into this 'where fragment'

displayname<='CZZ'

There could be zero or more of these fragments. Each of them is joined 
together with 'AND's and then added in as the WHERE clause of the SQL. 
Now, this only works because I can call the quote method to build up a 
valid fragment. Otherwise, I would have to write my own quoter (even 
more error prone than having it in the python driver), or somehow put 
?'s in there, and then pass all the parameters I receive down the chain 
to be joined up with the full select statement. I don't think it would 
take much to realise that this is MUCH messier than being able to 
'quote' as the where parts are being built up.

Now... all this only works if the DBMSs that use the '? convention ALSO 
support passing literal values (such as 'CZZ') inside the SQL. And, if 
that's the case, then these DBMSs also have quoting conventions, and if 
that's the case, then I think there should be a function INSIDE THE 
DRIVER that does the quoting for us just like most of the PostgreSQL 
drivers do.

Sure, in the case of the '?' DBMSes the quoting function wont be used 
for most applications, but I've just shown you one that does, and it's 
the most efficient way of doing it.

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

My intention is that the 'quote' function returns a single string, with 
all the parameters quoted and embedded into the SQL. It could also work 
like this:

def trivial_where_generator ():
	return db.quote ( "displayname = ?", displayname_value )

cur.execute ( "select * from artists where " + 
trivial_where_generator() )

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

Since that matches with my paraphrase a few emails ago, I agree with 
that.

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

Unless, of course, in the particular application its much more 
efficient to deal with full SQL strings.

Again, I'm not suggesting that this is a 'good thing' for all 
applications, only some :)

> 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. For example the following 'proposed' statement:

db.quote ( "select * from T where D=?", date )

would generate a different string depending on what DMBS it is talking 
to.

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

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.

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.

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

I'm asking that it be a straight string, since that's easiest to 
manipulate in the application. If for some reason the DMBS does NOT 
support some literals being passed as a straight string, then an error 
be returned. After all, if it can't be 'quoted', then there's no way of 
achieving what you want anyway.


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




More information about the DB-SIG mailing list