The Coded Database

The next example works with the database without using a DataControl control. This will step through the process of creating a similar database interface; the primary difference is that I rely much less on controls and binding for my functionality. This makes it a little more code intensive, but it also gives me more flexibility in how I manage the user interface, and it will enable me to add the features I just discussed in the previous section.

You can see the interface in Figure 8.14. Window1 contains a three-column ListBox (ListBox1) that will be used to display a list of RSS feeds that have been subscribed to. Two EditFields, named EditField1 and EditField2, are used to display the name of the feed and the URL of the feed, respectively. Finally, there are three buttons labeled Delete, Clear, and Save.

Figure 8.14. The user interface for the coded database sample project.

The Database Classes

To re-create the database using code, I need to introduce some additional classes. There are four database-related classes that I will be dealing with in this example. The first is the Database class itself, the parent class of all database types in REALbasic. The next is RecordSet, an instance of which is returned after calling the Database.SQLSelect method. Finally, there is DatabaseRecord and DatabaseField. You will use these classes primarily to create new records to insert into the database.

RecordSet and DatabaseRecord perform similar functions, and both use DatabaseField. Unfortunately, a RecordSet is not an array or collection of DatabaseRecords. It's an object all its own and it even uses some of its own terminology (for instance, a RecordSet has fields and a DatabaseRecord has columns) so that how the two work together may not be clear at first. Remember that RecordSet is a collection of existing records, so you can use it to do things to an existing record, such as updating it or deleting it. A DatabaseRecord is a new record, so you do with it what you would normally do with a new record, which is to insert it into a database.

Database Class

You've already seen the Database class in the "code-free" example when you created the database and established the tables using REALbasic's database editor. When a database is created that way, it is implicitly instantiated and you can refer to it by using its name. Some limitations exist to creating a database that way, the primary one being that it creates the database file and places a reference to it in your project. But what happens if that file is inadvertently deleted? If that happens, you would have to create the database "by hand" all over again. A better approach is to do it programmatically, which is how this example will be doing it.

The Database class is the parent class for all of REALbasic's database plug-ins that are available in the Professional edition. As such, it defines the generic REALbasic database API. There are many occasions when the properties and methods of the Database class are sufficient for all your needs. In fact, if you think you might change database back-ends in the future, you should try to rely on the members of the Database class primarily, and in those situations where you use database-specific features, try to avoid intermingling code that uses them throughout the application. Isolate it in its own method or methods so that you can change it more easily next time.

Database Properties

All databases have a name, which should be one word (alphabetical characters, plus an underscore) and assigned to the following property:

Database.DatabaseName as String

Later on in the chapter, you will see that you can attach a SQLite database to another SQLite database and execute SQL queries across databases. To do that, you have to refer to the fully qualified name of the field, which would be something like this:

aDatabaseName.aTableName.aFieldName

Note that the database name is not necessarily the same as the name of the file that stores the data for the database. When you create the database in the IDE, the database name is used for the filename (with the appropriate extension), but it is possible to change the database name so that it is different. When you create a database programmatically, you have to specify the filename and the database name explicitly, and you can choose to have them be the same or not. It's entirely up to you.

Like many other classes in REALbasic that are built using external libraries, the Database class takes a non-object-oriented approach to handling errors, using the following properties:

Database.Error as Boolean Database.ErrorCode as Integer Database.ErrorMessage as String 

Usually, you test for the presence of an error and then respond to it, using code like the following:

If Database1.Error Then   MsgBox Database1.ErrorMessage Else   // Continue End If 

When connecting to a multiuser database such as MySQL, you will need to have the following properties set (and the database should also be configured appropriately):

Database.Host as String Database.Password as String Database.UserName as String 

The host refers to the machine on which the database server is running. It can be either the hostname or an IP address. If the database server is running on the same machine as the application, you could use either Localhost or 127.0.0.1. You would also need to include the port number if it's running on a nonstandard port.

Database Methods

To access any database, you must first connect to it. Likewise, you can disconnect by calling the Close method.

