[SOLVED] a Lazarus/MySQL issue-vague characters(unsupported characters)

[aliboy]

hi everyone

system:
(win 7-64bit/Lazarus 1.0.2-32bit)
 
MySQL:
database name: amahi,table name:tlb / Collation=utf8_persian_ci /Type:InnoDB or MyISAM



ZEOSLib:(TZConnection)
 ZConnection1.ClientCodepage:='utf8';

I install Zcomponent in Lazarus and connected to MySQL correctly  .
I send  data (in english,defualt) to MySQL  and can store  them correctly  but when try to store data (in Persian/Arabic) they store such thing as  ??? characters.
 for example:
ZQuery2.Params.Items[1].AsString:=StrToInt(Edit2.Text);
I could get data in Persian from Edit2.Text then sent for MySQL and store it,but when try to retrieve from database in BDGrid they were ???.

notice:
I could store data (both Persian/Arabic and English) to MySQL manually also could retrieve it correctly with no errors.but as mentioned before i didn't retrieve  Persian/Arabic data from MySQL with Lazarus and finally I conclude the issue was between Lazarus and MySQL . 

any help? 
thank you in advance   

[denver]

Try :

ZQuery1.Clear ;
ZQuery1.Add( 'SET NAMES utf8_persian_ci' ) ;  // Add this first
ZQuery1.Clear ;
ZQuery1.Add( 'insert into ...........' ) ;  //  Your insert statement ;
....
...
...
ZQuery1.ExecSQL  ;

[aliboy]

thank you +denver.

this was my database configuration before:(photo)

http://aliboy.persiangig.com/programming/database-amahi.jpg


and with your tip write code such:

Code: [Select]


      ZQuery2.sql.Clear ;
      ZQuery2.sql.Add('SET name utf8_persian_ci');
      ZQuery2.sql.Add('SET family utf8_persian_ci');
     
      ZQuery2.Params.Items[0].AsInteger:=StrToInt(Edit1.Text);
      ZQuery2.Params.Items[1].AsString:=(Edit2.Text);
      ZQuery2.Params.Items[2].AsString:=(Edit3.Text);
      ZQuery2.ExecSQL;


after added 3 lines above said:

list index(0) out of bounds

thanks

[bobo]

This is what I am using:

Code: [Select]

    ZConnection1.Properties.Text :=
      'character_set_client=utf8'#13#10+
      'character_set_connection=utf8'#13#10+
      'character_set_database=utf8'#13#10+
      'character_set_results=utf8'#13#10+
      'character_set_server=utf8'#13#10+
      'character_set_system=utf8'#13#10+
      'collation_connection=utf8_general_ci'#13#10+
      'collation_database=utf8_general_ci'#13#10+
      'collation_server=utf8_general_ci'#13#10+
      'Codepage=utf8';

You can change it as you need, because you use persian.
Also, make sure you use the latest ZEOS, the 7.x.x is stable now.

[EgonHugeist]

@denver & bobo

'SET NAMES UTF8' would be right. But in the current implementation we don't need this any more.

Aliboy uses TZConnection.ClientCodePage = 'utf8'; which is equal to TZConnection.Properties.Values['codepage'] := 'utf8';
Both properties automatically executing 'SET NAMES 'utf8' after attaching the server.

So i guess we're talking about another issue.

@aliboy
Is TZConnection.ControlsCodePage = cCP_UTF8; set correctly? On the other hand if cGET_ACP(switch to the OS-CodePage like WIN1252)/cCP_UTF16(switch to TWideString/Memo fields) is set then the encoding is correct if your OS codepage(i guess USACII) doesn't support multibyte sequences for accendentual chars like 'âäüöä' or other characters. To avoid 'unsupported characters' exceptions they turn to '?' as usual. All USACII characters are supported for all available codepages a AnsiString can have.

The Lazarus controls do allways expect UTF8Encoded strings so check my suggestion first:
TZConnection.ControlsCodePage := cCP_UTF8;
On the other hand again: http://sourceforge.net/p/zeoslib/tickets/ or http://zeos.firmos.at/

Zeos isn't made for LCL only, Aliboy. Zeos7 can be used with D7-XE3, FPC, FPC+LCL.

Cheers, Michael
ZeosDevTeam

[denver]

Sorry, I missed somethings


ZQuery1.Clear ;
ZQuery1.Add( 'SET NAMES UTF8' ) ;  //  or ZQuery1.Add( 'SET NAMES utf8_persian_ci' ) ;
ZQuery1.ExecSQL ;  // This lines is missed ......This must execute before any SQL statement .
ZQuery1.Clear ;
ZQuery1.Add( 'insert into ...........' ) ;
....
...
...
ZQuery1.ExecSQL  ;

[aliboy]

thanks to all for participation and responses.

thank you (EgonHugeist) and all team members,developers & contributors!.Zeos ,it's great and easy for using.i will  try to test it with Delphi.   

