Display of non UTF charset in DBGrid using OracleConnection lazarus / 16-Databases ---------------------------------------------------------------------- salog · 2008-11-19 08:03 I connect to Oracle database with OracleConnection component, and use SQLQuery to get data from the server. The oracle server works in WIN-1251 charset. The SQLQuery works properly, but fields containing russian charasters are displayed in a DBGrid empty. How can I resolve this problem? ---------------------------------------------------------------------- salog · 2008-11-21 01:05 More over, I tryed to convert WIN1251 to UTF in GetText procedure of the field. So, I have defined, that the value of this field is empty just its' got from the database. It means that Russian characters are lost when they are being retrieved from the Oracle. - somewhere in deeper levels of the database component's code. Are developers going to add transliteration features to newer versions of Lazarus? :-( ---------------------------------------------------------------------- tatamata · 2008-11-23 11:52 Hi! It is simple. Iterate through you dataset rows, then through every field. Inspect every field whether field type is String, if yes then Dataset1.FieldBy Name('YourField').AsSring:=AnstiToUTF8(Dataset1.FieldByName('YourField ').AsSring); ---------------------------------------------------------------------- jesusr · 2008-11-24 15:25 When using firebird one can set the "connection" charset property to UTF8 so even when data is stored in some other encoding, the DB engine will do conversions on the fly to UTF8 which is what Lazarus expect. Try to research what charset value works for you, also, it seems Oracle client library can be configured to some specific encoding, some way, as I don't use Oracle I don't know the details though. ---------------------------------------------------------------------- net_goose · 2010-03-19 09:17 Please? reply about this topic: "Are developers going to add transliteration features to newer versions of Lazarus?" ---------------------------------------------------------------------- Zoran · 2010-03-19 11:08 Quote from: net_goose on March 19, 2010, 10:17:45 amPlease? reply about this topic: "Are developers going to add transliteration features to newer versions of Lazarus?" Use LConvEncoding unit. There are functions for converting between different charsets. Or you mean something else by "transliteration features"? ---------------------------------------------------------------------- net_goose · 2010-03-23 07:24 'll Show how it will affect in DBGRID. If you can please give an example! ---------------------------------------------------------------------- net_goose · 2010-03-23 07:36 How to display in the correct dbgrid Russian font ---------------------------------------------------------------------- Zoran · 2010-03-23 07:57 Quote from: net_goose on March 23, 2010, 08:36:06 amHow to display in the correct dbgrid Russian font Which database do you use? ---------------------------------------------------------------------- net_goose · 2010-03-23 08:52 oracle 10g on linux server. application (lazarus project) on windows xp. when i open recordset the dbgrid display ???-20 instead (УВК-20) ---------------------------------------------------------------------- Zoran · 2010-03-24 07:30 Quote from: net_goose on March 23, 2010, 09:52:56 amoracle 10g on linux server. application (lazarus project) on windows xp. when i open recordset the dbgrid display ???-20 instead (УВК-20) I can't test it with oracle now, but here is what I think might solve the problem. If you use ZEOS, try putting this line in ZConnection's "properties" property: (If you use SQLdb, you might try to add the line in OracleConnection's params property or perhaps SQLTransaction's params property...) Code: [Select]codepage=UTF8 Or perhaps it should be for you: Code: [Select]codepage=WIN1251 Try both of these (not both at same time). ---------------------------------------------------------------------- net_goose · 2010-03-24 07:56 I tried, and was surprised I enter data into params OracleConnections, click OK, open again, and the value of lost that. It may be? I used your advice but, unfortunately, did not help. I tried to use the ODAC with direct access to ORACLE, but this property is not available, but using Dephi7 no problems with the encoding but I have not changed anything in the parameters of the connection I think that the problem until it is in lazarus, sorry but I will wait for the decision! ---------------------------------------------------------------------- Zoran · 2010-03-24 08:14 Quote from: net_goose on March 24, 2010, 08:56:38 amI used your advice but, unfortunately, did not help. I tried to use the ODAC with direct access to ORACLE, but this property is not available, but using Dephi7 no problems with the encoding but I have not changed anything in the parameters of the connection I think that the problem until it is in lazarus, sorry but I will wait for the decision! I'm sorry, I can't do tests with oracle... I don't think that it has anything to do with DBGrid, the problem seems to be burried deeper. It would be nice that DB components developers join this discussion... As a start, try testing with ZEOS components. ---------------------------------------------------------------------- net_goose · 2010-03-24 13:13 I installed zeos 6.6.6, same result. Maybe the problem in the versions, I use Lazarus 0.9.28.2-fpc 2.2.4 and Zeos 6.6.6 ---------------------------------------------------------------------- Zoran · 2010-03-24 13:33 Quote from: net_goose on March 24, 2010, 02:13:06 pmI installed zeos 6.6.6, same result. Maybe the problem in the versions, I use Lazarus 0.9.28.2-fpc 2.2.4 and Zeos 6.6.6 No, I don't think so... I'm wandering... is it perhaps a font problem? Did you set the DBGrid's font explicitely? Are you sure that the used font can display cirilic? Set the font to some for which you are sure. ---------------------------------------------------------------------- net_goose · 2010-03-24 13:52 I changed the fonts did not help. But noticed a strange thing that when you insert a row into a table in Russian but not all, most of the letters are displayed as two question mark (??), for example the text Привет от штиблет displayed as Прив???? о?? ????и????????  I tried using TOAD, everything is fine, my project is written in Delphi is working fine and I have not changed anything in the coding!! ---------------------------------------------------------------------- Zoran · 2010-03-24 14:04 Quote from: net_goose on March 24, 2010, 02:52:41 pmI changed the fonts did not help. But noticed a strange thing that when you insert a row into a table in Russian but not all, most of the letters are displayed as two question mark (??), for example the text Привет от штиблет displayed as Прив???? о?? ????и????????   I tried using TOAD, everything is fine, my project is written in Delphi is working fine and I have not changed anything in the coding!! Try putting something like this in field's OnGetText event: Code: [Select] uses   ... , LConvEncoding; ..... procedure TForm2.ZQuery1Field2GetText(Sender: TField; var aText: string;   DisplayText: Boolean); begin    aText := CP1251ToUTF8(Sender.AsString); end; ---------------------------------------------------------------------- net_goose · 2010-03-25 07:09 After I used the property OnGetText, the data do not appear, apparently to pick up the desired function "CP1251ToUTF8", but it means that for every DBGRID I would need for each field, insert this code, it's terrible, tell whether the algorithm for the entire grid default, even if I pick up the encoding, although I doubt it? ---------------------------------------------------------------------- Zoran · 2010-03-25 07:14 Quote from: net_goose on March 25, 2010, 07:33:30 amIf I understand you correctly, I need to double click on DBGRID, add the field and in the event ongettext prescribe the code, but the events in the object inspector field - are empty? There are no OnGetText?! Simply inserting the procedure and renaming it, I get an error. Could you describe the items that I need to do, I just started to get acquainted with Lazarus. I thought it would be as easy as with Delphi, but ... a lot of pitfalls. Sorry. I assumed that you defined persistent fields. To define persistent fields: In your DataSet component (that is some Query or Table component you use), right click and chose "Edit fields". Then, select and add all fields. Note that you might need to temporary connect to database before you do this and after the fields are added, you can disconect again. Now, you can chose particular field in the object inspector, and edit the fields OnGetText event. ---------------------------------------------------------------------- Zoran · 2010-03-25 07:15 Quote from: net_goose on March 25, 2010, 08:09:33 amAfter I used the property OnGetText, the data do not appear, apparently to pick up the desired function "CP1251ToUTF8", but it means that for every DBGRID I would need for each field, insert this code, it's terrible, tell whether the algorithm for the entire grid default, even if I pick up the encoding, although I doubt it? There should be some better solution... For a start, does this work? ---------------------------------------------------------------------- Zoran · 2010-03-25 07:21 I found this topic on Zeos forum: http://zeos.firmos.at/viewtopic.php?t=2172 Maybe you should ask there. ---------------------------------------------------------------------- net_goose · 2010-03-25 07:22 Yes, everything worked correctly displays !!!!!!! But the question remains, can I default for all fields use the same procedure, or do I need to do this for each field. And I'll see what will happen when you insert data into a table? ---------------------------------------------------------------------- Zoran · 2010-03-25 07:30 Quote from: net_goose on March 25, 2010, 08:22:35 amYes, everything worked correctly displays !!!!!!! But the question remains, can I default for all fields use the same procedure, or do I need to do this for each field. And I'll see what will happen when you insert a table? Yes, you will have to do this for each field (of course, you will not have to create new procedures, you can use one). For input, you should use OnSetText and inverse procedure - UTF8ToCP1251. There must be better solution. See that topic on ZEOS forum, it seems that adding "codepage=..." should work finally. Maybe you should try "codepage = cp1251", instead of win1251 what I wrote in some of my previous posts. Note that if you try to get it to work, you must NOT use OnGetText. ---------------------------------------------------------------------- net_goose · 2010-03-25 07:57 Option "codepage = cp1251" does not help, I removed the code "ongettext" The forum Zeos, really nothing is said or something I do not understand! Give me, as a procedure to manage all fields ---------------------------------------------------------------------- Zoran · 2010-03-25 08:35 Quote from: net_goose on March 25, 2010, 08:57:53 amOption "codepage = cp1251" does not help, I removed the code "ongettext" The forum Zeos, really nothing is said or something I do not understand! Give me, as a procedure to manage all fields I don't understand something there either, I was hoping you will. I would expect "codepage=UTF8" to work... Maybe it would be "codepage=871"... Lazarus always use UTF8 charset. ---------------------------------------------------------------------- net_goose · 2010-03-25 09:46 I do not know probably something that no property "codepage" as if I had not twisted any way to display the data is not affected, and when used through the function OnGetText CP1251ToUTF8 everything worked fine. At that indicates "codepage", on the encoding of the database or Lazarus? ---------------------------------------------------------------------- Zoran · 2010-03-25 10:23 Quote from: net_goose on March 25, 2010, 10:46:43 amI do not know probably something that no property "codepage" as if I had not twisted any way to display the data is not affected, and when used through the function OnGetText CP1251ToUTF8 everything worked fine. At that indicates "codepage", on the encoding of the database or Lazarus? As I understand, but I am not sure, it is sent to oracle server to indicate the client codepage (in Lazarus it is always UTF8), so that the server should convert and send the appropriately encoded characters to client. We did not succeded in this, so server sends the characers in server codepage (cp1251 -- cirilic) and you have to use CP1251ToUTF8 in OnGetText event to convert what came from server. ---------------------------------------------------------------------- Zoran · 2010-03-25 10:38 Quote from: Zoran on March 25, 2010, 11:23:57 am... it is sent to oracle server to indicate the client codepage (in Lazarus it is always UTF8), ... Which means that you should put "codepage=UTF8", but again, I am not sure about all this, so I told you to try "codepage=cp1251"... I don't know all this actually and I can't try connecting to Oracle... There should be someone on this forum who would know better... ---------------------------------------------------------------------- net_goose · 2010-03-25 10:54 Oh, how I would find it? Another question, if you create a component-based Zquery, who himself would recoded data through OnGetText? And yet you please tell, as a procedure to get all the fields that I added to ZQuery, recoded to one procedure but not both: procedure TForm1.QueryFIELD1GetText (Sender: TField; var aText: string;   DisplayText: Boolean); begin   aText: = CP1251ToUTF8 (Sender.AsString); end; procedure TForm1.QueryFIELD2GetText (Sender: TField; var aText: string;   DisplayText: Boolean); begin   aText: = CP1251ToUTF8 (Sender.AsString); end; If possible, give an example ---------------------------------------------------------------------- Zoran · 2010-03-25 11:54 Quote from: net_goose on March 25, 2010, 11:54:40 am Another question, if you create a component-based Zquery, who himself would recoded data through OnGetText? I don't understand this question. Quote from: net_goose on March 25, 2010, 11:54:40 amAnd yet you please tell, as a procedure to get all the fields that I added to ZQuery, recoded to one procedure but not both: procedure TForm1.QueryFIELD1GetText (Sender: TField; var aText: string;   DisplayText: Boolean); begin   aText: = CP1251ToUTF8 (Sender.AsString); end; procedure TForm1.QueryFIELD2GetText (Sender: TField; var aText: string;   DisplayText: Boolean); begin   aText: = CP1251ToUTF8 (Sender.AsString); end; If possible, give an example  When you declare the first one (in your example it's QueryField1GetText), you could assign the same procedure to the field2 -- go to field2 in object inspector and in "OnGetText", you can select the first procedure from drop-down menu. ---------------------------------------------------------------------- Zoran · 2010-03-25 11:59 Or better, add this unit to project: Code: [Select]unit UnitGetSetText; {$mode objfpc}{$H+} interface uses   Classes, SysUtils, Forms, LConvEncoding, db; type   { TGetSetTextClass }   TGetSetTextClass = class   private     procedure GetText(Sender: TField; var aText: string;            DisplayText: Boolean);     procedure SetText(Sender: TField; const aText: string);   public     procedure AssignGetTextToQueries(F: TCustomForm);   end; var   GetSetTextClass: TGetSetTextClass; implementation { TGetSetTextClass } procedure TGetSetTextClass.GetText(Sender: TField; var aText: string;   DisplayText: Boolean); begin   aText := CP1251ToUTF8(Sender.AsString); end; procedure TGetSetTextClass.SetText(Sender: TField; const aText: string); begin   Sender.AsString := UTF8ToCP1251(aText); end; procedure TGetSetTextClass.AssignGetTextToQueries(F: TCustomForm); var   I, J: Integer;   Q: TDataSet; // doesn't matter if it's TQuery or TTable or TZQuery...   SF: TStringField; begin   for I := 0 to F.ComponentCount - 1 do     if F.Components[I] is TDataSet then begin       Q := TDataSet(F.Components[I]);       for J := 0 to Q.FieldCount - 1 do         if Q.Fields[J] is TStringField then begin           SF := TStringField(Q.Fields[J]);           SF.OnGetText := @GetText;           SF.OnSetText := @SetText;         end;     end; end; initialization   GetSetTextClass := TGetSetTextClass.Create; finalization   GetSetTextClass.Free; end. And for each form in project which contains datasets, add the new unit in uses section and add one line in Form's OnCreate event: Code: [Select]uses ..., UnitGetSetText; ... procedure TForm2.FormCreate(Sender: TObject); begin   // your code...   GetSetTextClass.AssignGetTextToQueries(Self); end; ---------------------------------------------------------------------- Zoran · 2010-03-25 12:21 The best would actually be using UTF8 encoding in your Oracle database, which should be standard nowadays. However, you might not be the db administrator, maybe many applications written in other programming languages already work on this base, the base itself is maybe very big so the changing of character encoding cannot be option for you now... I still beleive that there is a way to send the client request for UTF8 charset to server when connecting, which is in my opinion the best and simplest solution. However, the unit I gave in previous post should work for you as a workaround. ---------------------------------------------------------------------- net_goose · 2010-03-25 12:45 Thank you very much for your explanations and patience! I will try to convert the base to UTF8 and check with happiness base test (for future use). ---------------------------------------------------------------------- net_goose · 2010-03-25 15:57 But can it be the fault oracle-instantclient_10? ---------------------------------------------------------------------- Zoran · 2010-03-25 17:52 Quote from: net_goose on March 25, 2010, 01:45:29 pmThank you very much for your explanations and patience! You are welcome! Quote from: net_goose on March 25, 2010, 04:57:43 pmBut can it be the fault oracle-instantclient_10? I have no idea, sorry. ---------------------------------------------------------------------- thierrybo · 2010-03-27 19:43 Hi, maybe a little out of topic but ... I have a question. As the GetText event is defined at SQLQuery level, the code you write in GetText procedure should affect ALL DB controls, right ? For me if I want to populate a DBLookupComboBox ListSource with a field, the gettext procedure is not used. If I use a DBGrid instead, it works. ---------------------------------------------------------------------- bruce0829 · 2010-07-09 08:57 for any field of datatype = ftstring , do MyGetText() procedure TForm1.SQLQuery1AfterOpen(DataSet: TDataSet); begin   for i:=0 to DataSet.Fields.Count-1 do   begin     if DataSet.Fields.Fields.DataType=ftstring then     begin       DataSet.Fields.Fields.OnGetText:=@MyGetText;     end;   end; end; procedure TForm1.MyGetText(Sender: TField; var aText: string;   DisplayText: Boolean); begin    aText := ANSIToUTF8(Sender.AsString); end;