Database.Connect as Boolean Database.Close 

The following two methods provide the means for executing SQL statements. The SQLSelect method is used only for Select statements, and it is the only one of the two methods that gets a value returned to it, which is in the form of a RecordSet. You can use the RecordSet to update or delete individual records. To find out if the statement executed properly, you need to check the error properties described in the properties section. All the other associated methods and classes are optional because you can do everything the database classes do, and a lot more, by relying on SQL statements rather than using the classes directly.

Database.SQLSelect(SelectString as String) as RecordSet Database.SQLExecute(ExecuteString as String) 

You cannot delete or update records directly with a Database object. You are limited to being able to insert new records, for which you need to have instantiated a DatabaseRecord object that gets passed to the Database object.

Database.InsertRecord(TableName as String, Data as DatabaseRecord)

Regardless of how the record gets updated or inserted, the transaction is not complete until you call one of the following methods:

Database.Rollback Database.Commit 

If you have inserted a record and then change your mind, you can call Rollback, and the changes will be eliminated. Likewise, when you are sure everything has gone according to plan, you can commit the changes. This is how you can manage transactions in REALbasic. In database terms, a transaction is a series of SQL statements that are grouped together and identified as a particular transaction. The Commit and Rollback methods apply to the series of statements, rather than any individual statement. The reason is that you may have to insert data into three different tables and you need to make sure all three inserts worked, without error. If one of the inserts works, you need to roll back the changes caused by the previous inserts, and Rollback and Commit let you do that. When you use the DataControl, you can set a property in the IDE to tell REALbasic to automatically commit inserts, deletes, and updates. When using the Database class directly, you need to manually Commit or Rollback TRansactions. As with everything else, you can use SQL to manage the process for you with the statements BEGIN TRANSACTION and END TRANSACTION.

Finally, you can get information about the database tables and fields themselves using the following methods. At times, you may be accessing a table whose structure you do not know, or one that possibly could have changed. Being able to ask the database to describe itself can make it easier to manage these situations.

Database.TableSchema as RecordSet Database.FieldSchema(TableName as String) as RecordSet Database.IndexSchema(TableName as String) as RecordSet Database.GetProperty(Name as String) 

The GetProperty method currently applies only to the 4D server, so you can ignore it for all other databases.

REALSQLdatabase

The Database subclass I use in the examples is REALSQLdatabase, which is the new REALbasic database standard, based on SQLite. All of the database plug-ins are subclasses of Database, so most of what is discussed here is applicable with any database.

REALSQLdatabase Properties

SQLite is a single-user database, sometimes called an embedded database because it is often used within a single application as a means of storing data for that application. Most of the other databases you can use with REALbasic are multiuse databases that you access through a database server available on the network. As such, the REALSqlDatabase class has the following property that points to the file in which the data gets stored.

DatabaseFile as FolderItem

This serves as an alternative to setting the Host, Username, and Password properties in the Super class.

Whenever you refer to individual fields (or columns) in a SQL statement, you have two choices. You can use the fully qualified name, which is aTableName.aFieldName, or you can reference just the field name. The following property determines how REALbasic will handle the names when returned in a RecordSet:

ShortColumnNames as Boolean

This property is a little confusing, because the name isn't really descriptive of what it does. Basically, this is what it means: If the property is set to TRue, REALbasic will return the so-called short column names, which is the field name without the preceding reference to the tablethat is, unless there is some ambiguity that requires a fully qualified name. This situation usually arises when you have two tables with fields that have the same name. It also returns a fully qualified name when returned aliased fields. If the property is set to False, it relies on the format that you used when writing the SQL query. If you used fully qualified names in the query, the results will include fully qualified names.

REALSQLdatabase Methods

Although you can create a reference to a database file using the DatabaseFile property, I have yet to tell you how to create a database file in the first place. There is no FolderItem method to help you, like there is for creating text files and directories, but there is a REALSQLdatabase method that will create the file for you. You must first assign a reference to a FolderItem object to the DatabaseFile property, and if that FolderItem does not exist, you can create it by calling the following method:

