not getting any records from database

Databases
1

Hi All.

This is nuts.

I am trying to get the records in an encrypted database (yes I am getting better at the encryption stuff (smile)) but even though I know there are records I get a nilobjectexception.

When I go to read the database this is what I have as a method:

[code]Var dbFile As FolderItem //set database variable

dbFile = GetOpenFolderItem("") // let the person choose the database. I have filtered what could be chosen

If dbFile <> Nil Then // if it exists then do the following
Var db As New SQLiteDatabase //create an instance of the database
db.DatabaseFile = dbFile
db.EncryptionKey = “xxxxxxxxxx”

Try
db.Connect //try to connect

MessageBox("Connected to " + dbFile.Name)
LoadWorkEvents //this is the method I call to get the recordset

Catch error As DatabaseException
MessageBox("Error: " + error.Message) //what is the error
return
End Try

else
if dbFile.Exists = False Then
MessageBox “The database does not exist”
Return
else
end if

End If[/code]

This is what I do in the LoadWorkEvents method.

[code]mainWindowListbox.DeleteAllRows

dim sql as string //set up my sqlite string query
dim workRS as recordset

sql = “SELECT * from Activity ORDER BY groupType”

workRS = db.SQLSelect (sql)

IF dbError then return // look for an error with the Module dbError

if workRS <> NIL then
while not workRS.EOF
mainWindowListbox.AddRow ( workRS.Field(“groupType”).StringValue, _
workRS.Field(“Name”).stringValue, _
workRS.Field(“startTime”).stringValue, _
workRS.Field(“endTime”).stringValue, _
workRS.Field(“Notes”).stringValue, _
workRS.Field(“seconds”).stringValue, _
workRS.Field(“minutes”).stringValue, _
workRS.Field(“hours”).stringValue)

'mainWindowListbox.RowTagAt(mainWindowListbox.LastIndex) = workRS.Field("ID").IntegerValue

workRS.MoveNext

wend
workRS.close
end if[/code]

I have verified that there is, indeed, data in the sqlite database.

Any ideas, hints, tips?

Regards

2

if dbfile is nil then dbfile.exists executes and fires a nilobjectexception …

3

in your first bit of code you open & assign the DB to a local variable
when that goes out of scoep the DB is effectively closed

remove that local variable definition and make DB a property of you window (or app or something that wont go out of scope)

4

You didn’t indicate where you’re getting the NOE (Nil Object Exception). I’m guessing it’s on the “workRS = db.SQLSelect (sql)” line, which would be because you’re not using a global db variable (or passing it between methods).

There are other errors (or errors waiting to happen) in your code as well:

In your open method:

  • dbFile <> Nil does not guarantee the file exists, just that you have a valid file handle (useful for determining if the user cancelled the open file dialog). You have to also check dbFile.Exists to ensure the file is there. You are only using dbFile.Exists in the else part, which would also give you a NOE if it ever ran (if dbFile is Nil).
  • You define a local db variable, which does not exist outside of the method (in fact, it doesn’t exist outside of the enclosing If statement). Now, maybe you do have a global db variable, but if you Var/Dim an identical one, that creates a separate local version of the variable. After the open method (or the If statement in this case), db is back to whatever it was before the method (probably nil).
5

HI All.

The silly thing is that I have exactly the same code running in an earlier version, WITHOUT ENCRYPTION, which is making me think the problem is encryption.

I did a db.encrypt(password)

and put the

db.encryptionKey = “password”

when I go to open the file. That is about the only difference

6

Yeah, I’m pretty sure that is not the only difference. This code would not/will not run any better without the encryption.

7

HI Jay.

If you don’t believe me, I can send you the project file.

I know it works because I’m using it now…
But, since I have faith that it IS possible that I made some other change, I’ll do a step by step comparison. And if I find a difference, I’ll buy you a virtual Coca Cola… or Pepsi… or Fanta… or beer…

Regards

8

What Norman posted is probably the anwer, it seems this:

Var db As New SQLiteDatabase //create an instance of the database

is going out of scope.

9

  1. Where is the NilObjectException happening?

  2. Have you fixed the simple errors that have already been pointed out?

  3. Explain how workRS = db.SQLSelect (sql) in LoadWorkEvents can possibly work as db appears to be undefined and in any case won’t contain the value put in the local instance of db in your open-the-db method?

