Tips_sql   >   Omnissql   >   OmnisSQL (All Contents)

Omnis SQL

Using OmnisSQL you can use SQL with the Omnis Data file. Omnis is really storing the data in OmnisDML format. OmnisSQL is simply a SQL interface to the Omnis data file.

Omnis is not in the business of building SQL backends. They are not trying to compete with Oracle DB2, Frontbase and other commercial SQL backends. If you have many records and need lots of speed then you should be looking at 3rd party backends.

At the time of writing these I'd been using OmnisSQL for 2 years with about 10 users accessing the data file. Running Accounting, Payroll, Personnel, Purchasing, Inventory, etc. for a 200 staff operation. To date the speed has been quite acceptable and the data file rock solid reliable.


Since OmnisSQL is not a full blown SQL server back end, it has limitations. Listed below are the limitations that others have told me about. I'm not an SQL expert, so some of this information might not be explained 100% correct.

OmnisSQL does not have transaction management. There is no ROLLBACK or COMMITT.

You must generate your own primary keys.

OmnisSQL does not support foreign key relationships. OmnisSQL can not enforce restricted or cascading relationships. You must write the code to enforce foreign key relationships.

OmnisSQL does not support subqueries.

You can not join a table more than once to another table. To work around this you need to make the first join, then fetch the second parent record and copy its values to the correct columns in the initial join row.

If you know of more OmnisSQL limitations, please email them to me and I'll add them to the list.