Sw4   >   Sql   >   Table Classes

Table Classes

Table classes are the objects which communicate between your appliaction code and the database. By doing all of your communication with the database through the table class methods you give your application a layer of separation that makes it easy for your application to connect to SQL databases from different vendors.

All the communication with the database is through the table class, so if something has to be modified to suit a particular DBMS there is one central place, the table class, where the code can to be modified.

The base table class in StudioWorks is tBase. The base table class is able to use the SQL meta-data to make intelligent decisions when inserting, updating, deleting, and selecting database records. This saves you from writing a lot of custom table class code.

You should review all of the tBase table class methods using the Interface Manager.

This section covers some of the table class methods which you are likely to call from your application code.

List & Row Variables

The first step in communication with the database is to bind a list or row variable to a table class and setting its $sessionobject property.

If you were doing this without StudioWorks the code might look something like this:

; Define the list using the table class tAuthor which points to the schema class sAuthor.
; This 'binds' an instance of the table class to the list.
Do List.$definefromsqlclass('tAuthor')

; Get a session object from a session which has been opened with the database.
Calculate SessionObj as $sessions.SessionName.$sessionobject

; Set the session object for the table class instance which was bound to the list.
Do List.$sessionobject.$assign(SessionObj)

Using StudioWorks you would do all of the above (and some) like this:

; Define and bind a list to the default table class and set it to the default session.
Do lsts.$retDefinedList('tAuthor')

The table class which you bind to can be pointed to a schema class or a query class.

Once you have a list or row variable bound to a table class which has its session object property set you are ready to select and fetch records from the database. If your list or row variable is mapped to a schema class you can also insert, update, and delete records.

Tip

If you are stepping through code in the debugger your can right-click on any list or row variable that is bound to a table class and select Interface Manager to view all the available table class methods. You can drag any of the methods from the Interface Manager into a Do statement.

Schema & Query Classes

Schema classes are mapped to database tables or views. Generally in StudioWorks you create a schema class and then using the Database Admin tool you synch the database to the match the schema class. This creates the table and columns in the database.

Query classes are normally used to join multiple tables into a single list or row variable. In the SQL world you could define a view which joins multiple tables. You could then map a schema class to the view. This technique works well if you need to do several joins to the same table in a select.

Query classes can also be used to select a reduce number of columns from a single table.

One of the rules in the SQL world is that you cannot insert, update, or delete records using a view which includes more than one table. The reasons behind this are very logical... it just doesn't work.

If you have a List or Row variable bound to a query class with multiple tables and you attempt to $insert, $update, or $delete, Omnis Studio will ignore your request.

The StudioWorks tBase table class has custom methods which allow you to insert, update, and delete records with a List or Row variable bound to a query class that joins multiple tables. The technique used to accomplish this is that the meta-data specifies the base table for the query class and StudioWorks prepares a SQL statement which only includes base table columns found in the query class, and then issues the insert, update, or delete statement to the database.

You should always use the custom tBase methods for inserting, updating, or deleting database records:

Fetching Records

Omnis Studio provides the table class methods $select and $fetch for getting records from the database. To get all of the records in a database table you could use the following code after binding a list variable to a table class and setting the session object.

Do List.$select() Returns FlagOK
; The flag will be false if a SQL error occurred.
If FlagOK
   Do List.$fetch(kFetchAll) Returns kFetchStatus
   If kFetchError
      ; Oops a fetch error occurred. Get the SQL error from the statement object.
      Do List.$statementobject().$nativeerrortext Returns ErrText
   End If
Else
   ; Oops a select error occurred.
   Do List.$statementobject().$nativeerrortext Returns ErrText
End If

To accomplish the same thing in StudioWorks with a list variable defined using lsts.$retDefinedList you would do the following:

; Select and fetch all the records.
Do List.$getAllRecords() Returns FlagOK
If FlagOK
   ; No SQL error. The list could be empty if the table was empty.
Else
   ; The flag will be false if a SQL error occurred.
   ; The SQL error will have been logged with the SW error handler.
   ; If this is a visual class, prompt the user with the error message.
   Do errhndlr.$promptonceLastError()
End If

Note

In StudioWorks you can still use the Omnis Studio built $select and $fetch methods. StudioWorks has customer $selectWhere and $fetchBatch methods which give some extra functionality.

The tBase table class has the following $get... methods for selecting and fetching records.

Bind Variables

In SQL there are several pitfalls that can mess up the WHERE clause.

Using bind variables is the answer to the above problems. The StudioWorks methods $getWhere and $selectWhere accept a text variable or a row variable for the WHERE clause.

The following example demonstrates how you would use a WhereText variable for the $getWhere method. The same code works with the $selectWhere method.

; Find all the invoices on or after the specifed invoice date for the specified vendor.
Calculate VendorName as "O'Brian's Auto Repair"
Calculate InvoiceDate as '05-JUN-2005'

; Use WhereText to get records.

; Replace each quote character with a wildcard underscore character.
Calculate VendorName as replaceall(VendorName,"'","_") ;; Single quotes.
Calculate VendorName as replaceall(VendorName,'"','_') ;; Double quotes.

Calculate SQLText as con("WHERE VendorName = '",VendorName,"' AND InvoiceDate >= CAST('",jst(InvoiceDate,'D:y-M-D'),"' AS DATE)")
; Note: CAST() may not work for all back ends. The date format might not be the same for all back ends.

Do List.$getWhere(SQLText) Returns FlagOK

To accomplish the same thing using a WhereRow variable you would do the following:

