I am leaving this summary for anyone who stumbles into this conversation. My problem started from false assumptions I made while using a tool in the IDE:
Insert>>Database>>New SQLite Database
This menu choice does a couple things. Initially you create a somename.sqlite file which defaults to a location adjacent to your app. An icon referencing this database shows up in the Navigator. (I named mine dbHelpInfo.sqlite)
An editing window then appears that allows you to specify the tables and fields of your new SQLite database. I looked but can find no documentation that explains this tool. There are six buttons that appear across the top. There are no tool tips so their purpose might not be clear. From playing around, this is my conclusion. (from left to right)
- Creates a Table in Database
- Creates a Field in Table
- Saves the changes you have made (o/w they disappear). Unawareness of this function bedeviled me. Currently “unsaved” things are Bold. When saved they go to Plain.
- Deletes the selected Table
- Undoes last input
- Brings up a window to Execute an SQL statement. (I have no idea how this works or when you would want to use it)
The icon in the Navigator is useful in the sense you can return to the database editing to add fields or tables. Incidentally, I could not create a functional link to another SQLite database file, created with code, by dragging that file icon into the Navigator.
This whole area is rather buggy and I suffered several crashes while playing around.
The specific problem that I had when I started this thread was that
MsgBox(dbHelpInfo.LibraryVersion)
resulted in an error message. I had created an SQLite data file using the tool and it was called dbHelpInfo which was the name of the icon that referenced it in the Navigator. I merrily assumed that I was connected magically to the database when I wrote the line of code. But no, you have to provide the code to do this
Dim dbFile As FolderItem
dbFile = GetOpenFolderItem("") // Choose the dbHelpInfo file.
If dbFile <> Nil Then
Dim db As New SQLiteDatabase
db.DatabaseFile = dbFile
MsgBox(db.LibraryVersion)
End if
Then it works just fine.
So what does fooling around with the editing tool accomplish? Basically it saves you from writing and executing the equivalent code. (Here taken from Xojo Documentation.)
Dim dbFile As New FolderItem("dbHelpInfo.sqlite")
Dim db As SQLiteDatabase
db = New SQLiteDatabase
db.DatabaseFile = dbFile
If db.CreateDatabaseFile Then
Dim sql As String
sql = "CREATE TABLE Team (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));"
db.SQLExecute(sql)
Else
MsgBox("Database error: " + db.ErrorMessage)
End If
So, IMO, it does not accomplish very much. I did find a Paul Lefebvre Webinar where he uses this tool. He describes it as “Not the world’s best database editor”. Immediately after creating the database example he is using, he dumps the reference icon out of the Navigator. So I think he accurately displays a sort of deprecatory attitude to the thing.
My personal recommendation is not to use it. I just found it a source of confusion. Now that I know more about it, I do not see that it does very much. And it is buggy (Save often if you want to use it)
One peculiarity that I found is that when I would return to my database Schema all the fields that had been specified as Integer now read Boolean. That is not actually a field type in SQLite. I presume that it does not matter (SQLite does not care much about field types) but it is an example of the sort of confusion this tool can engender.