Programming in SQL

Why?

There are several very good reasons for programming Omnis data access using SQL instead of the standard Omnis data manipulation language (DML).

Multi-user web applications
Omnis web applications run in a single instance of the application server, but may serve several users. The traditional Omnis approach, involving reliance on a Current Record Buffer, is not stable under such circumstances.


Ability to use Industry Standard Data Servers
"Client-Server" solutions have many advantages over those using a native datafile:-

Application defined data objects
Systems can be designed to encapsulate data handling within objects, which are then independent from other parts of the application.


Connecting to SQL Data Sources

Omnis makes it fairly easy to organise a SQL log on, either to an Omnis datafile (which is not the same as opening the datafile in DML, or to any of the other supported data sources. the following code logs Omnis on to an Omnis datafile:-

Set current session MySession
Start session (dOMNIS)
Set hostname (%%PathToDatafile) ;;Where %%PathToDatafile is the datafile path 
Log on to host

You can also use the standard objects included with Studio to organise log ons, cursors, etc. for each of the supported data source formats. During development, you may wish to do a manual log on, using the SQL Object Browser. This tool also allows you to dynamically test SQL query code, or indeed run any SQL statement.

OmnisSQL

Each different data server has its own version of SQL. Of course, many SQL commands, such as a simple SELECT, are understood by all SQL data servers. But more complex commands may vary in syntax from one product to another. Omnis is no exception, and the syntax of SQL which you can use against an Omnis native datafile (called OmnisSQL) has some limitations. You can't use nested SELECT statements, and there is a special syntax for joins - INNER and OUTER JOIN clauses are not supported. A full guide to OmnisSQL is included in the Programming manual.

When programming applications, it may be a good idea to keep the SQL as generic as possible in order that minimal customisation is required for alternative data servers.

Sending SQL & Getting Results

You can simply use the Perform SQL command, or the SQL Script and Execute SQL Script commands, to send SQL to the database, and then return the results into a scalar variable or a list.

Set current session MySession ;;Important to make sure it's the right session
Perform SQL SELECT Index, Name, Address FROM Contact WHERE PostCode LIKE 'CV%'
Fetch next row into %%Index, %%Name, %%Address
; Populates the fields named with the first matching values
Do MyContactList.$define(%%Index,%%name,%%Address)
Build list MyContactList from select table
; Populates the list with remaining matching records

Using SQL Classes

Omnis Studio incorporates several classes designed specifically for communicating with SQL data.

Schema
This is the most basic SQL class, and is the equivalent, roughly speaking, of the Omnis Classic/DML file format. It defines the fields on the server data table. You can create schema classes by dragging tables from the SQL object browser into the library browser, and vice versa. When you do the latter, Omnis automatically generates the appropriate CREATE TABLE instruction and sends it to the data server.


Query
This class allows you to define a query across different data tables, with join conditions and calculated fields. Useful for frequently used data result sets.


Table
The table class is one of Omnis Studio's most powerful features, combining the properties of a schema or query with the abilities of an object class. This means that you can implement one class which holds both the definition of the data it represents, and the methods and properties relating to that data. To appreciate how this works, we need to know how to instantiate SQL classes.


To use one of these class types, you associate it with a list or row variable definition:-

Do MyList.$definefromsqlclass('skContact')

The list MyList is now associated with the schema skContact. It has columns matching the fields defined in the schema. You can use various special methods on a list when it has been defined from a SQL class, as in the following examples:-

Do MyList.$select('WHERE PostCode LIKE 'CV%')
Do MyList.$fetch(10) ;;Gets the first 10 matching records
Calculate MyList.2.Name as upp(MyList.2.Name) ;; Changes row 2 column Name to uppercase
Do MyList.$doupdates ;;Sends changes to the data server

The $doupdates method relies on the smartlist status of each list line, to tell it which ones have changed. When you use Table classes, you can define your own custom methods to further control data processing.

Using Bind Variables

Different syntaxes of SQL can vary as to the presentation of quote marks, date formats etc. Omnis gets around this neatly by using bind variables, which are translated by the DAM to the appropriate format. There are certain scope questions to take into account when using bind variables. If you're using instances of Omnis SQL classes, the bind variable will be evaluated within the scope of that class instance, so you can't use a local variable from the calling method. You don't have to worry about this when using Perform SQL. Here are a couple of examples:-

Calculate %%Date as '1/1/2000'
Perform SQL SELECT * FROM MyTable WHERE Date >= @[%%Date]

Do MyList.$definefromsqlclass('skMyTable')
Do MyList.$select('WHERE Date >= @[%%Date]') ;; Won't work
Calculate #S1 as %%Date
Do MyList.$select('WHERE Date >= @[#S1]') ;; Will work

Replacing the CRB

Using SQL means you have to get out of the Omnis Classic habit of relying on the current record buffer. This means using Object or Table classes to hold instances of the data being manipulated. For more information, see Further Reading.

Document prepared 8 AUG 2000 by Tim Stewart