0

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?

0

I'm going to guess that the problem might be the difference between your table collation and the data_charset. Maybe try using a compatible collation, so how about this? Use CHARACTER SET cp1250, COLLATE cp1250_general_ci:

CREATE TABLE pohoda.xfa (
  ID INT(10) NOT NULL,
  ... list of fields
)
ENGINE = CONNECT,
TABLE_TYPE = ODBC,
TABNAME = 'Fa',
BLOCK_SIZE = 10,
CHARACTER SET cp1250,
COLLATE cp1250_general_ci,
CONNECTION = 'DSN=pohoda_64b;DBQ=c:/path/to/mdb;';
| improve this answer | |
  • Heureka! It is simple and it works: yes, I was missing to add COLLATE cp1250_general_ci. Thanks a lot! – radimmachu Mar 31 at 13:53
  • @radimmachu - glad to hear that works! Now there is only the small matter of marking this as the correct answer :) – dbdemon Mar 31 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.