Accented characters using Firebird Zeos in Lazarus Pascal

[Bobito]

Hi everybody,
My english is not good, but i´ll try to explain my issue:

I have a form on which i have a TZConnection1 to a Firebird db, a ZSQLProcessor1, a ZQuery1, a DataSource1 and finally a DBGrid1 and they all are connected. Everything works fine, i get to see on the dbgrid the info from a table (Dimensiones), the problem is when a field (observaciones) of a record in that table has a character like: á, é, ó, í, ú or ñ or other characters we have in spanish, because the text gets truncated to the position where that character is.
I don´t know exactly how to attach an image but i tried, hopefully you will be able to see the image.

Captura.PNG

I also tried with a TIBConnection, in that case on the DBGrid i get to see that field complete but for every of those characters i get a '?' (question mark).

What should i do to solve this?

In advance thank you all for any help.

Bob

[marsupilami]

Hello Bob,

it seems that character set translations don't happen correctly. Please set the ClientCodepage property of your TZConnection object to 'UTF8'. This should correct your problems if the data is stored correctly in the database.

Best regards,

Jan

[Bobito]

Hi Jan, thanks for your response.
I realise I didn´t provide complete information on my settings; my os is windows 7 (64 bits),
Lazarus 1.8.4 and Zeos 7.2.4.

Lazarus.PNG

I tried your suggestion, I changed that property in the object inspector, but it didn´t work.

Additionally i tried this code :
procedure TDimensiones.FormShow(Sender: TObject);
begin
IBConnection1.UserName :='SYSDBA';
IBConnection1.Password:='masterkey';
IBConnection1.DatabaseName:='C:\costos_agave\data\costos_agave.fdb';
IBConnection1.HostName := getlocalip();
IBConnection1.Open;
Consulta:= 'SELECT d.CVE_DIMEN as CLAVE, d.DESCR as DESCRIPCION, d.OBSERVACIONES as OBSERVACIONES'
+ ' from dimensiones d'
+ ' order by cve_dimen';
SQLQuery1.SQL.Text:= Consulta;
SQLQuery1.Active:=True;
SQLQuery1.Open;
DBGrid1.Refresh;

ZConnection1.User :='SYSDBA';
ZConnection1.Password :='masterkey';
ZConnection1.Protocol:='firebirdd-2.5';
ZConnection1.Database :='C:\costos_agave\data\costos_agave.fdb';
ZConnection1.HostName := getlocalip();
ZConnection1.ClientCodepage :='UTF8';
ZConnection1.Connect;
//ShowMessage('Propiedades: '+ZConnection1.Properties.Text);

Consulta:= 'SELECT d.CVE_DIMEN as CLAVE, d.DESCR as DESCRIPCION, d.OBSERVACIONES as OBSERVACIONES'
+ ' from dimensiones d'
+ ' order by cve_dimen';
ZQuery1.SQL.Text := Consulta;
ZQuery1.Active :=True;
ZQuery1.Open;
DBGrid2.Refresh;
End;

Unfortunately the result was the same (niether accented characters nor ñ).
There is something i´d like to understand better; in your response you said:
'This should correct your problems if the data is stored correctly in the database.'
When i open that table with IBExpert or DatabaseTour Pro i see the field OBSERVACIONES correctly, even
i don´t set any properties to my database.
But maybe i need to revise more on that direction. Could you please elaborate a little more on this?

[marsupilami]

Hello Bob,

Character set conversion can only work correctly if the character set is known at all places. Usually there is a default character set at the database level. It is specified when the database is created. You can check it by checking the data in the table RDB$DATABASE. All columns that don't have an explicit character set attached store data in this charater set. On another level you can specify a character set per column. Firebird will then store characters using this character set.
When the Firebird client connects to the server, it tells the server, which character set it wants to use for communication. The server will then convert the stored characters to the character set the client wishes to use. But this will only work if the character sets are correctly set at the database level. This is what you specify in the ClientCodepage parameter of TZConenction. With Lazarus, UTF-8 is the suggested character set as it is used by Lazarus anyway. Also it is a safe bet because it can store any characters that are in use with computers nowadays - and then some.
In theory there is anotehr level, where conversions can happen. If the ClienCodepage parameter and the ControlsCodePage parameter don't match, Zeos will convert between them too. With Lazarus the ControlsCodePage parameter should be set to cCP_UTF8.

My guess is that your database doesn't know the correct character set. Maybe you didn't specify a character set and now all columns of your database operate with charater set NONE? What do you get if you run the query 'select RDB$CHARACTER_SET_NAME from RDB$DATABASE'?

Also some notes on your code:

Code: Select all

ZQuery1.Active :=True;
ZQuery1.Open;
DBGrid2.Refresh;

ZQuery1.Active and ZQuery1.Open do the same. You only need to do one of both operations. Also DBGrid2.Refresh should be redundant. The grid usually should display the result of your query without this call.

Best regards,

Jan

[Bobito]

Jan, thanks a lot. Your are absolutely right, I didn't specify the character set at the time i created the db. Honestly i didn´t know a thing about that.
When I ran the query 'select RDB$CHARACTER_SET_NAME from RDB$DATABASE' i got: NONE
As soon as I have time I will change/remove the lines:
ZQuery1.Active :=True;
ZQuery1.Open;
DBGrid2.Refresh;


I recreated the db and everything is ok now.

Captura_dos.PNG

Once I recreated the db I created the table again, and even I didn´t specify any character set this is the entry automatically generated:

/* Create table */
CREATE TABLE DIMENSIONES (
CVE_DIMENSION VARCHAR(6) CHARACTER SET UTF8 NOT NULL COLLATE UTF8,
DESCRIPCION VARCHAR(40) CHARACTER SET UTF8 NOT NULL COLLATE UTF8,
OBSERVACIONES VARCHAR(80) CHARACTER SET UTF8 COLLATE UTF8
);^
COMMIT;^
/* Create primary key */
ALTER TABLE DIMENSIONES ADD PRIMARY KEY (CVE_DIMENSION);^
/* Create foreign keys */
/* Create check constraints */
/* Create unique constraints */
/* Create indices */
/* Grant object privileges */
GRANT DELETE ON DIMENSIONES TO SYSDBA WITH GRANT OPTION;^
GRANT INSERT ON DIMENSIONES TO SYSDBA WITH GRANT OPTION;^
GRANT REFERENCES ON DIMENSIONES TO SYSDBA WITH GRANT OPTION;^
GRANT SELECT ON DIMENSIONES TO SYSDBA WITH GRANT OPTION;^
GRANT UPDATE ON DIMENSIONES TO SYSDBA WITH GRANT OPTION;^

So, I guess I won´t have to take care of that anymore.
Great help from you.
Best regards,
Bob.