Storing Unicode Characters in PostGreSQL DB table

[new_x 1]

Hi I am using  Delphi 11 Community Version, designed a DB in PostGreSQL and created a table, this table has a QuizAnswers" columns of type text, 

Created a Delphi Application having a memo into which user writes his own answer to the Quiz questions, I want to store user answer (which is entered into the memo) into the column "QuizAnswers" ,

The answers are in Turkish and may have non-standard characters like "ğ", "ç", "ü", "ı", "o".., I am using FireDAC FDCOnnection, FDPhysPgDriverLink, FDQuery and the final result is that these non-standard characters are not stored in their original form, instead "ğ" is stored as "g", "ç" is stored as "c" , "ö" is stored as "o" so on. I used the pgAdmin4 to store the same string consisting these Unicode Characters into the same column, all characters are stored and displayed correctly.

 

Regards,

 

[weirdo12 20]

What is the value of your TFDConnection CharacterSet parameter?

 

https://docwiki.embarcadero.com/RADStudio/Athens/en/Connect_to_PostgreSQL_(FireDAC)

[new_x 1]

It is UTF 8

[new_x 1]

There is no option in TFDConnection character set for "ISO-8859-9 or Windows-1254"

[Cristian Peța 103]

Is the database ENCODING = 'UTF8'?

I think it can be specified only when you create it.

[new_x 1]

Although DB encoding is UTF8, the characters are not stored correctly from Delphi application. But If I insert the data from pgAdmin4 they are correctly stored. So the DB Encoding is UTF8

 

[Cristian Peța 103]

Personally I would debug the FireDAC code to see where the characters change.

Do you use parameters or the values are in SQL text directly?

I suppose if you use TSQLMonitor the values are already wrong.

[new_x 1]

I use parameters, thank you

[Cristian Peța 103]

6 minutes ago, new_x said:

But If I insert the data from pgAdmin4 they are correctly stored. So the DB Encoding is UTF8

This is not the proof that Encoding is UTF8. You need to verify database encoding. Have you?

[new_x 1]

I used the following query from pgAdmin4

SHOW SERVER_ENCODING;

 

The result is;

"UTF8"

 

[Cristian Peța 103]

2 minutes ago, new_x said:

I use parameters

Using TSQLMonitor you can see parameters value. If there are wrong characters then is something from FireDAC otherwise is from PostgreSQL.

[new_x 1]

It is seemed to be something from FireDAC, since the same data can be inserted correctly within pgAdmin4 by using SQL insert statement. Anyway I will use TSQLMonitor. Thanks your suggestion

[Cristian Peța 103]

1 minute ago, new_x said:

I used the following query from pgAdmin4

SHOW SERVER_ENCODING;

 

The result is;

"UTF8" 

 

Not exactly the database ENCODING. Every database can have other encoding but probably it is OK.

[new_x 1]

To check for DB "Monitor", I used the following SQL statement,

SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = 'Monitor';

 

Result is;

UTF8

[weirdo12 20]

When you store the correct data using pgAdmin4, what do you see if you do a SELECT on the table from FireDAC?

[new_x 1]

When I dumped the data using pgAdmin4 it is correctly stored and when I used select statement in Delphi to get the data from table, it successfully gets the data correctly.