I'm trying to save text with emoji (like "hello world") to MYSQL
Everything goes fine when I just use it without FDparams:
FDQuery.SQL.text:='update USER set status="hello world"'
But if I try to use TFDparams, the troubles begin:
(A) FDParams.CreateParam(ftString,'status',ptInput).AsString:='hello world';
(B) FDParams.CreateParam(ftWideString,'status',ptInput).AsWideString:='hello world';
FDQuery.SQL.text:='update USER set status=:status'
FDQuery.Params.Assign(FDParams);
(A) just doesn't save the emoji properly ('hello ?? world' is sent to DB instead) - and I believe the emoji becomes '??' even before sending to DB (the call '.AsString' seems to spoil the unicode)
(B) gives a native MySQL error: Incorrect string value: '\xF0\x9F... for column 'status'
Mysql settings (config files):
[mysql]
default-character-set=utf8mb4
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
init_connect='SET collation_connection = utf8mb4_unicode_ci'
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake
Table USER show create:
CREATE TABLE `USER` (
`status` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
mysql -V
mysql Ver 14.14 Distrib 5.7.31, for Linux (x86_64) using EditLine wrapper
SHOW VARIABLES (sent from my client program)
character_set_client = utf8mb4
character_set_connection = utf8mb4
character_set_database = utf8mb4
character_set_filesystem = binary
character_set_results = utf8mb4
character_set_server = utf8mb4
character_set_system = utf8
character_sets_dir = /usr/share/mysql/charsets/
collation_connection = utf8mb4_unicode_ci
collation_database = utf8mb4_unicode_ci
collation_server = utf8mb4_unicode_ci
In short:
Query without FDparams - everything is fine, emoji is saved properly and no errors
FDparams[...].AsString - Firedac spoils the unicode emoji and sends just 'hello ?? world' to MySql
FDparams[...].AsWideString - I get MYSQL error "Incorrect string value: '\xF0\x9F..."