All About Table Methods

Das Goravani goravanis at gmail.com
Sun Oct 2 20:36:03 UTC 2022



The commands $select(), $insert(), etc., are called "Table Methods" because they are methods within any Table Class. They are inherted to any table class from Omnis, they are built in methods in this way. They can be over-ridden. Thus you can edit them, but they are FINE as is. If you have the need for your basic Insert statement to do more, for example, then you might over ride it, put in your other code, at some point put "Do default" so that it does what it usually does, what it was originally programmed to do, in this case INSERT, then put more code after that if you want.  Some things that are examples of what you might put in an edited Insert() method are Assigning the user ID to a field in the table, assign date/times to columns, your exclude from insert command for your PK column, massaging of the data from one form to another, things that ALWAYS have to happen when inserting either in ALL RECORDS, then you override and do your custom insert method in the superclass, or if it is specific to one table, you do it in that table class. Read on to understand.

Omnis instantiates a hidden table class when needed like when you issue the command Do myList.$select() etc. Unless you have your own table classes and define lists and rows from them.. then it uses yours and it’s superclass if any, and I recommend yes you should have a superclass, for sure. 

But if you make a Table Class for each schema you use for defining lists and rows, and a master table class which is the super class for them all, then you can have more control over what those methods do for one thing, you can write methods in the individual table classes you’ve created or in the master which means they all get what you put there, and also, you can make use of the SQLErrors method that is a part of any table class. There is a massive code typing benefit to setting all this up. It’s called the EASE OF CALLING TABLE METHODS. They are launched off a list or row, and operate on the list or row, though in fact you can write ANY code in the table class, but usually you write things that operate against the list or row it is being applied to. The issuing of table methods is just a Do statement with a list or row and the method name.

Do myList.$method()

And by typing that, something happens to your list or row. This is great ease. This is the tightest code it could be. This is cool shorthand. This is very cool !

You can also do this if you setup table methods:

If myRow.$isTheDataCorrect

So you can put METHODS wherever the List or Row Name can go, as in here, it’s in an IF statement… that’s the only way you can execute code as an if statement. This is very good shorthand, very OO

There is nothing shameful about having short concise code. In fact it is smart. 

Table classes are intelligent. They link a row or list to a connection, they contain a definition of the results list as they are the row or list, they contain a record or records that are being retrieved from the database or being put back into it, thus a Table Class is an intelligent chunk of data, the intelligent part means it knows it’s connection fully. Thus they contain a SQL error aspect by definition, as they contain a statement object which by definition is loaded with that type of data. 

Switch the SQL Errors method within the master table class to being a method you control, then put in it an ok message or a breakpoint and examine $cinst.$statementobject.$nativeerrortext

I think that’s the notation for it, typing from memory here…

There is a way to have a global error pop up or interrupt, useful for both windows and forms, I have worked in an an app that had that happening somehow, but I didn’t look how it’s done, can’t relate it anyway due to contract. What comes to mind possibly is using $events in the Startup task or remote task, and look for something in $nativeerrortext, though I don’t know offhand how to tell you to pick a statement object to read unless you, like me, create one main statement object ref that is used for the one main database connection during the entire life of running the Omnis app. Even that won’t cover when you use Table Methods as they run off a statement object that is created within the table class when it is instantiated.. derived from the session object you assign to $cinst in the master table class. Or in the individual table classes if need be. 

But it is possible. A global SQL error catch that you can program, that works for table methods and typed out SQL both. 

There are the "Table methods" as described above, and then there is doing SQL with Sta: commands… or by any means whereby you are spelling out the SQL yourself and then executing it. 

Spelling out the SQL each time when most of what we do is a single table grab, whole table, whole row, after typing them over and over throughout an app one would either make their own single line callable method to do this or they would use the Table Methods.

The Table Methods are you could say a Macro for a chunk of Basic SQL. They are thus first and foremost "Handy".  They are shorthand.  They save you keystrokes.

