Table Class

Table classes are non-visual classes that operate between other classes in your application and the database. Table classes give you a central point to control all of the database operations and communications. A very good thing from an object-oriented programming point of view.

Schema and query classes are used to define the columns in a list or row variable which we then use to fetch records from the database. We can not create instances of schema or query classes and we can not add methods to schema or query classes. They are simply used to define list or row variables which are mapped to tables in the database.

We can create instances of table classes and we can add methods to table classes. Once you see how this works, you begin to see how powerful table classes really are.

The Omnis Studio table classes comes with a very helpful set of built-in methods which we can see in the F6 Property Manager or through the Interface Manager.

Create a Table Class

To create a table class as follows:

1. F2 Browser > select Contacts library > click New Class > click Table.
2. Name the table class, tCountry.
3. F6 Property Manager > General tab > set the $sqlclassname property to sCountry.
   
   

   

   
   
   This maps tCountry to sCountry so that the sCountry schema class is used to define the list or row variable when you define a list using the table class.
   
   

   Do List.$definefromsqlclass('tCountry')

4. Select the Methods tab. Note all of the Omnis Studio built in table class methods. Of particular interest to us are the methods: $select, $fetch, $dowork. If you hover over any of the methods a tooltip will give you more information about the method.
   
   

   

5. Double-click tCountry in the F2 Browser.

We'll use the Programmer Test Method to test the table class:

1. Contacts menu > select Programmer Test Method. This takes you to the Breakpoint of the $event method.
2. Click the Stack button in IDE toolbar > select Clear Method Stack.
3. Below the Quit method enter the following code:
   
   

   Do List.$definefromsqlclass('tCountry')
   If List.$colcount=0
      OK message [sys(85)] (Icon) {The list has zero columns after $definefromsqlclass('tCountry')}
   Else
      Do List.$sessionobject.$assign($ctask.dbsessionobj)
      Do List.$select() Returns FlagOK
      If not(FlagOK)
         OK message [sys(85)] (Icon) {Flag false after $select()}
      Else
         Do List.$fetch(kFetchAll) Returns FetchStatus
         If not(FetchStatus)
            OK message [sys(85)] (Icon) {Flag false after $fetch(kFetchAll)}
         End If
      End If
   End If

4. Contacts menu > select Programmer Test Method. This takes you to the Breakpoint.
5. Double-click the first line of your test code to set the Go point to that line.
6. Click the Step Over button in the IDE toolbar repeatedly to step through the test code.
7. All going well by the last line of the test code the List variable will contain the four Country records which we inserted into the database.
8. Right-click on the List variable anywhere in the code > select Variable List... to view the records in the list variable.
9. Close the variable window.
10. Click the Stack button in IDE toolbar > select Clear Method Stack.

Add a Custom Table Class Method

Getting all the records from a table is a common task, especially for tables like the Country table which contain a small number of records. In this section we'll add a $getAllRecords custom method to the table class to select and fetch the records.

1. F2 Browser > select Contacts library > double-click tCountry table class.
2. Right-click Class methods > select Insert New Method.
3. Name the method, $getAllRecords.
4. Enter the following code in the method:
   
   

   ; Calculate the ORDER BY clause.
   Calculate OrderBy as "ORDER BY CountryName"
   
   ; Select all the records in the table.
   Do $cinst.$select(OrderBy) Returns FlagOK
   If not(FlagOK)
      OK message [sys(85)] (Icon) {Flag false after $select()}
   Else
      
      ; Fetch all the records in the table.
      Do $cinst.$fetch(kFetchAll) Returns FetchStatus
      If not(FetchStatus)
         Calculate FlagOK as kFalse
         OK message [sys(85)] (Icon) {Flag false after $fetch(kFetchAll)}
      Else
         
         ; Set the current line to the first line.
         Do $cinst.$line.$assign(1)
         
      End If
   End If
   Quit method FlagOK

   
   
   
   

   Notice the code is similar to our previous test code, but the variable, List, has been replaced with $cinst. This was confusing to me the first time I saw this in Omnis Studio. The reason $cinst is being used in the table class methods is that when we define a variable from a table class using Do List.$definefromsqlclass('tCountry'), we bind the variable to the table class. The list and the table class become one. Think of it like a wedding ceremony when the minister says and the two shall become one. Once the list variable and the table class are bound, and you are inside any of the table class methods, you are inside of the table class instance of the list variable, so to refer to the list, you must use $cinst rather than the list variable name.

