SQLite Connection

Databases
1

I had a functioning connection but it was based on an old method. So when I was cleaning up some code and converting my RecordSet queries to RowSet I found a bug in my application that traced back to how I was connecting to my SQLite Database.

' Primary Variable Declaration(s)
Var dbJHGCGC As New SQLiteDatabase

' Function(s) / Logic...
// Define the location and load the variable
dbJHGCGC.DatabaseFile = New FolderItem( "DB" )'dbJHGC.sqlite" )

Try
  dbJHGCGC.Connect
  // proceed with database operations here..
Catch error As DatabaseException
  MessageBox("The database couldn't be opened. Error: " + error.Message)
  Return
End Try

This is where I am at at the moment…
The problems lies HERE → dbJHGCGC.DatabaseFile = New FolderItem( “DB” )'dbJHGC.sqlite" )

The old method used a .Child to define the path to the database. But I haven’t figured out how to do that in the new connect method.

It used to be dbJHGCGC.FolderItem( “DB” ).Child( “dbJHGC.sqlite” )

That doesn’t work and I have no idea other than moving the database out of it’s directory into the root of the application… and I don’t want to do that.

2

for myself i use SpecialFolder.Documents and there i have a folder Database and there is my file.
https://documentation.xojo.com/api/files/specialfolder.html
https://documentation.xojo.com/api/files/folderitem.html

3

Where is your SQLite database? In something like the Documents folder? Or ina folder related to the application?

4

The process of setting the databasefile hasn’t changed. Your previous code should still work. If not, something else has changed.

5 
// Variable Declarations
Var dbFldrItm As FolderItem = modDB_Conn.mthGetAppFldr( )
// Mail set up


// Code in Action!
// Instantiate a new instance
dbJHGC_Events = New SQLiteDatabase
mMutex = New Mutex( "JHGC Event Tracking" )

// Define the location and load the variable
dbFldrItm = dbFldrItm.Child( "DB" ).Child( "dbJHGC_Event" )

// Test to ensure the folder and database actually exist.
If( dbFldrItm ) <> Nil Then
  If( dbFldrItm.Exists ) Then
    dbJHGC_Events.DatabaseFile = dbFldrItm
  Else
    MsgBox "Database isn't there." + EndOfLine + Str( dbJHGC_Events.ErrorCode ) + EndOfLine + dbJHGC_Events.ErrorMessage
    Return
    Exit
  End If
  
Else
  MsgBox "The folder containing the database isn't there!" + EndOfLine + "Error is located in: App.Open"
  Return
  
End If

If Not mMutex.TryEnter Then
  MessageBox( "You can ONLY run once instance of the Event Tracking application, so WHY are you trying to start another instance?!?!?!?!?!?!" )
  mMutex = Nil
  Quit
End If

This is the old way of connecting… and it stopped working after I made a change to one of my opening queries. Which doesn’t make sense but it’s what happened. I have this code in the Super.Application area.

The new way of connecting I am not sure how to get to a child. In the root folder for the application ( I run in Linux ) I have a the source file and a sub folder called DB, in DB is where I keep the database.

Example: This is my development path:
/home/ian/Development/Joyful Heart Garden Center/JHGC Store App/DB/dbJHGC.sqlite
The root for the source file is /home/ian/Development/Joyful Heart Garden Center/JHGC Store App

So from where the source file is I have to go down one more level (DB) to get the database. I’ll keep tinkering but so far big goose egg.

6

You could try this

dim f as new FolderItem("DB")
if f = nil or not f.Exists or not f.IsFolder then
  //handle this 
  return
end if

f = f.Child("dbJHGC.sqlite")
if not f.Exists then 
  //handle this
  return 
end if

dbJHGCGC.DatabaseFile = f

Also if you are on a mac you might be translocated and the root folder is not where you think it is

7

Time this is the error I get when opening the application using the old connect method:

Error: Operation cannot be completed because the database is closed.

Then I get a Nil Object Exception.

The code below is what I changed from the old style RecordSet to RowSet

Var rsEmployee As RowSet

' Function(s) / Logic...
Try
  If( strEmpId <> "" ) Then
    rsEmployee = dbJHGCGC.SelectSQL( "SELECT * FROM tblStaff WHERE employeeId=?", strEmpId )
    'WHERE barCode=?", strBarCode )
  Else
    rsEmployee = dbJHGCGC.SelectSQL( "SELECT * FROM tblStaff" ) **<-- This is the line that errors**
  End If
  
  If( rsEmployee<> Nil ) Then // So long as there is a record return it then close the rowset...
    Return rsEmployee
    rsEmployee.Close
  Else
    MessageBox( "What?!?!? No records?!" )
  End If
  
Catch error As DatabaseException
  MessageBox( "Error: " + error.Message )
End Try
8

I’m a Linux guy and thank you I’ll try that!

9

You are defining your database here, I assume this is all in the same function, or do you have a global database which would never be inited

10

Try:

Var f As FolderItem = new FolderItem ("")   // That's an empty string

f = f.child("DB").Child("dbJHGC_Event")
11

This is similar you Graham_Busch and it resolved the db.connect issue but now I am getting a Nil Object Exception when I try to run any select queries…

12

Okay I am completely stumped… up until the couple of changes made everything worked fine. I even reverted back to the original set up and still not able to connect to the database… keep getting Nil Object Exception… I have no idea what I hosed up.

13

I don’t know if this applies, but as Graham indicated, if you have a global variable named dbJHGCGC and you then var dbJHGCGC as New SQLiteDatabase, the local version will be used to open the database and it will leave the global version Nil. When you try to access it in other parts of the program, you will get an exception.

The fix is to change

Var dbJHGCGC As New SQLiteDatabase

to

dbJHGCGC = New SQLiteDatabase
14

So show us what your code is now and where you get the Nil.

15

K, I’ll try that Tim.

16

So I put the “old” method back in place for now and will revisit this later. It’s working again so I don’t want to mess with it anymore until I have more time to deal with it.

Thanks again fellows!