Converting FileMaker to Xojo And ActiveRecord

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, _
   
   oCompany.sStateCode, _
   
   oCompany.sZipCode, _
   
   oCompany.sCompanyStatusName, _
   
   oCompany.sAgentName, _
   
   oCompany.sParentName
   
   dim iRow as integer = lst.LastIndex
   
   lst.RowTag(iRow) = oCompany
   
next


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.

Screen Shot 2015-06-24 at 9.49.32 AM

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

pg.Display oCompany


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
      
   else
      
      lblCompanyID.text = moCompany.iCompany_ID.ToString
      
      pmStatus.setid moCompany.iCompanyStatus_ID
      
      pmStatus.Enabled = true
      
   end
   
   //Other code here
   
   if moCompany.IsNew then
      
      ccDatePicker1.dtmSelected = new date
      
   else
      
      ccDatePicker1.dtmSelected = moCompany.dtClientSince
      
   end
   
   txtCompany.text  = moCompany.sCompanyNameCurrent
   
   ccLastModified1.SetRecord moCompany
   
End Sub


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
   
   SetError ""
   
   if txtCompany.text.trim = "" then
      
      seterror "Validation Error.  Company name cannot be blank."
      
      txtCompany.SetFocus
      
      return false
      
   end
   
   if Data.T_Company.IsDuplicate(txtCompany.text.trim, moCompany.ID) then
      
      seterror "Validation Error. That Company name is already in use."
      
      txtCompany.SetFocus
      
      return false
      
   end
   
   return true
   
End Function


Then finally in our Save method we load data from the controls into the object for saving:



Private Sub Save()
   
   moCompany.CompanyStatus pmStatus.RowTag(pmStatus.ListIndex)
   
   moCompany.dtClientSince = ccDatePicker1.dtmSelected
   
   moCompany.sCompanyNameCurrent = txtCompany.text
   
   moCompany.iCompanyEmployeeCount = txtNumberOfEmployees.text.val
   
   moCompany.SICCode ccSic1.SICcode
   
   moCompany.sURL = txtWebSite.text
   
   moCompany.sTaxIDNumber = txtTaxID.text
   
   moCompany.bInactive = chkInactive.Value
   
   moCompany.save
   
End Sub


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:



Sub BeforeCreate()
   
   dtCreatedDate = new date
   
   if session.oUser <> nil then
      
      iCreatedBy = Session.ouser.iUser_ID
      
   end
   
End Sub


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:



Sub AfterCreate()
   
   dim oAudit as Data.T_Audit = Data.T_Audit.AuditAdd(self)
   
   oAudit.iCompany_ID = self.id
   
   oAudit.Save
   
End Sub


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.

ActiveRecord home page 

ARGen home page 

Xojo Training Site

ARGen 1.6.3

ARGen64BKeeney Software released a new version of ARGen today.  ARGen is an ActiveRecord class generator for Xojo and Real Studio.  It allows you to generate all of the classes required to use ActiveRecord in your projects.

Version 1.6.3 Fixes a CubeSQL bug that kept the Load function from working properly.  You could get around this by using the FindByID shared method but some people prefer the Load method.

This version also modifies the ActiveRecord classes a bit so it uses the prefix/suffix preferences used in ARGen to make sure your classes have the required property to match the field in the table.  For example, if you had a field PlaywrightID and were using a prefix of ‘i’ it will now flag the developer if there isn’t a property named iPlaywriteID.  In previous versions it didn’t really matter what you used.  This is just one more developer aid.

It is recommended that all Windows users update manually since a bug was discovered in earlier versions of the auto updater.

ARGen Version 1.6

ARGen256Today we released an update to ARGen, our utility to create ActiveRecord classes for your Xojo and Real Studio database projects. ARGen costs a mere $19.95 and can save you many hours of tedious and error-prone database coding.

ActiveRecord for Xojo does a number of things for you. First, it eliminates much (not all) of the SQL required to work with your database. Second, it lets the IDE autocomplete work for you using Table.Field notation as well as letting the compiler warn you when you are doing an illegal datatype conversion. Lastly, ActiveRecord will throw an exception if a field is missing from the AR classes (debug only).

Version 1.6 is a free update to all users of ARGen. The demo version allows you to output two AR classes at a time.

