SQLite and Boolean

Maybe so but not clear when Enric said:

and

BTW it looks like the bug was fixed before, I found this thread:

and this fixed bug:
#61636 - boolean returns always false in postgresql (closed)

That was in a RowSet (proven working in my test). DatabaseRow seems inherited a previous bugged version.

I’m using

for each row as DatabaseRow in rs

just in the new project, olders using

While Not rs.AfterLastRow

Maybe go back to While not will work.

1 Like

In the same project, other tables (same kind of code) loads the booleans correctly

Var rows As RowSet
Try
  rows = db.SelectSQL(sql)
  If rows <> Nil Then
    Var u as UnitClass
    mUnits.RemoveAll
    
    for each row as DatabaseRow in rows
      u = New UnitClass
      
      u.Advance = rows.Column("Advance").BooleanValue
      u.Attack = rows.Column("Attack").IntegerValue
      u.AtSea = rows.Column("AtSea").BooleanValue
      u.AtSeaTurn = rows.Column("AtSeaTurn").IntegerValue
      u.AV = rows.Column("AV").BooleanValue

I’ve no understanding why it works in some tables and not in others.

It’s a mix of bugs here

You are iterating rows using a row of a DataBaseRow but reading the RowSet

oopps there is a diference

The tables where it works I used

for each row as DatabaseRow in rows
  u = New UnitClass
  
  u.Advance = rows.Column("Advance").BooleanValue
  u.Attack = rows.Column("Attack").IntegerValue
  u.AtSea = rows.Column("AtSea").BooleanValue
  u.AtSeaTurn = rows.Column("AtSeaTurn").IntegerValue
  u.AV = rows.Column("AV").BooleanValue

not, for exemple

u.Advance = row.Column("Advance").BooleanValue

I was looping for each row, but asking the rowset.Column each time (maybe it was an error, but it has worked accidentaly)

In some tables you have the column defined as boolean and not in other tables (maybe?).

This code will produce False for DatabaseRow:

Var db As New SQLiteDatabase

db.Connect

db.ExecuteSQL("CREATE TABLE test (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, bvalue);")
db.ExecuteSQL("INSERT INTO test (bvalue) VALUES (False);")
db.ExecuteSQL("INSERT INTO test (bvalue) VALUES (True);")
db.ExecuteSQL("INSERT INTO test (bvalue) VALUES (0);")
db.ExecuteSQL("INSERT INTO test (bvalue) VALUES (1);")

Var Rows As RowSet = db.SelectSQL("SELECT * FROM test;")
var cnt as integer
For Each Row As DatabaseRow In Rows
  if row.Column("bvalue").BooleanValue = True then
    cnt = cnt + 1
  end if
  System.DebugLog((Row.Column("id").IntegerValue.ToString _
  + ", DatabaseRow Value - " +  Row.Column("bvalue").BooleanValue.ToString + ": RowSet Value - " +  Rows.Column("bvalue").BooleanValue.ToString))
Next
system.debuglog("CNT: "+ cnt.ToString)

image

This code will produce correct results for databserow:

Var db As New SQLiteDatabase

db.Connect

db.ExecuteSQL("CREATE TABLE test (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, bvalue boolean);")
db.ExecuteSQL("INSERT INTO test (bvalue) VALUES (False);")
db.ExecuteSQL("INSERT INTO test (bvalue) VALUES (True);")
db.ExecuteSQL("INSERT INTO test (bvalue) VALUES (0);")
db.ExecuteSQL("INSERT INTO test (bvalue) VALUES (1);")

Var Rows As RowSet = db.SelectSQL("SELECT * FROM test;")
var cnt as integer
For Each Row As DatabaseRow In Rows
  if row.Column("bvalue").BooleanValue = True then
    cnt = cnt + 1
  end if
  System.DebugLog((Row.Column("id").IntegerValue.ToString _
  + ", DatabaseRow Value - " +  Row.Column("bvalue").BooleanValue.ToString + ": RowSet Value - " +  Rows.Column("bvalue").BooleanValue.ToString))
Next
system.debuglog("CNT: "+ cnt.ToString)

image

The only difference bvalue boolean when created the table the second sample.

Edit: maybe that is why some people never find this bug, all their Booleans are created as Boolean

Seems like Xojo walks the rowset in sync, that’s an advantageous side effect in this case and could make
DatabaseRow dispensable.

I’ve never used DatabaseRow, nor do I use .BooleanValue. I can do everything just using plain old SQL, so there is no need of the former. And since SQLite has no Boolean storage class, I avoid the latter and just use 0, 1.

Finally I modified the code to:
Notice that not using row.column(“…, I’m using rs.column(”…

Var sql As String
sql = "SELECT * from Combats ;" 

Var rs As RowSet
Try
  rs = db.SelectSQL(sql)
  If rs <> Nil Then
    Var c as CombatClass
    mCombats.RemoveAll
    
    for each row as DatabaseRow in rs
      
      c = New CombatClass(false)
      
      c.applied = rs.column("applied").BooleanValue
      
      

And now the booleans are loaded ok. It seems the bug persists but this workaround is the easy to implement.

Old bug. Lots of :+1:t2:

https://tracker.xojo.com/xojoinc/xojo/-/issues/68894

I guess you missed my reply a few hours ago (third reply).

Nope. I’ve read it. But I think it does not add to the case. Your reply did not touched the bug here. It’s a known bug in the DatabaseRow.Column.BooleanValue() conversion of numeric types.

Sorry I’m lost.

If it does not add to the case then why you posted the bug report again?

I didn’t post a bug report again. I added info to a known forgotten bug.

The bug exists, all info is there, including what’s necessary to fix.

The affinity type of “boolean” is Integer, True renders as 1, False as 0;
Same if using type “integer”.

There’s a known bug there not respecting this. Same bug was fixed in the past for rowset as pointed out, fixing it, it will fix all the cases as it was fixed for rowset.

I posted:

and more than 3 hours later you posted:

That’s the same bug report.


And for the other bug report that is related to this one, too bad the sample code there used ‘bvalue boolean’, if boolean was not included the result would be:

1, DatabaseRow Value - False: RowSet Value - False
2, DatabaseRow Value - False: RowSet Value - True
3, DatabaseRow Value - False: RowSet Value - False
4, DatabaseRow Value - False: RowSet Value - True

1 Like

That’s not what I call “post a bug report”. Norman posted the bug report 7 month ago.

But his example wasn’t clear about what was going on, just “there’s something wrong here”.

The best pointer to an issue report is there too https://tracker.xojo.com/xojoinc/xojo/-/issues/61636

They know where they made a poopoo, they fixed the same bug in issue 61636.