Notation Methods

$definefromsqlclass

The $definefromsqlclass defines your list or row to match the columns in the SQL class. For table classes, the $sqlclassname property specifes the schema or query class used to define the list.

Do List|Row.$definefromsqlclass("[LibName.] TableClassName')
Do List|Row.$definefromsqlclass("[LibName.] SchemaClassName')
Do List|Row.$definefromsqlclass("[LibName.] QueryClassName')

You can also use an item reference variable which points to the table, schema, or query class.

Do List.$definefromsqlclass(rClass)

When you define from a schema class, Omnis binds its default table class to the schema class. The invisible default table class, has the Omnis $select, $fetch, $dowork, etc. methods.

When you define from a table class, you can add your own custom table class methods, and you can intercept the Omnis default table class methods ($select, $fetch, etc.).

You don't have to use table classes, but I strongly recommend you create a table class for every schema class in your application. You can also create a table class for a query class.

Table classes are powerful, don't ignore them. Table classes are a very compelling reason for moving from DML to SQL. You can read up more about table classes under that topic.

If you copy a list or row and the copy will also be bound to the table class. I find this faster than the $definefromsqlclass and less typing.


Do List.$definefromsqlclass('tBook')
Calculate Row as List


If the list has records in it and a current line is set. The row will contain the values from the current list row.

$select

Do List|Row.$select(WhereTextString)

What works best for me is to calculate the select criteria to a local variable called SQLText, then put that inside the $select() parenthesis. This gives me the chance to look at the actual select string as I'm stepping through and debugging the code.

; Define the list and set the session object.
Do List.$definefromsqlclass('tAuthor')
Do List.$sessionobject.$assign(sessobj)

; Select all authors which contain the name 'Ambler'
Calculate Value as '%Ambler%'
Calculate SQLText as con("WHERE AuthorName LIKE '",Value,"'")

Do List.$select(SQLText) Returns FlagOK
If FlagOK
   
   Do List.$fetch(kFetchAll) Returns FetchStatus
   Calculate FlagOK as FetchStatus<>kFetchError
End If

Quit method FlagOK

For SQL you must enclose character values in quotes, unless you use bind variables. If your comparison value includes single or double quotes you can replace them with the underscore _ character, or use bind variables. The underscore character is a single character wild card.

See the topic Bind Variables for more information.

% is a multiple character wild card.

• 'Kuy%' - will select all the names that begin with Kuy.
• '%Kuy' - will select all names containing Kuy

$select With Query Class

If you are using a query class with $extraquerytext, the SQL text included as a parameter with the $select will be appended to the $extraquerytext.

That affects whether you use WHERE or AND in the SQL text you send with the $select message.

In the following example we use a query class which includes columns from the Book table and the Author table. The query class includes the $extraquerytext, WHERE Author_fkey = Author_pkey, to join the Author records to the Book records.

Since the $extraquerytext is a WHERE clause, and the SQLText that we send with the $select message is appended to the $extraquerytext, we must begin our SQLText with AND rather than WHERE.

; Define the list and set the session object.
Do List.$definefromsqlclass('qBookAuthor')
Do List.$sessionobject.$assign(sessobj)

; Join books and authors using the qBookAuthor query class.
; Get all the books written by the author names which contain 'Ambler'
Calculate Value as '%Ambler%'

; Because there is $extraquerytext, we must use AND rather than WHERE in our SQL text.
Calculate ExtraQueryText as List.$extraquerytext
Calculate SQLText as con("AND AuthorName LIKE '",Value,"'")

Do List.$select(SQLText) Returns FlagOK
Calculate ExecutedSQLText as List.$statementobject.$sqltext
If FlagOK
   
   Do List.$fetch(kFetchAll) Returns FetchStatus
   Calculate FlagOK as FetchStatus<>kFetchError
End If

Quit method FlagOK

So that I don't have to think about whether to begin my SQLText with WHERE or AND, I include a $select method in my base table superclass and test for $extraquerytext which begins with a WHERE clause. If it does, I automatically replace the WHERE in the SQLText with AND.


; $select (method in base table superclass)

; Check if the $extraquerytext begins with WHERE.
If pos('WHERE ',upp($cinst.$extraquerytext))=1

; Check if the pSQLText also begins with WHERE.
If pos('WHERE ',upp(pSQLText))=1

; If so, replace the WHERE with AND.
Calculate pSQLText as con('AND ',mid(pSQLText,7))
End If
End If

; Do the built-in $select method.
Do default Returns FlagOK
Quit method FlagOK

$fetch

Do Row.$fetch()
Do List.$fetch([NumberOfRecords]) Returns FetchStatus

$fetch simply follows $select. $select prepares the list of records matching the select statement. $fetch then grabs the records. You don't have to take all the records at once. You can do a $fetch(10), then another, then another.

Do List.$fetch(kFetchAll) Returns FetchStatus
... or
Do List.$fetch(500) Returns FetchStatus

$insert

Do Row.$insert() Returns FlagOK

$update

Do Row.$update(OldRow) Returns FlagOK

$update updates the row in the database with the new row. $update only works for row variables. The row must have been $definefromsqlclass or copied from a list or row which was.

$delete

Do Row.$delete()

Deletes the row in the database. $delete only works for row variables.

The row must have been $definefromsqlclass or copied from a list or row which was.

If you set up a table class method called $delete, you can prevent deletion of critical records (Payroll records, AP Cheque records, etc.). For those tables I have a $delete method in the table class with the following code:

OK message Error. [sys(85)] (Icon,Sound bell) {You may not delete records in the [$cinst.$sqlclassname] table.////Notify the Programmer.}
Quit method kFalse

$dowork

Do List.$dowork()

The list must be a smartlist. Smartlists are a terrific feature of Omnis Studio. There is a separate topic in StudioTips for smartlists.

Smartlists keep track of list rows that are changed, rows that are added, and rows that are deleted. When you Do List.$dowork() Omnis Studio rips through the smartlist and carries out the deletes, updates, and inserts for you.

You can not make a row variable into a smartlist.

StudioTips Documentation - Copyright 2006 Vencor Software