Omnis Technical Note TNSQ0027 March 2010

Table Classes versus Object Classes

for Omnis Studio
by Andreas Pfeiffer

Developers often ask whether it would be better to use object classes as opposed to table classes to code SQL, since it is possible to develop object-oriented code without using table classes.
At first glance, this would appear to be a reasonable proposition. When using object classes, you have explicit access to the code and you do not need to rely on built-in table class methods to implement the correct SQL code.

This argument applies if the programmer is using schema classes on their own, as opposed to table classes connected to schema classes. This is because with table classes, the programmer can choose whether to enter SQL code himself or rely on the built-in methods and properties.

In a way, table classes are similar to object classes; with the difference that table classes have additional properties. For example; when instantiated, table classes are defined with the columns from list or row variables as well as built-in SQL methods. An instance of a table class is always a data object, i.e. a list or row variable. This means that you can implement your own methods within this data object. For example, it is possible to create a method in a table class and check its return value as follows:

  If myRow.$checkFields()
    Do myRow.$insert()
    OK message "Data is not valid"

The $checkFields() method in the table class might look like this:

  If len($ = 0      ;;Name is empty
    Quit method kFalse
    Quit method kTrue

Note that $cinst in this case points to the current row of the list variable used to define the table class instance.

Some might argue that the same result could be achieved using an object class. This is not strictly true as it would necessitate another layer of code: the object class would still need access to the list or row variable.

Using table classes, one can rely on the inherent behaviour to handle the list or row data. As such, table classes are self-contained. This enables the developer to place commonly used properties and methods in a superclass of the table class. In our example, we could place the $checkFields() method in the superclass, with the option to override this method in the derived table class if necessary. In our superclass method, this method simply contains:

  Quit method kTrue

In other words, if we do not override the checkFields() method in the table class, it will always return kTrue.

Returning to the argument over whether this would be better implemented using an object class, we can of course use the built-in methods of the table class; but we don't have to. If you prefer to write your own SQL code, you are of course free to do so. The only restriction is that the definition of the list or row variable is determined by the $definefromsqlclass() method. For example:

  Do myList.$definefromsqlclass($tables.T_Address,'Name','Firstname','City','ADDRESS_ID')

Here, myList is defined from table class T_Address with the columns names; "Name", "Firstname", "City" and "ADDRESS_ID".

When you want to process your own SQL using these column names, you can do so by implementing a method directly in the table class, which will use its built-in statement object to execute the code. Note that for this to work, the session object must be assigned to the table class (i.e. in the $construct() method of the superclass).


  Begin Statement
  STA: SELECT [$cinst.$selectnames()]
  STA FROM ... (your own SQL)
  End Statement
  Do $cinst.$statementobject().$prepare()
  Do $cinst.$statementobject().$execute() Returns Ok
  If not(Ok)
    Do $cinst.$sqlerror()
    Quit method kFalse
    Do $cinst.$fetch(kFetchAll)    ;; loads all records found into the object
    Quit method kTrue

where $cinst.$selectnames() returns the column names of the list or row variable used to define the table class instance.

The table class method would then be called like this:

  If myList.$loadExample()
    Do $cinst.$redraw()
    Ok message "Could not load data: [myList.$getErrorText()]

Using this approach, the object will load its data independently and can also be used to deliver the error message (which can also be implemented in the superclass):


  Quit method $cinst.$statementobject().$nativeerrortext()

This completes the circle, since we are now directly accessing the properties of the statement object located inside the data object. The $getErrorText() method returns the native error text that the OK message uses to display the error message.

When you are using your own method to insert data you need to give special attention to bind variables. Bind variables allow special characters to be passed correctly and avoid the need to escape quotation marks in strings such as "O'Hara". This points to an additional benefit of table classes. If you use the inherent $insert() method, the object automatically uses bind variables for each column of the list or row so the developer does not have to.

Using table classes, you also have control over the SQL which is actually sent to the server. For example:

  Calculate mySQLText as $cinst.$statementobject().$sqltext

If you choose not to pass certain columns, you can exclude them from the $insert() in the $construct() method of your table superclass:

  Do $cinst.$cols.ident.$excludefrominsert.$assign(kTrue)

In this example, the "ident" column will be excluded from insertion.

Incidentally, the built-in $sqlerror() method of the table class is accessed automatically if an inherent method such as $insert(), $update(), $delete() or $select() fails for any reason. It therefore makes sense to override this method in the table superclass, giving you a general SQL error handler.

You can find out more about this issue in Technote TNSQ0017