Database Basics

The initial code to prepare and open an existing database file consists of a few small steps, and I have done mine like so:

Add a module to the App.

Add a property to the module ‘db As SQLiteDatabase’
Add a property to the module ‘dbFile as FolderItem’

Added the following to the App.Open event handler :
‘db = New SQLiteDatabase’
'dbFile = GetFolderItem(“foo.sqlite”)
‘db.Database = dbFile’

If Not db.Connect Then
MsgBox(“Open database failed”)
End If

At this point I’m ready to Dim the RecordSet and perform the SQL query etc.

Is the approach I have used correct ?

I tried setting ‘db’ and ‘dbFile’ as properties of the App itself but that didn’t work, hence adding them to a module instead.

I believe you want ‘db.DatabaseFile’ for SQLiteDatabase, http://documentation.xojo.com/index.php/SQLiteDatabase

Also, just to be on the safe side going forward, add checks to make sure the file exists, is readable and writeable, erring out with a nice message to the user if not.

[quote=124626:@Jeremy Cowgar]I believe you want ‘db.DatabaseFile’ for SQLiteDatabase, http://documentation.xojo.com/index.php/SQLiteDatabase

Also, just to be on the safe side going forward, add checks to make sure the file exists, is readable and writeable, erring out with a nice message to the user if not.[/quote]

Yep I just realised that after I posted. It does in fact say ‘db.DatabaseFile’.

Is there a reason why the db and dbFile properties only work in a module ?

I don’t understand what you are asking here, can you expand your question?

If I add ‘db As SQLiteDatabase’ and ‘dbFile As FolderItem’ as properties of the App, it doesn’t work.

If I add them as properties of a module contained within the App, it does work.

A lot of reading I have done so far seems to suggest it should work when defined as properties of the App.

If it is in app you would reference it as app.db. By putting it in a module with a global scope you can simply use db. to reference it.

Yes, it should work as properties of the Application. I’d suggest you try again, there has to be an error in code somewhere when you are adding them as properties to the App class. Give it a try and if you have problems the second time around, let us know exactly what doesn’t work.

Now… Do you have the properties marked as Global in the module while you added the properties to the App class? That could cause some confusion. Also, if you are marking the properties Global in the module, bear in mind that properties in a Class do not have a “Global” attribute, so simply accessing “db” from some other class/location than your App class will fail. You would need to make them Public and access them as App.Db.

Bear in mind, globals are generally a bad idea and should be avoided. There are some cases where it may be permissible, a database handle may be one of those places, but be stingy in granting properties a global status in general.

One more note, you probably do not need dbFile if you are keeping a handle around to the actual SqliteDatabase, as you can simply access db.DatabaseFile to get to the same place.

Stephen,
It may also be worth naming your actual database files, so as to avoid confusion if you have multiple databases.

Example:

Songsdb = New SQLiteDatabase

Is clearer and less error prone when you have multiple databases, as opposed to:

db = New SQLiteDatabase

HTH.

[quote=124646:@Richard Summers]Stephen,
It may also be worth naming your actual database files, so as to avoid confusion if you have multiple databases.

Example:

Songsdb = New SQLiteDatabase

Is clearer and less error prone when you have multiple databases, as opposed to:

db = New SQLiteDatabase

HTH.[/quote]

Yep, thanks. I was coming to that too. I posted a reply to Jeremys last post but it has disappeared.

Where I currently stand is this:

I think I’ve been bitten by the copy/paste bug yet again. I copied/pasted the code with the intention of avoiding errors. Hmm.

After starting fresh and typing everything in myself, it is working better. Taking into account what was said in a few previous posts, I now have the following:

App property of ‘db As SQLiteDatabase’ (Private)

App.open event handler contains this :

  db = New SQLiteDatabase
  db.DatabaseFile = GetFolderItem("Resources.sqlite")
  
  If Not db.Connect Then
    MsgBox("Failed to open database")
  End If

This opens the database successfully, and if I change the name of the database file, it shows me the msgbox correctly.

In answer to Jeremys post regarding globals, are you saying that its perfectly ok to use these variables so long as they are set to Private where possible ?

And regarding dbFile, Ive changed the code as shown above so that dbFile is not actually used at all and has been removed. Is that what you meant ?

Thanks for all the replies guys, this is helping a lot.

This is what it looks like in the code window :

In regards to global variables… all variables everywhere should be limited to the most narrow scope possible. If you only need db in a single method in your App class, then the db should be a method variable, not a property, a property will be accessible throughout the entire class. Now, presumably, you are going to access the db from other places in your App class, as well as other places in your program (other classes, modules, etc…). Thus, a variable defined right inside a method will not work as it is not accessible outside of that method.

So, the opposite of that is to make a module and declare all sorts of global variables, such as db. That would be accessible anywhere in your application with no namespace at all. You simply type db and it works. This is handy, especially for the database connection. What I was suggesting is that you limit the number of variables that are declared as global, as it is generally bad.

Imagine, for a second, taken to the extreme you make all your variables global, name, age, i (used for for loop counter), etc… All over your code hundreds of methods would be using and updating these global variables. Your application would be a time bomb and when it explodes, good luck figuring out why.

So, in general, limit your global variables to a very, very small number. It all depends on the application and design. The most I have ever had is Db, CurrentUser and Log. Again, that’s a general principal, not a hard fast do or die rule (but more on the do or die side than use globals all willy-nilly :-D).

Hope that clarified things a bit instead of adding to the confusion.

Ok, so going from that then:

Use method variables where possible.

If a method variable is not sufficient, then use a class variable. Presumably setting this to private keeps it available only within the said class.

If it needs to be truly global, create a module and add it to that as a global variable.

Is that a fair approach to it ?