SQLite boolean value always false

Gabriele_Marchionni · July 30, 2018, 1:47pm

When I use this code the booblean field in the recordset is always false. But in the table all boolean value is set to true.

Dim rsTmp As RecordSet
strSql = "select * form MYTABLE"
rsTmp = DB.SQLSelect(strSql)
dim bVal as Boolean
bVal  = rsTmp.Field("MYBOOLFIELD").BooleanValue

Why?

DaveS · July 30, 2018, 1:57pm

be aware that SQLite (assuming this is what you are using) does not have a BOOLEAN datatype, and the BooleanValue function for the Xojo Recordset is “derived”…

Try replacing you “.BooleanValue” with “.StringValue” and see what it produces (including any leading or trailing spaces)

Jay_Madren · July 30, 2018, 2:05pm

I assume you typed the above code instead of pasting it from your project, as you misspelled “from” as “form” in the sql query.

In either case, you should always check for errors on database operations. Right after the SQLSelect you should do something like

If DB.Error Then MsgBox "Database error: " + DB.ErrorMessage Return End If
This will help keep you from chasing your tail when trying to track down a problem.

AlbertoD · July 30, 2018, 3:16pm

I did several tests with in-memory SQLiteDatabase, I get the correct boolean value (True) as long as I have True or 1 on MYBOOLFIELD, even saving tRuE into the database.

As Dave said, be careful with SQLite. I did some tests, if the in-memory database definition for MYBOOLFIELD is Boolean, if I send 1 or tRuE I always get back ‘true’ stringvalue. If I change MYBOOLFIELD to Text, then I get the stringvalue I sent to it (tRuE or 1), anyway using Boolean or Text for MYBOOLFIELD and the value there being 1, True, true or tRuE using

boleano=rsTmp.Field("MYBOOLFIELD").BooleanValue

I always get the correct value (True). If I have something else, I get False.

The good thing about setting the SQLite database with that field as Boolean, is that if I send ’ true’ (with one extra space), then that is converted to boolean false, in other words, I get a BooleanValue of False back. If I set the field to Text, I get a BooleanValue of False but writing that information to a listbox (for example) I see true (no easy way to spot the extra space).

This is not information for people with experience with SQLite because you already know this, I just write this for me and others that are starting with SQLite and Xojo.

A few commands I used:

db.SQLExecute("CREATE TABLE MYTABLE (ID Integer PRIMARY KEY, MYBOOLFIELD Boolean)") db.SQLExecute("CREATE TABLE MYTABLE (ID Integer PRIMARY KEY, MYBOOLFIELD TEXT)") db.SQLExecute("INSERT INTO MYTABLE (MYBOOLFIELD) VALUES ('1')") db.SQLExecute("INSERT INTO MYTABLE (MYBOOLFIELD) VALUES (' true')") listbox1.AddRow(rsTmp.IdxField(1).StringValue,rsTmp.IdxField(2).StringValue) boleano=rsTmp.Field("MYBOOLFIELD").BooleanValue

Gabriele_Marchionni · July 30, 2018, 3:36pm

with this code:

    Dim bolVal As Boolean = rsTmp.Field("AN02_FLGADMIN").BooleanValue
    Dim intVal As Integer = rsTmp.Field("AN02_FLGADMIN").IntegerValue
    Dim strVal As String = rsTmp.Field("AN02_FLGADMIN").StringValue

In Debug all value is false:

The Recordset value is false:

But in the table is true:

This did not happen with the 2017 version of Xojo. It does not work anymore since I use the latest version 2018 r1.1

Gabriele_Marchionni · July 30, 2018, 3:40pm

this is just a example…

there are no errors, I check, and until yesterday it worked.

DaveS · July 30, 2018, 3:40pm

So if you run the exact same test in 2017 it returns the correct values, but if you run it in 2018 it does not?

AlbertoD · July 30, 2018, 4:47pm

[quote=398503:@Gabriele Marchionni]with this code:

    Dim bolVal As Boolean = rsTmp.Field("AN02_FLGADMIN").BooleanValue
    Dim intVal As Integer = rsTmp.Field("AN02_FLGADMIN").IntegerValue
    Dim strVal As String = rsTmp.Field("AN02_FLGADMIN").StringValue