REALSqlDatabase.CreateDatabaseFile

The following methods allow you to attach and detach other databases to this particular database object:

REALSqlDatabase.AttachDatabase(file as FolderITem, databaseName as String) REALSqlDatabase.DetachDatabase(databaseName as String) 

The benefit to doing this would be to be able to perform SQL Select queries across multiple database files, something that is normally limited to being executed across tables within a single database. Although JOINS have not been covered yet, the following code provides a quick example to clarify how you identify fields within the different databases:

SELECT * FROM database1.thistable, database2.thattable WHERE database1.thistable.thisfield=database2.thattable.thatfield 

The following returns a string representing the database schema, which is basically the blueprint for the database, describing what tables exist, what their fields are, and so on:

REALSqlDatabase.GetSchemaData() as String

Finally, the following helpful method returns the ID of the last row that was add to the database:

REALSqlDatabase.LastRowID() as Integer

You will use this when you are updating two different tables whose data are related. For example, I might have an additional table in the RSSSub database that contains additional information about individual sites that I have subscribed to. After I add a new subscription to the Subscriptions table, I will also want to add a new record to the Feeds table with the data from the feed associated with that particular Subscription. After adding the Subscription record, I can call the LastRowID method to get the RowID number from the database, and I can use that number as the value of a field in the Feeds database that will link that Feeds record to that particular Subscription record.

RecordSet

RecordSets are the results of a SQL Select statement. The class provides methods for navigating the sequence of records, as well as for modifying and deleting them. One thing to note is that you use a RecordSet only for updates and deletions and not for inserts. The reason for it is that a RecordSet can exist only as the result of a Select statement, and a Select statement returns existing records from a table or group of tables.

Properties

The properties of RecordSets are primarily used to help you iterate through a series of records. The following two properties can tell you whether you are at the beginning of the RecordSet (BOF) or at the end of the RecordSet (EOF):

RecordSet.BOF as Boolean RecordSet.EOF as Boolean 

The next property tells you how many records are contained in the RecordSet:

RecordSet.RecordCount as Integer

Finally, this property will let you know how many fields exist within a given record. You can use this when you do not know in advance the names of the fields available in a record and need to refer to them by number:

RecordSet.FieldCount as Integers

Methods

If you are going to modify the values of a record to update it, you must first call the Edit method:

RecordSet.Edit

If you have changed the value of a field in a record and want to save that change, you can call the Update method:

RecordSet.Update

Likewise, if you want to delete a record, use the following:

RecordSet.DeleteRecord

There is one important caveat to add to the use of the previous two methods. JOINS are discussed later on in the chapter, but it is worth mentioning them now. A JOIN is a kind of Select statement that pulls fields from two or more tables. A single RecordSet is returned as a result of the statement. The reason I mention it now is because the Update and Delete methods of RecordSet will not work with a RecordSet that exists as the result of a JOIN.

RecordSet.Field(Name as String) RecordSet.IdxField(Index as Integer) RecordSet.MoveFirst RecordSet.MoveLast RecordSet.MoveNext RecordSet.MovePrevious RecordSet.Close 

DatabaseRecord

A DatabaseRecord object represents a single record that is to be inserted into a table.

DatabaseRecord Properties

DatabaseRecord.BlobColumn DatabaseRecord.BooleanColumn DatabaseRecord.Column DatabaseRecord.DateColumn DatabaseRecord.DoubleColumn DatabaseRecord.IntegerColumn DatabaseRecord.JPEGColumn DatabaseRecord.MacPictColumn 

DatabaseField

Both RecordSets and DatabaseRecords use DatabaseField objects to represent individual fields (or columns) in a table.

SQLite is not a strongly typed database (although the other databases available to Professional edition users are strongly typed). It uses affinity typing, which means that SQLite will accept any string as valid data for any field. At the same time, you can define a type for the field, which will help developers know how to interpret the output. The DatabaseField class provides several properties that returned the value of a specific field as a specified type. This works much like it does with the Variant class. If you know that the data is a Boolean value, you can access the data like so:

