Automating Xojo Database Development with ARGen

Database applications are relatively easy with Xojo. The native Database, DatabaseRecord, and Recordset classes make it easy to connect, add, edit, and delete data in your console, desktop, and web applications. What we’ve found over the years isn’t that it’s hard to create these applications but that it’s tedious and also the very nature of Xojo tends to make life more complicated. Let me explain.

The Xojo database classes are very generic. To insert data into the recordset you either use an SQL Insert statement or use the DatabaseRecord class. The SQL statement, by itself if fraught with peril as it’s very easy to mistype the statement and miss it in testing. Even using PreparedStatements (which you should be using whenever possible) you can still screw it up. Using the DatabaseRecord class is more object oriented and without a doubt easier to use but it has one big problem: It doesn’t check datatypes. At design time it will happily let you try to put a date value into an integer field (depends on how you try, of course). It is not until runtime that you may get any sort error which leads to the next issue.

Database errors, at least in the global framework, do not generate exceptions. This means that unless you, the developer, check for the error, you may not get the results you expect. On an Insert statement, either using SQL or the DatabaseRecord, your data may simply never be inserted and you’ll never know it unless you’re checking the Error property on the database connection. The Error must be checked after EVERY SQL action whether that be SQL Select, Insert, or Delete. Even checking after a Prepare statement is wise to do so.

The good news (maybe) is that Xojo for iOS and the iOSSQLiteDatabase class will throw an exception if it experiences a database error. It’s good that you don’t need to check for the error manually, but now you need to put in exception handling around your database code and deal with exceptions accordingly. It’s not hard, but it’s also not trivial code either.

The IDE knows next to nothing about the database and certainly when you code it you have to query the database for Table and Field schema’s. To make it worse, the Xojo database framework has no idea about the concept of Views requiring you to query the database specifically to find and learn more about them. The IDE has no idea about the field datatypes either and the compiler can’t warn you about mismatched datatypes. These types of errors are only caught at runtime which is usually way too late in the process (hopefully not by the client either).

The database frameworks are not very object oriented friendly. If you have a simple Add/Edit dialog you need to know if the record is new, or not, since this changes how you code saving the data. New requires the DatabaseRecord object and an existing record can be updated via the Recordset object (both can use SQL statements but the statements themselves require different statements).

Xojo is easy to use but the ability to stick code in control events tends to make big database applications unwieldy and full of spaghetti code. Changing a field name in a commonly used table, or even worse changing the field datatype, is often at your own peril because you might have SQL statements referencing it in hundreds of places in the code. Forget it one place and you have an error in the waiting.

Finally, there’s the hooking your user interface up to your database code. Many people have Load/Save methods in every window/dialog/container that needs it. Use a SQL statement to get the data, then use the Recordset to load the user interface, and then use a Save method (that knows the difference between new and existing records) to put it back into the database. Again, the IDE compiler can’t help you if you make either a spelling mistake in the SQL statement or Field names so you won’t find these errors out until runtime (which is often too late).

There are options, to all of these problems. Creating your own data classes is a step above the standard Xojo code, in my opinion. You encapsulate a record into a Xojo class and implement your own Load and Save methods. This is a better object and forces some compiler warnings into your code. It also tends to put your database code all in one folder and/or NameSpace. You can use field name constants for field names, and force all db code into one class or module. Unfortunately, if you have hundreds of tables it’s also tedious to do all this.

There are other solutions out there, but ARGen takes some of the tediousness out of the work of creating a Xojo database application and does some other nifty things too like creating a basic User Interface for you.. ARGen is short for ActiveRecord Generator. ActiveRecord is a set of classes that map the database into equivalent Xojo namespaced classes.

Say you have a table named Employee. ARGen would create a class named ‘Employee’ in the ‘Data’ namespace and you would reference it in code as Data.Employee. Each field in the table is then mapped to a property in the class. The FirstName text field would be mapped to a string property named “FirstName”. This property is referenced as Data.Employee.FirstName and since the compiler knows this property is a string it will complain if you try and put an integer into it. Since there’s only one place you would ever define FirstName in your project it becomes really easy to change the name or change the datatype and then have the compiler catch any errors. It also means that AutoComplete in the code editor works meaning you’ll never mistype a table or field name again without the compiler catching it.