New in version 1.6:

  • ARGen now outputs the entire ActiveRecord class structure so you no longer have to download the ActiveRecord package from our website to add it to your project.
  • Outputs only for the database you’ve selected
  • Restructured the OpenDB a bit to include the table registration

Download and more info at http://www.bkeeney.com/allproducts/argen/ 

 

ActiveRecord and ARGen Updates

ARGen256We’ve been consistently updating ActiveRecord for our consulting and internal projects.  We find ActiveRecord to be a wonderfully way to get the Xojo IDE to do more work for you.  It reduces table/field name errors and gives you some nice autocomplete features in the Xojo Code Editor.  In addition to all that you never have an issue with silent data type conversion errors.  It has saved us hundreds of hours of development time in the past couple of years.  Another huge bonus for us in team development is having it flag the developer (runtime exception) when a new field is added but it’s not in the class yet (at runtime it’s silent).

Today, we released an update to the general ActiveRecord release and is available through the ActiveRecord page at http://www.bkeeney.com/rbinto/activerecord/

New in this release:

  • Support for CubeSQL Server
  • Provisional support (meaning don’t call us if it doesn’t work) for Oracle DB servers
  • Fixed a number of issues with MySQL, and PostgreSQL
  • Added SQLiteDatabase for Xojo users migrating away from REALSQLDatabase
  • Added more comments for errors when there are missing plugins (now very likely with CubeSQL support).

We also updated ARGen, our utility to generate the classes required to use ActiveRecord in your Xojo applications.  This $19.95 utility is a tool that makes creating the ActiveRecord classes a breeze.  Point it to the database you want, select the tables, hit the create button and it generates the Xojo classes required to use the database with your ActiveRecord application.

While it’s not very hard to create your own classes but this utility is a huge time saver.  For a test I took one of our big databases (about 350 tables with varying number of fields in each) and ran it through ARGen.  In 20 seconds it generated all of the data classes for the database and all I had to do was open the resulting XML in Xojo and drag the data classes into a project.

More information on ARGen at http://www.bkeeney.com/allproducts/argen/

New in this release:

  • Support for CubeSQL Server
  • Provisional support (meaning don’t call us if it doesn’t work) for Oracle db servers
  • Added SQLiteDatabase for Xojo users migrating away from REALSQLDatabase
  • Added the ability to add suffixes to your field names (in addition to the prefix)

ActiveRecord Generator

ARGen256

Real Studio makes doing database applications very easy.  The strength of the database classes in Real Studio is that they’re generic enough to do almost anything.  The drawback to the database classes in Real Studio is that they’re generic enough to do almost anything.  No contradiction there, eh?

Database programming in Real Studio can be very tedious.  It’s the exact same code over and over again.  I generally have Load, SaveExisting, SaveNew, and Validation methods where I am literally just using the same combination of SQLSelect, SQLExecute, InsertDatabaseRecord statements over and over again.  Since the IDE has no idea what tables and fields are in the database it can’t help me detect table and field misspellings (a common issue) or tell me when I’m trying to convert data improperly.  Depending upon the database data conversion errors will happen silently with no errors (I’m looking at you SQLite and MySQL).

For years we’ve been using ActiveRecord for Real Studio.  It’s been a great way to abstract all the boring and tedious database stuff out of the way and let Real Studio work with us and for us.  It’s very nice to have AutoComplete work (most of the time) and the compiler warn of datatype mismatches.  It’s been a great timesaver but sometimes the work involved creating the classes in Real Studio was a hindrance.

About a year ago we were working on an conversion project where the original app was created in Microsoft Access.  The database had a hundred or so tables and some of those tables had two hundred fields in each table (I didn’t say it was a well designed database) and the client wanted a Real Studio version of it for Mac and Windows.  Doing all that work of creating the Real Studio classes would have taken twenty or thirty hours (or more) so we came up with a generator to create the classes for us.

This is where ARGen, the ActiveRecord Generator, for Real Studio was born.  Since then we’ve used it in a couple of projects and found it very, very handy.  It is now available in our web store and lets you scan an SQLite, MySQL, PostgreSQL or Microsoft SQL Server 2008 databases.  It then simply generates the necessary classes in a Real Studio XML file that you can open in Real Studio and copy and paste the namespace data files into your own application.

ARGen conveniently adds a generic DBOpen, DBClose, and Register functions into the namespace module letting you start using ActiveRecord as quickly as possible.  Each ActiveRecord class then has FindByID and List shared methods for your convenience.  Naturally you’ll add your own shared methods over time but it’s a good starting place for most developers.

