SQLite boolean value always FALSE in release 2020R1

Databases
1

In release 2020R1 a BooleanValue returns always FALSE. In release 2019R3.1 it return TRUE or FALSE

Steps:
make a new sqlite database, add two fields “title, checked”

BEGIN;

– CREATE TABLE “table1” ---------------------------------------
CREATE TABLE “table1”(
“titel” Text,
“checked” Boolean );

COMMIT;

Add some entries into your database.
BEGIN;

INSERT INTO “table1” (“titel”,“checked”) VALUES
( ‘My title’, 1 ),
( ‘No title’, 0 ),
( ‘Good title’, NULL );

COMMIT;

Now read the entries:
var sql as string = “select * FROM table1”
Var rowsFound As RowSet
Try
rowsFound = myDB.SelectSQL(sql)
If rowsFound <> Nil Then
For Each row As DatabaseRow In rowsFound
if DebugBuild then
system.DebugLog “row.Column(”“checked”").BooleanValue=" + row.Column(“checked”).BooleanValue.ToString
end if
Next
rowsFound.Close

Catch error As DatabaseException
MessageBox(CurrentMethodName + ": Error: " + error.Message)
End Try

2

The problem is row.
If you break your code at system.debuglog you will see that rowsFound ha the right value, but the row variabile has no value

or add this line
System.DebugLog "rowsFound.Column(""checked"").BooleanValue=" + rowsFound.Column("checked").BooleanValue.ToString

and you will see the expected values

1 Like
3

Hello,
yes, that is working now.

4

Has this been logged as a bug?

5

Hello,
sqlite boolean.value is OK

write:
Var row As New DatabaseRow
row.Column(“color”).BooleanValue =checkbox1.value

read:
checkbox1.value = mCurrentRowSet.Column(“color”).BooleanValue

6

From https://documentation.xojo.com/api/databases/database.html#database-selectsql

// db is a valid connection to a SQLite database
Var rowsFound As RowSet
Try
  rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=" + PostalCode.Value)
  For Each row As DatabaseRow In] rowsFound
    ListBox1.AddRow(row.Column("Name").StringValue)
  Next
  rowsFound.Close
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

The sample is using row.Column and not rowsFound.Column, so my question, is this a docs bug or bug with row.Column(“xxx”).BooleanValue ?

7

As far as my knowledge goes for this, DatabaseRow is only used to create and add rows.
Not for use in reading, to i guess it’s a docs bug.

https://documentation.xojo.com/api/databases/database.htmlRow

Used to create new Database rows (records). The methods are used to populate the columns in a row (record).

@Paul_Lefebvre could you verify?

8

I was surprised to see that too, but from the docs, starting with 2019r2:

Iterator

You can iterate through a RowSet using For Each and a DatabaseRow. This lets you get at the column values of the row but does not let you make any changes to the row (if you do then a DatabaseException is raised).

Guess my old-fashioned way of moving through the rowset manually prevented me from seeing what I would consider a bug according to the docs.

9

On 19r3.1 it works without problem

I updated a feedback about this problem (was noted as postgres bug)

10

You can use DatabaseRow with the RowSet iterator as shown in the docs.

11

Aha thanks, than this could be a bug why he isn’t getting it’s values…