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)
// 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
End Sub

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 ]
Post subject: 

New tip:

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
// process current record
rs.moveNext// move to next record

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 ]
Post subject: 

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
return replaceAll( data, "'", "''" )
End Function

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 ]
Post subject: 

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
data = ReplaceAll(data, "'", "''")
Return ConvertEncoding(data, Encodings.UTF8)
End Function

Author:  Mike Bailey [ Thu Sep 21, 2006 3:30 pm ]
Post subject: 

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.
// We add in the variable values as they are needed.
sql = "select * from users where name = '"+ editField1.text +"'"+ _
" AND Type = '"+ popupMenu1.text +"'"

// This example will have the complete SQL statemnt pre-built in a
// string literal. You will notice that the string contains a tag
// that marks where data should be placed. The tag in this example
// are the two characters ?1. To put the data in place of the tag
// we use the Replace function.
sql = "select * from users where name = '?1' AND Type = '?2'"
sql = replace( sql, "?1", editField1.text )
sql = replace( sql, "?2", popupMenu1.text )

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 ]
Post subject: 

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
// Convert a picture to a string by saving the picture
// out to a file on disk and then reading in the binary
// data from that file.
dim f as folderItem
dim bs as binaryStream
dim data as string

// save picture to a temporary file
f = getTemporaryFolderItem()
f.saveAsPicture p

// read the data in from the temp file
bs = f.openAsBinaryFile( false )
data = bs.length )

// remove temporary file

// return the binary data
return data

exception// an exception occurred preventing the conversion
return ""
End Function

Function StringToPicture(data as string) As picture
// Convert a string to a picture by saving the binary data
// to a file on disk and then reading the file as a picture.
dim f as folderItem
dim bs as binaryStream
dim p as picture

// write binary data to temporary file
f = getTemporaryFolderItem()
bs = f.createBinaryFile( "any" )
bs.write data

// open the file as a picture
p = f.openAsPicture()

// remove temporary file

// return the binary data
return p

exception// an exception occurred preventing the conversion
return NIL
End Function

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
dim db as new REALSQLDatabase
if not db.connect() then

// create a table
db.sqlExecute "create table pictures (name varchar, picture binary)"

// generate a picture to be inserted into the database
dim inPicture as new picture( 50, 50, 32 ) = &c000088 0, 0, inPicture.width, inPicture.height

// insert a picture
dim rec as new databaseRecord
rec.column( "name" ) = "square"// set the name column
rec.blobColumn( "picture" ) = pictureToString( inPicture )// set the picture column
db.insertRecord "pictures",rec// insert the record
db.commit// commit the changes

// retrieve a picture from the database
dim rs as recordSet
dim outPicture as picture
rs = db.sqlSelect( "select * from pictures where name = 'square'" )
outPicture = stringToPicture( rs.field( "picture" ).stringValue )

// display the picture
self.backdrop = outPicture

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 ]
Post subject: 

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
if db.error then// check for errors on deleting the records
db.rollBack// rollback changes
msgBox db.errorMessage// display error message

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
db.sqlExecute "insert into numbers ( x ) value ("+ str(i) +")"
if db.error then// check for errors (this code is NOT correct)
msgBox db.errorMessage

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
db.sqlExecute "insert into numbers ( x ) value ("+ str(i) +")"
if db.error then// check for errors
msgBox db.errorMessage

Author:  Mike Bailey [ Tue Nov 21, 2006 3:09 pm ]
Post subject: 

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
db.databaseFile = getFolderItem( "CustomerData.rsd" )

if db.databaseFile.exists then
// if the database file exists then we just need to connect to it
if not db.connect() then
// handle connection error

// the database file does not exist so we will create a new one
if not db.createDatabaseFile() then
// handle error creating database


// the database has a valid connection at this point

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 ]
Post subject: 

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 ]
Post subject: 

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
db.databaseFile = getFolderItem( "data.rsd" )
if db.createDatabaseFile() = false then// create and connect to the database
return// add some error checking here
// create the customers table
db.sqlExecute "create table customers (name varchar, email varchar)"
db.close// close the database since it will be opened when we attach it

// attach the database file to the in-memory one
if not memdb.attachDatabase( db.databaseFile,"db2" ) then
return// database could not be attached - add error checking

// copy the records
memdb.sqlExecute "begin transaction"
memdb.sqlExecute "insert into db2.customers from select * from customers"

// detach the database
memdb.detachDatabase "db2"

Author:  Mike Bailey [ Wed Apr 18, 2007 11:19 am ]
Post subject: 

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
dim rs as recordSet
dim userID as integer
// make sure user and password are properly escaped for sql (see above tip)
sql = "select ID from Users where Name = '"+ name +"' AND Password = '"+ password +"'"
rs = db.sqlSelect( sql )
userID = rs.idxField( 1 ).integerValue

// make sure we got back a valid user id
if userID = 0 then
msgBox "Login failed. Your username or password is not correct."

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 ]
Post subject: 

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
dim rs as recordSet
dim sql,value as string

sql = "select value from variables where name = '"+ replaceAll(name,"'","''") +"'"
rs = db.sqlSelect( sql )
value = rs.idxField( 1 ).stringValue

return value
End Function

You can also have the equivalent for setting a variable value.

Author:  Mike Bailey [ Sat Apr 28, 2007 12:39 pm ]
Post subject: 

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:
Sub CheckSchemaVersion()
dim dbSchemaVersion as integer = ReadDatabaseVariable( "schema_version" )

// The database is current
if kAppSchemaVersion = dbSchemaVersion then

// The database is newer than the application is expecting. We should
// check compatibility here to see if it can be used.
if kAppSchemaVersion < dbSchemaVersion then
if kAppSchemaCompatible > ReadDatabaseVariable( "schema_compatible" ) then
msgBox "This application is not compatible with this database."

// Our database needs to be updated. Let's update each schema version until
// we are current.
dim i as integer
for i = dbSchemaVersion + 1 to kAppSchemaVersion
select case i
case 2// added users table
db.sqlExecute "create table users (id integer, name varchar)"

case 3// email column was added to users
db.sqlExecute "alter table add column email varchar"

case 4// indexed the user name column
db.sqlExecute "create index user_name_idx on users (name)"


// Now update the schema version in the database
SetDatabaseVariable "schema_version", kAppSchemaVersion

// Commit the changes
End Sub

Author:  Mike Bailey [ Mon May 07, 2007 10:47 am ]
Post subject: 

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
// Setup the database connection and specify an encryption key
// since we want the database to be encrypted.
dim db as new REALSQLDatabase
db.databaseFile = getFolderItem( "data.sdb" )

// This is where we set the key that will be used to encrypt the database
db.encryptionKey = "supersecret"

if not db.databaseFile.exists then
// the database needs to be created
if not db.createDatabaseFile() then
return NIL

// create the schema
db.sqlExecute "create table users (name varchar, password varchar)"
db.sqlExecute "insert into users (name, password) values ('mike','hidden')"

// the database already exists so we just need to connect to it
if not db.connect() then
return NIL


// now we are connected to our encrypted database
return db
End Function

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
db.databaseFile = getFolderItem( "test.sdb" )
if db.connect() then// connect to the database
db.encrypt "supersecret"// encrypt the database with the key provided

Author:  Mike Bailey [ Wed May 16, 2007 7:31 am ]
Post subject: 

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
// this line of code is not necessary
rs = new recordSet

// here we destroy the recordset that we just spent time creating
// and assign the recordset from the SQLSelect function to that variable
rs = db.sqlSelect( sql )

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 )
rs.moveFirst// we are already on the first record so we do not need this
while not rs.eof

Page 1 of 2 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group