Dim b as Boolean b = DatabaseField1.BooleanValue 

Here is the complete list of properties:

DatabaseField.BooleanValue as Boolean DatabaseField.DateValue as Date DatabaseField.DoubleValue as Double DatabaseField.IntegerValue as Integer DatabaseField.JPEGValue as Picture DatabaseField.MacPICTValue as Picture DatabaseField.Name as String DatabaseField.StringValue as String DatabaseField.Value as Variant 

The Coded Application

The biggest difference in the "coded" version of the database takes place in the App.Open event. First, unlike when you used the IDE to create the database, you must first assign a reference to the new database somewhere. In this example, I created a property for App called RSSSub of type REALSQLdatabase. Then, in the Open event, I have to do the following, because this is what I want the application to do:

1. I want it to open the database from a database file.

2. If the file doesn't exist, I want it to create the database and add a few default records.

3. I want the database reference to be a property of the App class so that it remains in scope throughout the lifetime of the application.

Most of the logic takes place in the App.Open event, shown in Listing 8.1:

Listing 8.1. App.Open()

Dim dbFile as FolderItem Dim dr as DatabaseRecord // Instantiate the database RSSSub = New REALSQLDatabase // Get a reference to the database file // and associate the file with the database dbFile = GetFolderItem("RSSSub") RSSSub.DatabaseFile = dbFile // Check to see if the database file exists // and then try to connect to it. If this // were not a sqlite database, but a multiuser // database like MySql, you would need to have // the username and password properties set. If RSSSub.DatabaseFile.Exists Then   If RSSSub.Connect Then     //All is wellElse     MsgBox "Could not read database file"   End if Else   // If the file does not exist, create the database file   // using the following method.   If RSSSub.CreateDatabaseFile Then     // Connect to the new database     If RSSSub.Connect Then         // Create the new table using SQL        RSSSub.SQLExecute("CREATE TABLE Subscriptions" _           + "(Name VarChar UNIQUE, URL VarChar NOT NULL)")        If RSSSub.Error Then           MsgBox "Error Creating Database: " _             + RSSSub.ErrorMessage        End If         // Insert a new record using the         // DatabaseRecord class.         // You could also use SQL to insert         // the record as well.         dr = New DatabaseRecord         dr.Column("Name") = "choate.info"         dr.Column("URL") = http://choate.info/ _              +"Blog/Business/atom.xml"         RSSSub.InsertRecord("Subscriptions", dr)         If RSSSub.Error Then           MsgBox RSSSub.ErrorMessage         End If         // Insert another record         dr = New DatabaseRecord         dr.Column("Name") = "REAL Software"         dr.Column("URL") = http://www.realsoftware.com/" _         +"xml/realsoftware.xml"       RSSSub.InsertRecord("Subscriptions", dr)       If RSSSub.Error Then         MsgBox RSSSub.ErrorMessage       End If      Else        // If you cannot connect to the new        // database file, then you should raise        // an error or respond appropriately        MsgBox "Could not create database file."      End If   End If End If // When the database was created in the IDE, I was able // to associate the database to the DatabaseQuery control // in the IDE itself, as well as assign the SQL statement to it. // In this case, since I am creating the database // programmatically I need to associate it with the control // after I have instantiated it. Window1.DatabaseQuery1.Database = App.RSSSub Window1.DatabaseQuery1.SQLQuery = "Select * From" _   + "Subscriptions" Window1.DatabaseQuery1.RunQuery 

DatabaseQuery1 automatically handles populating ListBox1, but I want the user to be able to select a row and have the name and URL for that particular record appear in EditField1 and EditField2, respectively. I can bind an EditField to a ListBox, but only at the row level, so I cannot bind EditField1 to the first column of ListBox1 and EditField2 to the second column. This means I need to manage this process myself. An interesting project would be to create a custom binding that enabled just that kind of data binding, but for now I'll take a simpler approach.

