How do I handle a non-existant database?

Databases
1

Hi Everyone,

I thought I had a handle on databases. But, apparently, I’m confused and it’s probably on something very simple. But I’m hoping someone can set me on the right path here.

In my App instance, I have a property called DB of type SQLIteDatabase and it’s access is public. In my App.Open event, I have the following code:

Dim dbFile as FolderItem
Dim DB as new SQLiteDatabase

#if TargetWin32
    dbFile = SpecialFolder.ApplicationData.Child("MyApp").Child("mydb.sql")
#elseif TargetLinux or TargetMacOS
    dbFile = SpecialFolder.UserHome.Child(".config").Child("MyApp").Child("mydb.sql")
#EndIf

if not dbFile.Exists then
    MsgBox("The database file does not exist.")
    Quit
end if

if not DB.Connect then
    MsgBox("I could not connect to the existing database")
    Quit
end if

From everything i’ve read, including sample code, this should work. But when I get to the line that checks if the dbFile is a valid file, I get a NilObjectException.

What am I doing wrong? I don’t understand why it’s erroring out when I’m only CHECKING for the existence of a file. Can anyone offer advice?

Thanks,
Dave

2

first off… you are creating a multiple level folderitem (2 children)
which is fine… IF you know that the upper level exists (which I am betting it does not)

I copied your code exactly as you presented it… and it failed exactly as you said (I obvioiusly did NOT have this path set up)

BUT… change the code to THIS… and I think it will work for you

  Dim dbFile as FolderItem
  Dim DB as new SQLiteDatabase
  
  #if TargetWin32
    dbFile = SpecialFolder.ApplicationData.Child("MyApp")
  #elseif TargetLinux or TargetMacOS
    dbFile = SpecialFolder.UserHome.Child(".config").Child("MyApp")
  #EndIf
  
  
  if not dbFile.Exists then
    MsgBox("The database root does not exist.")
    Quit
  end if
  
  dbFile = dbFile.child("mydb.sql")
  
  if not dbFile.Exists then
    MsgBox("The database file does not exist.")
    Quit
  end if
  
  if not DB.Connect then
    MsgBox("I could not connect to the existing database")
    Quit
  end if

As to the REASON… EXISTS assumes the path is legit. but the file is not there
But your initial path is NOT legit… since it is looking for a child inside a child where neither exists (yet)

I question the use of a hidden .CONFIG file for macOS… you may need another level check… but I don’t think that how you should store this

3

Thank you, Dave. Great answer. You’re right: the folder structure doesn’t exist yet. Is there a way to verify that a folder exists so that I could use CreateAsFolder() if needed?

4

As Dave says, your path setup is likely wrong.

Set a breakpoint on this line:

if not dbFile.Exists then

And then check dbFile in the debugger. You should see that it is Nil.

To debug situations like this, break your folder assignments into separate variables so you can check each one. This will allow you to verify each component so you can then create the folder if needed.

5

simply replace your QUITS with appropriate CreateAsFolder commands (with necessary exception handling of course)

6

Thank you guys. This worked fine and it’s working now. Just had to add a few lines of code to make sure the folder structure was there. Many thanks again!

7

@David Sisneros I noticed in your open event you are creating a local instance of the sqlite database

Dim db as new SQLiteDatabase

Based on your initial post I believe you want to use the App property you created so it should be

db = new SQLiteDatabase

By creating a local variable “db” you won’t be able to access it outside of the open event.