Omnis Technical Note TNSQ0004

Omnis Studio Examples - SQL

For Omnis Studio
By Rudolf Bargholz

The following topics are included in the example library and datafile provided below:

1) Making the connection, especially to Oracle.
2) Basic SQL statements and examples
3) Omnis code implementing basic SQL
4) Using Row variables
5) Using List variables
6) Supertable classes and overriding standard Omnis methods
7) More complex SQL, specifically to do with Oracle:
  - cc
  - creating and using stored procedures
  - creating and using Oracle functions
  - Oracle ROWID etc.

There are many comments placed in the code to explain exactly what is happening and why, including
a lot of documentation about specific commands. The best way to navigate is by stepping through the
code. By the way, if you press SHIFT and ALT while pressing on a button you will automatically get to
the code behind the button.

The following is necessary to use the demonstration:

1) An Oracle server
2) An Easy Config alias to the Oracle server called 'oracle_nt'
3) If the 'Customer' table, or any other of the three needed tables, which can be found on the 'GeekWeek.df1' datafile, is not on the Oracle server, one has to use the SQL object browser
to drag and drop them from the Omnis session to the Oracle session.
4) Omnis Studio 2.0 or higher with a SQL serial number, i.e. it would start with D2DYA... or D2DNA....

There is also a PowerPoint slide show from the Euro Geek conference included below.

This includes an introduction to SQL with Omnis Studio and the native Omnis Data Manipulation Language. Other topics include making the connection, DAMs, DML Vs SQL, example logons, transaction handling, SQL basics, Omnis sys() values, the schema and table classes and much more.

Download the sample libraries (Studio-examples-sql.zip)