First, anytime the selection of ListBox1 changes, I want the data displayed in the EditFields to change. The code displayed in Listing 8.2 gets entered in the ListBox1.Change event.

Listing 8.2. ListBox1.Change Handles Event

Dim rs as RecordSet Dim aName as String Dim idx as Integer // Get the data from the cell. If the cell is // empty, or nothing is selected, then aName will // be empty. idx = Me.ListIndex If idx > -1 Then   // If the value for ListIndex is greater than   // -1, then the ListBox has a selection.   aName = Me.Cell(idx, 0)   // This assumes that aName is unique, so it searches for   // a record where the Name field is equal to aName   rs = App.RSSSub.SQLSelect("SELECT * FROM Subscriptions" _     +"WHERE Name='" + Trim(aName) + "'")   If App.RSSSub.Error Then   MsgBox App.RSSSub.ErrorMessage   Else     // The EditFields are populated with the data from     // the returned RecordSet. If the RecordSet is empty     // then the fields are emptied.     EditField1.Text = rs.Field("Name").StringValue     EditField2.Text = rs.Field("URL").StringValue     PushButton1.Caption = "Update"   End If Else   // Nothing is selected so   // set the caption of PushButton1   // to save.   PushButton1.Caption = "Save" End If 

One other thing I want to do is to allow the user to both update an existing record through the two EditFields and insert a new record through the EditFields. At the same time, I do not want to have two buttons, one for inserting and one for updating. Instead, I'd like to have one button labeled Update when the context is appropriate for updating and labeled Save when the context is appropriate for saving a new record, which would call for inserting a new record. One part of this process is to change the caption of PushButton1 to Update whenever a cell is clicked in ListBox1 and the contents of the record are displayed in the EditFields. The presumption is that the user has selected this particular row and any changes made to it will call for an update of that record. When nothing is selected in the ListBox, PushButton1.Caption will be set to Save instead.

In addition to binding a ListBox to a DatabaseQuery control, you can also bind PushButtons to ListBoxes. I will not go into the details here, but if you follow the steps outlined previously for creating a binding, you can bind the Delete button (PushButton3) with ListBox1 so that it is enabled only when there is a selection on the ListBox. I would also like to define how PushButton1 is enabled, and this depends on the state of both of the EditFields. I want PushButton1 enabled only when there is text for both EditFields. Therefore, I place the code in Listing 8.3 in EditField1's TextChange event and Listing 8.4 in EditField2's TextChange event.

Listing 8.3. EditField1.TextChange Handles Event

If (Me.Text = "") Or (EditField2.Text="") Then   PushButton1.Enabled = False Else   PushButton1.Enabled = True End If 

Listing 8.4. EditField2.TextChange Handles Event

If (Me.Text = "") Or (EditField1.Text="") Then   PushButton1.Enabled = False Else   PushButton1.Enabled = True End If 

PushButton2 is the button that is clicked to clear the EditFields to insert a new record into the database. Clearing the EditFields is simple enough, but I also want to check first to see if any data in the EditFields has changed and ask the user whether to update the current record with the data that exists in the EditField. There are a few ways of doing this, but in this example, I do a quick query to find out what the value is in the database itself and compare it with the value of the text in the EditField. If they are different, I prompt the user to see what the user wants to do. I could also subclass EditField, add a new property called something like OriginalText, and assign the value of that property when I populate the data in the EditField when a user selects a row in ListBox1. I could then simply check the current text of the EditField in question, compare it with the value of OriginalText, and then go from there.

I used this particular approach because I thought it would make for a simpler example without any subclassing, but also because there is always the possibility that the underlying database has changed since the query was run on the DatabaseQuery control. This allows me to check the current value, rather than the value that was current when the EditFields were first populated.

The code for the PushButton2.Action event is shown in Listing 8.5. This is also the place where the value of the caption for PushButton1 is set. If the fields are cleared to add a new record, the caption is changed to read Save.

Listing 8.5. PushButton2.Action Handles Event

