Notation Methods

$definefromsqlclass

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

The $definefromsqlclass defines your list or row to match the columns in the SQL class.

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.

$delete

Do Row.$delete()

$delete deletes the row in the datafile.

$delete only works for Row variables.

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

TIP: 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:

$dowork

Do List.$dowork()

The list must be a smartlist. Smartlists are a terrific feature of 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()" Studio rips through the smartlist and carries out the $updates, $inserts, and $deletes for you.

NOTE: You can not make a Row variable into a smartlist.

$fetch

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

$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. So far I've always been fetching all the records. I use kFetchAll.

Do List.$fetch(kFetchAll)
... or
Do List.$fetch($ref.$linemax)

If you don't include a parameter or are using a Row variable fetch will only return 1 record at a time. If I recall correctly, in the case of the list, whatever value you $fetch sets the list.$linemax.

kFetchAll = 1,000,000,000 (One billion) $linemax = 1,000,000,000 for a new list.

$insert

Do Row.$insert()

$insert simply inserts the current row data into the data file as a new record. $insert only works with a row, not a list.

$select

Do List|Row.$select('where ColName = Value)

I'm no expert with SQL selects, so if you can get better advice, use it (and e-mail it to me).

I got confused by the syntax of the stuff that goes between the brackets in the $select().

What worked best for me was to calculate my select criteria to a local variable called 'Script', then put that in the brackets. This also give me the chance to look at my select string as I'm stepping through the code to figure why it doesn't work. So the $selects in my code tend to look like this:

Calculate Script as con("where ColName ='",Value,"'")
Do List|Row.$select(Script)
Do List|Row.$fetch(kFetchAll)

Note the single quote ' after the = sign, and between the double quotes after the Value. For SQL you must enclose character and date values in quotes.

% is a wild card, so where ColName ='Kuy%', will select all the names starting with Kuy.

Read up in the manual on the various operators you can use > = < %

$update

Do Row.$update(OldRow)

$update updates the row in the datafile 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.

Note: Make CERTAIN the schema class has a primarykey column set to kTrue.

StudioTips Documentation - Copyright 2006 Vencor Software