Be sure to use the MySQL ODBC 8.0 Unicode driver. We recommend using the latest version (we used 8.0.18 during development). There are downloads/installers for both 32bit and 64bit. Even if you are going to focus on 32-bit for your application, you must also install the 64-bit MySQL driver because the DataFlex Studio and other DataFlex tools are 64 bits.
When creating a new schema (== new databases) in MySQL Workbench, use charset utf8mb4 and collation utf8mb4_general_ci
CREATE SCHEMA ` mysqlorder199` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
The default charset is set to utf8mb4 and the default collating to utf8mb4_0900_ai_ci. Do NOT use those default values as that combination will cause finds to not always work correctly. Any collating with 0900 in the name will not work and should not be chosen.
Background: Collating with 0900 in the name use NO PAD collating. String comparisons on strings with trailing spaces and without trailing spaces will compare as not equal. For instance...
Select * from SalesPerson Where ID = ‘MM ‘
...will not find the record if it is stored in the database as ‘MM’ See Padding and Trimming in SQL Databases for more information.
Make sure to choose the MySQL ODBC Unicode driver. Important! Set the Character Set setting in the DSN to utf8mb4
MySQL.int has a new configuration keyword: Character_Data_Handling
For MySQL this should be set to UTF8
;******************************************************************************
; Character_Data_Handling
; Determines how odbc_drv will put and get character data to the backends odbc driver
; Possible values:
; Character_Data_Handling WIDESTRING
; Character_Data_Handling UTF8
;
; Default value: WIDESTRING
;
; For MySQL ODBC 8.0 Unicode Driver this should be specified as
; Character_Data_Handling UTF8
;
; Also set the codeset in the DSN to utf8mb4.
Character_Data_Handling UTF8