Accessing a local SQLite database

I’m having problems connecting to an SQLite database in a local folder (MacOs). I’ve gone through a number of tutorials, but I can’t seem to find the right way to do it. Thanks for the help.


Please post the code you’ve tried and we’ll be happy to help.

You say you can’t connect to the database. Do you get any error message or is an exception raised ? If so please specify the message or exception.

Also please state which Xojo version and OS.

Hello, thank you for the replies.

In the Chapter 12 binary of the AddressBook demo, there’s this line that points to the data base. I’m trying to use this as a template for my project. How do I change this line to point to a different SQLite database. Thanks much. (Monterey, 12.3.1; Xojo 2021 R. 3.1)

MyDBFile = SpecialFolder.ApplicationData.Child(“Ch12”)

That depends on if the different database is in a different path, has a different name but same path, or both are wrong.

Typically a file with have an extension – so you may just be trying Ch12 instead of Ch12.db or Ch12.sql or whatever. The final .Child() here should have the full name, including extension.

What leads up to the final .Child() is the path on where to find the database. In the example, that is SpecialFolder.ApplicationData, which gives you a standardized way to get to where an OS suggests you keep data used by application. See SpecialFolder docs on how that is interpreted on various OS.

If you can clarify for us what the file is called – including extension – and where it is located on your drive we can give you a more complete answer.


Here is the code I use

Var DbFolderItem As FolderItem = // put your file location here

// Initialize Database
db = New SQLiteDatabase
db.DatabaseFile = DbFolderItem

// Connect Database
If DbFolderItem.Exists Then
  // Connect to Database
  Catch error As IOException
    System.debugLog (error.Message)
  End Try
  // The database file doesn't exists
  // You need to create Database (file)
    // kCreateDatabase is a constant with SQL  Create Table statements
    // and other SQL query to initialize database structure and content
  Catch error As IOException
    System.debugLog (error.Message)
  End Try
End If

Look to the FolderItem documentation to initialize your file path.

I tried Sebastian’s suggestion. I’m getting an error right where I put the file location, thus:

Var DbFolderItem As FolderItem = “Users/rmc/work/2022/wika/srce/dbase/bisoogo.sqlite”

The error message was

Type mismatch error. Expected class FolderItem, but got Text.Literal.

you should use this :

Var DbFolderItem As FolderItem = new FolderItem( “Users/rmc/work/2022/wika/srce/dbase/bisoogo.sqlite”)

There is a / missing before Users.
And on the end you should post a type for the path, e.g. FolderItem.PathTypeNative


Using the above will work( (probably) at build time (debug time).

But in real use, try to avoid that and build your FolderItem using successive .Child and test each step / report error in case the user move the data base file or change the path.

Else, you will get an error and have to check each and every parts of the data base file path.

This points to the Application Data folder, a file called Ch12, apparently.

Of course, you placed your file elsewhere and using a different name.

If the example use SpecialFolder.ApplicationData it is because it is a correct location to store your data file.

In fact, you have to create there a folder using your “Bundle Identifier” in reverse order.
The Buindle Identifier for this example is:

So, the example is meant to create a folder called:

then place its data inside that folder.

This have to be done in App.Open(ing), stored in a globar variable and used for each and every File I/O operation (Preferences, etc.) unless the user need specifically to export (or save for external use) data.

Of course, I may be wrong somewhere, it’s Sunday !

Went back to fully specified path, and db connect works — yes, that initial forward slash was there I swear! 

MyDBFile = SpecialFolder.ApplicationData.Child("/Users/rmc/work/2022/wika/srce/dbase/bisoogo.sqlite")

If MyDBFile.Exists Then
  MyDatabase = New SQLiteDatabase
  MyDatabase.DatabaseFile = MyDBFile
    //MessageBox("Connected to database.")
    Return True
  Catch error As DatabaseException
    MessageBox("Error connecting to database.")
  End Try
  Return CreateDatabase
End If

Now I’m having problem in the Populate method, on the line:

 rs = MyDatabase.SelectSQL(sql). Please see below. The sql statement is 
SELECT meta_id, filename FROM meta ORDER BY meta_id;

which works in SQL Pro Studio. 
Thanks for the help. 

Private Sub Populate()
  Var sql As String
  Var rs As RowSet
  sql = "SELECT meta_id, filename "
  sql = sql + "FROM meta "
  //If SearchField.Text <> "" Then
  //sql = sql + "WHERE LOWER(meta_id) LIKE LOWER('%" + SQLify(SearchField.Text) + "%') "
  //sql = sql + "OR LOWER(filename) LIKE LOWER('%" + SQLify(SearchField.Text) + "%') "
  //End If
  sql = sql + "ORDER BY meta_id;"
    rs = MyDatabase.SelectSQL(sql)
    While Not rs.AfterLastRow
      MetaBox.CellTextAt(MetaBox.LastAddedRowIndex, 1)= rs.Column("filename").StringValue
  Catch error As DatabaseException
  End Try
End Sub

As Emile says, don’t use paths like this, “/Users/rmc/work/2022/wika/srce/dbase/bisoogo.sqlite”

Use Child( “Users” ).Child( “rmc” ) etc. If there is any chance of one of the folders not existing do it step by step.

Suggestion is good and it’s the way to go. I don’t know (yet) how to format the path that way, where to put it, how to refer to it. Thanks much.

That is why a documentation exists.

You will get plenty information about FolderItem at this link.

Look at the bottom for See also who hold links to Read/Write Text files, access (Read/Write) to Special Folders, etc.

Yes, it is not easy (and slow) at the beginning, but will improve with time “like good wine”.:grinning:

1 Like

Emile, if that were true, then guys like you and me that are older than dirt would be the two finest prize bottles of wine in the cellar … a description that my wife says she just won’t buy into! :rofl: :joy: :rofl:

1 Like

Yes !

The problem is that… I do not drink wine (I do not like that). ;-:slight_smile:

PS: I use REALbasic since 1988… (version 1a26…)

This is what you should be doing to get started:

Var DbFolderItem As FolderItem = new FolderItem (“/Users/rmc/work/2022/wika/srce/dbase/bisoogo.sqlite”, FolderItem.PathModes.Native)

Other have suggested you should use Child and so on, and while this is probably correct, it’s nicer to get something working. You can always improve it later.

Exactly my thoughts, Tim. Just get something going for now. Thanks.

Are you getting an error message? An empty result set? An exception? Put a breakpoint on that line and examine the contents of sql. Make sure it isn’t malformed (although the code looks ok).

1 Like