SQLite and Boolean

Loading a SQLite file some booleans load always as false.
The row loaded looks like:
image

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)
      
      var test as Boolean = row.Column("applied").BooleanValue
      var test2 as integer = row.Column("applied").IntegerValue

      c.applied = row.Column("applied").BooleanValue
      

test is always false, test2 is 1
Is anyone experimente something similar?

seems like a “bug” at .BooleanValue

“Boolean Datatype. SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true)”
https://www.sqlite.org/datatype3.html

originally i know true as -1 and false as 0 (MS VB 6)

I use .BooleanValue all over the place and have never noticed a problem. What is your Xojo version and what OS do you use?

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

The true/false concept is

0 = False
True = Not False

So 1, 7, -383 -1 4534534 are all true, only 0 is false

1 Like

Do you use RowSet or RecordSet?
Do you use for each row as DatabaseRow in rs?

The error more clearly in a runnable sample:


Var db As New SQLiteDatabase

call db.Connect

call db.ExecuteSQL("CREATE TABLE t1 (applied Int); INSERT INTO t1 (applied) VALUES (1);")

Var rs As RowSet

rs = db.SelectSQL("SELECT * FROM t1")

var asBoolean as Boolean = rs.Column("applied").BooleanValue  // RowSet Returns True correctly
var asInteger as Integer = rs.Column("applied").IntegerValue

break

If rs <> Nil Then
  
  For each row as DatabaseRow in rs
    
    asBoolean = row.Column("applied").BooleanValue  // Another DatabaseRow error, False???
    asInteger = row.Column("applied").IntegerValue
    
    break
    
  Next
  
End

RowSet, look at the code exemple.

that means you read -1,0,1
and what do you save?
-1,0 or 1,0

Sorry for the confusion, I’m asking @Beatrix_Willius

Enric, your code is ok, is a bug with databaserow as mentioned on the bug report and as you can see from Rick’s code.

1 Like

I also have used this code for years, but now…
MacOS Mojave
Latest Xojo version 2022 4.1

I don’t know what you are talking about

I save the values I need. The usual default for SQL is 0 = False, 1 = True. Take for example MSSQL Server, internally its boolean type is a bit field of 1 bit. So it can be Just those 2 values.

But, when evaluating expressions casting numbers to booleans, whatever non zero is True, Zero = False.

In SQLite we have just Integer fields to store it, so the rule 0 = False is what is used.

0,1

Since the conception, DatabaseRow seems a never tested feature. Never seem one feature so plagued by bugs as it is. I guess a dozen where found, and I thought it finally was stable, but I see it isn’t.

DatabaseRow .Column( ).BooleanValue // does not work

Accessing the value directly from the RowSet works.

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

var test as integer = row.Column(“applied”).IntegerValue
if test = 1 then c.applied = true

Or there is a better, easy way? I’ve a lot of boolean in diferent tables…

c.applied = row.Column("applied").IntegerValue <> 0 // could work around it

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 just mean interpreting 3 values as 2 into true and false will convert data.

-1,0,1

True, False, True

Seems he tried to use

DatabaseRow .Column( ).BooleanValue

in a new version, found the bug, and previously he used

DatabaseRow .Column( ).IntegerValue

And checked for: x = 1