cannot insert unicode value in table (Mydac 9.1.3 for RAD Studio 10.2)

cannot insert unicode value in table (Mydac 9.1.3 for RAD Studio 10.2)

Hi,
I have create a very simple database to test inserting unicode characters:

  1. CREATE TABLE `tencodingtest` (
  2.   `pk` int(11) NOT NULL AUTO_INCREMENT,
  3.   `value_as_utf8` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  4.   `value_as_utf16` varchar(30) CHARACTER SET utf16 COLLATE utf16_unicode_ci DEFAULT NULL,
  5.   `normalvarchar` varchar(30) DEFAULT NULL,
  6.   PRIMARY KEY (`pk`)
  7. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 

In my test, I want to try to insert this character into the field 'value_as_utf8': Unicode Character “𠀋” (U+2000B).  
This character in UTF-8 is:  0xF0 0xA0 0x80 0x8B
This character in UTF-16 is: 0xD840 0xDC0B 


I have made several attempts to insert this value with this function, but all attempts fail:

  1. procedure TfmDatabase.Button5Click(Sender: TObject);
  2. var
  3.   s: String;
  4.   s8: UTF8String;
  5. begin
  6.   MyConnection1.Connected := False;
  7.   MyConnection1.Options.UseUnicode := True; //IMPORTANT!!
  8.   MyConnection1.Connect;

  10.   MyQuery2.SQL.Text := 'select * from tencodingtest';
  11.   MyQuery2.Execute;

  13.   s := '𠀋';
  14.   s8 := '𠀋';
  15.   //---[ATTEMPT 1]
  16.   try
  17.     MyQuery2.Append;
  18.     MyQuery2.FieldByName('value_as_utf16').AsString := s;
  19.     MyQuery2.Post;
  20.   except on e: Exception do
  21.     begin
  22.       MyQuery2.Cancel;
  23.       mmLog.Lines.Add('[ATTEMPT 1]: Exception: ' + e.Message);
  24.     end;
  25.   end;

  27.   //---[ATTEMPT 2]
  28.   try
  29.     MyQuery2.Append;
  30.     MyQuery2.FieldByName('value_as_utf8').AsString := s;
  31.     MyQuery2.Post;
  32.   except on e: Exception do
  33.     begin
  34.       MyQuery2.Cancel;
  35.       mmLog.Lines.Add('[ATTEMPT 2]: Exception: ' + e.Message);
  36.     end;
  37.   end;

  39.   //---[ATTEMPT 3]
  40.   try
  41.     MyQuery2.Append;
  42.     MyQuery2.FieldByName('value_as_utf8').AsString := s8;
  43.     MyQuery2.Post;
  44.   except on e: Exception do
  45.     begin
  46.       MyQuery2.Cancel;
  47.       mmLog.Lines.Add('[ATTEMPT 3]: Exception: ' + e.Message);
  48.     end;
  49.   end;

  51.   //---[ATTEMPT 4]
  52.   try
  53.     MyQuery2.Append;
  54.     MyQuery2.FieldByName('value_as_utf8').AsWideString := s8;
  55.     MyQuery2.Post;
  56.   except on e: Exception do
  57.     begin
  58.       MyQuery2.Cancel;
  59.       mmLog.Lines.Add('[ATTEMPT 4]: Exception: ' + e.Message);
  60.     end;
  61.   end;

  63.   //---[ATTEMPT 5]
  64.   try
  65.     MyQuery2.Append;
  66.     MyQuery2.FieldByName('value_as_utf8').AsWideString := s;
  67.     MyQuery2.Post;
  68.   except on e: Exception do
  69.     begin
  70.       MyQuery2.Cancel;
  71.       mmLog.Lines.Add('[ATTEMPT 5]: Exception: ' + e.Message);
  72.     end;
  73.   end;

  75. end;

All 5 attempts fail with these results:
[ATTEMPT 1]: Exception: #HY000Incorrect string value: '\xF0\xA0\x80\x8B' for column 'value_as_utf16' at row 1
[ATTEMPT 2]: Exception: #HY000Incorrect string value: '\xF0\xA0\x80\x8B' for column 'value_as_utf8' at row 1
[ATTEMPT 3]: Exception: #HY000Incorrect string value: '\xF0\xA0\x80\x8B' for column 'value_as_utf8' at row 1
[ATTEMPT 4]: Exception: #HY000Incorrect string value: '\xF0\xA0\x80\x8B' for column 'value_as_utf8' at row 1
[ATTEMPT 5]: Exception: #HY000Incorrect string value: '\xF0\xA0\x80\x8B' for column 'value_as_utf8' at row 1

I tried inserting this character just with a 3rd party editor (HeidiSql), and it can be inserted without problems in both fields (value_as_utf8 and value_as_utf16) (see attachment please).


Am I doing something wrong why this is not working from Delphi?

Mydac Version = 9.1.3 for RAD Studio 10.2.

Regards, Vincent