3

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..."

fewrandom
  • 312
  • 1
  • 9
  • 1
    You don't mention FireDAC macros, but have you looked at this [recent answer](https://stackoverflow.com/a/63371681)? – MartynA Aug 17 '20 at 07:38
  • @MartynA nope, "ResourceOptions.MacroCreate:=false" - nothing changed – fewrandom Aug 17 '20 at 09:40
  • Did you try to cast `AsWideString:= `? `AsString` will save the emoji as `??` while `AsWideString` won't. – Ilyes Aug 17 '20 at 11:27
  • @Ilyes ofc I did, that's the case 'B' I described in my question. I get mysql error when I use WideString – fewrandom Aug 17 '20 at 14:39
  • I think you need to add a [minimal, reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) to your q. – MartynA Aug 17 '20 at 16:02
  • Assuming you use `TFDConnection` for your `FDQuery`, have you tried to set the `CharacterSet` parameter of the `TFDConnection` to 'UTF8'? Something like `FDQuery.Connection.Params.Values['CharacterSet'] := 'UTF8';` Or, if the `TFDConnection` is a form component, this can be done on Object Inspector for the property of `Params` → `CharacterSet`: `csUTF8`. Based on http://docwiki.embarcadero.com/RADStudio/Sydney/en/Unicode_Support_(FireDAC) – peter.aryanto Aug 17 '20 at 16:15

0 Answers0