Dim rs as RecordSet Dim res as Integer Dim b1,b2 as Boolean rs = App.RSSSub.SQLSelect("SELECT * FROM Subscriptions WHERE URL= '" + Trim(EditField2.Text) + "'") If App.RSSSub.Error Then   MsgBox App.RSSSub.ErrorMessage Else   b1 = rs.Field("Name").StringValue <> Trim(EditField1.Text)   b2 = rs.Field("URL").StringValue <> Trim(EditField2.Text)   PushButton1.Caption = "Save"   If b1 OR b2 Then     res = MsgBox("Fields have changed. Would you like to" _       + "update the record?", 35, "Save changes")   Else     If Trim(EditField1.Text) = "" Or Trim(EditField2.Text) = "" Then       EditField1.Text = ""       EditField2.Text = ""     End if   End If   Select Case res     Case 2 // Cancel       PushButton1.Caption = "Update"     Case 6 // Yes       rs.Edit       rs.Field("Name").StringValue = Trim(EditField1.Text)       rs.Field("URL").StringValue = Trim(EditField2.Text)       rs.Update       EditField1.Text = ""       EditField2.Text = ""     Case 7 // No       EditField1.Text = ""       EditField2.Text = ""   End Select   DatabaseQuery1.RunQuery End If 

Pushbutton1.Action is the event where the data is either updated or inserted into the database. The value of Pushbutton1.Caption is used to determine the proper course of action. If the caption reads Update, the record must be updated. I could update the record with a SQL statement or I could use a RecordSet instance, which is what I do here. Because the URL is a unique identifier, I do a Select statement for that URL, and the reference I get back is a RecordSet that contains one record, the one I want to update. See the code in Listing 8.6.

Listing 8.6. Pushbutton1.Action Handles Event

Dim dr as DatabaseRecord Dim rs as RecordSet If Me.Caption = "Save"     Then   dr = new DatabaseRecord   dr.Column("Name") = Trim(EditField1.Text)   dr.Column("URL") = Trim(EditField2.Text)   App.RSSSub.InsertRecord("Subscriptions", dr)   If App.RSSSub.Error Then     MsgBox App.RSSSub.ErrorMessage   Else     Me.Caption = "Update"   End If Else   rs = App.RSSSub.SQLSelect("SELECT * FROM Subscriptions WHERE URL='" _     + "Trim(EditField2.Text) + "'")   If App.RSSSub.Error Then     MsgBox App.RSSSub.ErrorMessage   Else     // You must call Edit before modifying any     // values in the RecordSet     rs.Edit     rs.Field("Name").StringValue = Trim(EditField1.Text)     rs.Field("URL").StringValue = Trim(EditField2.Text)     rs.Update   End If End If DatabaseQuery1.RunQuery 

Finally, when PushButton3 is clicked, I need to delete the current record. Again, much like the previous event, I have decided to get a reference to a RecordSet for that record represented in the EditFields and use the RecordSet to delete the record in question. However, this is advisable only if there is one record per URL, as shown in Listing 8.7.

Listing 8.7. PushButton3.Action Handles Event

Dim rs as RecordSet rs = App.RSSSub.SQLSelect("SELECT * FROM Subscriptions WHERE URL='" _  + Trim(EditField2.Text) + "'") rs.Field("Name").StringValue = Trim(EditField1.Text) rs.Field("URL").StringValue = Trim(EditField2.Text) rs.DeleteRecord If App.RSSSub.Error Then   MsgBox App.RSSSub.ErrorMessage Else   App.RSSSub.commit   DatabaseQuery1.RunQuery End If 

RowID

I used a slightly modified Select statement for displaying the data in the ListBox. I said earlier that the original statement would return all the fields and records from the Subscriptions table. That was only partially true, because SQLite also automatically generates an ID for each row. Most of the time you do not know it's there and do not need to worry about it, but if for some reason you need to know the value, you have to refer to it specifically in the statement. The following line of code can be added to the App.Open event to create a query that includes values for the row ID:

Window1.DatabaseQuery1.SQLQuery = "Select rowid,* From Subscriptions"