SQLite record issues, maybe

Aaaarrrgh. I’m having a really bad day with this. It started out with seeing a problem adding a DatabaseRow to a SQLite database. I was building a record with the intent on using AddRow() to put the data in the database. I kept seeing a problem with the fact that the Boolean fields weren’t being populated with their value but were, instead, being left empty. So I decided to build a small test app. That’s when things got worse. Here’s the code in its entriety, all located in the Open event of the window:

[code]Var theDB As New SQLiteDatabase
Dim ff As New FolderItem("", FolderItem.PathModes.Native)
ff = ff.Parent.child(App.ExecutableFile.DisplayName + “DB.db”)
theDB.DatabaseFile = ff
If theDB.databaseFile.exists = True Then
End If

theDB.ExecuteSQL(“CREATE TABLE Preferences (pk integer NOT NULL PRIMARY KEY, FullScreen boolean, SSSS Boolean)”)

// Let’s initialize using a DatabaseRecord
Dim rec As DatabaseRow

rec = New DatabaseRow
rec.Column(“FullScreen”).BooleanValue = False
rec.Column(“SSSS”).BooleanValue = False

theDB.AddRow(“Preferences”, rec)

For some reason I’m getting an “Error 1, near “)”: syntax error” on the AddRow statement. Thinking that there might be a bogus character hidden somewhere, I copied the code and pasted it into NotePad and then deleted the code in the IDE and pasted it back in from NotePad. I cleaned the caches and am out of ideas. Why would I be getting a Syntax error on this?
BTW, this is on Windows 10, Xojo 2019r2.1

“Boolean” instead of “boolean” ?

API 2.0 turns everything into a prepared statement, and that seems to be causing problems. Try the classic API.

Seems you’ve found a framework bug connected to .BooleanValue and .AddRow()

Please file a bug report.

Bug report filed.

BTW, coding in API1 style is ok.

I’m using R2.1 MacOS only and this code works
(b_hasPDF is boolean property)
… partial code …
rec.Column(“HasPDF”).BooleanValue = b_hasPDF

App.sql_Library.AddRow “digiBooks”, rec
Catch error As DatabaseException
MessageBox(“Database Error: Not Saved”)
End Try

I also use a checkbox value
… partial code …
rs.Column(“Fav”).BooleanValue = chk_Fav(1).Value

Catch error As DatabaseException
MessageBox(“Database Error: Not Saved”)
End Try

Downloaded the test app and get the same results.
It looks like is not setting the boolean value to the column, tested with this code:

rec.Column("FullScreen").StringValue = "True" rec.Column("SSSS").BooleanValue = False Dim var1 As Variant Dim var2 As Variant var1 = rec.Column("FullScreen").BooleanValue var2 = rec.Column("SSSS").BooleanValue
I get exception with var2, reason: No row with name SSSS

As you may know, if you set an SQLite column to boolean and you put true (no case sensitive) or 1 it will be boolean True and anything else will be False. When you use Xojo to pull the information, even if you do:


from a boolean column SSSS that has a ‘3’ in it, Xojo will report False.
If we use .IntegerValue it will report 0

Shouldn’t it be:
rec.[b]BooleanColumn/b.BooleanValue = False
rec.[b]BooleanColumn/b.BooleanValue = False

as regards the used xojo version :slight_smile:

[quote=464586:@David Cox]Shouldn’t it be:
rec.[b]BooleanColumn/b.BooleanValue = False
rec.[b]BooleanColumn/b.BooleanValue = False[/quote]
No, not in Xojo 2019r2.1 coding for API2.

Thanks, I assumed the databaseRecord used the same nomenclature in API 2.