When an ActiveRecord project first starts up and connects to the database we call a Register method that calls out which tables/views are added into ActiveRecord. ActiveRecord scans the schema of the table and then attempts to match up every field with a corresponding property on the class. If a field is missing from your class it will generate an error that’s caught only while debugging (a “table x is missing field y” type of message).

Each ActiveRecord instance is a complete record. It has built-in Save and Delete methods. It also has events for Before and After Create, Before and After Update, to let you do things globally for each record. One example we use all the time are CreatedBy, CreatedDate fields in the Before Create event and ModifiedBy and ModifiedDate fields in the Before Update event. One place, ever, to put that data into the database rather than the potential thousands of places it might. There is also a convenient IsModified method to figure out if data has actually changed or not.

The Save method knows if it’s a new record or an existing record and does the right thing behind the scenes for you. Using a transaction, using a PreparedStatement. I think this one feature alone saves us hundreds of hours of coding because it’s a simple Save call. Nothing more be done. It really takes the worry out of it.

There are other coding features that we could get into but it all revolves around making creating database applications in Xojo fast and easy. ARGen creates a bunch of helper functions like IsDuplicate, List, and so on to help make your life easier. ARGen has a free mode which allows you to get the ActiveRecord classes and up to two tables at a time done.

For many years this is all ARGen did and we used it with great success. However, we found ourselves spending a lot of time on the next step – creating windows, dialogs, and containers and hooking up the database classes to the User Interface. Practically every table you run across has a List and Edit form. In version 2 we added the ability create a rudimentary User Interface which saves even more time. You only get this ability with the fully paid version.

When generating a Xojo project ARGen puts in a number of #pragma error statements where the developer needs to look at code and uncomment code and/or fix code. It’s hard to guess and frankly we don’t want your newly generated project to compile without you having to fix some code. Trust me – it’s better this way. There are simply too many variables and ways of doing things. Because of the #pragma error statements I like to bring over classes and UI over from the generated project as I need them rather than all at once. No need to fix hundreds of pieces of code until you’re ready to look at them.

ARGen and ActiveRecord isn’t perfect and it’s not the only solution around. ARGen lets the compiler do some work for you and eliminates some of the very common, but trivial, mistakes. It also lets you save a ton of time when it comes to standard database code and building user interfaces. It isn’t a panacea for development but ARGen can save you a lot of time and effort.

If you’d like to learn more about ARGen and what it can do for you, please download the free/demo version from the product page at http://www.bkeeney.com/allproducts/argen/. There are several videos on the ARGen menu that show more details.

Update:  I did a Xojo webinar a while back that talks about some of this in detail.  It’s a little dated but worth watching:  http://developer.xojo.com/webinar-simplying-db-access-with-bkeeney-activerecord

