Recent

Author Topic: [SOLVED] a Lazarus/MySQL issue-vague characters(unsupported characters)  (Read 12616 times)

aliboy

  • New Member
  • *
  • Posts: 23
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   
« Last Edit: January 19, 2013, 08:33:20 pm by aliboy »

denver

  • Jr. Member
  • **
  • Posts: 67
Re: a Lazarus/MySQL issue-vague characters
« Reply #1 on: January 14, 2013, 01:54:08 pm »
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

  • New Member
  • *
  • Posts: 23
Re: a Lazarus/MySQL issue-vague characters
« Reply #2 on: January 14, 2013, 06:46:58 pm »
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:
Quote
list index(0) out of bounds


thanks

bobo

  • Full Member
  • ***
  • Posts: 171
Re: a Lazarus/MySQL issue-vague characters
« Reply #3 on: January 14, 2013, 07:36:20 pm »
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

  • Jr. Member
  • **
  • Posts: 62
Re: a Lazarus/MySQL issue-vague characters
« Reply #4 on: January 14, 2013, 09:00:00 pm »
@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

  • Jr. Member
  • **
  • Posts: 67
Re: a Lazarus/MySQL issue-vague characters
« Reply #5 on: January 14, 2013, 09:39:09 pm »
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

  • New Member
  • *
  • Posts: 23
Re: a Lazarus/MySQL issue-vague characters
« Reply #6 on: January 14, 2013, 10:50:06 pm »

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"

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

what do it(#13#10) means?

EgonHugeist

  • Jr. Member
  • **
  • Posts: 62
Re: a Lazarus/MySQL issue-vague characters(unsupported characters)
« Reply #7 on: January 14, 2013, 10:59:06 pm »
@aliboy

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

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

  • New Member
  • *
  • Posts: 23
Re: a Lazarus/MySQL issue-vague characters(unsupported characters)
« Reply #8 on: January 17, 2013, 07:04:47 pm »
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

  • New Member
  • *
  • Posts: 30
    • my personal website
Re: a Lazarus/MySQL issue-vague characters(unsupported characters)
« Reply #9 on: January 17, 2013, 09:18:14 pm »
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???
 
« Last Edit: January 17, 2013, 09:28:15 pm by delphi887 »

EgonHugeist

  • Jr. Member
  • **
  • Posts: 62
Re: a Lazarus/MySQL issue-vague characters(unsupported characters)
« Reply #10 on: January 17, 2013, 10:19:33 pm »
@Aliboy

sade.
Quote
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

  • New Member
  • *
  • Posts: 30
    • my personal website
Re: a Lazarus/MySQL issue-vague characters(unsupported characters)
« Reply #11 on: January 18, 2013, 08:51:16 am »
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

  • New Member
  • *
  • Posts: 23
Re: a Lazarus/MySQL issue-vague characters(unsupported characters)
« Reply #12 on: January 18, 2013, 09:53:04 am »

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

  • New Member
  • *
  • Posts: 23
Re: a Lazarus/MySQL issue-vague characters(unsupported characters)
« Reply #13 on: January 18, 2013, 10:11:44 am »
@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

  • New Member
  • *
  • Posts: 30
    • my personal website
Re: a Lazarus/MySQL issue-vague characters(unsupported characters)
« Reply #14 on: January 18, 2013, 12:03:23 pm »
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 ???? %)
 
« Last Edit: January 18, 2013, 12:05:28 pm by delphi887 »