SQLite Boolean rowset issue

Just loaded up my main project in 2020 R1. Loaded, launched, compiled, runs no issues. So far so good.

However on launch some watch points are hit in my startup methods that load data from the database , ones that shouldn’t be triggered. Looking at the database table all looks good. Debugging the values in the rowset however there is no value for the BOOLEAN fields!! This is one of the few methods that I’ve updated to using rowset. Load back up in 2019 and everything is fine.

logically it appears that rowset isn’t reading boolean values in SQLite…but how can that be the case. I have trouble believing that that bug got through. Can anyone else reproduce? Running Win 10 64 build.

Um, no. It works fine for me. I have a test app that creates a SQLite DB with three Booleans, setting one to True, one to False, and leaving the third as nil. All three are reading back properly and showing in the debugger.

What values do you have in your columns in the DB ?
I wonder if what the plugin interprets as true & false has somehow changed ?

EDIT : That said this code in 2019r1.1 and 2020r1 gives me the same results


Dim db As New SQLiteDatabase

If db.Connect Then
  
  // create a table
  
  db.SQLExecute("create table foo( boolColumn)")
  
  db.SQLExecute("insert into foo( boolColumn ) values(1)")
  db.SQLExecute("insert into foo( boolColumn ) values(0)")
  db.SQLExecute("insert into foo( boolColumn ) values(100)")
  db.SQLExecute("insert into foo( boolColumn ) values('true')")
  db.SQLExecute("insert into foo( boolColumn ) values('false')")
  db.SQLExecute("insert into foo( boolColumn ) values('TrUe')")
  db.SQLExecute("insert into foo( boolColumn ) values('fAlSe')")
  
  Dim rs As recordset = db.SQLSelect("select rowid, boolcolumn from foo order by rowid")
  
  While rs<> Nil And rs.eof <> True
    
    Dim tf As Boolean
    Dim id As Integer 
    
    Id = rs.field("rowid").IntegerValue
    tf = rs.field("boolColumn").BooleanValue
    
    System.debuglog "got " + Str(id) + " = [" + Str(tf) + "]"
    
    rs.MoveNext
    
  Wend
  
End 

EDIT 2 : altering the code to


Dim rows As rowset = db.SelectSQL("select rowid, boolcolumn from foo order by rowid")

While rows <> Nil And rows.AfterLastRow <> True
  
  Dim tf As Boolean
  Dim id As Integer 
  
  Id = rows.Column("rowid").IntegerValue
  tf = rows.Column("boolColumn").BooleanValue
  
  System.debuglog "got " + Str(id) + " = [" + Str(tf) + "]"
  
  rows.MoveToNextRow
  
Wend

sill gives the same result so … :man_shrugging:

I wonder if the issue could be with the databaserow … I’ll have to check that out later. Might also see if changing the representation from 1/0 to “true”/“false” makes any difference.

see my test code above - I used & tried both and they seemed to all behave as I expected

I can’t explain it either except perhaps the use of the iterator might make a difference?

 Var rows As RowSet = table.GetRowSetAll

    For Each row As DatabaseRow In rows
    //Debugging the contents of the current rows object shows a boolean value of true
    //the row object is empty
    next

Here’s the contents of the rowset
image

Here’s the view of the databaserow
image

Not the first oddity I’ve seen reported with the API 2 database classes
Report it if you can figure out a simple test and rollback to the older stuff if it still works for you and move on ?

Unfortunately I just migrated our ORM to use rowsets so changing the code back isn’t going to work. I think we’ll just have to stay at 2019.3 for now. This should be a pretty easy one to replicate but have to put together a test project as the production project is too big to submit for a bug report.

I’ve become a big believer in unit tests the last few years. Seems like a lot of the regression issues would be caught by better testing coverage?

You’d think