You can try out ARGen for free but if you want to unleash its full potential it’s a paltry $9.95.  I think this is a steal since it might be saving you many, many hours of coding.

Downloads for the Mac OS  X and Windows versions can be found at http://www.bkeeney.com/allproducts/argen/ 

Purchase in the BKeeney web store here.

 

Real Studio Database Days

If you’d like to learn more about Real Studio and how it integrates with your favorite database there is an event coming up that might be of interest to you.  MonkeyBread Software is hosting the Real Studio Database Days, November 3, and 4, 2011 in Frankfurt, Germany.

Currently there are over 30 people registered from eight countries including Germany, Austria, Belgium, France, United Kingdom, Netherlands, and the USA.  This is a good time of the year to get away.

More info can be found at http://www.monkeybreadsoftware.de/realbasic/events/#database days

After checking flight costs there’s a possibility I’ll be there.  If I do, I’ll do a short presentation on our REALbasic implementation of ActiveRecord.  If you’ve not used it, ActiveRecord lets you use auto-complete of your tables/fields in the Real Studio IDE, takes advantage of the strong type-casting in Real Studio and, takes advantage of REALbasic events.

 

 

 

 

 

 

 

 

 

ActiveRecord for Real Studio

We are Real Studio consultants.  It’s what we do and we do a LOT of projects.  If I had to put a percentage on the projects that are database driven I’d have to say that it’s above 95% for the past ten years.

Real Studio doesn’t have database binding like Visual Basic 6 but it’s not a real big deal.  If anything, the lack of binding makes the code more explicit (i.e. easier to read) and you don’t have to go hunting through control properties to find table and field names.  The Real Studio database classes are generic so it doesn’t matter, generally, what database you’re connecting to.  The drawback to the lack of binding and the generic classes is that it does lend itself to creating the same code over and over and over again.

Because of the nature of Real Studio many users tend to put their db code into the form (window) and tie it to controls.  This leads to spaghetti code with the database specific code all over the place and makes changes to your database harder.  Seth has done two presentations at ARBP conferences 2009, 2011 and introduced attendees to ActiveRecord that we’ve used for years now.

Active Record is a very simple, and limited Object Relational Model (ORM) system.  It allows us to create REALbasic classes that the IDE knows about.  It’s not exceptionally strong with the relational data, or large blobs, but it can be programmed to handle it.

In a new project we’re converting an existing Visual Basic 6 project with roughly 25 tables and several tables have over a hundred fields each.  Using conventional means it would mean having a database editor open so I can copy and paste field names all the time.  However, using ActiveRecord we created the classes (we have a utility to do this) and now the IDE knows the table and field names.  This makes coding very fast and they’re is no worrying about spelling errors and there’s no longer any issue of what the data type is because the class knows what it is.  This is nice since the compiler will pick up any many errors that may not usually find until runtime.

The client was ecstatic after the conversion since he figured that would have taken about 20 hours to convert the VB6 code into something useable in RB.  Instead, between our utility and ActiveRecord it took me less than 4 hours.  So now instead of spending all the time getting classes ready, we’re doing the real work of connecting up the UI to a set of data aware classes.

Another feature that was added was to flag the developer if a field is in the database that isn’t in the class.  How many times do you add a field to the database (or a coworker does) and you forget to hook it up.  This doesn’t happen using ActiveRecord.  You can have class properties that aren’t a field, but if you delete a field property that’s been used in the application the compiler will flag you on it and that’s very useful too.

ActiveRecord makes extensive use of Shared Methods so that all of the database code for that table is access from that class and that class only.  It has a number of methods built-in such as getting a list of rows (in array form) and finding a record by the primary key.  It’s easily extensible.

Like I said earlier, it’s not perfect.  It doesn’t handle relational data at all, but it can be modified to do so.  Large blobs can slow it down, but in the few times this has been a big deal we’ve implemented ‘lazy loading’ where we don’t load that particular field until we ask for it.

We have a single tutorial page up for it now at the main website.  We’ll eventually turn this into video tutorials and we’ll demonstrate it in more video’s.  It’s an MIT style license so feel free to use it.  If you have additions and suggestions, please don’t hesitate to contact us.

More information, and downloadable classes can be found at http://www.bkeeney.com/realbasic/activerecord