Bob Keeney has a series of training videos available on his website (www.bkeeney.com) which cover many of the key aspects of Xojo with working examples and he talks you through what he is doing and why during the videos. It is a commercial product but looking at the prices, very good value I reckon. Bob is also an active member of the forums here.
Code re-use, for me, is down to how I want to use it, where, and how many times. Xojo ‘Methods’ can accept multiple parameters and be set to return one or more values too. So you could pass say the DB name and table name as 2 parameters and then return an integer value that indicates whether everything has succeeded. Multiple return values are also possible but is a little more involved. I have always stuck to using a single return value in the form of an integer; I always return 0 for success, and then other positive values to indicate something has not worked as expected. Coding for databases also has its own set of errorcodes which could be passed as a return value instead.
I have not seen a best practise document as such, but there are several users here on the forums who will usually tell you the best way to do something if you are not sure, and give a reason as to why.
From what I have picked up here on the forums, and the examples I have looked at, here is what I have done, which is attached to the open event of a login window to a program, that checks user credentials:
Dim UserDB As New SQLiteDatabase
UserDB.DatabaseFile = GetFolderItem("User.sqlite")
If Not UserDB.Connect Then
Msgbox("Unable to open database")
Else
Dim UserNames As RecordSet
UserNames = UserDB.SQLSelect("SELECT UserName FROM Users ORDER BY UserName")
If UserDB.Error Then
MsgBox("Database error : " + UserDB.ErrorMessage)
Exit
End If
LoginControl.pmUsername.DeleteAllRows
If UserNames <> Nil Then ' Populate the 'pmUserName' popupmenu with User Names
While Not UserNames.EOF
LoginControl.pmUsername.AddRow(UserNames.IdxField(1).StringValue)
UserNames.MoveNext
Wend
End If
UserNames.Close
Dim UserRoles As RecordSet
UserRoles = UserDB.SQLSelect("SELECT RoleDescription FROM Roles ORDER BY RoleDescription")
If UserDB.Error Then
MsgBox("Database error : " + UserDB.ErrorMessage)
Exit
End If
LoginControl.pmRole.DeleteAllRows
If UserRoles <> Nil Then ' Populate the 'pmRole' popupmenu with User Roles
While Not UserRoles.EOF
LoginControl.pmRole.AddRow(UserRoles.IdxField(1).StringValue)
UserRoles.MoveNext
Wend
End If
UserRoles.Close
End If
In this code, I query the same table twice to load a list of User Names into a popupmenu, then load a list of User Roles into a second popupmenu.
There are several things in this code that I have picked up as I’m going along:
After setting the name of the database, the rest of the code sits in an ‘If… Then… Else’ where I first check to see if the database has opened successfully. If not, it shows me a message box warning me and then exits since there is no point trying to run any more of the code.
Once opened, I create a recordset to hold the results of the SQL query and then issue the SQL itself.
Immediately after SQL statement is issued, I then check for errors again. This is good practise generally and tells me straight away if I have something wrong with my SQL statements.
Assuming no errors, I clear the popupmenu control of any existing data and load the contents of the recordset into them accordingly.
The process is then repeated, but using the same database I have already opened since both tables reside there.
Once I get to the end of the data, I close the recordset.
I am only querying a single column of each table in this case, so the code is quite simple, but I use these same principles whenever I use a database.
You could also add this same code to a containercontrol, and even add some pre-defined SQL statements in your program; these pre-defined SQL statements are useful as you can effectively write them once, test them, and then store them, which means less errors and less coding.
Ive even built a containercontrol which adds a series of search boxes directly beneath a listbox; it then queries the selected database for whatever is entered in the boxes. I think I called it ‘DBsearch’ or something similar and then just add it on screen whenever i use a listbox. 90% of the code including the actual search routine is already written and tested and it makes extensive use of pre-defined SQL statements. I simply change a few parameters each time I use it. Its about as close to a drag and drop control as I want and works perfectly.
