Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.16
Description
CONNECT doesn't put quotes around DATETIME values returned from local functions.
For example, consider the now() function:
MariaDB [tmp]> SELECT * From datetime_table where modifiedon > now();
|
ERROR 1296 (HY000): Got error 174 '[FreeTDS][SQL Server]Incorrect syntax near '16'.' from CONNECT
|
|
|
MariaDB [tmp]> SELECT NOW();
|
+---------------------+
|
| NOW() |
|
+---------------------+
|
| 2015-02-05 16:22:39 |
|
+---------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [tmp]> SELECT * From datetime_table where modifiedon > '2015-02-05 16:22:39';
|
+----+---------------------+
|
| id | modifiedon |
|
+----+---------------------+
|
| 2 | 2016-01-01 00:00:00 |
|
+----+---------------------+
|
1 row in set (0.45 sec)
|
The ODBC trace log shows:
[ODBC][14801][1423171100.511886][SQLExecDirect.c][240]
|
Entry:
|
Statement = 0x7f8aa868d600
|
SQL = [SELECT id, modifiedon FROM dbo.datetime_table WHERE modifiedon > 2015-02-05 16:18:20][length = 84 (SQL_NTS)]
|
There should be quotes around the DATETIME value in the query sent to the remote server.
As a matter of facts, this is more than simply puting quotes around date values.
The issue is that the syntax for placing dates in SQL statements varies from data sources to data sources. for instance:
SELECT * FROM t1 WHERE cold = '2015-02-08 13:40:00';fails with MS Access because Access put '#' around dates. But you cannot ask the query as:
SELECT * FROM t1 WHERE cold = #2015-02-08 13:40:00#;Because it is firstly parsed by MariaDB and produces a syntax error.
Fortunately, ODBC provides escape sequences to represent temporal values and what CONNECT does is trying as much as possible to replace temporal values by these escape sequences.
Also remember the SRCDEF and EXECSRC ODBC table types that CONNECT features. They permit sending queries and commands using the data source syntax and can be used when the CONNECT fails to provide the correct replacement. This can happen in unfrequent cases such as complex where clauses or when the temporal values are not in the where clause.
Note that this fixes also most of INSERT and DELETE statements. INSERT does not require this fix because values are sent directly, using a prepared statement.