True/False values and SQL tables

[skipper]

What is the right way to define a Boolean field in a SQLite table and how to store/retrieve such boolean value?

I tried different field type (Boolean, Integer) and different way to store the boolean value. Opening the table with an external SQL Browser (SQLiteManager) the field store the bool value as 'true' (lowercase and no quotes), but reading the valued with the Reader the returned value is everytime False.

Could you please giveme a little help?

Many thanks
Mimmo

[Erel]

True and False values in Basic4ppc are actually the strings "true" and "false" (without the quotes).

What value do you get with Reader.GetValue (or in the table if you use ExecuteTable) ?

[glook]

I am no expert with B4ppc or SQLite (still learning), but here is my experience:

I use many boolean fields in a large database, which was converted from a JET/Access database in an existing VB6 application.

Each is defined as a BIT field (example - evergreen BIT NOT NULL). When viewed with a database manager, in this case the Firefox SQLite add-on, the values are 0 for false and 1 for true.

When returned by reader.GetValue(i) and viewed in the IDE watch boxes, the values are either True or False. So Basic4ppc evaluates them correctly. The results on the PocketPC are compared with the VB6 desktop app and are all perfect.

When inserting or updating records, I use the numeric values - perhaps there is a more elegant way, but it works correctly! In my code I use a small sub to set 0 or 1 from the boolean, e.g.
evergreen = BoolNum(chkEvergreen.Checked)

HTH,
Geoff.

[skipper]

Many thansk to both for your answers.

@erel: please try the attached code: I'm storing alternatively true/false values (displaying with MsgBox, just to be sure, only true values).
Retrieving the values issuing an ExcuteTable i got only False.
The same happens with the Reader.
If you open the resulting table with any external SQL browser (eg. SQLite Database Browser from sqlite.org) you will notice that the sequence true/false is what I'm expecting to find.

@glook:
In effect, as a workaround I adopted a solution similar to your (using -1/0 as True/False and 2 subs MkSqlBool and CvtSqlBool. The first one convert a True/False boolean into a signed integer (-1/0). The second makes the opposite, reversing the signed integer read from SQL Table into a Basic4PPC bool.

The problem seems to be related to the reading routines in SQL libraries.
The data are stored in the right way (at least for what I see from the browser).

Mimmo

[glook]

Yes, confusing - but interesting!

I believe (someone correct me if I'm wrong!) that this is an effect of the type-less nature of SQLite.

In your example, you are in fact storing all the values as strings in both columns. Regardless of the column definition, SQLite will store any data type in any column. But there a notable exception - "INTEGER PRIMARY KEY".

If you add "PRIMARY KEY" to your first column, then your example works correctly - returning True or False alternately. Why this effects the boolean column I do not know , but the values are then stored as 0 or 1.

In your original code, you can test that the values are strings by adding a new button with this sub:
Sub Button3_Click
Con.Open("Data Source = " & AppPath & "\TestBool.db3")
xCommand="SELECT * FROM test_1 WHERE m_bool = 'true'"
Cmd.CommandText = xCommand
Cmd.ExecuteTable("tbl",0)
Con.Close
End Sub

The rows with the string values "true" are returned, but shown as "False", presumably because B4ppc or the SQLite wrapper it trying to interpret the string as an actual boolean - which it is not.

Your insert statement should be something like (no single apostrophes):
xCommand="INSERT INTO test_1 VALUES (" & k & "," & BoolNum(xVal) & ")"

This will ensure the values are stored as numeric and will return them correctly in the table.

Only my thoughts - Geoff.

[skipper]

Hi Geoff,
thanks for testing and trying to understand what's happening.
You are right, still confusing...One thing I'm not sure about..you say
<...that the values are (stored) as strings...>

May be you are right...but please try this:

Change in my code all the occurrence of 'true' and 'false' lowercase into 'True' and 'False' with the first letter in uppercase, then run again.
Then, if you look at what is stored in the database (open it whit notepad, does not matter) you will notice that they are stored again as 'true' and 'false' in lowercase. The question is: if they are strings, why SQLite convert them? is it beacuse I defined the field as BOOLEAN? or the engine will convert any real string "True" and "False" supposing they are boolean value?

Still confusing... you are right! SQLITE gurus...please

[glook]

In your code example, changing "true" to "True" will not make any difference because they are being use as case insensitive reserved words and not actual strings. As I understand it, B4ppc stores booleans as "true" or "false". They are treated as string values in the SQL statement because they are surrounded by single apostrophes.

There are some other threads about this, such as:
Conditional check failing for a Boolean type

So:
xVal = True - sets the value to "true"
xVal = "True" - set it to the string "True"

I don't think the weak typing design of Basic4ppc is a problem, but I guess some care is needed when also using SQLite. However, in reality I have found that it works really well - searching boolean database values is critical to my application and performance is great.

Geoff.