SQLite record issues, maybe

Databases
1

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
theDB.Connect
Else
theDB.CreateDatabase
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)

theDB.Close
[/code]
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

2

“Boolean” instead of “boolean” ?

3

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

4

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

Please file a bug report.

5

Bug report filed.
<https://xojo.com/issue/58403>

BTW, coding in API1 style is ok.

6

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

Try
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.EditRow
rs.Column(“Fav”).BooleanValue = chk_Fav(1).Value
rs.SaveRow

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

7

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:

row.Column("SSSS").StringValue

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

8

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

9

as regards the used xojo version :slight_smile:

10

[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.

11

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