Why Do I Keep Getting a NilObjectException?

Hey All,

So I’ve been working on a function to check for a database and if necessary, programmatically create it. After reading some documentation I realized I wasn’t checking for the database file the right way, I started again this time checking for the db file before doing anything else. The if statement below correctly identifies the presence or absence of a database file, however when I go to set the databasefile value of my sessions database property (Session.DB.databasefile) I get a NilObjectException regardless of what I do to set the property’s value. This makes no sense because AFAIK I have correctly searched for the database file so obviously the object exists but when I go to specify it it’s not there.

I believe this code should work because SQLiteDatabase.DatabaseFile expects a FolderItem type which, as you can see, DatabaseFile is. Further, in accordance with the GetFolderItem documentation, I’m checking the exists property before trying to use the folder item. If the FolderItem Object wasn’t there like the NilObjectException suggests, then my check would have failed and I would have gone to the “else” section of code, but alas, I’m not.

any thoughts?

Other considerations:

  1. Per this post - I’ve made sure that CheckDB function is not an event of Session.Open, but rather the session itself, so the DB object should persist and work correctly for as long as the session runs.
  2. The scope of my DB session property is Public so the database should be available to every page of the website.
dim DatabasePath as FolderItem = App.ExecutableFile.Parent.Parent.Child("liffboi.db")
Dim DatabaseFile as FolderItem = GetFolderItem(DatabasePath.AbsolutePath)

if DatabaseFile.Exists = True Then

Session.DB.DatabaseFile = DatabaseFile
MsgBox("Database is found!")

else
MsgBox("Database not found!")
End If

You can delete the second line of code.

You can change the if/else to

if DatabaseFile.exists then 'do something else 'do something else end if

[quote=431514:@Beatrix Willius]You can delete the second line of code.

You can change the if/else to

if DatabaseFile.exists then 'do something else 'do something else end if[/quote]

Done, Updated code in project and post.

Thank you!

[quote]You can delete the second line of code.

You can change the if/else to

if DatabaseFile.exists then
'do something
else
'do something else
end if[/quote]

If the second line is deleted, then DatabaseFile is not defined.
If the second line is deleted, then the following code would be

[code]if DatabasePath.Exists = True Then

Session.DB.DatabaseFile = DatabasePath
MsgBox(“Database is found!”)

else
MsgBox(“Database not found!”)
End If[/code]

And the reason for the nilobject is likely to be due to the difference in relative paths between debugging and compiled versions of the app.

You should put the db into Specialfolder.applicationdata.child(“MyApp”) folder
and refer to it from there.
Right now, you assume it is ‘local’ to the executable, and that’s not always going to be the case using the path you have defined here.

Also, OSX and Windows both frown upon updating files in the Applications and Program Files folder … going so far as to enforce these files being read-only.
That too is avoided by having the database in the ApplicationData area.

If you ship a file embedded, it will start life in the Resources folder.
Xojo now has a SpecialFolder.Resources , but if you are using an older version of Xojo, you may like to make use of the equivalent function I did for use in my apps:

[code]public Function SpecialFolderResources() as folderitem
dim f as folderitem
#if targetMacOS
f =app.ExecutableFile.Parent.Parent.Child(“Resources”)
#else
#if debugBuild
f =app.executableFile.parent.child(“DebugMyApp Resources”)
#else
f =app.ExecutableFile.Parent.Child(“MyApp Resources”)
#endif

#endif
if f = nil or f.exists = false then
f = GetFolderItem("")
end if
return f[/code]

[quote=431518:@Jeff Tullin]And the reason for the nilobject is likely to be due to the difference in relative paths between debugging and compiled versions of the app.

You should put the db into Specialfolder.applicationdata.child(“MyApp”) folder
and refer to it from there.
Right now, you assume it is ‘local’ to the executable, and that’s not always going to be the case using the path you have defined here.

Also, OSX and Windows both frown upon updating files in the Applications and Program Files folder … going so far as to enforce these files being read-only.
That too is avoided by having the database in the ApplicationData area.

If you ship a file embedded, it will start life in the Resources folder.
Xojo now has a SpecialFolder.Resources , but if you are using an older version of Xojo, you may like to make use of the equivalent function I did for use in my apps:[/quote]