I'm going to set   ControlsCodePage to  cCP_UTF8 but didn't saw  ControlsCodePage.?! (I'm using Zeos 7.0.1-beta)


@bobo"

'character_set_client=utf8'#13#10+
      'character_set_connection=utf8'#13#10+

what do it(#13#10) means?

[EgonHugeist]

@aliboy

beta? -> time for an upgrade, i guess! 

you can also use the new svn-locations: http://zeos.firmos.at/viewtopic.php?t=3654
Advantage for using SVN repositories:
- getting allways the latest code
- your able to tell other people your current version you use.
- help testing and check the latest patches

#10#13 is line-ending for Microsoft OS. Assing TStrings.Text with a string adds a single line, adding #10#13 between two strings, adds two lines.

Michael

[aliboy]

thank you for responses.

@denver & bobo

'SET NAMES UTF8' would be right. But in the current implementation we don't need this any more.

Aliboy uses TZConnection.ClientCodePage = 'utf8'; which is equal to TZConnection.Properties.Values['codepage'] := 'utf8';
Both properties automatically executing 'SET NAMES 'utf8' after attaching the server.

So i guess we're talking about another issue.

@aliboy
Is TZConnection.ControlsCodePage = cCP_UTF8; set correctly? On the other hand if cGET_ACP(switch to the OS-CodePage like WIN1252)/cCP_UTF16(switch to TWideString/Memo fields) is set then the encoding is correct if your OS codepage(i guess USACII) doesn't support multibyte sequences for accendentual chars like 'âäüöä' or other characters. To avoid 'unsupported characters' exceptions they turn to '?' as usual. All USACII characters are supported for all available codepages a AnsiString can have.

The Lazarus controls do allways expect UTF8Encoded strings so check my suggestion first:
TZConnection.ControlsCodePage := cCP_UTF8;
On the other hand again: http://sourceforge.net/p/zeoslib/tickets/ or http://zeos.firmos.at/

Zeos isn't made for LCL only, Aliboy. Zeos7 can be used with D7-XE3, FPC, FPC+LCL.

Cheers, Michael
ZeosDevTeam

after upgrade zcomponent to stable version and  set TZConnection.ControlsCodePage = cCP_UTF8, didn't change.
does it necessary to change 'Non unicode programs'(region and Language) to Persian? (default=English)

 

[delphi887]

hi @aliboy 
how are you? 

does it necessary to change 'Non unicode programs'(region and Language) to Persian? (default=English)

yes, Change non unicode program to Farsi for solved your problem
-------------------------------------------------
i install Zeos 7.03 stable and working in code-page UTF8 for Read or Write Persian string into MySQL Database.
But recently I got a new problem.
in my Database and one table Entered Several Records in UTF8 code page.
http://shahriary.staffcms.um.ac.ir/imagesm/1064/stories/lazarus/table1.jpg
when in my program create Zeos Query and write simple code and open it

Code: [Select]

select * from table1;
but in program show characters Shorter and Not properly.
http://shahriary.staffcms.um.ac.ir/imagesm/1064/stories/lazarus/program.jpg

Does anyone know why this problem is coming???
 

[EgonHugeist]

@Aliboy

sade.

after upgrade zcomponent to stable version and  set TZConnection.ControlsCodePage = cCP_UTF8, didn't change.
does it necessary to change 'Non unicode programs'(region and Language) to Persian? (default=English)

I don't know. May i ask you if the issue you descibe happens @designtime and @runtime? From where exactly do you update/insert the values?

@delphi887

Can you please compare TField.Size and TField.DisplaySize? Are the Strings trunced because of a to small DisplaySize or is the Field.Size to small? Are theire differneces if you assign the Fields to your Formular @designtime? And start your app?

[delphi887]

Can you please compare TField.Size and TField.DisplaySize? Are the Strings trunced because of a to small DisplaySize or is the Field.Size to small? Are theire differneces if you assign the Fields to your Formular @designtime? And start your app?

hi Michael
i compare  TField.Size and TField.DisplaySize they are equal .
in zeos 7 alpha This problem did not exist and every thing fine.
What should I do؟

[aliboy]

hi @aliboy 
how are you? 

does it necessary to change 'Non unicode programs'(region and Language) to Persian? (default=English)

yes, Change non unicode program to Farsi for solved your problem
------------------------------------------------

hi delphi887
thank you dear 
I Change 'non unicode program' to Farsi(Persian) but didn't change,didn't solved.

[aliboy]

@Aliboy

I don't know. May i ask you if the issue you descibe happens @designtime and @runtime? From where exactly do you update/insert the values?

thank you

My database:Database name:amahi  table:tbl (with 3 fields )
http://aliboy.persiangig.com/programming/database-amahi.jpg

on FormCreate:  //Connecting to MySQL (Zquery 1)

Code: [Select]

ZQuery1.SQL.Clear;
         ZQuery1.SQL.Add('select * from tlb');
        ZQuery1.Open;
        ZQuery1.Active:=true;
INSERT button:  //Sending data to MySQL   (zquery 2)

Code: [Select]

      ZQuery2.Params.Items[0].AsInteger:=StrToInt(Edit1.Text);
     ZQuery2.Params.Items[1].AsString:=(Edit2.Text);
      ZQuery2.Params.Items[2].AsString:=(Edit3.Text);
     ZQuery2.ExecSQL;



Workbench:(  insert_p procedure )

Code: [Select]


DELIMITER // 

USE `amahi`;

//

DROP procedure IF EXISTS `insert_p`;
//

CREATE PROCEDURE `amahi`.`insert_p` (IN `p_id` int(10),IN `p_name`

varchar(20),IN `p_family` varchar(30))

BEGIN


INSERT INTO  tlb(id,name,family)

VALUES (p_id,p_name,p_family);


END //


and,SQL property of Zquery 2 is: call `amahi`.`insert_p` (:id,:name,:family);

ran project ,after set input (for example 1020,علی,علوی) and hit INSERT button,data send and  register successfully but both DBgrid and MySQL show ??? .

[delphi887]

hi again
i uninstall Zeos 7.0.3 stable and install Zeos 7.0.0-dev in lazarus 1.0.4
http://shahriary.staffcms.um.ac.ir/imagesm/1064/stories/lazarus/zeos_7_0_0_dev.jpg
my program working complete and show utf8 string completely.
http://shahriary.staffcms.um.ac.ir/imagesm/1064/stories/lazarus/program_2.jpg
my problem solved .
but why occurred this problem in Zeos 7.0.3 stable ????