NilObjectException with SQLite3

Databases
1

(Running latest Xojo under Linux, using SQLite3)

I’ve read a few threads here in the forums, but none seemed to help.

I have some global_properties setup and I’ll post the ones I think are relevant to my question…

mDB
IsConnected

When my app first pops up, in the “open” event handler for the app, I open the database and read in some defaults, leaving the db open as seen here:

[code] Dim dbFile As New FolderItem("/home/themagicm/.portflowanalyzer/portflowdb.sqlite")

mDb = New SQLiteDatabase
mDb.DatabaseFile = dbFile
[/code]

In my main window, I have a button (moved the code there because of the nilobjectexception error), I click the button and get the error

[code]If Not IsConnected Then
MsgBox(“ERROR: Not connected to the database! Cannot populate list!”)
End If

flowedheadslistbox.DeleteAllRows

Dim sql2 As String
sql2 = “SELECT jobnumber, owner, employee_name, enginetype, head_description FROM flow_head”

Dim data2 As RecordSet
data2 = mDB.SQLSelect(sql2) <----- ERROR HERE

If mDB.Error Then
MsgBox("DB Error: " + mDB.ErrorMessage)
Return
End If[/code]

I know the database is open, because there is another window I can click on and when I hit save, it queries the database to see if a record exists. I’m stumped…

2

This doesn’t connect to the DB
It just gets a reference to the file that contains the DB
You need to call connect

Dim dbFile As New FolderItem("/home/themagicm/.portflowanalyzer/portflowdb.sqlite")
  
  mDb = New SQLiteDatabase
  mDb.DatabaseFile = dbFile
  if mDB.Connect then
    IsConnected = true
  else
    isConnected = false
    mdb = nil
  end if

The other issue is that your check is incomplete

  If  Not IsConnected Then
    MsgBox("ERROR: Not connected to the database!  Cannot populate list!")
  End If
  // execution continues here

You need to put a return after the msgbox otherwise the code continues on eve after you said you’re not connected

3

my connect code:

[code] Dim dbFile As New FolderItem("/home/themagicm/.portflowanalyzer/portflowdb.sqlite")

mDb = New SQLiteDatabase
mDb.DatabaseFile = dbFile

If mDb.Connect Then
mIsConnected = True
Else
mIsConnected = False
msgbox("Error opening SQLite database: " + mDb.ErrorMessage)
quit
End If

[/code]

Still getting the same error. The other thing you pointed out…yea…just havent gotten around to fixing it.

If I comment out my code above and click on a different screen which checks to see if a job number is already in the db, I get database not connected. So I know for a fact that I do have an open db connection when that code is enabled. So it should work when I click my button that has the code to retrieve data to populate my listbox. It has to be something dumb I’m doing.

4

Did you include Dim mDB at the top of that code? Did you add another mDB property to the window? Either of those would account for the error.

5

mDB is a global property.
Its not related to database connectivity.

6

I’m unclear. Did you check to see if you declared it a second time somewhere?

7

no second declaration. My previous post showing my connect db code works… the app itself works…just loading values into the listbox fails at the data2 = mDB.SQLSelect(sql2) portion…

in my app.open, if I tell it, first thing…connect to the db, then populate listbox, it fails.

8

That line has nothing to do with a ListBox. At that point, you’re still retrieving data from the database.

Did you try setting a breakpoint there to check the value of mDB in the debugger?

9

Ok…I’m farther along… If you look at the While not data2.eof section… I’m reading in stuff from the db. The first commented out portion that populates my listbox fails with the nilobjexception… So what I did was did it one at a time…
I added just the jobnumber…worked, next I stopped the app, then told it to populate owner…worked…then got to employee…and then I get the nilobjexception error.

[code] Dim sql2 As String
sql2 = “SELECT jobnumber, owner, employee_name, enginetype, head_description FROM flow_head”

Dim data2 As RecordSet
data2 = mDB.SQLSelect(sql2)

If mDB.Error Then
MsgBox("DB Error: " + mDB.ErrorMessage)
Return
End If

If data2 <> Nil Then
While Not data2.EOF
'portflowmain.flowedheadslistbox.AddRow(data2.Field(“JobNumber”).StringValue, data2.Field(“Owner”).StringValue, data2.Field(“Employee”).StringValue, data2.Field(“Engine”).StringValue, data2.field(“Head”).StringValue)

portflowmain.flowedheadslistbox.AddRow(data2.Field(“JobNumber”).StringValue,data2.Field(“Owner”).StringValue, _
data2.Field(“Employee”).StringValue)
data2.MoveNext
Wend
data2.Close
End If[/code]

10

aaagghhh!!! I’m a dummy. the problem is… data2.field(" ") has to have the correct field from the sql query in it otherwise it fails. LOL!

11

Look at your SQL. You are selecting “employee_name” not “employee”.

12

YEP! I thought I was supposed to put my listbox column names in the data2.field…

13

This xojo stuff is pretty bad ass. Having a blast with it! :slight_smile:

14

You can use AS in the SQL to get them to match, if you’d like. That will let you use the alias.

15

cool…thanks for the tip.

16

“As” is optional but greatly improves readability

Select long_field_name As fName, another_field, yetAnotherField From myTable

and

Select long_field_name fName, another_field, yetAnotherField From myTable

gives identical results