9 thoughts on “Automating Xojo Database Development with ARGen

  1. ARGen is a great product and has saved me scores if not hundreds of hours already. I’d recommend it without a second thought to anyone doing a database related Xojo project.

    Can you tell us what the next big version might have in store for us? Specifically – when in the Auto Create UI panel, could we get the option to create properties as well as controls (choosing one or the other)?

    For example, for one table, I save a series of data as a comma-separated string field like “1,2,2,3,3,2,4,2,5” and would love for ARGen to autogen a property rather than a label or any other “text holding” control.

    I can select “none”, of course, and put the code in myself, but then ARGen leaves out the commented code in the “init” and “save” methods. A property option in ARGen would be a great help. (The workaround is not hard, but it is tedious – to select label and then replace the code with my own property.)

    Second, implementing a canvas control option for images / BLOBS would be super helpful even though the workaround for that is not very difficult.

    • Glad ARGen has saved you so much time! We came up with ARGen because we were doing the same repetitive tasks over and over again so I get where you’re coming from.

      We have no specific plans for future versions. The easiest/best way to log a feature request or bugs is on our Mantis page at http://mantis.bkeeney.com. If you don’t already have an account you’ll have to do so to report something.

      In regards to the data you have in your field I’d call that a huge no-no in our book. That series should be in a table rather than aggregating data into a single field. Of course, if it’s in a separate table ARGen can do some of the work for you.

      The reason I point this is out is that having a comma separated string means that your app has to do some processing *after* it’s read the data from the database and it has to do processing *before* it can put data back into the database. Anytime I’ve had to do this it’s been better to be in a separate table.

      If you need a better explanation I’m sure I can drag Carol into this conversation. We’ve worked on a couple of OPC (other peoples code) projects where people have attempted to do something similar with emails and it drives us nuts because it’s such a hassle!

      • Ok, granted that may have been a poor example. The database structure is still a work in process; as the GUI matures, it draws out new insights into the db.

        Still, there are sometimes fields in tables which need no visible representation in a GUI and are better handled as properties, IMO and it would be nice for ARGen to handle that.

        Once I do get to finalizing the db structure though, I do intend to take it to professionals.

          • I’m afraid you misunderstood.

            I *want* ARGen to generate code like this from a save/edit window “init” method (I’ve removed a lot of code to simplify the example):

            [code]
            bLoading = true
            lbl_DateModified.Text = m_oRecord.dt_DateModified.SQLDateTime
            lbl_PerformanceTestScaleID.Text = m_oRecord.i_PerformanceTestScaleID.ToText
            me.i_StationID = m_oRecord.i_PerformanceTestStationID

            me.pop_RankID.SetID = m_oRecord.i_RankID

            s_Results = m_oRecord.s_Results
            s_Scores = m_oRecord.s_Scores

            bLoading = false

            [/code]

            s_Results and s_Scores are string properties. If I choose “none”, I don’t get those specific lines of code related to those two properties (and not just in “init”). So, the workaround is to choose “label” and then replace the control with the property. It would be nice if I could simply choose “property” instead of “textfield” in step 5.

            For my purposes, GUI-wise, I take those properties (fields) and manipulate them to show side by side in two columns of a subclassed listbox that of course ARGen cannot be expected to generate for me like it does generic controls.

            While I can’t speak for others, I know my projects will use a lot of custom classes to show data in the GUI and having ARGen generate *properties* would be a very welcome solution.

          • Hm…since you already have the AR object m_oRecord you, in reality, already have the property on your window. I see no point in mirroring it with a local property on the window. Instead of naming a property ms_Results you already m_oRecord.s_Results and because of AutoComplete this will be easy to do on your own. Sorry, I don’t think we’ll be implementing this feature any time soon unless a lot of people start asking for it.

  2. I also use ActiveRecord and ArGEN from the beginning of my RealStudio / XOJO Time in 2013.

    It’s really a pleasure to make Applications with XOJO and your ActiveRecord solution. I use it in Desktop and WebApps mostly with Postgre-DB (my database of choice in most projects).

    Where i get in trouble is when i delete a field from the table in Postgres DB and the Property from the XOJO Class. Vacuum the DB seems not to fix shrink structure. Perhaps you can help me in this topic. Perhaps the problem is solved in Version2? never tried it because most of my projects started when only Version 1 of ActiveRecord / ARGen was released

    Thanks for a great product and the cheap price for the XOJO community

    • Thanks for the kind comments!

      I’m not sure I understand the issue. You’ve deleted the field from the table and you’ve deleted the property from the class. From the ActiveRecord perspective you’ve done everything you need. If Postgres isn’t giving up the space for that field it sounds like a Postgres issue. Am I misunderstanding?

      ActiveRecord will warn you about new fields added to the table but it does nothing for fields that were removed. For a while we’ve been thinking about adding something that would scan the class properties and see if it matches the table. To do this we’d have to use an attribute on the property (or something) to say that the property is an ActiveRecord field property. Thoughts on this? It seems to be a pretty rare situation but it *would* be helpful – just not sure if it’s worth the overhead to add.

  3. I’ve experienced the same issue as Björn Dohle. I had a table in Postgres and I deleted fields from it and recreated new fields. Then I ran ARGen and it produced strange results. I finally figured out that ARGen does not like tables in Postgres that have had fields deleted.

Comments are closed.