Openquery from SQL Server to MariaDB 10.1.48 using MariaDB ODBC Connector 3.1
I'm trying to query a vendor managed MariaDB (10.1.48 - I can't upgrade) from a linked SQL Server 2019 using the MariaDB ODBC Connector 3.1. Due to serious performance issues querying a large table over a linked server, I am using openquery to get a much faster response time. There is an issue with data functions in the where clause. The openquery will not calculate the date range.
Examples:
a) select * from openquery([MariaDB], 'Select DATE_ADD(CURRENT_DATE(), interval -3 DAY)') returns the correct date
b) select * from openquery([MariaDB], 'Select date_modified from tbl WHERE date(date_modified) = CURRENT_DATE () ') returns the correct records
c) select * from openquery([MariaDB], 'Select date_modified from tbl WHERE date(date_modified) = CURRENT_DATE () - INTERVAL 3 DAY') returns nothing. Using the same query directly in MariaDB (HeidiSQL), there are 91,000 records.
d) select * from openquery([MariaDB], 'Select date_modified from tbl date(date_modified) BETWEEN DATE_SUB(CURRENT_DATE(), interval 3 day) and CURRENT_DATE()') returns records from Current_Date and Current_Date -1, which is incorrect.
e) select * from openquery([MariaDB], 'Select date_modified from tbl date(date_modified) = DATE_SUB(CURRENT_DATE(), interval 3 day)') returns nothing.
f) select * from openquery([MariaDB], 'Select date_modified from tbl date(date_modified) = DATE_ADD(CURRENT_DATE(), interval -3 DAY)') returns nothing.
g) variations using >, >= to <, > to <= instead of BETWEEN result in either the Current_Date to Current_Date + 1 or nothing.
In the 64 bit connector, other than Auto Reconnect, no other options are enabled. In the Linked Server Provider MSDASQL (ODBC) Dynamic Parameter, Level zero only, Allow inprocess, Supports 'Like' Operator are all checked.
Answer Answered by John Smith in this comment.
Figured it out. Someone else raised this on Stack Overflow for mySQL back in 2015. https://stackoverflow.com/questions/28440535/unable-to-filter-by-calculated-datetime-when-pulling-data-from-mysql-into-ms-sql
You have to convert the SQL server date to VARCHAR.
Code example using the StackOverflow solution, adapted to my situation:
DECLARE @TSQL varchar(8000) DECLARE @VAR varchar(29) SET @VAR = CONVERT(VARCHAR, cast(DATEADD(day, -5, getdate()) as date) ,120) --Select @var SET @TSQL = 'select modified from OPENQUERY(MariaDB, ''Select date_modified FROM tbl WHERE date(date_modified) >= ''''' + @VAR + ''''''') ' EXEC (@TSQL)
