[Tcl-tdbc] TDBC tokenizer escaping and MS SQL Server

[Trevor D. (Twylite) <tw...@cr...>]

Hi,

This is a follow up on bug 
http://core.tcl.tk/tdbc/tktview?name=751477d142 "Problem with MS SQL 
SERVER (via tdbcodbc) stored procedures" (closed in the TDBC repo and 
moved to http://core.tcl.tk/tdbcodbc/tkthistory/751477d142).

This is a TDBC bug, not an ODBC one; and we're very keen to see it 
resolved before 8.6.0 is released (if possible).

The problem is that the TDBC tokenizer (Tdbc_TokenizeSql() in 
tdbcTokenize.c) give special meaning to '@', and doesn't have an escape 
syntax to allow '@' to be passed uninterpreted to the underlying driver.

In MS SQL Server the character '@' is required in the DDL, in particular 
for defining stored procedures.  The current behaviour of TDBC (not 
tdbcodbc) prevents stored procedures from being created from Tcl code 
(via TDBC).

There are several possible solutions:

(1) Remove the special handling of '@'.  See diff 
http://core.tcl.tk/tdbc/fdiff?v1=f602a0664f456f9b&v2=fbe1b103c15ccd17 
for the necessary change (minus documentation fixes).  We are currently 
working with a special build that includes this change.

KBK has previously indicated that this option may have a problematic 
interaction with Sqlite, but I don't know the details.

(2) Add specific escaping syntax to Tdbc_TokenizeSql().  For example, a 
specific escape may be that '@@' is a literal '@'.

(3) Add generic escaping syntax to Tdbc_TokenizeSql().  A general escape 
may be that '\x' is a literal char x for x in '@', '$', ':', ...

(4) Provide a way to bypass host parameter substitution in the 
tokenizer.  This is a wider-reaching change that would allow a flag to 
be conveyed down to Tdbc_TokenizeSql() to bypass substitution.

(5) Provide a way to bypass the tokenizer.  As for (4), except the 
tokenizer is bypassed entirely.  Since the tokenizer also handles 
comments this may have unintended consequences.


We would be quite happy with (1), although it is a fairly myopic change 
that may not address future problems with drivers needing literal 
characters where the tokenizer gives them special meaning.

If an approach can be agreed upon we have time now (before things get 
busy again in January) to do the implementation.

Regards,
Twylite