[SOLVED] a Lazarus/MySQL issue-vague characters(unsupported characters) lazarus / 16-Databases ---------------------------------------------------------------------- aliboy · 2013-01-14 08:29 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 · 2013-01-14 12:54 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 · 2013-01-14 17:46 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: Quotelist index(0) out of bounds thanks ---------------------------------------------------------------------- bobo · 2013-01-14 18:36 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 · 2013-01-14 20:00 @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 · 2013-01-14 20:39 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 · 2013-01-14 21:50 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 · 2013-01-14 21:59 @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 · 2013-01-17 18:04 thank you for responses. Quote from: EgonHugeist 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 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 · 2013-01-17 20:18 hi @aliboy  how are you?  Quote from: aliboy on January 17, 2013, 07:04:47 pmdoes 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/progra m.jpg Does anyone know why this problem is coming??? ---------------------------------------------------------------------- EgonHugeist · 2013-01-17 21:19 @Aliboy sade. Quoteafter 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 · 2013-01-18 07:51 Quote from: EgonHugeist on January 17, 2013, 10:19:33 pmCan 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 · 2013-01-18 08:53 Quote from: delphi887 on January 17, 2013, 09:18:14 pmhi @aliboy  how are you?  Quote from: aliboy on January 17, 2013, 07:04:47 pmdoes 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 · 2013-01-18 09:11 Quote from: EgonHugeist on January 17, 2013, 10:19:33 pm@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 · 2013-01-18 11:03 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/progra m_2.jpg my problem solved . but why occurred this problem in Zeos 7.0.3 stable ???? ---------------------------------------------------------------------- aliboy · 2013-01-19 19:32 thanks to all for sharing and responses  Finally,I could solved the issue JUST with change Collation=utf8_persian_ci  to Collation=utf8_general_ci ,so  data (in persian and other unicode) can store/retrieve  in/from MySQL correctly. (first of all remove my database and its table then create it again with Collation=utf8_general_ci ) thanks to all.