So I’ve updated my code to put my database in the resources folder, and I’ve verified that works however I’m still having the same issue when it comes time to set my session’s db.databaseFIle property

dim DatabaseFile as FolderItem = SpecialFolder.Resources.Child("liffboi.db")


//If that file exists...
if DatabaseFile.Exists = True Then

//
// Set Session Property for Database Here
//
Session.DB.DatabaseFile = DatabaseFile
Session.dbIsConnected = True

MsgBox("Database is found!")

//Otherwise...
elseif DatabaseFile.Exists = False Then

Session.dbIsConnected = False

//Create a new file for the database
Dim NewDatabaseFile as FolderItem
NewDatabaseFile = New FolderItem(DatabaseFile)

// Create a new local variable of type SQLiteDatabase and set it's name to db
Dim db As New SQLiteDatabase 

// Set the file Attribute to the location we checked earlier, IE Make the file where we're looking for it.
db.DatabaseFile = NewDatabaseFile

// If we're creating that database file, we are, Then do the following...
If db.CreateDatabaseFile Then

// Start a new Transaction to set up the tables the application will need
db.SQLExecute("BEGIN TRANSACTION")

// Craft Strings for the SQL Queries we will need to run in order to make the necessary Tables...
dim UserTableQuery as String = "CREATE TABLE 'UserTable' ('userID' INTEGER UNIQUE, 'userUserName' TEXT, 'userPassword' TEXT, 'userFirstName' TEXT,'userLastName' TEXT,'userAge' INTEGER,'userHeight' TEXT,'userZipcode' INTEGER,'userActivityLevel' INTEGER,PRIMARY KEY('userID'));"
dim ExerciseTableQuery as String = "CREATE TABLE 'ExerciseTable' ('exerciseID' INTEGER UNIQUE,'exerciseName' TEXT,'exerciseType' TEXT,'exerciseTargets' TEXT,'exerciseDifficulty' INTEGER, PRIMARY KEY('exerciseID'));"


// Execute our Crafted SQL Statements
db.SQLExecute(UserTableQuery)
db.SQLExecute(ExerciseTableQuery)

// Save changes and leave the database open for other functions/code to use
db.Commit

//
// Set Session Property for Database Here
//

Session.DB.DatabaseFile = NewDatabaseFile <---This line causes an error
Session.dbIsConnected = True


End If
End if

also Two questions:

  1. Can you explain to me what those lines starting with a “#” are?
  2. How can I test my application with a persistent database when the resources folder gets blown away after each debug session?

Thank you for your help!

A nilobject exception?

1/ you also need to test if DatabaseFile.Exists is not nil. If it is missing , it will be nil before it is testable for .exists

if DatabaseFile <> nil and Databasefile.Exists = True

2/ Is it the session.db which is nil? Where do you initialise it?

elseif DatabaseFile.Exists = False Then

This is redundant.
You just need else here.

But really…

Dim db As New SQLiteDatabase db.DatabaseFile = NewDatabaseFile If db.CreateDatabaseFile Then db.SQLExecute("BEGIN TRANSACTION")

and then you get an error on
Session.DB.DatabaseFile = NewDatabaseFile <—This line causes an error

If the session has a db object, then you need to be consistent.
before that line you are using a LOCAL db variable, not the session one.

Does this work better?

--Dim db As New SQLiteDatabase //wipe this session.db.DatabaseFile = NewDatabaseFile If session.db.CreateDatabaseFile Then session.db.SQLExecute("BEGIN TRANSACTION")

yes sir. I Still get that error

[quote=431523:@Jeff Tullin]
This is redundant.
You just need else here.[/quote]

This is changed.

[quote=431523:@Jeff Tullin]
If the session has a db object, then you need to be consistent.
before that line you are using a LOCAL db variable, not the session one.

Does this work better? …[/quote]

So I’ve adjusted my code to use ONLY the Session DB property to make it consistent. So now my code still creates the new database file from the folderitem in the resources folder, but uses the session.DB property to do all the SQL Stuff. HOWEVER, when now that I am using the session Property, I get the NilObjectException Error again. Below are two screenshots from the IDE which I think will help concisely illustrate the problem.

