I run 10.4.12-MariaDB on Windows10 and I use 64-bit ODBC driver to connect tables from MS Access to MariaDB database. I am simply glad that it works (!). But going deeper and working more with connected tables (yes, this way to link mdb and open source db world is too complicated, but no other way for me...), I found some constraints.
Now I am confused with charsets. My configuration of MariaDb is as follows:
version: 10.4.12-MariaDB on Windows10 64-bit [mysqld] ... character-set-server=utf8
My table connected to Access ?2003? mdb file:
CREATE TABLE pohoda.xfa ( ID INT(10) NOT NULL, ... list of fields ) ENGINE = CONNECT, TABLE_TYPE = ODBC, TABNAME = 'Fa', BLOCK_SIZE = 10, CHARACTER SET utf8, COLLATE utf8_general_ci, CONNECTION = 'DSN=pohoda_64b;DBQ=c:/path/to/mdb;';
and further connection details from information_schema.tables.CREATE_OPTIONS:
`TABLE_TYPE`='ODBC' `TABNAME`='DJjizdy' `DATA_CHARSET`='cp1250' `BLOCK_SIZE`=10
To summarize, when I created the connected tables, I tested that I should set
DATA_CHARSET='cp1250' to display non-ascii characters right.
As for browsing the tables, it works great. But when I came to the query, a problem appeared.
Simple SELECT with non-ascii character in WHERE clause does not return appropriate and really existing rows:
MariaDB [pohoda]> SELECT id, Firma FROM xfa WHERE Firma LIKE 'Adam Vič%' Empty set (0.120 sec) MariaDB [pohoda]> SELECT id, Firma FROM xfa WHERE Firma LIKE 'Adam Vi%'; +-------+--------------+ | id | Firma | +-------+--------------+ | 14443 | Adam Vička | | 14488 | Adam Vička |
Thus, while WHERE Firma LIKE 'Adam Vič%' returns an empty set, just removing the character "č" from where clause Firma LIKE 'Adam Vi%' gives the right recordset. I tested it from command and from HeidiSQL with same results.
Could you please help me to set a charset for connected table?