FIREDAC-UTF8 encoding is not supported when reading FTS table

1
votes

When in DELPHI XE3 with Firedac we create a table table0 and insert text in UTF8 encoding all is OK.

CREATE TABLE table0 (mytext Ntext,publishdate date);
INSERT INTO table0 (mytext,publishdate) VALUES ('привет','1998-12-07');

But, when we create a FTS table and insert text in UTF8.

CREATE VIRTUAL TABLE table1 USING FTS4(mytext,publishdate);
INSERT INTO table1 (mytext,publishdate) VALUES ('привет','1998-12-07');

and read the data with

SELECT mytext FROM table1

we get "??????".

The same commands in SQLITE Expert Personal return "привет". It means that in the table after Insert command we have 'привет' and select returns the data not in UTF8 encoding.

What may be done to get from the table correct value with Firedac 

ADQuery.Open('SELECT mytext FROM table1')?
2
sqlitedelphidelphi-xe3firedac
So here AsWideString, or AsWideMemo returns question marks? Or what do you use to get values? Also, for inserting you should use parameters with the similar access. - Victoria
for future readers try set StringFormat=Unicode in the connection string, this will make firedac treat all string types as Unicode strings, in the time of writing this is not the default - Nasreddine Galfout

2 Answers

0
votes

I think it is FireDac bug.

I've changed the following lines in ADSQLiteTypeName2ADDataType procedure of uADPhysSQLite.pas unit

SetLen(AOptions.FormatOptions.MaxStringSize, False);
AType := dtAnsiString;

to 

SetLen(AOptions.FormatOptions.MaxStringSize, True);
AType := dtWideString;

for the case ABaseTypeName = 'VARCHAR'

and 

SELECT mytext FROM table0

returns the correct value at runtime. But at design time still we have '??????'.

But I don't think it's a good solution.

0
votes

I believe you should directly set parameter type as AsWideString:

Query.SQL.Text:='INSERT INTO table1 (mytext, publishdate) VALUES (:mytext,: publishdate);';
Query.Params[0].AsWideString := mytext;
Query.Params[1].AsDate := publishdate;
Query.ExecSQL;

Reference: http://docwiki.embarcadero.com/RADStudio/Rio/en/Unicode_Support_(FireDAC)#Parameter_Values