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 :
-
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 ?
-
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.
-
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 ?
-
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 ?
-
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.