Especially if you put a few things in key places.

You don’t HAVE to do this to use Omnis successfully. TRUE. It’s just EASE OF CODING happens as a result of doing it this way. A slick type of "Calling methods power". Table Classes are Omnis taken all the way.. the Omnis way all the way. 

In construct of your Table Superclass, put the assigning of the session object to $cinst as in

Do $cinst.$sessionobject.$assign(tSessionObject)

Where tSessionObject is a task variable you already assigned as either a session object directly, or a ref to a session object as in a SessionObjRef

If you have multiple SQL Sessions connected, then you will have to instead put that line in the construct of the individual table classes depending on which session and thus statement object that table belongs to. 

Then put in $construct of windows or forms that use a certain schema thus table class anywhere in the code, put the defining of the list you will use in memory on that form, window, define the list and optionally to above method, put the assigning of the session object also

By putting them in construct they are done when the class is opening, and they stay useable for the entire time the class is open, re usable, don’t need redefining or re assigning

Then, within that form or window and all code you write within it, you can assume these things remain constant, and just use the Table Methods without any other lines of code.

So at this point, they are REALLY handy

When you want to do a select, you do a single line select easily, the definition of the list tells it all it needs to know about columns and it’s session object assignment gives it a statement object too.. these describe which database session to "look out through" for this data… thus $select() is VERY Handy and powerful. 

It is equal to and as powerful as fully spelling out the SQL

Just as it is, it is BASIC. Which is WHAT WE MOST OFTEN WANT.

It is whole row one table.

You can define it to be only certain columns, then it is not whole row. Use the 2nd parameter description in the pop up help. You can define the list how you want. 

So that is why the shorthand table methods exist.. it is to save you keystrokes.  They are based on the concept of "What is done the MOST often?"

Spelling out the SQL is thus done when you want to go beyond WHOLE ROW SINGLE TABLE. Or beyond being able to simply define your list and still then only single table. 

That’s how far you can go with table methods as they are. Unedited. 

So thus if you want to say define your list, AND you want to do a join, AND you have a WHERE clause, it may just be easier to do it with SQL.

Table methods join the data object, the row or list text and definitions, to a SQL session object, or a connection to a database, it is all signed in and ready to get and put

So then the command structure

Do SomeList.$select('WHERE…')

Then becomes shorthand for a LOT of other things that are by this point "In Place" including your connection, your list definition, columns, match your database, thus within Omnis it is really easy to have various lists and rows based on different database connections or various sessions in other words

You can write methods in the master table class, and because all your other use it as the super class, they receive those methods too, so you can call those methods off of any list thus, your own table methods, that you can launch off of any list app wide.

And there are various ways to spell out SQL but the Begin and End Statement approach is the one that is most dialed in, connected, to the coding process

Then you can use PREPARE and EXECUTE to have things broken up into their most basic SQL components that match the standard

Or you can use $execdirect() to run the SQL 

The connection is being done through the data in the Statement Object that you are using which connects to it’s parent Session Object.  That’s the database connection.

So when you do $StatementObject.$fetch(SomeListName)

Now it knows the connection, the select table from the statement object, then the list you name as the place to define and put the results.

So both Table Methods and spelling out SQL need the same things as in their connection, their list to put results, a way to define the results list if that has to happen, that is drawn from the select statement when you spell out SQL, or you use a feature that allows you to define the list, such as the second parameter on $definefromSQLClass() or you spell out the names in your SQL, or you use $selectnames() in a method to get the names, then that is how your results list is defined, there are various ways

They all work

Tests may reveal one method is faster than another. But this only matters when doing huge loops where you do selects, inserts, updates, deletes…

In regular use, almost all use, go ahead and use the simplicity of Table Methods when they suit common simple needs esp to do whole row single table

You don’t have to define Table Classes, and their Master, yes, you can skip table classes.