In Debug all value is false:

The Recordset value is false:

But in the table is true:

This did not happen with the 2017 version of Xojo. It does not work anymore since I use the latest version 2018 r1.1[/quote]
What are you using to see the Table information (last image)?
Your Recordset is showing a record with AN02_DATAULTACC of 2018-07-30 but the image from the table show 09/09/2017 (guess is saved as SQLDateTime).

Can you check the same record?

Bogdan_Pavlovic · July 30, 2018, 4:57pm

Your sql query is say give me all records.

Try with with sql query which will select only rows which has TRUE (value = 1 or value = ‘true’ for AN02_FLGADMIN and see what you will get.

Example

SELECT * FROM an02 WHERE AN02_FLGADMIN = 1

OR

SELECT * FROM an02 WHERE AN02_FLGADMIN = 1 LIMIT 1

Notice that SQLite does not provide Boolean data type therefore 1 means TRUE, and 0 means FALSE.

Gabriele_Marchionni · July 30, 2018, 7:38pm

Sorry for last image. The date is updated after reading, not before.
I only have 4 recordsets, and I filter in for AN02_USERNAME = ‘SUPER’
With this app (below) the Boolean values are visible with a Y and N, while in the one on windows you could see a flag selected or not selected.

I tried to delete all records with the value = false and leave only those with value = true. But the result is the same, the value read by Xojo is always = false, which reads Boolean or String, or 0 if I read it as an Integer.

DaveS · July 30, 2018, 7:44pm

what is the result of this query

SELECT an02_flgadmin,count(*) FROM yourTablename GROUP BY an02flgadmin

Y and N are not valid “boolean” values, 0, 1, true, false

DaveS · July 30, 2018, 7:45pm

at the risk of repeating myself…

Gabriele_Marchionni · July 30, 2018, 8:15pm

I know that Y/N is not valid boolean values, it’s just how “SQLPro for SQLite” app show it.

Gabriele_Marchionni · July 30, 2018, 8:20pm

I’m trying on last version of Xojo 2017 r1.1 and it does not work any more. But I do not understand, the last compilation made last year with Xojo 2017 r1.1 and published on the Apple Store everything worked.

Why does not it work anymore?

Tim_Parnell · July 30, 2018, 8:30pm

Well what are the actual raw values? Those are what Xojo needs to have in the correct format, and as Dave said that can be True or 1 and False or 0.

Try using SQLiteManager, which written in Xojo, will tell us the values Xojo is seeing.

Edit: and if all else fails, share an example project with us

AlbertoD · July 30, 2018, 8:38pm

I downloaded the 7 day trial of SQLPro for SQLite, and I think it let you put whatever you want. So for some reason all True values were changed to Y and all false values were changed to N, and both are boolean false.

Edit: or the program always used Y/N instead of boolean

Tim_Parnell · July 30, 2018, 8:42pm

Thanks for going into detective mode, Alberto!

DaveS · July 30, 2018, 8:52pm

It will… SQLite does not have a Boolean datatype, as a matter of fact it has NO Datatypes… (it has what are called Affinity)
so it is very loose about what it accepts (as in any field can contain almost anything)

AlbertoD · July 30, 2018, 8:54pm

I still don’t understand why strVal = false, so I did a test.

If I create the in-memory database and MYBOOLFIELD is TEXT and I send a value of Y, then I get strVal = Y
If I create MYBOOLFIELD as Boolean and send value of Y, then I get strVal = false.

I guess that SQLite even if they don’t have a strict Boolean datatype, there are some differences creating the tables with Text and Boolean columns.

AlbertoD · July 30, 2018, 8:57pm

[quote=398580:@Dave S]It will… SQLite does not have a Boolean datatype, as a matter of fact it has NO Datatypes… (it has what are called Affinity)
so it is very loose about what it accepts (as in any field can contain almost anything)[/quote]
Thank you Dave, I was talking about SQLPro for SQLite. Because Gabriele said that this program show Y/N if the values are True/False on a boolean field. And that will be strange because SQLite let you put almost anything in any field as you said.