About the various SQL Lists Possible

Das Goravani goravanis at gmail.com
Fri Jun 17 20:10:50 UTC 2022


Dear $OS SQL People,

I’m only used to using the Table Methods approach to SQL in Omnis. Now I have studied how to do the more manual method where you spell out your SQL manually for example using the Sta: command a lot.

To jump right in:

With the table methods, You have to assign the session object to the list.
Question: When you do SQL manually, using Sta: $prepare $execute and $fetch, you do NOT need to assign the session object to the results list used in $fetch(myListorRow) do you?

Is all of the following TRUE: 

When you do SQL manually you often fetch lists or rows which do not contain all the columns present in any schema, or "the" schema in question. Sometimes fetched results lists or rows have derived columns too that are not columns in the database. These result lists are kept small for performance reasons and other reasons. 

They are NOT assigned the session object because the $prepare() and $execute() methods are done off of a specific statement object or ref and that is how it is known which database session you are connecting to, because the statement object is made from a session object which represents a connection to a specific database, possibly logged in as a certain user. 

You don’t need to define the list you are fetching into.. if it is not defined, $fetch() will define it according to the SELECT statement including derived columns or columns renamed with the AS aliasName clause. In this case the list is defined with the column names named in the SELECT statement and it will respect AS aliases. When doing manual SQL re. The INSERT and UPDATE statements: For the values in both of these you simply use bind variables with references to your list or row in this form   @[myList.myColumnName] when you want to pass a row or list column as the value (as opposed to some calculation). 

Thank you for your time, 

Das


More information about the omnisdev-en mailing list