SQLite and Boolean

[AlbertoD]

Maybe so but not clear when Enric said:

@Enric_Herrera

I also have used this code for years, but now…

and

@Enric_Herrera

Should I change all code that has been working till now?

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

DatabaseColumn.BooleanValue does not return true for 1

I have been converting a web app to API2.0 and found when I use a rowset and databaserow instead of a recordset, I get False instead of True when the column value is 1. dim rs As rowset = Session.db.SelectSQL(“SELECT * FROM Test”) if rs<>nil Then for each row as DatabaseRow in rs var a As string = row.Column(“Item”).StringValue var b As Boolean = row.Column(“Item”).BooleanValue CheckBox1.Value = row.Column(“Item”).booleanValue next end if If the ‘Item’ field = 1, a will be 1 and b will…

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

[Rick_Araujo]

@AlbertoD

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

I have been converting a web app to API2.0 and found when I use a rowset and databaserow instead of a recordset, I get False instead of True when the column value is 1. dim rs As rowset = Session.db.SelectSQL(“SELECT * FROM Test”) if rs<>nil Then for each row as DatabaseRow in rs var a As string = row.Column(“Item”).StringValue var b As Boolean = row.Column(“Item”).BooleanValue CheckBox1.Value = row.Column(“Item”).booleanValue next end if If the ‘Item’ field = 1, a will be 1 and b will…

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.

[Enric_Herrera]

@AlbertoD

It is strange that you say that your code worked until now.
If you visit the reported bug in Issues, they say that the problem is present in, at least, Xojo 2022r2, 2022r3.2, 2022r4.1

The bug is with the use of databaserow (from what I can tell):

for each row as DatabaseRow in rs

so, if you change the for each row logic to use the rs directly (do…loop maybe?) then it should work.

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.

[Enric_Herrera]

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.

[Rick_Araujo]

@Enric_Herrera

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

[Enric_Herrera]

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)

[AlbertoD]

@Enric_Herrera

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

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)

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)

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

[Rick_Araujo]

@Enric_Herrera

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

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

[TimStreater]

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.

[Enric_Herrera]

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.

[Rick_Araujo]

Old bug. Lots of

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

[AlbertoD]

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

SQLite and Boolean

See #68894 - database row interprets boolean from sqlite different than direct access through row set

[Rick_Araujo]

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.

[AlbertoD]

Sorry I’m lost.

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

[Rick_Araujo]

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.

[AlbertoD]

@Rick_Araujo

I didn’t post a bug report again

I posted:

@AlbertoD

See #68894 - database row interprets boolean from sqlite different than direct access through row set

and more than 3 hours later you posted:

@Rick_Araujo

Old bug. Lots of

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

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

[Rick_Araujo]

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

@AlbertoD

if boolean was not included…

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