SQLDB will not work with MySQLboolean fields lazarus / 16-Databases ---------------------------------------------------------------------- mangakissa · 2013-10-17 13:03 I've set up a small application to check in the booleanfields are working in KDBGrid. KDBGrid will not see the booleanfields, because SQLDB will convert the tinyint(1) field into a TSmallintfield instead of TBooleanfield. Is this a bug or do I something to do to get it right. ---------------------------------------------------------------------- LacaK · 2013-10-18 05:20 AFAIK MySQL does not have true SQL boolean data type. BOOLEAN is only synonym for TINYINT. So MySQL report such columns as tinyint. IMO it is not bug on FPC side, it is "bug" in MySQL ---------------------------------------------------------------------- mangakissa · 2013-10-18 06:24 So, using Oracle, who doesn't have an booleanfield either, should have a bug? MyDAC from Devart gives an boolean back. ---------------------------------------------------------------------- marcov · 2013-10-18 08:14 Quote from: LacaK on October 18, 2013, 07:20:10 amAFAIK MySQL does not have true SQL boolean data type. BOOLEAN is only synonym for TINYINT. So MySQL report such columns as tinyint. IMO it is not bug on FPC side, it is "bug" in MySQL  Do mysql admin tools, if you make a field with type boolean and after closing/reopening back report back "tinyint" or "boolean" for that field ? If boolean, maybe there are flags in the field definition that allow to make the difference? ---------------------------------------------------------------------- taazz · 2013-10-18 08:52 I don't think rapport means what you think it means. I think you want to say report. ---------------------------------------------------------------------- marcov · 2013-10-18 08:54 Quote from: taazz on October 18, 2013, 10:52:38 amI don't think rapport means what you think it means. I think you want to say report. Yes, typo, report=rapport in dutch. ---------------------------------------------------------------------- LacaK · 2013-10-18 09:48 Quote from: mangakissa on October 18, 2013, 08:24:49 amSo, using Oracle, who doesn't have an booleanfield either, should have a bug? MyDAC from Devart gives an boolean back. May be, that I am wrong, but I see diiference: MySQL privides BOOLEAN, but it is same as TINYINT. So here it is violation with SQL standard. Oracle des not provide BOOLEAN at all. So here it is incomplete implementation of SQL standard. http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html "BOOL, BOOLEAN These types are synonyms for TINYINT(1)" AFAIK there is no flag indicating boolean, so at client side there is no way how to distinguish BOOLEAN and TINYINY. When you create table f.e.: create table t1 (Fbool boolean); And then you type: show create table t1; you will see: ... Fbool TINYINT(1) ... ---------------------------------------------------------------------- mangakissa · 2013-10-21 14:10 Firebird has a similar problem. For using a boolean, you have to create a domain. But actually it is a smallint. MySQL is doing the same, but have already a builtin boolean domain. But SQLdb will see the field as a TSmallintfield (ZEOS also). Devart has a solution for it: Code: [Select] TMyQuery.DataTypeMap.Clear;  TMyQuery..DataTypeMap.AddFieldNameRule('booleanfield', ftBoolean, true); ---------------------------------------------------------------------- jack616 · 2013-10-28 14:11 my take on this - Everyone is talking about a "problem". However since the days of the first (let us say 8bit) microprocessor based computers there have been no "boolean" processes outside the the micro at all. This means that at some stage all boolean operations are simply a cast of an 8bit byte (or higher)  to a result a compiler considers to be a boolean value of on or off rather than a 'one' or 'zero' This allows the freedom of any interpretation such as "any non-zero result = true" as well as still being able to define it as a specific "one" only as being true (and all the variations). dbase implementors have rightly decided it seems  that a true binary boolean  is not possible and not necessary and indeed would limit things to no purpose. I havn't ready the SQL specification but I rather doubt that any of this contravenes anything in it since there is no such thing as 1 bit computing. This isn't any sort of bug  - it's just logical design. ---------------------------------------------------------------------- goodname · 2013-10-28 18:18 Database end users do not care how a boolean field is handled behind the scenes. They just want a boolean data type. What would be so difficult about defining a boolean type and completely hiding how the type is handled from the end user? It is simply a matter of being end user friendly. ---------------------------------------------------------------------- jack616 · 2013-10-29 14:22 Quote from: goodname on October 28, 2013, 07:18:17 pmDatabase end users do not care how a boolean field is handled behind the scenes. They just want a boolean data type. What would be so difficult about defining a boolean type and completely hiding how the type is handled from the end user? It is simply a matter of being end user friendly. Yes - and all databases do just that as explained. In MySQL it's called bool or boolean just for the convenience of end users. ---------------------------------------------------------------------- goodname · 2013-10-29 14:52 If all databases really handled boolean properly then we would not know that for example the MySQL boolean key word is handled as a tinyint(1) behind the scenes. The SQLdb library would see a boolean data type instead of a tinyint data type. Because the boolean key word is mapped to another type does not mean a boolean type actually exists. Handling it as a key word search and replace means client libraries such as SQLdb have no way of knowing the difference between boolean and whatever it is replaced with. That is only being partially end user friendly. ---------------------------------------------------------------------- EgonHugeist · 2013-10-29 21:21 @mangakissa Zeos assumes a TBoolenField for MySQL defined as: AField = enum('Y','N'). Using the tinyint as Boolean-Field is terribly wrong. See: http://dev.mysql.com/doc/refman/5.0/en/integer-types.html If the overengeniered, slow DevArt component is doing that... Not a thing for Zeos OR SQLDB, i think... Firebird3.0 will have a true Boolean-Type. Oracle: It's your turn to implement that. Nothing prevents you to provide a patch which SQLDB/Zeos gracefully will check and apply. ---------------------------------------------------------------------- jack616 · 2013-10-30 13:05 Quote from: goodname on October 29, 2013, 03:52:02 pmIf all databases really handled boolean properly then we would not know that for example the MySQL boolean key word is handled as a tinyint(1) behind the scenes. OK so one of us is very confused here. Why not explain to me exactly what you mean by handling booleans properly? ---------------------------------------------------------------------- goodname · 2013-10-30 14:08 CREATE TABLE tbl (  fld BOOLEAN ); Running the above script in MySQL or Oracle will change the word BOOLEAN to something else since the boolean data type does not really exist. This means when a library such as ZEOS or SQLdb reads the table it does not see a boolean but it sees the data type it has been changed to. The PostgreSQL does have a boolean data type defined so client libraries like zeos or SQLdb will read boolean. The end user does not know how PostgreSQL stores the boolean type and it does not really matter to the end user. ---------------------------------------------------------------------- jack616 · 2013-11-03 12:17 Thank you for explaining. I see the problem now. You are trying to use incompatible  translation layers. If I understand correctly you are trying to read a table created by one database with the interface of another. That is unlikely ever to work - it isnt just a database issue. If you want to do such a thing it would be essential for the end user to know exactly how postgreSQL stores the boolean type AND how it is interpreted. But the postgre is probably doing exactly the same as every othe DB including mySQL and storing it as a tinyint or byte for the reason explained above.  i.e. A boolean is not the same as a bit. It must be stored as a byte or multiples thereof. A boolean is only "considered" to have 2 states. Because it will be stored (I expect) as a byte in all databases it may have 256 actual states in all of them. A boolean returning "false" may not necessarily have the same binary value  from another database therefore. both 0 and -1 have been used both as true and as false in many systems for example. They all have booleans defined as you have just demonstrated yourself in mySQL. Binary compatibility may however vary from one manufacturer to another. A "type" remember is just a label we apply to a group of binary digits in order to raise programming to a higher level than throwing switches one at a time. It doesnt actually exist as an entity. ---------------------------------------------------------------------- goodname · 2013-11-03 15:06 The incompatible translation layer is built into the database engine. The (Data Manipulation Language)DML and (Data Definition Language)DDL are incompatible. SQLdb or Zeos can submit the create table statement directly and then read the table and something besides boolean can be read. Don't see what is so difficult about database engines defining a boolean type or label as you have called it. Databases use three value logic which means by definition database booleans can be true, false, or null. Whether null is evaluated as true or false changes on a case by case basis and is one of the more difficult things for new or even experienced database programmers to handle. ---------------------------------------------------------------------- mangakissa · 2013-11-04 16:31 It's not a problem of the database engine. If they use a integer or an enum, I don't care. I do care SQLdb reads a new dataset and my DBcheckbox or DBGrid with checkboxes are not functioning, because it is an integer and not a boolean. The SQLdb engine shall read the fielddefinitions and if this is a tinyint(1), a fielddefinition will change into a boolean. That's all. The TDBCheckbox has the property ValueChecked and ValueUnchecked to check if 1 is true and 0 is false. But most advanced DBGrids asking a booleanfield. ---------------------------------------------------------------------- goodname · 2013-11-04 18:11 If a SQLdb change is made then I suggest using the same work around as Zeos. It seams to be a common MySQL practice to define enum for boolean. Unless such a patch has already been applied. I have not checked. Quote from: EgonHugeist on October 29, 2013, 10:21:23 pm@mangakissa Zeos assumes a TBoolenField for MySQL defined as: AField = enum('Y','N'). Using the tinyint as Boolean-Field is terribly wrong. See: http://dev.mysql.com/doc/refman/5.0/en/integer-types.html If the overengeniered, slow DevArt component is doing that... Not a thing for Zeos OR SQLDB, i think... Firebird3.0 will have a true Boolean-Type. Oracle: It's your turn to implement that. Nothing prevents you to provide a patch which SQLDB/Zeos gracefully will check and apply. ---------------------------------------------------------------------- mangakissa · 2013-11-05 13:23 As far as I know, Delphi and lazarus can't work with enums. Ciaran McNulty (stack overflow) says: Quoteif you use the BOOLEAN type, this is aliased to TINYINT(1). This is best if you want to use standardised SQL and don't mind that the field could contain an out of range value (basically anything that isn't 0 will be 'true'). ENUM('False', 'True') will let you use the strings in your SQL, and MySQL will store the field internally as an integer where 'False'=0 and 'True'=1 based on the order the Enum is specified. In MySQL 5+ you can use a BIT(1) field to indicate a 1-bit numeric type. I don't believe this actually uses any less space in the storage but again allows you to constrain the possible values to 1 or 0. All of the above will use approximately the same amount of storage, so it's best to pick the one you find easiest to work with. share|improve this answer    Why is it so difficult to remap a (mysql) tinyint(1) to a boolean when getting back a dataset? Firebird uses an integer if you're creating a constraint as BOOLEAN ---------------------------------------------------------------------- LacaK · 2013-11-05 13:28 Quote from: mangakissa on November 05, 2013, 02:23:27 pmWhy is it so difficult to remap a (mysql) tinyint(1) to a boolean when getting back a dataset? Because on client side you can not distinguish between column created as: CREATE TABLE t (col1 BOOLEAN) and CREATE TABLE t(col2 TINYINT) // user want signed integer in range -128 to 127 on client side (sqlDB) you will see above mentioned case as EQUALS, because MySQL translates BOOLEAN to TINYINT on the fly and stores table definition as "CREATE TABLE t(col2 TINYINT)" ---------------------------------------------------------------------- AKCarlow · 2014-07-15 16:20 Quote from: mangakissa on November 04, 2013, 05:31:22 pm The TDBCheckbox has the property ValueChecked and ValueUnchecked to check if 1 is true and 0 is false. But most advanced DBGrids asking a booleanfield.    Mangakissa, thank you, that was just the information I was looking for. However, when I select those properties in the Object Inspector the rather misleading description that appears is "ValueUnchecked -the box for the value is unchecked" and "ValueChecked - the box for the value has been checked ". These should be rephrased, maybe as "the value when the box is unchecked" and "the value when the box is checked". Would this need a bug report? Though it is a documentation issue rather than a bug - the checkbox works as expected with the TinyInt 0/1 values from the MySQL database, once these properties have been set. ---------------------------------------------------------------------- BigChimp · 2014-07-15 16:24 Definitely worth a bug report - documentation issues, feature requests etc are also tracked in the bug tracker. ---------------------------------------------------------------------- AKCarlow · 2014-07-15 22:37 Quote from: BigChimp on July 15, 2014, 06:24:14 pmDefinitely worth a bug report - documentation issues, feature requests etc are also tracked in the bug tracker. Done that - ID 0026490