Omnis Technical Note TNSQ0027 March 2010
Table Classes versus Object Classes
for Omnis Studio
by Andreas Pfeiffer
Introduction
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.
Discussion
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()
Else
OK message "Data is not valid"
Endif
The $checkFields() method in the table class might look like this:
If len($cinst.name) = 0 ;;Name
is empty
Quit method kFalse
Else
Quit method kTrue
Endif
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).
$loadExample()
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
Else
Do $cinst.$fetch(kFetchAll) ;;
loads all records found into the object
Quit method kTrue
Endif
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()
Else
Ok message "Could not load data: [myList.$getErrorText()]
Endif
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):
$getErrorText()
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