You can still type those table methods on lists and rows, because Omnis opens a hidden table class when you issue one of those commands and then it executes the code in that method in the table class.. it’s just a place to put code. That is all. Table classes perform various little tricks due to have the session object assigned to them (via $cinst) so they know their connection, their table, and it’s schema, so Tables have links and definitions already set, ready to perform SQL, but they are also Data Objects, as in row or list, they are that in the programming language, that is how we use them, but they are code in another part of themselves, the fact that you can type methods into the Table Class, and perform them like this:

Do someList.$myMethod

Is very handy, as it is A connection, A table, A list definition, AND A METHOD to be performed against those other things. 

Quite powerful, the ultimate shorthand for executing SQL

TAKE the EASE..  take the intelligence of this, and USE IT.  Use the Table Classes. It only takes a couple minutes to create one for each of your schemas, and attach them all to one which is the master or super class

You assign the session object to $cinst in the construct of this master class, so it happens ANYTIME YOU DEFINE A LIST FROM A TABLE CLASS

WHICH IS HOW YOU SHOULD DEFINE YOUR SQL LISTS… USE YOUR TABLE CLASSES

AS IN

Do myList.$definefromSQLclass($tables.MyTableName)

That connects this list to that table class, it will run it’s methods if you type them against this list or row.

It will look to the super class for the methods too.

Going further, the above being the basic way to go especially if you have one database connection 

It can SO do MORE.. just some of my tricks laid out herein need to be edited

And to mention your question again, or say again, that Table Classes have a SQL Error Method already built in, which you should override and do something with, like somehow examine $native error text and the other error messages that Omnis has for SQL

Examine the relevant Session and Statement objects for SQL error text methods or properties, as I don’t remember them offhand right now.

There are I think 4 main ones, Error Code and Text, twice, one is "native" and the other is not.. forget the syntax sorry

I’m pretty sure the main one is $statementobject.$nativeErrorText

You have have to put a list name before $statementobject or some other place which hosts statement object references of some type. There are a few such locations within the notation tree, just I’d have to hunt for them


When using Postgres, you cannot pass in the Primary Key as a column when doing inserts, you have to omit in the columns definition on INSERT statements, including Table Methods, you need a way to remove this column from the SQL.

Omnis provides the way. It is

SomeList.$cols.1.$excludefrominsert.$assign(kTrue)

The 1 can also be the column name. 

You can put this in $construct for the table super class so that they all get it, in that case you use

$cinst instead of SomeList


When using Postgres, and you do an insert, you want to get back the Primary Key it assigned to the record, so that you can tell that number potentially to other records in other tables as it’s FK

But usually thus you want to know the Primary Key right after inserting the record

The way to do this is to put 

RETURNING my_field

On the end of the insert statement table method as in

Do SomeRow.$insert(RETURNING the_primarykey)
Do SomeRow.$fetch(OtherListOrRowThatReceivesThePrimaryKeyValue)

This is necessary with Postgres and most likely many other databases. 

Put the RETURNING clause on your other SQL Insert Statements if you use Postgres or many others..




> On Oct 2, 2022, at 2:26 PM, Martin Obongita via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
> 
> Hi All,
> I am also confused why we have two types of sql commands in Omnis:
> 1. Begin...End statement
> 2. Do iDataList.$select()... Do iDataList.$fetch()
> When is each one recommended for use?
> 
> 
> 
> 
>    On Sunday, October 2, 2022 at 09:19:31 PM GMT+3, Martin Obongita via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:  
> 
> Hi All,
> I was going through the archives looking for information on $select sql statement.
> (The documentation has scanty info)
> I found a 2014 discussion that is close to what I am looking for.
> O6 - $select()
> 
> 
> | 
> | 
> |  | 
> O6 - $select()
> 
> 
> |
> 
> |
> 
> |
> 
> 
> 
> The question I wish to follow up with this discussion is,
> How do I get or trap for sql errors in a select command with a where clause?eg:
> Do iDataList.$select('WHERE name = Martin')
> 
> Martin Obongita
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 



More information about the omnisdev-en mailing list