WL#1511: Escape Sequences

Affects: Connector/ODBC-3.53   —   Status: Un-Assigned   —   Priority: High

An input string can contain escape sequences for  
date, time, and timestamp.  
The task is to find escape sequences inside an SQL statement  
string during SQLPrepare or SQLExecDirect, and replace them  
with date/time/timestamp strings that the MYSQL server can  
understand. Escape sequences can also appear in passed  
parameters so the same check should be in SQLBindCol.  
The format of an escape sequences is:  
{d '...'}   
{t '...'}  
{ts '...'}  
Where ... is an arbitrary character sequence with a literal  
value for the date, time or timestamp. Examples:  
{d '1994-02-04'}              /* date */  
{t '12:23:00'}                /* time */  
{ts '1994-02-04 12:23:00'}    /* timestamp */  
The driver should intercept the escape sequence, if and only if  
(a) it is not inside ''s or ""s or ``s or comments (/*... */ or after --)  
(b) it begins with '{d ' or '{t ' or '{ts ' and ends with '}  
    (the letters must be lower case)  
    (the space is compulsory, you may choose to allow other spaces)  
There is no validity test to ensure '...' is a valid datetime literal.  
If the escape sequence begins with {d convert to a date: DATE '...'  
If the escape sequence begins with {t convert to a time: TIME '...'  
If the escape sequence begins with {ts convert to a timestamp: TIMESTAMP '...'  
Original string = "SELECT * FROM t WHERE column1 = {d '1992-04-04'}"  
Result string =   "SELECT * FROM t WHERE column1 = DATE '1992-02-04'"  
We do not need escape sequences for function,  
procedure call, like, interval, or guid.  
Escape sequences are normal with ODBC and JDBC, see  
but is not an SQL/CLI requirement.  
Currently the server will strip, for example changes {d '1994-01-01'} 
to '1994-01-01'. But that results in a CHAR, and the type should be 
DATE. Support for these sequences within the server (instead of within 
the driver) would be reasonable if it was optional (with a compile 
switch), and if the resultant type was correct.