5. Go to the Programmer Test Method and remove the old code.
6. Enter the following code in the Programmer Test Method:
   
   

   Do List.$definefromsqlclass('tCountry')
   If List.$colcount=0
      OK message [sys(85)] (Icon) {The list has zero columns after $definefromsqlclass('tCountry')}
   Else
      Do List.$sessionobject.$assign($ctask.dbsessionobj)
      
      ; Get all the records in the table.
      Do List.$getAllRecords() Returns FlagOK
   End If

Test the code:

1. Contacts menu > select Programmer Test Method. This takes you to the Breakpoint.
2. Double-click the first line of your test code to set the Go point to that line.
3. Click the Step In button in the IDE toolbar repeatedly to step through the test code.
4. When you Step In to the Do List.$getAllRecords() line of code, you will step into the tCountry.$getAllRecords method.
5. Continue stepping through the $getAllRecords method until you return to the Programmer Test Method.
6. All going well the custom $getAllRecords method will select, fetch, and sort the records for you.

If you accidentally or intentionally Step In to a method and then decide you want to get out of that method and return to stepping through the code in the preceeding method click the Step Out button in the IDE toolbar. The Step Out button is a handy feature that was added in Omnis Studio v4.1.

Add a $setPrimaryKey Method

When we inserted the records into the Country table we manually set the primary key values. In this section we'll add a $setPrimaryKey custom method to automatically set the primary key to the one more than the maximum value primary key currently in the table.

1. F2 Browser > select Contacts library > double-click tCountry table class.
2. Right-click Class methods > select Insert New Method.
3. Name the method, $setPrimaryKey.
4. Enter the following code in the $setPrimaryKey method:
   
   

   ; Check if the primary key column is null and not zero.
   Calculate ColName as 'Country_pkey'
   If isnull($cinst.[ColName])&$cinst.[ColName]<>0
      Calculate FlagOK as kTrue
   Else
      
      ; Get a new statement object from this table instance's session object.
      Do $cinst.$sessionobject().$newstatement() Returns StmntObj
      
      ; Select and fetch the maximum primary key column value froma the table's records.
      Calculate TableName as 'Country'
      Calculate SQLText as con("SELECT MAX(",ColName,") FROM ",TableName)
      Do StmntObj.$execdirect(SQLText) Returns FlagOK
      If not(FlagOK)
         OK message [sys(85)] (Icon) {SQL error when issuing the following SQL Text: [SQLText]}
      Else
         Do StmntObj.$fetchinto(MaxPKey) Returns FetchStatus
         If FetchStatus=kFetchError
            Calculate FlagOK as kFalse
            OK message [sys(85)] (Icon) {SQL error when fetching the max pkey after issuing the following SQL Text: [SQLText]}
         Else
            
            ; If the maximum value is less than 1000, set it to 1000.
            If MaxPKey<1000
               Calculate MaxPKey as 1000
            End If
            
            ; Set the primary key column to the maximum primary key value plus one.
            Calculate $cinst.[ColName] as MaxPKey+1
         End If
      End If
   End If
   Quit method FlagOK

Let's test the method:

1. Go to the Programmer Test Method and remove the old code.
2. Enter the following code in the Programmer Test Method.
   
   

   ; Define the list binding it to the table class.
   Do List.$definefromsqlclass('tCountry')
   If List.$colcount=0
      OK message [sys(85)] (Icon) {The list has zero columns after $definefromsqlclass('tCountry')}
   Else
      
      ; Set the session object.
      Do List.$sessionobject.$assign($ctask.dbsessionobj)
      
      ; Add a line to the list and make it the current line.
      Do List.$add()
      Do List.$line.$assign(1)
      
      ; Set the country name and the primary key.
      Calculate List.CountryName as 'Italy'
      Do List.$setPrimaryKey() Returns FlagOK
      
   End If

3. Contacts menu > select Programmer Test Method. This takes you to the Breakpoint.
4. Double-click the first line of your test code to set the Go point to that line.
5. Click the Step In button in the IDE toolbar repeatedly to step through the test code.
6. When you Step In to the Do List.$setPrimaryKey() line of code, you will step into the tCountry.$setPrimaryKey method.
7. Continue stepping through the $setPrimaryKey method until you return to the Programmer Test Method.
8. All going well the custom $setPrimaryKey method will set the Country_pkey column value to the next primary key value, 1005.

