|Real Software Forums
|Tips for using databases with REALbasic
|Page 1 of 2|
|Author:||Mike Bailey [ Fri Apr 21, 2006 2:41 pm ]|
|Post subject:||Tips for using databases with REALbasic|
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)
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.
|Author:||Mike Bailey [ Thu Sep 14, 2006 10:10 pm ]|
7. When looping through a RecordSet use the EOF property to determine when you have reached the end of a RecordSet instead of the RecordCount property. Some database engines support forward only cursors which make some functions such as MovePrevious, MoveFirst, and RecordCount not work properly. The EOF flag will always work:
while not rs.eof// keep looping until we hit the end
If you are using a database engine that you know supports RecordCount then you can use it for looping, but keep this in mind if you are ever going to switch backend engines.
|Author:||Mike Bailey [ Fri Sep 15, 2006 8:30 am ]|
8. Escape textual data when building a SQL statement. The SQL syntax uses some characters as tokens, such as the apostrophe or single quote. This character denotes the beginning or ending of a string literal. If you are building a SQL command and inserting text data what will happen if this data contains a single quote in it. For example: Username O'Reilly
select * from users where name = 'O'Reilly'
This will cause an error because the SQL parser is going to look at this and start the string literal before the O character and end it before the R character. The way to fix this is to tell the parser that quote is part of the string literal and is not marking the end of it. You do this by escaping the single quote with a second one so it will look like this:
select * from users where name = 'O''Reilly'
Here is a function that can help:
Function EscapeQuotes( data as string ) as string
Now you can build a statement like this:
sql = "select * from users where name = '"+ EscapeQuotes( userFld.text ) +"'"
|Author:||Jym [ Sun Sep 17, 2006 2:37 pm ]|
9. Encoding can get you into a mess of trouble when you are attempting to insert / search /update a field. (on Windows) If the user has pasted text to an Editfield with different encoding, and that's how you save the data, or you use that pasted text to search for data with different encoding, you aren't going to get the desired results.
You can change the encoding of Text in a Method, in a similar way ( 8 ) Mike has doubled up the single quotes. Personnally I like to do both in one Method, so Mike's method would become.
Function SQLiteFriendly (data as string) as string
|Author:||Mike Bailey [ Thu Sep 21, 2006 3:30 pm ]|
10. Adding variables into your SQL command means that you need to be able to modify the SQL command at runtime. There are two basic techniques to do this. The first one is to build your SQL command on the fly and add in the data where it is necessary. The other one is to put tags in your SQL command to mark where the data should be placed, and then inject the data before you run the command.
This example shows how to add values from controls into your SQL command:
// In this example we build the SQL command by piecing it together.
Both of those result in the same SQL command so you can choose whichever technique works best for you. Also refer to tip 8 and 9 to make sure your data is encoded correctly when using it in a SQL command.
Note: See tip 24 for some more advanced functions to help with this.
|Author:||Mike Bailey [ Thu Sep 28, 2006 10:03 am ]|
11. Storing a picture into a database. This tip will show you one way to store a picture in a database. Since all database plugins may not support the JPEGColumn properties I am going to show how to do this without the use of those properties. What I will do instead is have two functions to convert between a picture and a string. Those two functions are:
Function PictureToString(p as picture) As string
Now we have some code that shows how to use these functions for both putting a picture into a database and then pulling it out again. For this example just put a pushbutton on a window with the following code:
// create a new in-memory database
When you run this you should see that the picture was put into the database, then retrieved, and displayed in the window.
Edit: You should also look at the PNG Utilities plugin (by Frank Condello) which provides you with a way to convert pictures to strings and back again. This can be used in place of the two conversion functions above. You can find the PNG Utilities plugin at:
|Author:||Mike Bailey [ Thu Nov 09, 2006 9:26 am ]|
12. Checking for errors. Use the Database.Error, ErrorMessage, and ErrorCode properties to check for errors which the engine is reporting. The values of these properties correspond to the last action executed with the database. For example take the following code:
db.sqlExecute "delete from myTable"// delete records
The trick with this code is that the line which displays the MsgBox will show the ErrorMessage which results from the Rollback, not the delete. This is because the Rollback is the last action taken on the database and the error properties are reset for the result of that action.
This is also very important when using a loop. In the example below, the code will only display an error if the last iteration through the loop fails. In other words the first 4 inserts could all fail, but if the last one is successful, this code will not report an error:
for i = 0 to 5
You need to move the error checking inside of the loop after the action is executed by the database. This way you will get a more accurate error report for that routine:
for i = 0 to 5
|Author:||Mike Bailey [ Tue Nov 21, 2006 3:09 pm ]|
13. Creating and connecting to a REALSQLDatabase in code. After you assign a folderitem to a REALSQLDatabase you need to connect to the database or create the database, but you do not need to do both. The REALSQLDatabase.CreateDatabaseFile call will create the database and establish a valid connect, so you do not need to call Connect after creating a new database. Here is an example of how to handle this situation:
dim db as new REALSQLDatabase
Note - if you do not provide a DatabaseFile then an in-memory database will be created. The data will be discarded when the database is closed.
|Author:||Mike Bailey [ Fri Jan 19, 2007 9:29 am ]|
14. This tip refers to the REAL SQL Database which is based on the SQLite engine. SQLite tables always have a ROWID column. If you create a column which is an integer primary key, then that column will be an alias to the ROWID.
I recommend always creating a column to be an integer primary key if you are going to use the rowid data. One reason to do this is so that the column's data will appear when you do a 'select * from table'. If you have not explicitly created a column then it will not be returned with that statement.
If you are using the rowid value to establish relationships between tables then you definitely want to create an explicit column. If you don't have a column, when you do a VACUUM on the database, it will rearrange the rowid values which could cause your relationships to break. The values will not be modified if you have an explicit column in the table.
In short, if you are creating a table and plan to use the ROWID value then you should always create an explicit column for this. This is especially true if you are using it to related data in other tables where the value cannot change. This is not as important when using the value to just select, update, or delete records.
Update: I have received confirmation from the developer of SQLite:
"Rowids can change at any time and without notice. If you need to depend on your rowid, make it an INTEGER PRIMARY KEY, then it is guaranteed not to change."
As I mentioned earlier in the tip, if you are planning on using the value for anything where the value cannot change then you should definitely create a column for it.
|Author:||Mike Bailey [ Thu Mar 01, 2007 8:42 am ]|
15. This tip talks about how to move records from one database file to another. This can be useful in many different situations. For example you may have an in-memory database that you now want to write out to disk, or maybe you just need to load data into the in-memory database, etc.
The REALSQLDatabase allows you to 'attach' databases, which essentially combine all of the tables so that they can be used together. When you attach two databases you can even run queries that do joins across the database files. We will use this feature to migrate the data.
The first step is to open the database that you want to move the data into. Create any tables that might be necessary for this database if they do not already exist. Now we attach the second database by using REALSQLdatabase.AttachDatabase, and passing in the folderitem for the second database, and a name for the database. The name is used when you need to reference tables from the attached database.
After the database has been attached then you can copy the records over. To do this you just need a single "INSERT from SELECT" statement per table that you want to move. This statement looks like this:
INSERT INTO customers FROM select * from db2.customers
Here is some sample code to export data from an in-memory database (memdb):
dim db as new REALSQLDatabase
|Author:||Mike Bailey [ Wed Apr 18, 2007 11:19 am ]|
16. Verify a user login information that is stored in a database. This question has come up a number of times where there is a need to check user login credentials against data that is stored in a database.
One common answer is to select the user's password then check to see if the password they enter matches. This works, however there is a better more secure solution to this problem.
The problem getting the password from the server and checking it on the client is that the real password has been provided to the client. This provides opportunity for the password to be sniffed out, while it is sitting in memory or being sent over the network to the client. Somebody could potentially get access to the user's real password by just entering a fake one and seeing what is sent to the client and compared with.
How can we do the same thing without exposing this security hole? We make the server do all of the work. You send the server both the username and password which is being checked and you let it do the comparison. Now the actual user password is never being sent or stored to the client application. Here is an example:
dim sql as string
For extra security you might want to consider hashing or encrypting your passwords on the server, so that they are not stored in plain text.
|Author:||Mike Bailey [ Thu Apr 26, 2007 8:44 am ]|
17. Store variable values in the database. This is especially useful if you are using a database server that has multiple clients connecting to it. You can store variable values in the database rather than hardcoding them in the clients. That way if they ever need to change you do not need to update all of the clients for a simple value change.
For example, lets say you send out an email from your application and these emails come from a particular address. Instead of hardcoding that email address and then having to update your entire application if the address changes, just store the email address in the database and query for the value. Then you just need to update the database and your clients will automatically start using that address without having to issue a new build.
The schema for this table can be very basic. You just need the name and a value.
create table variables (id integer primary key, name varchar, value varchar)
You can then have a simple function to read the values:
Protected Function ReadDatabaseVariable(name as string) As string
You can also have the equivalent for setting a variable value.
|Author:||Mike Bailey [ Sat Apr 28, 2007 12:39 pm ]|
18. Updating the database - There are times when the new version of your application includes changes to the database schema. This tip talks about how to have your application update an existing database schema.
The first thing that you need is a mechanism to know when the schema needs to be updated. I use the variables table mentioned in the tip above, with a variable called 'schema_version' that contains an integer value of the schema version of the database. The application also has a constant which defines the version of the schema that it is expecting.
When the application starts up it checks the database version with its own version. If the version is the same then the database is up to date. If the database version is newer than the application you have a situation where the database might not be compatible. I suggest having a second database variable which defines the schema compatibility version. Your application can then tell if it can run with the newer schema.
In the situation where your application's schema version if greater than the schema version of the database, then it is time to update the database to the latest schema. Remember, that you need to handle the situation where the user's might be more than 1 version behind. Instead of writing an update routine for every possible upgrade path, we will solve this by updating the schema version 1 by 1 until we are at the current version.
For example instead of: v2 -> v4
The update will look like: v2 -> v3 -> v4
An example of what the update function might look like:
|Author:||Mike Bailey [ Mon May 07, 2007 10:47 am ]|
19. Encrypting your data with REAL SQL Database. The REAL SQL Database supports encrypting your database so that you cannot open the database file unless you know and provide the correct encryption key. The data is encrypted with AES-256bit encryption, which prevents the data from being read with a text or hex editor.
To create or connect to an encrypted database all you have to do set the REALSQLDatabase.EncryptionKey property to the key, then connect or create your database. The rest of the code is exactly the same as it is for unencrypted databases. This example will create a new encrypted database or connect to one if it already exists:
Private Function ConnectToDatabase() As REALSQLDatabase
If your database already exists in the unencrypted format then you can use the REALSQLDatabase.Encrypt function to encrypt it. After the file is encrypted then you must provide the EncryptionKey when connecting to it, as shown above. The same goes for decrypting an encrypted database so that it can be opened without the use of the EncryptionKey, except you would use the REALSQLDatabase.Decrypt function. Both of these functions require that you be connected to the database:
dim db as new REALSQLDatabase
|Author:||Mike Bailey [ Wed May 16, 2007 7:31 am ]|
20. There are some things that I see a lot of users do with RecordSets which are not necessary.
Creating a new instance of a RecordSet before calling SQLSelect. This is a wasted operation as SQLSelect will return an instance of the recordset which will just overwrite the instance you created and destroy it.
dim rs as recordSet
Also, when you get a RecordSet back from SQLSelect it is pointing at the first record, which means you do not need to call RecordSet.MoveFirst on it. This call will have no effect on your recordset since that is the record you are already viewing:
rs = db.sqlSelect( sql )
|Page 1 of 2||All times are UTC - 5 hours|
|Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group