I am a newbie in terms of using the SQLite database (actually a renewbie because 8 years ago I did this sort of thing, but that is a gone memory)
Using the IDE to create an SQL database, I have have a database with two fields in a single table as best I can tell. The file: dbHelpInfo.sqlite is sitting in the same folder with the application. The IDE shows what appears to be a “alias” reference to the database in the sidebar.

Just to “test” whether things were working I have tried putting some minimal code the in the Action event of a button:


That results in an error message: — Type “Database” has no member named “LibraryVersion”. I noticed when writing this code that “LibraryVersion” did not appear as an autocomplete option.

Another attempt:


That will run but I see an empty MsgBox.

So I get the feeling that somehow I am really not interacting with this database.

Any suggestions of where I have gone astray? Should I have created the database in code rather than using the IDE?

Mac / Xojo 2017 R3

Have you tried doing a sql query over the data? Maybe the database does not have a name.

dim rs as RecordSet = dbHelpInfo.SQLSelect("SELECT * FROM TABLENAME")

Then break and look at rs.

EDIT: From database documentation

The DatabaseName is typically used with server databases (such as MySQL or PostgreSQL) to identify the specific database to use on the server.

also to answer your question do this

dim rs as RecordSet = dbHelpInfo.SQLSelect("select sqlite_version()")

rs will contain the sql lite version (3.19.3) for my version of Xojo (pointing to a an aliased db on my desktop created in Xojo IDE)

You might consider finding a freeware SQLite GUI tool for your computer to examine the database structure and content.

I typically don’t use Xojo to create and examine database structures because it is not a friendly as a GUI SQL tool. For any complex SQL I also use a query tool to get the SQL syntax correct before putting it into Xojo. Debugging is much easier.

that IS the correct syntax…
However… is dbHelpInfo a SQLiteDatabase?
just simply say

		dbHelpInfo=New SQLiteDatabase

works just fine for me

[quote=366596:@Dave S]that IS the correct syntax…
However… is dbHelpInfo a SQLiteDatabase?
just simply say

		dbHelpInfo=New SQLiteDatabase

works just fine for me[/quote]
A database that appears in the navigator is type Database. You’d have to cast it.
Also, LibraryVersion returns the version of sqlite that’s compiled into the plugin, not the version the database was created with.

You get illegal cast exceptions doing that.

You could create a new SQLiteDatabase() class, set the databaseFile property, then connect to the file.

Thanks for all the interesting comments. I think that I do not understand what exactly I am accomplishing when I use the Insert Database menu option in the IDE. At the time that you do this, you are given several options. One of these is

New SQLite Database.

I selected that. Then A file dialogue opens up to create a .sqlite (dotssqlite) file which I did. And it takes what name you give it. The default location is just next to the application which other documentation suggests is not the ideal place to actually leave the file. That sort of puzzled me too. That I wasn’t being guided to a “better” location.

Then the interface seems to have the ability to define Tables in that database and Fields in those tables. I blithely did all of that and I assumed I had basically created the structure of an SQLite database as if I had used an SQLiteGUI tool. I had no idea that I would have to [quote]cast[/quote] something after I had defined it as an SQLite database at the get-go.

But when I “tested” it, as I mentioned in my initial post, it was squirrely. Now I notice that when I go back to the database in the IDE, all the Tables and Fields that I thought I had created are not there. It just shows a “blank” and invites me to create some Tables and Fields.

So it is clear to me that I was making assumptions about the IDE database creation tool that are wrong. However sweet their siren song.

I thought that it was so simple that I could just intuitively walk though this.

I will come back to leave another post when I have read and experimented more. I think that the interface invites you to think things that are not true. At least it drew me that way. When I used databases in REALbasic (O so Long Ago) I was doing it with commands and evaluating things with third party tools and not doing anything in the IDE in terms of creating the database. Things worked fine.

I will try recover in my mind those old techniques along the lines suggested by Mark.

Thanks for all of you who took time to contribute thoughts. More later.

It is not very clear but there is a save changes button you need to click to commit new tables and fields to your database

Nothing. That has never been very useful.

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)

  1. Creates a Table in Database
  2. Creates a Field in Table
  3. 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.
  4. Deletes the selected Table
  5. Undoes last input
  6. 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


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.

it seems you hit <> here

The good thing is that apparently this will be fixed in upcoming versions of Xojo.