SQLdb: TDBCheckbox still gray lazarus / 16-Databases ---------------------------------------------------------------------- mangakissa · 2015-12-02 14:40 Database : firebird (embedded) Table       : field keurmeester char (1) not null default N Lazarus 1.44 /FPC 2.6.4 Windows I put on my field a TDBCheckbox. Property ValueChecked set to J and property ValueUnchecked set TO N, datasource and fieldname filled in. But when I run my application and go to my form, the TDBCheckbox is gray. Do I miss something? ---------------------------------------------------------------------- rvk · 2015-12-02 16:39 I traced your problem to the following routine: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i function TDBCheckBox.GetFieldCheckState: TCheckBoxState;var  FieldText: string;  DataLinkField: TField;begin  DataLinkField := FDataLink.Field;  if DatalinkField=nil then begin    Result:=cbUnchecked;    exit;  end;  if DataLinkField.IsNull then    Result:=cbGrayed  else if DataLinkField.DataType = ftBoolean then begin    if DataLinkField.AsBoolean then      Result:=cbChecked    else      Result:=cbUnchecked;  end else begin    FieldText:=UpperCase(DatalinkField.AsString);       // FieldText = 'J  ' and FValueChecked = 'J'    if FindWord(FieldText,UpperCase(FValueChecked)) then      Result:=cbChecked    else if FindWord(FieldText,UpperCase(FValueUnchecked)) then      Result:=cbUnchecked    else      Result:=cbGrayed;  end;end; Somehow it ends up in the highlighted part (DataType <> ftBoolean) but the DatalinkField.AsString is 'J  '. (note the 2 spaces behind J.) Do you have any idea why internally it retrieves 3 characters (i.e. 'J  ') instead of just 'J' ?? KEURMEESTER is a CHAR(1) so I'm not sure where the extra 2 spaces come from. ---------------------------------------------------------------------- rvk · 2015-12-02 16:52 Further debugging gives: DBCKKeurmeester.Field.DataType = ftFixedChar DBCKKeurmeester.Field.Size = 4 DBCKKeurmeester.Field.DataSize = 5 '.' + DBCKKeurmeester.Field.AsString + '.' = .J   . That last one shows the asString has 2 spaces after it !! Who knows why? Maybe this is related: http://lazarus.lazarus.freepascal.narkive.com/asjjYdOm/tdbedit- shows-4-characters-from-a-char-1-field (maybe an UTF-8 issue) and http://lists.freepascal.org/fpc-devel/2009-July/017074.html ---------------------------------------------------------------------- mangakissa · 2015-12-03 09:01 QuoteDo you have any idea why internally it retrieves 3 characters (i.e. 'J  ') instead of just 'J' ?? KEURMEESTER is a CHAR(1) so I'm not sure where the extra 2 spaces come from. rvk, you go deep I looked at my database settings and is set on UTF-8. Searching on internet gave me the option to set property charset to 'utf8', but I don't see any changes. I also checked another field with varchar(30) and gives me DBEAchternaam.Field.DataSize = 121. Do I have to change the characterset in my firebird database or is there still an option in Lazarus to read the fields properly? Am I the only person who uses SQLdb/firebird with UTF-8? ---------------------------------------------------------------------- rvk · 2015-12-03 09:44 Quote from: mangakissa on December 03, 2015, 10:01:02 amQuoteDo you have any idea why internally it retrieves 3 characters (i.e. 'J  ') instead of just 'J' ?? KEURMEESTER is a CHAR(1) so I'm not sure where the extra 2 spaces come from. I looked at my database settings and is set on UTF-8. Searching on internet gave me the option to set property charset to 'utf8', but I don't see any changes. I also checked another field with varchar(30) and gives me DBEAchternaam.Field.DataSize = 121. Do I have to change the characterset in my firebird database or is there still an option in Lazarus to read the fields properly? Am I the only person who uses SQLdb/firebird with UTF-8? I also tried setting the connection-parameters to UTF8 but it didn't help. The fact is that sqldb doesn't support a ftFixedChar with length of 1 (CHAR(1)) in combination with TDBCheckbox when it comes to UTF8. You have 4 options: Wait until somebody capable fixes the ftFixedChar/DBCheckBox/UTF8 problem in sqldb. (You probably need an example and open a bugtrack- entry for it to get noticed if there isn't one already)Set your boolean-field to VARCHAR(1). You need to re-store all values for already present records. But after that old and new records will be interpreted correctly for the TDBCheckbox. ftFixedChar, CHAR(xx) in Firebird, is always interpreted to have trailing spaces. So if you use it in combination with UTF8 your CHAR(1) will have 'J  '. If you make it a VARCHAR(1), sqldb will cut off any trailing spaces and the TDBCheckbox will work correctly.Use another charset for the database instead of UTF8.Try another DB-component-set like Zeos.Yes, you are probably the first to use Firebird/UTF8 Database/SQLdb/TDBCheckBox/CHAR(1) combination ---------------------------------------------------------------------- mangakissa · 2015-12-03 10:05 I'm going to create a new database with ansicode. The data within is useless now and can be filled very fast in the new database. QuoteTry another DB-component-set like Zeos. It could, but I want to use the basic components. Besides, SQLdb is not so bad, but is not fully function yet. But I haven't test it with FPC 3.0 ---------------------------------------------------------------------- rvk · 2015-12-03 11:56 Quote from: mangakissa on December 03, 2015, 11:05:20 amQuoteTry another DB-component-set like Zeos. It could, but I want to use the basic components. Besides, SQLdb is not so bad, but is not fully function yet. But I haven't test it with FPC 3.0 I have. FPC trunk has the same problems. ---------------------------------------------------------------------- mangakissa · 2015-12-03 15:42 Created new database with charsettings to NONE. The DBCheckbox is working now. The only thing I see now is 'België' is converted to 'België', but lazarus can handle it. Hopefully FR5 converts correctly, but thats another issue. Is it solved. No, I don't think so. UTF-8 should be working. The framework SQLdb should be working with these conversions. ---------------------------------------------------------------------- rvk · 2015-12-03 15:45 Quote from: mangakissa on December 03, 2015, 04:42:37 pmThe only thing I see now is 'België' is converted to 'België', but lazarus can handle it. So FPC is still putting UTF8 into the database? That's quite a mess  You did specify NONE for Charset in the connection-component? ---------------------------------------------------------------------- LacaK · 2015-12-03 18:11 You are correct, that it is UTF-8 related and connection CharSet related. sqlDB reads data as they are provided by underlaying fbclient library. And as far as fixed length character data are passed in fixed length buffers, client library allocates buffer large enough to hold any unicode character. In UTF-8 encoding one unicode character can occupy from 1 to 4 bytes, so it is reason why you see "one char" followed by 3 spaces. You can simply use for ValueChecked='Y    ' and TDBCheckBox will work as expected. ---------------------------------------------------------------------- mangakissa · 2015-12-05 11:42 Quote from: rvkSo FPC is still putting UTF8 into the database? That's quite a mess  You did specify NONE for Charset in the connection-component? Oops. The charset in TIBConnection was filled in. I removed it and tried again. But the result is the same. Quote from: LacaKYou can simply use for ValueChecked='Y    ' and TDBCheckBox will work as expected. I tried that, but didn't work. I'm concerned the database now can only work with Lazarus SQLdb and not with Delphi ZEOS/firedac (haven't tested yet). ---------------------------------------------------------------------- rvk · 2015-12-05 13:31 Why not use a smallint in your domain for boolean? No problem with UTF8 whatsoever Although you will keep hitting the België problem  http://www.firebirdfaq.org/faq12/ Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE DOMAIN BOOLEANAS SMALLINT CHECK (value is null or value in (0, 1)); ---------------------------------------------------------------------- rvk · 2015-12-05 13:52 I made a very small test-project. The IBConnection1.CreateDB should take the Charset to create a new UTF8 database. It all seems to work in here. (or am I missing something?) (I used my local firebird-server but you can copy the embedded files in there instead) I used the 1/0 domain and België also doesn't seem to give any trouble. (Lazarus 1.4.4 / FPC 2.6.4) ---------------------------------------------------------------------- mangakissa · 2015-12-05 14:12 Yes, because the database is not created with TIBConnection. ---------------------------------------------------------------------- rvk · 2015-12-05 14:17 Quote from: mangakissa on December 05, 2015, 03:12:22 pmYes, because the database is not created with TIBConnection. That doesn't matter. You can change the CreateDB line into anything you like for creating the DB yourself. You could also remove the creation of the DB (and deletion) and use your own DB. But does this code work for you? If so... then there is something else wrong. B.T.W. if the value België was not added by your program in Lazarus it could also be a problem with the program which you used to add België into the database. ---------------------------------------------------------------------- mangakissa · 2015-12-06 14:33 That's correct. That's why I noticed there is some kind of conversion in the SQLdb framework. QuoteWhy not use a smallint in your domain for boolean? No problem with UTF8 whatsoever . Do you know wat SQLdb does with domains. Nothing. I tried this the first time I used firebird. SQLdb converts the datatype as TIntegerfield, not as TBooleanfield (but that was with version 1.2, and haven't tested in 1.44. In my opinion it hasn't changed for a bit). ---------------------------------------------------------------------- rvk · 2015-12-06 14:56 Quote from: mangakissa on December 06, 2015, 03:33:57 pmThat's correct. That's why I noticed there is some kind of conversion in the SQLdb framework.What's correct?? You didn't quote anything so it's hard to put into perspective. QuoteQuoteWhy not use a smallint in your domain for boolean? No problem with UTF8 whatsoever Do you know wat SQLdb does with domains. Nothing. I tried this the first time I used firebird. SQLdb converts the datatype as TIntegerfield, not as TBooleanfieldDid you TRY running my example? SQLdb doesn't HAVE to do anything with domains. The domain I created BOOLEAN was just an example. If you look at my example you can just use a SMALLINT and put 1 and 0 in the TrueValue and FalseValue of the TDBCheckBox and you don't have to worry about the UTF8 conversion for a CHAR(1). Of course it's a TIntegerField but that doesn't matter for TDBCheckBox. A CHAR(1) will also not be a TBooleanField. Have you checked what CHAR(1) translate to? It translates to ftFixedField and will NEVER be automatically a TBooleanField. That will never happen for SQLdb/Firebird<3.0. I don't even think it will happen in Delphi because Firebird doesn't know "Boolean". You can however address the TStringField with an asBoolean function if you use 0/1 or N/Y (but never use N/J). So to summarize... don't use CHAR(1) for a booleanfield in Firebird but use SMALLINT (or domain BOOLEAN) and use 1/0 in the TDBCheckBox. For the rest of the VARCHAR fields... I didn't see any problems you mentioned with België in an UTF8 database.