Boolean in SQLite database

General
1

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 ?

2

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.

3

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
4

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.

5

i know booleans as
-1 true
0 false

6

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.

7

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

8

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

2 Likes
9

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

1 Like
10

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

11

And „false“, of course.

12

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.

13

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
14

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
15

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

1 Like