We are currently converting a FileMaker app to a Xojo web app. We are about 3/4 of the way through the project and it’s been a surprisingly easy conversion. Our biggest challenge has been normalizing the database since the original FileMaker developer did some things that were less than ideal.
Hal Gumbert over at Camp Software is starting a series of blogposts on their own transition from FileMaker to Xojo. It is a recommended read.
One of the big things that many developers want coming from FileMaker and MS Access and other tools where the database is tightly integrated into the development tool is data binding. It makes for a quick way to load/save data to and from the user interface. We don’t do data binding and I’ll get into that a bit later.
In Hal’s blog post he goes into the various Xojo options and ActiveRecord is one of them. I thought I’d spend a little time talking about ActiveRecord to fill you in on what it does.
ActiveRecord eliminates many common mistakes that developers have when creating database applications using Xojo. How many times have you mistyped a table or field name in an SQL query? We used to do it a lot and ActiveRecord eliminates much of it. It does this by creating a NameSpace module and creating a class for each table. The properties in those classes then map to the field in each table.
A register function for ActiveRecord uses Introspection to ensure you have all of the tables and fields from the database mapped in your classes. If not, an assertion takes place in debug mode which tells the developer if they’re missing a table, field, or if a field is mapped to the wrong datatype. This is very handy on large projects where you might be adding a bunch of fields to meet changing conditions and this way you definitely will not forget to add them to the ActiveRecord classes.
Creating the classes can be tedious especially with very large databases. Our ARGen utility will help generate the classes for your by scanning your database and creating the classes for you. For some this might seem backwards but we tend to design the database first and then code to it and we find that ARGen does 75% of the repetitive work for us by creating the classes and adding some shared methods to each class that help in queries and finding a particular record.
Once created, using ARGen is fairly simple. To get a list of records in normal Xojo code you would create a query. ActiveRecord does something similar using a class shared method. Here is an example of using the List shared method to load a ListBox:
for each oCompany as Data.T_Company in Data.T_Company.List(sCriteria, sSort, iOffset)
lst.AddRow oCompany.sCompanyNameCurrent, _
oCompany.sStreet1, oCompany.sCity, _
dim iRow as integer = lst.LastIndex
lst.RowTag(iRow) = oCompany
Data is the NameSpace and we are calling the T_Company List method and we pass in three parameters. The first is our search criteria, the second is the sort criteria, and the last is the offset which allows us to ‘page’ our data. It returns an array of Data.T_Company objects and we simply add what we need to the ListBox and stash the object in the RowTag event. The best part about this is that AutoComplete in the Xojo code editor will show us the table and field names and we don’t have to remember any of it.
When we wish to edit the record we grab it from the ListBox.RowTag property and pass it in to our editor.
dim oCompany as Data.T_Company = lst.RowTag(lst.ListIndex)
dim pg as new pgCompanyDetails
ActiveRecord doesn’t do data binding. We simply don’t find it useful for a variety of reasons. First, to do data binding your need to have controls that can handle the data source. We could create control subclasses but after working with custom data bindings in Xojo on a project or two I was not happy with the endless tweaking we had to do to get them to work properly. Maybe someone with more patience that I do will be satisfied with it but I never was. Plus, most developers I’ve met that have done data binding on large projects remain unsatisfied in some form or another or go to extraordinary lengths to make it ‘easy’ (like having every field be string even for things that should clearly be a numeric data type).
Instead we chose a much simpler route. In our edit forms we have three methods: Load, Save, Validate. We feel this offers us some advantages over binding. First, everything is local to the window. We don’t have to go find the subclass that handles the data load, save, and validate. This lets us customize everything for that particular form. An example Load method:
Private Sub Load()
if moCompany.IsNew then
lblCompanyID.text = "New"
pmStatus.Enabled = false
lblCompanyID.text = moCompany.iCompany_ID.ToString
pmStatus.Enabled = true
//Other code here
if moCompany.IsNew then
ccDatePicker1.dtmSelected = new date
ccDatePicker1.dtmSelected = moCompany.dtClientSince
txtCompany.text = moCompany.sCompanyNameCurrent
Right away we can see that what we load depends if the record is new or existing. Data binding wouldn’t help us there. Labels and TextFields are the easies to do data binding with but since you’ll need a TextField to do a numbers only field or a date field you now have to create multiple subclasses. Probably not a big deal but we’ve found it to be a hassle. Having everything local means we can handle the edge cases with ease rather than having to modify the control subclass that’s doing the binding.
Before we can call our save method we have a Validate method that simply returns true if everything is okay. If not, it presents a message to the user:
Private Function Validate() As boolean
if txtCompany.text.trim = "" then
seterror "Validation Error. Company name cannot be blank."
if Data.T_Company.IsDuplicate(txtCompany.text.trim, moCompany.ID) then
seterror "Validation Error. That Company name is already in use."
Then finally in our Save method we load data from the controls into the object for saving:
Private Sub Save()
moCompany.dtClientSince = ccDatePicker1.dtmSelected
moCompany.sCompanyNameCurrent = txtCompany.text
moCompany.iCompanyEmployeeCount = txtNumberOfEmployees.text.val
moCompany.sURL = txtWebSite.text
moCompany.sTaxIDNumber = txtTaxID.text
moCompany.bInactive = chkInactive.Value
Note that our save method doesn’t care if it’s a new or existing record. Behind the scenes ActiveRecord does the appropriate Insert or Update prepared statements.
Every place where we are editing data we have these three Load, Save, Validate methods. Everyone on our team knows to look for those so it’s very easy for our team to work on projects collaboratively and know pretty much what’s going on.
Could ActiveRecord do data binding? Sure. The classes are open source so feel free to modify them to your hearts content but I truly believe it’s more a matter of the controls being the real pain.
ActiveRecord has a number of events that are handy to use. We track who created and who changed the records using 4 fields on each table CreatedDate, ModifiedDate, CreatedByID, and ModifiedByID. We add the BeforeCreate and BeforeUpdate events. For example, the BeforeCreate event looks like this:
dtCreatedDate = new date
if session.oUser <> nil then
iCreatedBy = Session.ouser.iUser_ID
This gets called before we save anything so the class properties get modified before we attempt to save. In many projects we have an audit trail to know who changed what data so we add the AfterCreate and AfterSave events of Data.T_Company and pass the entire object into the Audit table:
dim oAudit as Data.T_Audit = Data.T_Audit.AuditAdd(self)
oAudit.iCompany_ID = self.id
Then it’s up to the Audit class to query the ActiveRecord class to find changed data and put that into its table. Again, the code to do this is one one spot rather than all over the project.
I could spend hours talking about ActiveRecord as we tend to use on all of our new database projects. It speeds up development of database applications. It eliminates many of the common errors. It tends to force most database code into the NameSpace classes. And the compiler can warn you if you’re doing bad things with data.
ActiveRecord is not for EVERY project but we’ve found it incredibly useful in our consulting. If you dread doing a database project because of the tediousness of database coding then perhaps ActiveRecord is for you.
We recently did a webinar with Xojo on ActiveRecord. You can view it at http://developer.xojo.com/webinar-simplying-db-access-with-bkeeney-activerecord. ActiveRecord itself is open source. ARGen is $19.95. We also use ActiveRecord in one of start to finish training projects at our training site called Link Share.