database won't connect

Databases
1

Hi All.

I have a little issue with my database program (this thing is going to be the death of me… )

I create a database and know it exists, as I can see the file!

Here is my create code:

[code]/ add the database here.
dim dbfile as FolderItem

// I am going a static path here. next iteration will be a get folder item.
dbfile = new FolderItem (“theEncryptedPWPG.sqlite”)

// if the database already exists, DON’T delete it

If dbFile <> Nil And dbFile.Exists Then
MessageBox (“The file already exists!”)
CreateStatusLabel.text = dbfile.Name
SQLiteWindow.CreateDBButton.Enabled = FALSE

else
// ok it doesn’t exist, so create it
App.db = New SQLiteDatabase
App.db.DatabaseFile = dbFile
CreateStatusLabel.text = dbfile.Name
SQLiteWindow.CreateDBButton.Enabled = FALSE

Try
App.db.CreateDatabase

Catch err As DatabaseException
isConnected = false
CreateStatusLabel.Value = "Error: " + err.Message
Return
End Try
End If[/code]

here is my connect code:

[code]// CREATE TABLE command to define the password table
// Because ID is a PRIMARY KEY and an INTEGER, it will increment automatically.
var sql As String = “CREATE TABLE passwords (ID INTEGER NOT NULL, siteName TEXT, userName TEXT, Password TEXT, PRIMARY KEY(ID));”

If App.db.Connect Then
// Run the SQL command
Try
App.db.ExecuteSQL(sql) —> it hangs here and the variables tell me I’m not connected
Catch err As DatabaseException
CreateTableStatusLabel.Value = "Error: " + err.Message
Return
End Try

CreateTableStatusLabel.Value = “OK”
Else
MessageDialog.Show(“Create the database first”)
End If[/code]

Any ideas?

Regards

2

What error are you actually getting?
Does the msgbox pop up telling you the file exists?

3

You’re mixing API 1 and API 2 code. The API 2 version of Database.Connect does not return a boolean, and uses exceptions. API 1 returns a boolean and you have to check for errors. To use all API 2 code, move the db.Connect into the Try block without the If statement:

Try App.db.Connect App.db.ExecuteSQL(sql) Catch ...
Also note that when dealing with exceptions while running from the IDE (debug mode), the IDE will break on any exceptions unless you turn that option off. To see the exception play out, just hit resume.

4

Hi Kosh / Jay.

Ok it is working now.
I did as you suggested and removed the IF… ENDIF from the createTable action.

The only problem I have right now is that if the table has been created I get a NILObjectExtension. The first time I create it, it works fine. The second time, if the database exists is when the table creation dies.

I guess I have to check and see if the table already exists, and stop the re-creation… re-creation… recreation???

Regards

5

[quote=483678:@Michael Cebasek]Hi Kosh / Jay.
The only problem I have right now is that if the table has been created I get a NILObjectExtension. The first time I create it, it works fine. The second time, if the database exists is when the table creation dies.

I guess I have to check and see if the table already exists, and stop the re-creation… re-creation… recreation???
[/quote]

You should use the IF NOT EXISTS option in the CREATE TABLE statement (CREATE TABLE IF NOT EXISTS passwords …).

But that’s not your present problem. The NilObjectException is the clue. If it were a query error (or any other Database error). It would be a DatabaseException error - what you’re catching in the Try.

The problem is your file logic. You are only creating an instance of db and assigning the file if it doesn’t exist, not when it already exists. So therefore when you try to connect to the db later for an existing file, the db is still Nil (and of course has no file assigned).

6

Why would it be NIL if I created a property in the App?
It is public, which should allow it to be used throughout the application?

Oh, and I forgot, I did try the IF NOT EXISTS earlier.
So to get rid of the NILObjectException, I always have to do the db file connect?

Regards

7

[quote=483699:@Michael Cebasek]Why would it be NIL if I created a property in the App?
It is public, which should allow it to be used throughout the application?
[/quote]
All objects are Nil until initialized. Making it a property doesn’t initialize it (unlike intrinsic data types like Integer and String).

In the Else part of your If statement you have these lines:

App.db = New SQLiteDatabase App.db.DatabaseFile = dbFile
The New operator initializes the db object. You don’t have either of these statements in the non-Else part, so for an existing file the db is never initialized. Hence the NilObjectException (this exception means you tried to access an object that is Nil!)

8

Thanks Jay.

You are making things clearer for me.
I have been reading the Introduction to Xojo book, and trying to understand the example.
Slowly I’ll get it.

(According to my boss, I work in a different field than programming, I’m not the sharpest knife in the light socket. (Yes, mixed metaphor I know)).

Regards