; Find all the invoices on or after the specifed invoice date for the specified vendor.
Calculate VendorName as "O'Brian's Auto Repair"
Calculate InvoiceDate as '05-JUN-2005'

; Use WhereRow to get records.
;
; Values in [] square brackets are the column number of the where row that the value will be found.
Calculate SQLText as con("WHERE VendorName = [2] AND InvoiceDate >= [3]")

; Defined and set the values of where row.
Calculate WhereRow as row(SQLText,VendorName,InvoiceDate)

Do List.$getWhere(WhereRow) Returns FlagOK

The WhereRow technique might seem like extra work, but it guarantees that your where clause will work with different back end databases.

Note

OmnisSQL does not support bind variables for the LIKE operators however if you are connected to the Omnis data file the tBase methods in StudioWorks will automatically check for any LIKE operators and move the value from the bind variables row into the SQL text and replacing any single or double quotes in the value with underscore characters. So whether you are using the Omnis data file or another RDBMS you should always use bind variables for your values.

$doworkBatch

The $doworkBatch method is an StudioWorks table class method which does more than the built-in $dowork method provided by Omnis Studio.

Both methods will do the smartlist deletes, updates, and inserts for you.

The $doworkBatch method takes care of setting primary keys, administrative columns (InsBy, InsModDate, ModBy, ModDateTime, EditNum), checking for no null, no zero, no blank values as per the meta-data, and checking for child records before doing restricted or cascading deletes.

The $doworkBatch method has 2 parameters:

  1. pbSaveListWork - default kTrue. The $doworkBatch method will issue a $savelistwork for you if there are no errors reported. This sets the history list to match the normal list.

    If you are doing manual transactions with rollback/commit you will want to set pbSaveListWork to kFalse and handle the $savelistwork yourself. Here is sample code of how you would do this.

    ; Define the list.
    Do lsts.$retDefinedList('sSchemaClassName') Returns List

    ; Set it to be a smartlist.
    Do List.$smartlist.$assign(kTrue)

    ; Merge the import list records. (Status will be kRowInserted)
    Do List.$merge(ImportRecordsList,kTrue) ;; (Match column names)

    ; Store the transaction mode setting before we change it.
    Calculate StartTransSetting as List.$sessionobject.$transactionmode

    ; Set to manual transactions.
    Do List.$sessionobject.$transactionmode.$assign(kSessionTranManual)

    ; Open a working message
    Calculate workingmssg as "Inserting imported data..."
    Working message /-1072741743,-1072741739;50;0;0 {[workingmssg]}

    ; Do the inserts.
    Do List.$doworkBatch(kFalse,kFalse) Returns FlagOK ;; (Save list work, Check data)
    If FlagOK
       
       ; All went well, commit the transactions and make the history list match the normal list.
       Do List.$commit()
       Do List.$savelistwork()
       
    Else
       
       ; Error occurred. Rollback the transactions.
       Do List.$rollback()
       
    End If

    ; Set the transaction mode back to what is was when we started.
    Do List.$sessionobject().$transactionmode.$assign(StartTransSetting)

    ; If an error occurred report it to the user. (If this is a visual class method)
    If not(FlagOK)
       Do errhndlr.$promptonceLastError()
    End If
    Quit method FlagOK

  2. pbCheckData - default kTrue. The method will check for no null, no zero, no blank values as per the meta-data, and checking for child records before doing restricted or cascading deletes before trying to write the changes to the data base. The assumption is that the database is dumb (e.g. Omnis data file). Having StudioWorks check the data does hurt performance! If you are doing a large batch of database writes (e.g. Importing data) you will want to set pbCheckData to kFalse. There is a huge speed difference. However, be warned, you need to make sure the data is clean before you issue the $doworkBatch.

If you are only doing inserts use the $doinsertsBatch method. If you are only doing updates use the $doupdatesBatch method. $doworkBatch calls $dodeletesBatch, $doupdatesBatch, and $doinsertsBatch, so you save a couple method calls if you just call the $do...Batch method which applies directly to your operation.

If you are going all out after speed use the built-in $dowork method with manual transactions... but be sure to take care of setting the primary key and admin columns before issuing $dowork. The built-in $dowork uses different parameters that $doworkBatch... be sure to use the correct parameters!

For the fastest possible performance use manual transactions, a statement object, $prepare your statement using bind variables, and then use $execute inside a loop, followed by $commit if all went well, or $rollback if there was an error.

Set Default Value

You can specify default values for new records in the SQL Meta-Data Editor under the SQL Columns Editor tab of any schema class. If you hover of the Default Value field a tooltip will appear providing you with different options available to you for setting the default value for a column. The following examples give you an idea of what you can enter in the Default Value meta-data field.

  1. Number or Character value e.g. 0, 1, Male, Female
  2. Hash Variable. e.g. [#D], [#T]
  3. Omnis Function. e.g. [fday(kMonth,#D)], [dpart(kYear,#D)]
  4. Any Column Value in the same row. e.g. [???.Parent_pkey], [con(???.LastName,', ',???.FirstName]
  5. Security Object $:User... prefixed method in brackets. e.g. [$:UserKey], [$:UserFormalName]
  6. Task Variable Property Method in brackets. e.g. [cn.$:MainCompanyFormalName]
Note

You can set the default value to call any task variable method which returns a value.

The $setDefaults method of tBase is where the default values are set from the meta-data. If you put a red breakpoint in that method before creating a new record you can step through the code.

StudioWorks Documentation - Copyright 2005 Vencor Software