Boolean in SQLite database

This code

System.DebugLog("—")
System.DebugLog(row.Column(kRowIsSelected).StringValue)
System.DebugLog(row.Column(kRowIsSelected).IntegerValue.ToString)
System.DebugLog(row.Column(kRowIsSelected).BooleanValue.ToString)
System.DebugLog("—")

Return


1
1
False

I expected to get


1
1
True

Somebody have any explanation ?

What did you put in that column? I suspect it was the string: “1”, which quite possibly evaluates to False. Booleans in SQLite are stored as the integers 0 and 1.

It store as an Integer :

CREATE TABLE Third(ID INTEGER, Name TEXT, OrderValue INTEGER, isProtected INTEGER, isSelected INTEGER, PRIMARY KEY(ID))

I don’t have any problem with the reverse code when storing data

Var row As New DatabaseRow

row.Column(kRowName).StringValue = item.Name
row.Column(kRowOrderValue).IntegerValue = item.OrderValue
row.Column(kRowIsProtected).BooleanValue = item.isProtected
row.Column(kRowisSelected).BooleanValue = item.isSelected

That’s the table definition. The column isSelected could still contain a string. Unfortunately I know nothing of databaserows so I don’t know what happens underneath… I always set values in the db using INSERT or UPDATE SQL statements.

i know booleans as
-1 true
0 false

I have a rule: Never put a boolean in a database. The way the world works, sometime after the project is actually in use someone will come up with a third value.

I made my own “tostring” for a boolean, that check for “1” in a string and gives the correct result…

The standard boolean valuation is: x = 0 → false; x <> 0 → true

2 Likes

It’s looks like a Xojo bug on DatabaseColumn.BooleanValue getter. I will open a case in Feedback

1 Like

I do expect those non native boolean values being valuated as FALSE:

number = 0
Nil
NULL
string = “” (empty string, length = 0)

Anything else would be TRUE

And „false“, of course.

I’ve not cited the obvious “when the type is boolean, return itself”, because it’s obvious.

The Python rules for reference:

In the context of Boolean operations, and also when expressions are used by control flow statements, the following values are interpreted as false: False, None, numeric zero of all types, and empty strings and containers (including strings, tuples, lists, dictionaries, sets and frozensets). All other values are interpreted as true. User-defined objects can customize their truth value by providing a bool() method.

I found a similar fixed/closed case in feedback : #61636

After reading the project sample in this case I change my create table statement by :

CREATE TABLE Third(ID INTEGER, Name TEXT, OrderValue INTEGER, isProtected BOOLEAN, isSelected BOOLEAN, PRIMARY KEY(ID))

I replace INTEGER by BOOLEAN and everything works fine now.

Thanks everybody for your help :clap:

1 Like

Well, SQLite doesn’t have a boolean type anyway. Using BOOLEAN gives your column numeric rather than integer affinity. As far as I can tell from Section 3 of:

https://www.sqlite.org/datatype3.html

that shouldn’t make any difference to SQLite, but it may ake a difference to Xojo.

1 Like

yes, seems a bug in DatabaseRow with .BooleanValue in 2021r2.1
it can not convert integer with 1 or -1 …

1 Like