Using boolean value with SQLite

I have created a field in my DB as a boolean value, but now I’m finding out that SQLite doesn’t really recognize booleans. Discovered this when

myCheckBox.Value = rs.Field("recorded").booleanValue didn’t work.
So do I store True and in integer (1) and False = 0?
If so, is there a cleaner way to handle setting things like checkbox.value than

if rs.field("recorded").IntegerValue = 1 Then myCheckBox.Value = True Else myCheckBox.Value = False end if

Thanks

use .booleanvalue! You have to set the Column-type in your DB to Boolean and it should work!

and be sure, that you really store boolean in the fields!

but that’s the problem, it doesn’t.

[code]dim rs As RecordSet = gData.csbbData.SQLSelect(“SELECT * FROM Games WHERE schedID= '” + mSchedID + “'”)

if rs <> Nil and rs.RecordCount > 0 Then
rs.Edit

rs.Field("W_L_Tie").StringValue = resultsStr
rs.Field("recorded").BooleanValue = True

rs.Update

if gData.csbbData.Error then
  gData.DisplayDatabaseError()
  return
end if

// Commit Database If No Errors
gData.csbbData.Commit

end if[/code]
‘recorded’ is a field in the table Games of type Boolean. When I went to get the result of the ‘recorded’ field, it was always False. Checked elsewhere in my code where I “thought” I was using boolean values and got the same result. Finding this on the sqlite.org web site:

Led me to believe that the Boolean type in sqlite is virtually useless. That’s why I’m wondering what others do.
Thanks for the reply.

I would store 0 or 1 in an INTEGER field like the SQLite docs suggest.

But the BooleanValue properly ought to convert properly. From the SQLiteDatabase page in the Language Reference:

Ah… so if I store a 1 and then do…

rs.field("recorded").IntegerValue = 1

and then recover it with

myBool = rs.Field("recorded").booleanValue

Then myBool should = True

Yes, that is my understanding.

If booleanvalue doesn’t work for whatever reason, you can do

myCheckBox.Value = (rs.field("recorded").IntegerValue = 1)

Thanks for the suggestion, Tim, but my early tests indicate that

myBool = rs.Field("recorded").booleanValue

Is working to recover the value of an Integer field = 0 to 1

I know this is an old thread… but SQLite will recognize True and False for boolean values…

BUT… you have to do it exactly this way

SELECT * from myTable where field1='True' or field2='False'

First character uppercase, rest lowercase, any other combination does NOT work

so… True/False

not TRUE/FALSE or true/false

SQLite evaluates any boolean expressions to 0 or 1, or even NULL if mixed with the values, fact.
The results you got can’t be considered reliable because they are not documented, if they are, please share, I’m not aware.

Interesting the internal engine considers [false] expression = 0, and considers [true] any expression <> 0 (and not NULL).

A “SELECT 2=2” returns 1. A “SELECT 1<>1” returns 0. A “SELECT 1>NULL” returns NULL.

That’s the nature of C based languages (and machine code in general). Testing for zero/not-zero is a single instruction. It is much more expensive (relatively speaking) to test for equal-to-one.

:slight_smile: Well, I was affirming, not questioning. You are almost 100% correct, but not exactly. It’s not language related, it’s less expensive and it easily provides binary decisions (not off = on) allowing shortcuts for a vast set of possible fast and easy bitwise tests like:
SELECT “At least one of the 3 flags are set” WHERE 1|2|4