[MDEV-7549] CONNECT doesn't put quotes around DATETIME values returned from local functions Created: 2015-02-05  Updated: 2015-02-09  Resolved: 2015-02-09

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.0.16
Fix Version/s: 10.0.17

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: connect-engine


 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.

 Comments   
Comment by Olivier Bertrand [ 2015-02-09 ]

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.

Generated at Thu Feb 08 07:20:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.