The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

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)

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.