Objects Symbol

What is ActiveRecord?

TP_ActiveRecord is an ORM (Object-Relational Mapping) library for Xojo. The Active Record Design Pattern associates the database record row to an object.

For Xojo, each table becomes a class and the columns of the table are properties. Developers can access the column t_Person.FirstName like they would check TextField.Value.

Working with Xojo code instead of SQL protects developers from runtime exceptions caused by minor mistakes. The class based design enables the IDE auto-complete to suggest table and column names. Data type mismatches are a thing of the past, as the compiler can warn about incompatibilities.

ActiveRecord abstracts database operations to Xojo code as well. Record.Save handles INSERT and UPDATE. Similarly Record.Delete handles the delete. A handy Record.Clone function is available.

Record objects indicate to developers useful insight like Record.IsModified and Record.IsNew

Using ActiveRecord

Connecting to the Database

  1. Connect to the datbase with Xojo code
  2. Connect ActiveRecord to the database
  3. Register any table class definitions with ActiveRecord
    TP_ActiveRecord.Table(DB, "t_TableName", _
  4. Register any view class definitions with ActiveRecord
    TP_ActiveRecord.View(DB, "vw_ViewName", _

Once connected developers can work with database rows using the associated Xojo classes.

Handling multiple database connections
Multiple Connections

To connect to multiple databases, ActiveRecord maintains an internal lookup table of class assocations. Pass a class model to associate with the database to the Connect method. For this to work, the model passed must be a subclass of BKS_ActiveRecord.Base.

Class clsAutitDatabase Inherits TP_ActiveRecord.Base
Class clsUsersDatabase Inherits TP_ActiveRecord.Base

TP_ActiveRecord.Connect(GetTypeInfo(clsUsersDatabase), dbUsers)
TP_ActiveRecord.Connect(GetTypeInfo(clsAutitDatabase), dbAuditLog)

Additionally, the table classes must be a subclass of the TP_ActiveRecord.Base subclass which was passed to the Connect method.

Class t_User Inherits clsUsersDatabase

ARGen can automate generation of classes for every table with this structure.

Working with Rows

Creating a Record
  1. To create a new record, create a new object and save it
    var oPerson as new DataFile.t_Person
    oPerson.FirstName = "Jean-Luc"
    oPerson.LastName = "Picard"
Loading a Record
  1. A row can be loaded by primary key using the Load method
    var oPerson as new DataFile.t_Person
    if oPerson.Load(1) = true then
      // The person record loaded successfully!
Updating a Record
  1. Save a record after making changes, the update is automatic!
    var oPerson as new DataFile.t_Person
    if oPerson.Load(1) = true then
      // The person record loaded successfully!
      oPerson.Quote = "To boldly go..."

Class Definition


Name Description
Clone as Variant Returns a new object where the properties match the source record except for the primary key. Saving will create a new row.
Constructor Default constructor which takes no parameters.
Constructor(RecordSet) Constructor which accepts a RecordSet parameter to load the current record from.
Db as Database Returns a reference to the database to which this instance is connected.
Delete Deletes the record associated with the object.
Load(Integer) Reads the database record with the passed primary key. Returns True if the record was found and False if the record was not found. Also accepts a String primary key.
Save Save changes to the object. Automatically handles CREATE or UPDATE.
Validate as Boolean Raises the Validate event and returns true if no errors are found.


Name Description
GUID as String Returns the primary key of the record. Use this for String GUID primary keys.
ID as Integer Returns the primary key of the record. Use this for Integer primary keys.
IsNew as Boolean Returns True if the object has not yet been saved in the database.
IsModified as Boolean Returns True if the object has been modified since it was last saved. New objects return False until at least one property has changed.

Download ActiveRecord

Copyright Tim Parnell, 2020