Mysql boolean

Databases
1

Mysql uses tinyint for boolean values.
In xojo I get:

'field = 0
field.booleanvalue is false

'field = 1
field.booleanvalue is false

Of course, with something like if(row.column(“field”).integervalue = 1, true, false) there is a workaround, but that is crazy.

I’m absolutely shure, I used booleanvalue with mysql many time before.
Is that a problem with newer mysql-server, newer xojo?!

Marius

2

Tiny Int seems working here… BUT “bit” is NOT.

I’ve created this test table:

SET NAMES utf8mb4;

DROP TABLE IF EXISTS `test_boolean`;

CREATE TABLE `test_boolean` (
  `f_bit` bit(1) NOT NULL DEFAULT b'0',
  `f_tiny` tinyint(1) NOT NULL DEFAULT 0
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

-- Test values

INSERT INTO `test_boolean` VALUES (b'0', 0);
INSERT INTO `test_boolean` VALUES (b'0', 1);
INSERT INTO `test_boolean` VALUES (b'1', 0);
INSERT INTO `test_boolean` VALUES (b'1', 1);


SELECT * FROM test_boolean;

image

Now run this app connecting to it:

Var db As new MySQLCommunityServer
db.Host = "db1.thedatabase.com"
db.Port = 3306
db.DatabaseName = "test_database"
db.UserName = "theUser"
db.Password = "thePassword"

Try
  db.Connect
  // Use the database
Catch error As DatabaseException
  // DB Connection error
  MessageBox(error.Message)
End Try

Var rs As RowSet = db.SelectSQL("select * from test_boolean")

Do until rs.AfterLastRow
  System.DebugLog If(rs.Column("f_bit").BooleanValue, "True ", "False ")+If(rs.Column("f_tiny").BooleanValue, "True", "False")
  rs.MoveToNextRow
Loop

break // Check messages

It results as

False False
False True
False False
False True

And should be

False False
False True
True False
True True

Clearly a bug on

rs.Column("some_bit_field").BooleanValue
3

change that part to

For Each row As DatabaseRow In rs
  
  System.DebugLog If(row.Column("f_bit").BooleanValue, "True ", "False ")+If(row.Column("f_tiny").BooleanValue, "True", "False")
  
Next

And you get:

12:48:52 : False False
False False
False False
False False

4

You’ve just found another bug in the 'dozen times+'™ DatabaseRow object.

DatabaseRow Columns data and methods differed many times from RowSet Columns data and methods, Xojo extended the surface of errors and mistakes duplicating codes and introducing bugs in one place and fixing the other and vice-versa. They implemented the reverse of “single point of failure”, kind of spreading points of failure.

So until now we have found 3 related bugs. Open an Issue report:

// Those 3 method calls are returning False all the time, even for 1 values
// DB engine used on test: MySQL (make sure using another one does not repeat the bug)

RowSet.Column("bit_field").BooleanValue

DatabaseRow.Column("bit_field").BooleanValue

DatabaseRow.Column("tiny_int_field").BooleanValue