The $setPrimaryKey code is not necessarily the code you would use to to set the primary key in a large application with lots of users. There is a problem with the $setPrimaryKey method code which we just wrote, and that is, if you are setting the primary key for a batch of records before inserting the first record, the method will keep returning the same primary key value... not good. An alternate technique which I use is to have a separate table in the database which keeps track of the last primary key for each table. If you are using a RDBMS it likely has a technique for getting a unique primary key value for each table.

Override a Built-In Table Class Method

You can override any of the Omnis Studio built-in table class methods by adding a custom method by the same name as the built-in method to the table class.

The $dowork built-in table class method is a great code saver for Omnis Studio developers.

After you define a list from a SQL class and set the session object you can set the list to be a smartlist. When you set a list to be smartlist, Omnis Studio immediately creates a history list which keeps track of the original values in the list and tracks any changes you make to the smartlist.

When you are finished with adding, updating, and deleting lines in the list you simply, Do List.$dowork(), and Omnis Studio generates and executes all the SQL for deleting, updating, and inserting, records into the database.

In this section we will add a $dowork method to override the built-in $dowork. The $dowork method will call the $setPrimaryKey method for any records being inserted, and then do the Omnis Studio default $dowork method. By making this modification the table class will be responsible for making sure the primary key is set for any new records we are inserting into the database.

1. F2 Browser > select Contacts library > double-click tCountry table class.
2. Right-click Class methods > select Insert New Method.
3. Name the method, $dowork.
4. Enter the following code in the $dowork method:
   
   

   ; Store the current line number.
   Calculate StartLineNum as $cinst.$line
   
   ; Reduce the smartlist to the lines to be inserted only.
   Do $cinst.$includelines(kRowInserted)
   
   ; Preset the flag to true before the loop.
   Calculate FlagOK as kTrue
   
   ; Loop through the lines to be inserted.
   For $cinst.$line from 1 to $cinst.$linecount step 1
      
      ; Set the primary key.
      Do $cinst.$setPrimaryKey() Returns FlagOK
      If not(FlagOK)
         Break to end of loop
      End If
      
   End For
   
   ; Include all the normal list lines.
   Do $cinst.$includelines(kRowInserted+kRowUnchanged+kRowUpdated)
   
   ; Restore the current line.
   Do $cinst.$line.$assign(StartLineNum)
   
   If FlagOK
      
      ; Do the built-in default $dowork method.
      Do default Returns FlagOK
      If not(FlagOK)
         OK message [sys(85)] (Icon) {Flag false after running the default $dowork method.}
      End If
   End If
   
   Quit method FlagOK

Test the code:

1. Go to the Programmer Test Method and remove the old code.
2. Enter the following code in the Programmer Test Method.
   
   

   ; Define the list binding it to the table class.
   Do List.$definefromsqlclass('tCountry')
   If List.$colcount=0
      OK message [sys(85)] (Icon) {The list has zero columns after $definefromsqlclass('tCountry')}
   Else
      
      ; Set the session object.
      Do List.$sessionobject.$assign($ctask.dbsessionobj)
      
      ; Set the line to be a smartlist before adding a new line to the list.
      Do List.$smartlist.$assign(kTrue)
      
      ; Add a line to the list and make it the current line.
      Do List.$add()
      Do List.$line.$assign(1)
      
      ; Set the country name.
      Calculate List.CountryName as 'Sweden'
      
      ; Issue a $dowork to save smartlist changes to the database.
      Do List.$dowork() Returns FlagOK
      If FlagOK
         
         ; Get all records to check to see if the record was added.
         Do List.$getAllRecords() Returns FlagOK
      End If
   End If

3. Contacts menu > select Programmer Test Method. This takes you to the Breakpoint.
4. Double-click the first line of your test code to set the Go point to that line.
5. Click the Step Over button in the IDE toolbar repeatedly to step through the test code.
6. Before and after you Step Over the Do List.$dowork() line of code, right-click on the List variable and select Variable List... to check the value of Country_pkey column. All going well the Country_pkey column value will be set to the next primary key value and the record will be inserted into the database.

Summary

In this section you have learned:

1. How table classes are mapped to schema or query classes, bound to list or row variables, and assigned to a session.
2. How to add custom methods to table classes.
3. How to override an Omnis Studio built-in table class method by adding a custom method with the same name as the built-in method.
4. How the Omnis Studio smartlist feature can save developers from writing SQL scripts for inserting, updating, and deleting records in the database.

StudioTips Documentation - Copyright 2006 Vencor Software