This is an old revision of the document!
Omnidex provides a high-level SQL interface to access databases enhanced with Omnidex indexes. The SQL supported is a subset of the ANSI SQL2 and ANSI SQL99 standard along with some very powerful extensions specific to Omnidex.
SQL should be used for all Omnidex application development including ODBC applications, Java applications using the Omnidex JDBC driver, and OmniAccess applications through oaexecsql. The Query Language extensions work in conjunction with the SQL standard syntax, in all types of applications.
Third party ODBC tools such as Microstratey, Cognos Impromptu, and Microsoft's Query, Access, Excel, and Word communicate with Omnidex via the Omnidex ODBC driver. Development tools such as Microsoft's Interdev, Visual Basic, and Visual C++ can communicate with Omnidex via the OmniAccess API or the Omnidex ODBC driver using object layers such as ActiveX Data Objects (ADO).
Java applications such as applets, servlets, and standard console applications can communicate with Omnidex via the Omnidex JDBC driver.
Omnidex SQL is a rich implementation of the SQL2 and SQL99 standards along with extensions specific to Omnidex.
Omnidex SQL Statements are callable via the ODBC and JDBC Interfaces.
Omnidex SQL statements can also be tested and scripted using the OdxSQL program.
| SELECT | Issue an Omnidex SQL SELECT Statement. |
| INSERT | Issue an Omnidex SQL INSERT Statement. |
| UPDATE | Issue an Omnidex SQL UPDATE Statement. |
| DELETE | Issue the SQL DELETE Statement. |
| OPEN CURSOR | Open a Cursor. |
| ASSIGNVALUES | Assign values for a $VALUES clause to be used in a subsequent SELECT. |
| JOIN | Join tables using Omnidex Indexes. |
| QUALIFY | Qualify rows using Omnidex criteria. |
| DESCRIBE | Describe a SELECT Statement. |
| BIND | Bind a SELECT Statement. |
| FETCH | Explicitly fetch data from the database. |
| FETCHKEYS | Explicitly fetch keys from the Omnidex Indexes. |
| CLOSE CURSOR | Close a previously opened cursor. |
| DDL Statement | Description |
|---|---|
| CREATE ENVIRONMENT | Create an Omnidex Environment. |
| CREATE DATABASE | Create ad Database within an Omnidex Environment. |
| CREATE TABLE | Create a Table. |
| CREATE INDEX | Create Index |
| CREATE INDEXES | Create Omnidex Indexes. |
| DROP ENVIRONMENT | Drop Environment. |
| DROP DATABASE | Drop Database |
| DROP TABLE | Drop a previously created temporary table. |
| DROP INDEX | Drop Index. |
| DROP INDEXES | Drop Indexes. |
| INSERTINDEX | Insert values directly into the Omnidex Indexes. |
| UPDATEINDEX | Update values directly in the Omnidex Indexes. |
| DELETEINDEX | Delete values directly from the Omnidex Indexes. |
| Statement | Description |
|---|---|
| ASSIGNVALUES | Use an undetermined set of values as criteria in a query. |
| ATTACH | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment for queries and manipulation. |
| CLOSE CURSOR | Closes a previously opened cursor. |
| OS Command | Execute an operating system command. |
| CONNECT | Connect to an Omnidex Environment. |
| CREATE DATABASE | Create a Database with an Omnidex Environment. |
| CREATE DIRECTORY | Create a Directory in the underlying operating system. |
| CREATE ENVIRONMENT | V5.1 Create an Omnidex Environment. |
| CREATE FILE | Create a file in the underlying operating system. |
| CREATE INDEX | V5.1 Create an Index. |
| CREATE INDEXES | ??? |
| CREATE TABLE | Create a table within an Omnidex Environment or create a table in the underlying RDBMS. |
| DELETE | Delete a row or rows of data. |
| DELETEINDEX | Delete an Omnidex Index value. |
| DETACH | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. |
| DISCONNECT | Disconnect from a connected Omnidex Environment. |
| DROP DATABASE | V5.1 Drop a Database from within an Omnidex Environment. |
| DROP ENVIRONMENT | V5.1 Drop an Omnidex XML Environment and purge the underlying operating system file. |
| DROP INDEX | V5.1 |
| DROP INDEXES | ??? |
| DROP TABLE | Drop a table created with a CREATE TABLE or SELECT INTO statement. |
| EXPORT | Export rows or rowids to an external file. |
| HELPER | ??? |
| INSERT | Insert a row of data. |
| INSERTINDEX | Insert values directly into the Omnidex indexes. |
| JOIN | Manually join two tables for a joined query |
| LOAD OFX | Load an OFX file. |
| OPEN CURSOR | Open a cursor |
| PARTITION | Partition Data based on criteria. |
| QUALIFY | Qualify rows in a query. |
| SELECT | Select rows , aggregate rows, using Omnidex enhanced criteria. |
| SET | Set Omnidex SQL processing options. |
| UPDATE | Update rows in a table. |
| UPDATE INDEXES | V5.1 |
| UPDATE ROLLUP | Update Rollup Tables with new calculations. |
| UPDATE ROLLUPS | ??? |
| UPDATE STATISTICS | Update the Omnidex Statistics information. |
| UPDATE TEXT | Update Text information. |
| UPDATEINDEX | Update Omnidex Index Information directly. |
| VERSION | Retrieve Omnidex Version Information. |