The Debugger Not Liking the use of Session.DB (Full Res: https://i.imgur.com/e3cnk9P.png)

The Value of the Session’s DB property, from session.open event (Full Res: https://i.imgur.com/ZXCSqzD.png)

You still haven’t understood the folderitems. You are using 2 where you need one.

If your folderitem is nil you need to deal with an error. If the folderitem doesn’t exist don’t do the second folderitem. You don’t need it and it doesn’t make sense. For SQLite you then need to do a CreateDatabaseFile on the NOT-existing folderitem.

Review the example project Database\SQLite\SQLiteExample.xojo_binary_project supplied with the IDE.
To follow up on Beatrix’s comment review the code in SQLiteWindow.CreateDBButton.Action event.
This is not the only way to achieve what you want but it should be a good place to start.

[quote=431525:@Beatrix Willius]You still haven’t understood the folderitems. You are using 2 where you need one.

If your folderitem is nil you need to deal with an error. If the folderitem doesn’t exist don’t do the second folderitem. You don’t need it and it doesn’t make sense. For SQLite you then need to do a CreateDatabaseFile on the NOT-existing folderitem.[/quote]

I’m not sure how that would work because If I change my code to a simple “else” and do the code to setup the database using the DatabaseFile variable declared at the top of the function, it says the item does not exist in the debugger, so how can I use that?

like I think I understand the file needs to exist in my resources directory before I can do anything with it, but I thought when I typed the code:

NewDatabaseFile = New FolderItem(DatabaseFile)

that that line is CREATING the file that is missing in my resources directory where my app will check for the database next time it starts, giving me a file to work as a database. so even though there are two FolderItems, they’re essentially the same thing. AFAIK I cannot do something like

dim DatabaseFile as FolderItem = New FolderItem(SpecialFolder.Resources.Child("liffboi.db"))

because that would make a new folderItem EVERY TIME regardless of if the database file is in my resources folder or not.

I totally admit to being confused at this point, this is my first go at anything with xojo or a language that isn’t python.

[quote=431527:@Robin Lauryssen-Mitchell]Review the example project Database\SQLite\SQLiteExample.xojo_binary_project supplied with the IDE.
To follow up on Beatrix’s comment review the code in SQLiteWindow.CreateDBButton.Action event.
This is not the only way to achieve what you want but it should be a good place to start.[/quote]

So in looking at this code, I’m noticing the Database is a property of the APP vs a Session. Should I have moved my DB property to the APP? is there a best practice or something for this case?

AFAIK The way my code is written I’m handling the exception in that I’m doing an If/else for the file existing and when my code recognizes that the database file does not exist, the first instructions it gets are to make the file with the lines of code:

Dim NewDatabaseFile as FolderItem
NewDatabaseFile = New FolderItem(DatabaseFile)

by referencing the DatabaseFile variable it should put that file where my code expects the database to be in the future however, I’m starting to think the problem is something to the effect of referencing my database before assignment

[code]
// Set the file Attribute to the location we checked earlier, IE Make the file where we’re looking for it.
App.DB.DatabaseFile = NewDatabaseFile

// If we’re creating that database file, we are, Then do the following…
If App.DB.CreateDatabaseFile Then

// Start a new Transaction to set up the tables the application will need
App.DB.SQLExecute(“BEGIN TRANSACTION”)

// Craft Strings for the SQL Queries we will need to run in order to make the necessary Tables…
dim UserTableQuery as String = “CREATE TABLE ‘UserTable’ (‘userID’ INTEGER UNIQUE, ‘userUserName’ TEXT, ‘userPassword’ TEXT, ‘userFirstName’ TEXT,‘userLastName’ TEXT,‘userAge’ INTEGER,‘userHeight’ TEXT,‘userZipcode’ INTEGER,‘userActivityLevel’ INTEGER,PRIMARY KEY(‘userID’));”
dim ExerciseTableQuery as String = “CREATE TABLE ‘ExerciseTable’ (‘exerciseID’ INTEGER UNIQUE,‘exerciseName’ TEXT,‘exerciseType’ TEXT,‘exerciseTargets’ TEXT,‘exerciseDifficulty’ INTEGER, PRIMARY KEY(‘exerciseID’));”

// Execute our Crafted SQL Statements
App.DB.SQLExecute(UserTableQuery)
App.DB.SQLExecute(ExerciseTableQuery)

// Save changes and leave the database open for other functions/code to use
App.DB.Commit

//
// Set Session Property for Database Here
//

App.DB.DatabaseFile = NewDatabaseFile
App.dbIsConnected = True[/code]

it feels like the top line:

// Set the file Attribute to the location we checked earlier, IE Make the file where we're looking for it.
App.DB.DatabaseFile = NewDatabaseFile

should actually come later after the database is set up, but I’m not sure how that would work. After all, Im executing that same line of code at the end of the else block

I figured it out, I’m going to try to write a post up on what I’ve learned so that newbies down the road will have an easier time Programmatically creating databases for their applications. It seems a short coming in the documentation available is that it is either in the context of a desktop application or it is written from the perspective of creating the database via the IDE

Session is just fine for DB. Not sure if there is a ‘best practice’ or not. I’ll try to remember to ask the PTB’s.
For an example web project that uses SQLite take a look at ‘Sample Applications\EddiesElectronics\Web\EEWeb.xojo_binary_project’.
It is a rather more complex project but it should give you some guidance.

[quote=431532:@Robin Lauryssen-Mitchell]Session is just fine for DB. Not sure if there is a ‘best practice’ or not. I’ll try to remember to ask the PTB’s.
For an example web project that uses SQLite take a look at ‘Sample Applications\EddiesElectronics\Web\EEWeb.xojo_binary_project’.
It is a rather more complex project but it should give you some guidance.[/quote]

Thank you for your guidance, Your directing all the way down to the button in the sample application helped me understand what I was doing wrong, I have since been able to change my code and this function works as I expect.

I do have one last question:

How is one supposed to test thier app if the debug folder is blown away after each debug? If I understand correctly, the best place for a resources like an embedded database is in the resources folder, so I’ve written my code to put it there. Since it’s blown away I will never have a database of user or application data to test with unless I repeat the steps to recreate the database each time.

Do you have any advice on how I can overcome this and use a persistent database even while debugging?

Maybe create your database elsewhere and use a ‘Copy Files’ step?

Wow
This post got big in a hurry.

Yup.

[quote]
You should put the db into Specialfolder.applicationdata.child(“MyApp”) folder
and refer to it from there.[/quote]

If you do this NOW on YOUR machine, it will be there every time you run the app, compiled or debug.
You need to put one there on a user’s machine when your app starts, if it does not exist.

[quote=431545:@Jeff Tullin]Wow
This post got big in a hurry.

Yup.

If you do this NOW on YOUR machine, it will be there every time you run the app, compiled or debug.
You need to put one there on a user’s machine when your app starts, if it does not exist.[/quote]

So I’ve done this and the LAST FRIGGEN PROBLEM I’m having with this function is that after it checks and sees that the database is in my appdata folder, I have no way to set the databasefile property.

[code]
dim DatabaseFile as FolderItem = SpecialFolder.ApplicationData.Child(“liffboi.db”)

//If that file exists…
if DatabaseFile <> nil and Databasefile.Exists = True Then
MsgBox(“Database Found!”)
//
// Set Session Property for Database Here
//
App.DB.DatabaseFile = DatabaseFile
App.dbIsConnected = True

MsgBox(“Database is found!”)

//Otherwise…
else[/code]

anything stand out as to what I might be doing wrong? My reasoning might be flawed, but after looking at the documentation for this exception it makes no sense that this exception would be raised seeing as this file is validated by the IF statement above
before being passed as an argument. Also, there is definitely enough memory on my machine to handle a 20K database lol

Where do you initialise the App.DB object?
It can only be that which is nil here.

try

if App.DB = nil then msgbox "The DB object had not been initialised by this point" App.DB = new SQLiteDatabase end if App.DB.DatabaseFile = DatabaseFile App.dbIsConnected = True

[quote=431562:@Jeff Tullin]Where do you initialise the App.DB object?
It can only be that which is nil here.

try

if App.DB = nil then msgbox "The DB object had not been initialised by this point" App.DB = new SQLiteDatabase end if App.DB.DatabaseFile = DatabaseFile App.dbIsConnected = True[/quote]

Through the IDE, so I added a property to “App” as type SQLite Database. Doesn’t that mean it’s already initialized? Or does it need to be explicitly initialized before use? how does that work?