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

[Jonathan L. <jon...@gm...>]

On Sun, May 11, 2008 at 11:46 AM, Kevin Kenny <kk...@ny...> wrote:

> Jonathan Leffler wrote:
>
> > Presumably, TIP 308 should be upgraded to recognize that:
> >
> > SELECT ':name' FROM SomeTable;
> >
> > is not a placeholder but a string.
> >
>
> Indeed.  Moreover, ":name" is not a placeholder but a column name,
> and --:name is a comment. You're right, the description of the
> tokenizer should be clarified to reflect what it actually does.
>

Again - true enough in the SQL standard, but Informix uses single quotes and
double quotes interchangeably for strings unless an environment variable,
DELIMIDENT, is set, whereupon it behaves according to the standard.  It may
be the that TDBC driver for Informix has to set that.

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.


What about comments?  Which comment conventions?  (Informix has 3, just for
> fun!  And one of those is context sensitive, to boot.  No, I don't want to
> have to explain - but be aware that real systems have weird notations that
> are not part of the standard.)
>

I think we have to approach these from a "lowest common denominator"
> perspective.  Really, we have two possibilities.
>
> (1) Accept the local database's dialect of SQL, and its notation
>    for substitutions.  That means that all calling code has to
>    be adapted to the local database.  I tend to think this sort
>    of defeats the purpose of TDBC as a portability layer.
>

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.

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.

(2) Do some limited rewriting of queries from a subset of standard
>    SQL to whatever the local database's notation is.  This is the
>    approach taken in both TDBC and JDBC; I'm reasonably certain that
>    if there is an Informix JDBC driver, it will accept
>    somedb.usertable and rewrite to somedb:usertable, accept
>    TO_INTEGER(mycolumn) and rewrite to mycolumn::Integer,
>    and so on.  This places a much heavier onus on driver implementors
>    who must deal with nonstandard systems, but has the advantage
>    that calling code is uniform.  It also constrains the system
>    to a "lowest common denominator" approach, since the more
>    esoteric aspects of a native database may well not be
>    reflected. This in turn implies that there will likely
>    still be a place for full native APIs for applications
>    that need a specific database's capabilities. These apps
>    will _ipso facto_ remain nonportable.
>

Clearly, the less of this there is to do, the better.  Writing a full-scale
parser is onerous at best; writing an accurate ad hoc parser is just about
as bad.  TTBOMK, the SQL standard only supports question marks as
placeholders -- that would be my recommendation/preference.  It's the lowest
common denominator, and it is the standard.  (You can find rules in the
standard about colon and host identifier, but those apply to embedded SQL,
and not to SQL statements that are not in embedded SQL.  To claim that TDBC
or TIP 308 is using embedded SQL would be a large leap, IMO; large and not
dreadfully accurate.

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

-- 
Jonathan Leffler <jon...@gm...> #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0229 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."