Basic Database Setup & Editing

Hi all,

Im getting back to writing some database code after a while away so may be a little rusty.

I’m using Xojo 2014 R2 on a Mac.

I’ve looked up the various code snippets for opening the database and managing the records, and this is what Ive come up with so far.

Opening the database file

Dim dbFile As FolderItem
dbFile = GetFolderItem(“databasefileondisk”)

Dim db As New SQLiteDatbase
db.DatabaseFile = dbFile

If db.Connect Then
    Msgbox(“Database opened OK.”)
Else
    Msgbox(“Error opening database:” + db.ErrorMessage”)
End If

Populating the listbox to display data

Dim rs as RecordSet
rs=db.SQLSelect(“SELECT * FROM tablename”)

if db.Error Then
    Msgbox(“Database error: “ + db.ErrorMessage)
    Return
End If

While Not rs.EOF
    Listbox.Addrow(rs.IdxField(1).StringValue, rs.IdxField(2).StringValue
    rs.MoveNext
Wend
rs.Close

Inserting a new record

db.SQLExecute(“BEGIN TRANSACTION”)
    db.SQLExecute(INSERT INTO tablename “Fieldname1”, “Fieldname2” VALUES “Value1”, “Value2”)
If Not db.Error Then
    db.Commit
Else
    db.Rollback
    Msgbox(“Database error: “ + db.ErrorMessage)
End If

Deleting a record

db.SQLExecute(“BEGIN TRANSACTION”)
    db.SQLExecute(DELETE FROM tablename WHERE Fieldname=“Criteria”) 
If Not db.Error Then
    db.Commit
Else
    db.Rollback
    Msgbox(“Database error: “ + db.ErrorMessage)
End If

Updating a record

db.SQLExecute(“BEGIN TRANSACTION”)
    db.SQLExecute(UPDATE tablename SET Fieldname1=“Value1”, Fieldname2=“Value2” WHERE Fieldname=“Criteria”)
If Not db.Error Then
    db.Commit
Else
    db.Rollback
    Msgbox(“Database error: “ + db.ErrorMessage)
End If

Questions on the code above are :

  1. Where the listbox is populated, is the error check in the right place ? Does it even need to be included in that part of the code if we already know the database has opened ok ?

  2. For Insert, Update and Delete operations, I’ve used largely the same process but with different SQL code to perform the operation. Is this the most appropriate / recommended way ? I’ve read in several forums that the purest way is to use SQLExecute instead of defining and working on recordsets all the time.

  3. I have also read that it is considered good practise to close the database after each Query/Insert/Update/Delete and then reopen it again when needed for the next transaction. Is this appropriate here ?

  4. Regarding the initial opening of the database, if I add a property to the app ‘db as New SQLitedatabase’, it removes the space inbetween ‘New’ and ‘SQLiteDatabase’. Is it still correct to add the ‘dbFile as FolderItem’ and ‘db As New SQLitedatabase’ as App properties ?

  5. Would it be more appropriate to add a method containing the code to open the database and then call it from App.Open or Window.Open ? And following on from question 3 above, I was thinking of maybe adding 2 methods, the first to open the database, the second to close it. Then I could simply call these methods at the beginning and end of the Insert/Update/Delete processes above, so that the database is only open long enough for a transaction to be completed. Would this be a good approach ?

The database will be SQLite to begin with but possibly move to a MySql server installed locally at some time in the future.

As i mentioned to begin with, Im quite rusty here, so I reliase all of the above may be a perfect example of how NOT to do things.

Any comments/suggestions gratefully received.

:slight_smile:

Something I forgot to ask also, regarding the code for Insert/Delete/Update, would it be best practise to add the code to the button click events, or create a method for each then call the method from the button click ?

Thanks again.

So many questions!

Doing the error check immediately after the SQLSelect command is a good idea. In general, you should always check for an error after every DB command. For example, the SQL could have a syntax error.

This will work fine, but you should probably use SQLitePreparedStatement to avoid string concatenation and possible SQL injection.
The “BEGIN TRANSACTION” and Commit statements are not necessary for just a single SQL command.

There is little reason to close and reopen the DB when using SQLite.

Properties are just declarations, so “New” is not allowed. You can declare the property as:

db As SQLiteDatabase

And then in code, instantiate it:

db = New SQLiteDatabase

That depends on your design of course, but I wouldn’t go through all that trouble for SQLite. If you do end up going with MySQL and decide to open/close the DB connection each time you’d definitely want to have methods that are easy to call.

That depends on how they’ll be called. If you might want to call them from a menu, for example, then put the code in a method and call it from the button event handler and the menu event handler.

Thankyou Paul, I will work through that.

Regarding the first four lines:

Dim dbFile As FolderItem
dbFile = GetFolderItem(“databasefileondisk”)
Dim db As New SQLiteDatbase
db.DatabaseFile = dbFile

What is the best location for these ? I’ve seen these in a mixture of locations, including App properties, Window properties, added as part of a module etc.

Thanks.

The best location depends on how you need to access them. Having “db” as a local variable means you can only access it in the method. Most likely you’ll want that to have a wider scope, so putting it in a module or a public property on App are common choices.

Thanks again. I think I’ve cracked this part of it as much by trial and error than anything else. I tried various combinations of adding App properties, Window properties, App open, Window open and module.

Before I go further, I think I’ve been bitten by a or the copy & paste bug. That is to say if I copy any of the code snippets I posted above into the IDE and then run the project it fails; there is no colour highlighting and various objects are not ‘found’ even though they are confirmed to exist. All of the text stays black in colour. If I then type in exactly the same code on a new line and delete the pasted code, it comes back to life and progresses.

Anyway, after bashing my poor keyboard several times over the above issue, I found a combination that seems happy enough, which is like so :

First add a new module to the project.

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

Add App.open event which includes the following:

'db = New SQLiteDatabase
'dbFile = GetFolderItem( “databasefilename” )
'db.DatabaseFile = dbFile
If db.Connect Then
MsgBox(“Connection success.”)
Else
MsgBox(“Connection failed.”)
End If

I was glad to see the MsgBox working at this point even before the first window is opened.

I then added the following to the ListBox.open :

'Dim rs As RecordSet
'db.SQLSelect(“SQL SELECT STATEMENT HERE”)
If db.Error Then
MsgBox(“Database error:” + db.ErrorMessage)
Else
MsgBox(“Table : “TABLENAME GOES HERE””+CHR(13)+CHR(13)+“Loaded”)
End If

And finally added the ‘While Not rs.EOF’ loop to populate the ListBox. So the error checking has been retained as you suggested. I’ve not added the Update/Insert/Delete code yet, but that should hopefully be a lot easier now, assuming the above seems reasonable ?

Just thinking ahead a little to when it comes time to load maybe several database files, it should be identical except for multiple declarations where I will probably stick with the above format but use :

[b]
db1 As SQLiteDatabase
db2 As SQLiteDatabase
db3 As SQLiteDatabase

db1File=GetFolderItem(“DATABASEFILENAME”)
db2File=GetFolderItem(“DATABASEFILENAME”)
db3File=GetFolderItem(“DATABASEFILENAME”)
[/b]

And so on, Im sure you get the picture. The main thing will be to make sure I keep a track of the database file handles, but I dont think that will be a big problem. The majority of it will be for read-only operations.

Im still trying to get my head fully around the ‘Dim rs As RecordSet’, ‘rs.Close’ and the other associated bits for using the RecordSets, where and when they need to be repeated or redefined, that kinda thing.

It is a good idea to dim your recordset locally, when and where it is needed. Doing so encapsulates the database access, which should be done as quickly and discretely as possible. Get the recordset, use it, and dispose of it. Dimming it locally also removes the need to Close it, as that will be done for you when it goes out of scope.

Database should be global. Recordset should be local.

[quote=123742:@Tim Hare]It is a good idea to dim your recordset locally, when and where it is needed. Doing so encapsulates the database access, which should be done as quickly and discretely as possible. Get the recordset, use it, and dispose of it. Dimming it locally also removes the need to Close it, as that will be done for you when it goes out of scope.

Database should be global. Recordset should be local.[/quote]

So if Im reading you correctly:

The ‘Dim rs As RecordSet’ and use of the RecordSet above are correctly placed in the ListBox.Open ?

And

db As SQLiteDatabase’ should be an App.property ?

Thanks for replying.