Here are a couple of tips for using databases in REALbasic. If you have some other useful tips please send them to me in a private message and I will try to maintain this list and keep it up to date.
1. Avoid using the DatabaseQuery control. This control does one very simple task and that is to display data into a listbox. The problem is you will most likely run into a limitation of the control at some point. You may need the list to be sortable by clicking on the headers or you might need to customize how some data is displayed. Neither of these can be done with the DatabaseQuery control. The code to do the same functionality is very simple (just a handful of lines) and it will give you the ability to expand and grow. It will probably be cheaper to invest the time upfront to populate the list yourself than it will be to go back and change the way your application works.
Here is a quick generic function to populate a listbox. This should help you get started.
Sub PopulateListbox(lb as listbox, db as database, sql as string)
// Populate the listbox control which is specified in the first
// parameter with the resulting records from the data and sql
// that are provided.
// clear the listbox
// run the query
dim rs as recordSet = db.sqlSelect( sql )
if rs = NIL then return
// populate the listbox
while not rs.eof
lb.addRow ""// add a new row
for i as integer = 1 to rs.fieldCount// add data to columns
lb.cell( lb.lastIndex, i-1 ) = rs.idxField( i ).stringValue
rs.moveNext// move to next record
rs.close// close the recordset
2. Instantiate your database connection in code rather than adding it to your project. If you are using a database that requires a file connection it is much easier to know exactly what database file the application will access if you specify and connect to the database via code. When you add the database to your project, does that mean the database has to be next to your application or in the same location that it was when you added it? What about when you move your application to a different machine or completely different platform? These questions won't come up if you connect to your database via code since you will be telling your app specifically where the database is.
If you are connecting to a remote server you can have the user enter their username and login for the database if you connect to it via code. If you add the connection in your project you have to provide a username and password for that connection and then all users will be logging in with that account.
This also keeps your server from having to take the extra CPU cycles to keep authenticating your connections.
See tip #13 for more information about connecting to a REALSQLDatabase file.
3. Keep your database connection open until you are done with the database. The original database engine that shipped with REALbasic 2.0 through version 4.5 had a reputation of easily becoming corrupted. One of the ways to reduce this was to open the database whenever you were accessing data and then close it. There is no longer any reason to do this and can actually just waste time (development and cpu cycles) to implement.
Aside from the efficiency, using a single connections allows you to properly lock records on servers and use other features which require a static connection.
4. Use transactions whenever you update your data. Always start a transaction before you modify data. Some engines, such as the SQLite engine, actually improve in performance when a transaction is active. It also gives you the ability to rollback changes if something goes wrong.
To start a transaction you can use SQLExecute and pass in "begin transaction". Note that your specific engine may have a different syntax for starting a transaction.
After you are done modifying your records then you should either commit or rollback your changes. To keep the changes you will use Database.Commit, or to discard all changes since the transaction was started you would use Database.Rollback.
5. Use the error properties on the Database class! When you call SQLExecute or SQLSelect and you have an error in your SQL statement then nothing will happen. It can sometimes be hard to figure out why your code is not working. When the database engine encounters an error because of a bad SQL command the Database class has three properties: Error as boolean, ErrorCode as integer, and ErrorMessage as string. You can check these properties to get information on what went wrong. A lot of times the ErrorMessage will even tell you approximately where the error is in your SQL command. Use defensive coding and check for errors after executing commands.
6. Transactions are not a cheap undo mechanism. When you start a transaction and modify a table, that table may become locked until you close that transaction. What does this mean? Other users may grind to a halt when trying to access certain tables. Transactions should always happen very quickly. Start the transaction, modify the data, close the transaction. If you have the user interacting with data inside of that process you can have a situation where your database becomes unusable until that user finishes their task. You will also want to be mindful that some database engines have a timeout period for transactions, which means they will be closed for you if they are open too long.