10

Not saying these are solutions but…

If dbFile <> Nil Then // if it exists then do the following

That isnt checking for existence. It is checking that there is text in the name of dbfile. If someone has typed ‘banana’ then dbfile may not be nil, but the file may not exist

You should use

If dbFile <> Nil and dbfile.exists then

When?

Var db As New SQLiteDatabase //create an instance of the database

This should be a global variable, or a property of the app.
eg

app.db = new SQLiteDatabase
IF dbError then return // look for an error with the Module dbError

But you dont.
You return straight away.
You should be checking dberror here, and returning true or false from the function to indicate success or failure

11

Hi Hector.

The db is a property of the main window which has the OpenDatabase method in it.

TIm:

The NilObjectException occurs when the loadWorkEvents Method is called. It is a method within the mainWindow window.

Regards

12

Think I found something… will advise…

Regards

13

[quote]Hi Hector.

The db is a property of the main window which has the OpenDatabase method in it.
[/quote]

If it is, you have declared another one locally.
It is the local one that you initialise

Var db As New SQLiteDatabase //create an instance of the database

Try removing the Var

db = New SQLiteDatabase //create an instance of the database

14

[quote=496240:@Michael Cebasek]Hi Hector.

The db is a property of the main window which has the OpenDatabase method in it.[/quote]

Then instead of saying:

var db as new sqlitedatabase

you should be saying:

db = new sqlitedatabase

Yeah I know. But you put the try/catch round it as well as a load of other stuff.

15

There’s a number of what I would call structural issues with your code.

The principleis to handle errors separately from processing. So do this:

[code]Var dbFile As FolderItem //set database variable

dbFile = GetOpenFolderItem("") // let the person choose the database. I have filtered what could be chosen

If dbFile = Nil Then // if the user didn’t choose a file then there’s nothing to do
// msgbox the user here
return
end if

db.DatabaseFile = dbFile
db.EncryptionKey = “xxxxxxxxxx”

Try
db.Connect //try to connect
Catch error As DatabaseException
MessageBox("Error: couldn’t connect - " + error.Message) //what is the error
return
End Try

LoadWorkEvents //this is the method I call to get the recordset
[/code]

Much simpler strructure and if the catch fires then you know exactly what caused it - only one possible statement can make it fire, not loads of them.

Similarly inside loadworkevents:

[code]dim sql as string //set up my sqlite string query
dim workRS as recordset

sql = “SELECT * from Activity ORDER BY groupType”

try
workRS = db.SQLSelect (sql)
Catch error As DatabaseException
MessageBox("Error: couldn’t select - " + error.Message) //what is the error
return
End Try

IF workRS = NIL then
//msgbox here about no records found or whatever
return
end if

// now you have your loop treating records[/code]

Structuring like this separates the error treatment from the interesting work, making each easier to read.

16

[quote=496270:@Tim Streater]Similarly inside loadworkevents:
[/quote]
Tim, your changes won’t work in LoadWordEvents, as he’s using API1 code there (RecordSet and SQLSelect instead of RowSet and SelectSQL).

17

Then I’ll learn to use rowset.

18

Where do I see which api is being used? I looked under app, but I remember seeing it somewhere, just can’t find it. It originally said something like api1 /api2 when I saw it.

Regards

19

Thanks Tim.

Believe it or not, I used rowset in a different program I had that uses a database.
Go figure.
Alzheimer’s I guess.

Regards

20

[quote=496333:@Michael Cebasek]Where do I see which api is being used? I looked under app, but I remember seeing it somewhere, just can’t find it. It originally said something like api1 /api2 when I saw it.
[/quote]
Those are compatibility flags, which are on all classes/properties/methods/etc. under the Advanced tab of the Property Inspector (gear icon). They only indicate which API your properties/methods are compatible with, not which one is being used.

API 2 is just the new versions of most of the framework classes/functions. You can mix both API 1 and API 2 code, and the only way to know which is which is by looking it up in the Language Reference - it will say introduced in 2019R2.

Refer to this article for more details.

Edit: I forgot, there is one way to determine if you’re using any API 1 code that has an API 2 replacement: Analyze Project (assuming you have the deprecated warnings turned on in settings - the default is yes).