Re: [Tcl-tdbc] TIP 308: Placeholders in prepared statements - an alternative perspective

[Kevin K. <kk...@ny...>]

Jonathan Leffler wrote:
> Also, I should point out that my perspective is from the Informix ESQL/C 
> viewpoint; there is also a choice of two separate ODBC drivers (one 
> talking native Informix SQLI protocol, the other talking the IBM DRDA 
> protocol) which do all sorts of weird analysis of SQL (there's a 
> significant chunk of a complete SQL parser in the drivers).  It may be 
> better to write the TDBC driver using one of those, instead of ESQL/C, 
> because the problems can be punted; they become SEPs -- someone else's 
> problem.

Great.  I like Other People's Problems.

> OK - but then we need to be sure that we have defined the comment 
> conventions that must be supported.  Informix supports double-dash to 
> end-of-line (per the standard); it also supports C style slash-star to 
> star-slash comments.  These are both in SQL 2003 and so should, 
> presumably, be supported by TDBC.

Right.  The supplied tokenizer recognizes both -- and /*...*/.
They're removed from the native SQL before statements are prepared,
so a SQL that lacks one or the other syntax would also be covered.
(Assuming

> Also, for legacy reasons (that is, from the days before the SQL-86 
> standard) Informix supports open-brace to close-brace except when the 
> open brace is syntactically significant as something other than a 
> comment - the context sensitive stuff I mentioned.  It's loathsome; it's 
> there.  If I read the SQL/Foundation accurately, then <left brace> is 
> used in one context and that is in a regular expression.  So, the 
> chances are braces won't have much effect on portable code - portable 
> code won't use braces.

OK. So TDBC doesn't need to worry about Informix's braces. Good.

> Treat these remarks as a precaution - a warning.  Beware of making it 
> too onerous for people to adhere to your proposed standard.

What the ODBC bridge does is to rewrite ':variable' that appears
outside a comment, single quoted string, or double quoted column
name, replacing it with a ?. It remembers the position, so the
Tcl code sees the parameter by name, but the ODBC driver sees the
question mark.  That's obviously not adequate to handle Informix's
schema::table or column:type notations, but it handles the simple
case of variable substitution quite nicely. I'm still not
entirely convinced that we have to give up named parameters.

We'd need to look more closely at the remaining Informix syntax
quirks. I admit that I had been unaware of them; I'd looked
fairly closely at Oracle, Sybase, SQL Server, Jet, MySQL,
Postgres95, SQLite and DB2, but was ignorant of how Informix
"interprets" the standard.  My guess is that we can come up
with either appropriate _ad hoc_ tokenization that handles
common cases, or else layer atop ODBC (the ODBC bridge is already
there) and let the driver worry about it for us.

-- 
73 de ke9tv/2, Kevin