OdxSQL processes Omnidex SQL statements and OdxSQL specific commands either interactively or in batch oriented script files via the USE command.
The following is a list of Omnidex SQL Statements and OdxSQL commands organized by type with a complete alphabetic list shown below.
OdxSQL works by first connecting to an Omnidex Environment or an ODBC or JDBC DataSource (DSN) that references an Omnidex Environment on a remote server. Although ODBC and JDBC applications use a connection call or object, OdxSQL uses Connect and Disconnect commands.
| Statement | Description |
|---|---|
| CONNECT | Connect to an Omnidex Environment or ODBC/JDBC Datasource File (DSN). |
| DISCONNECT | Disconnect from a previously Connected Omnidex Environment or ODBC or JDBC Datasource (DSN). |
| ATTACH | Attach an OST file as a table to the currently connected Omnidex Environment. |
| DETACH | Detach a previously attached Omnidex Standalone Table (OST) file. |
OdxSQL can process all Omnidex SQL Data Manipulation Statements (SELECT, INSERT, UPDATE, and DELETE) as well as lower level Omnidex SQL routines.
| 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 INDEX GROUP | Create Index Group |
| 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. |
| SHOW DDL | Show Data Definition Statements. |
| DML Statement | Description |
|---|---|
| JOIN | Join tables using Omnidex Indexes. |
| QUALIFY | Qualify rows using Omnidex criteria. |
| FETCH | Explicitly fetch data from the database. |
| FETCHKEYS | Explicitly fetch keys from the Omnidex Indexes. |
| OPEN CURSOR | Open a Cursor. |
| CLOSE CURSOR | Close a previously opened cursor. |
OdxSQL has commands that operate and update information for an Omnidex Environment and the individual databases contained in the Omnidex Environment.
| Statement | Description |
|---|---|
| PARTITION | Partition a table into smaller sub tables based on criteria. |
| UPDATE ROLLUP | Update Rollup Table with summarized information. |
| UPDATE ROLLUPS | Update all Rollup Tables for an Omnidex Database. |
| UPDATE STATISTICS | Update Statistics Information. |
| UPDATE TEXT | Update Textual Statistics. |
| LOAD OFX | Load Indexes for a table. |
| SET | Set Omnidex SQL Conditions. |
OdxSQL has commands that can be used to export and partition data. These can be used to easily pull data from RDBMS data stores into Omnidex Snapshots.
| Command | Description |
|---|---|
| CONVERT | Export a file with data conversion. |
| EXTRACT | Extracts SQL Statements from a table and writes them to a file. Removed in 5.1 |
| SHOW | Show Omnidex Environment information including Databases, Tables, Column layouts, Query Statistics, etc. |
| VALIDATE | Validate the Omnidex Environment's view of the underlying data. Removed in 5.1 |
OdxSQL has several commands that facilitate the processing, editing, re-issue of previous commands and processing multiple command blocks.
| Command | Description |
|---|---|
| USE | Read and process a file of OdxSQL Commands and Omnidex SQL Statements, optionally with WHERE criteria. |
| Redo Command ( . ) | Execute the last command. |
| HISTORY | Show the history of OdxSQL Commands and Statements. |
| n | Execute the numbered OdxSQL History Command or Statement. |
| EDIT | Edit the current command, a previous command or file using Notepad, vi or a named editor. |
| SAVE HISTORY | Save the OdxSQL Command History to a file. |
| RESET HISTORY | Resets the OdxSQL History of Commands and Statements. |
| SET | Set an OdxSQL setting. |
| SAVE SETTINGS | Save OdxSQL Settings to a file for use with a later RESTORE SETTINGS Command. |
| RESTORE SETTINGS | Restore Settings, optionally from a file that was created with a previous SAVE SETTINGS command. |
| FOR | Execute the next one or more commands the specified number of times. |
| IF | Form an IF ELIF ELSE ENDIF Construct. |
| HELP | Show a list of commands help on individual commands, COMMANDS, SYNTAX and API. |
| IS | Check Table Conditions. |
| DATE | Show the current Date and Time. |
| CALC | Perform basic calculator functions. |
| EXIT | Exit the OdxSQL program. |
| QUIT | Quit the OdxSQL program. Same as Exit. |
| Comment ( ; ) | Use a semicolon as the first character of a line for a comment line. |
| VERSION | Display current Omnidex Software versions. |
OdxSQL contains several commands that facilitate the testing and optimization of an Omnidex Environment.
| Command | Description |
|---|---|
| EXPLAIN | Explain the optimization approach to a previous retrieval. |
| RESET TIMER | Reset the OdxSQL Cummulative Timer. |
| BENCHMARK | Execute Benchmarks to analyze performance. |
| FORMAT | Format a file of Omnidex SQL Statements. |
| ERROR | Display the text of an error message from the Omnidex Error Message file. |
| EXECSQL | Execute an Omnidex SQL Statement directly with the oaexecsql routine without OdxSQL doing any processing. |
OdxSQL contains several commands that access the underlying operating to facilitate the Omnidex Administrator or Developer staying inside of OdxSQL for many tasks.
| Command | Description |
|---|---|
| ! <shell command> | Run an OS command in the OS Shell and return or just an ( ! ) for an interactive shell. |
| COMMAND | Execute a shell command via the $COMMAND table. |
| CD Change Directories | Change Directories in the underlying Operating System. |
| CREATE DIRECTORY | Create a directory in the underlying Operating System. |
| PWD | Print (display) the current working directory from the underlying Operating System. |
| DIR | Display a directory of files from the current directory in the underlying Operating System. |
| EDIT | Edit a previous command or file by running Notepad or vi or named editor. |
| COPY | Copy a file or fileset. |
| CREATE FILE | Create a File in the underlying Operating System. |
| REMOVE | Remove (delete or purge) one or more files or directories from the underlying Operating System. |
| RENAME | Rename a file or fileset in the underlying Operating System. |
| VIEW | View a text file. |
The following is a complete list of all OdxSQL Commands and all Omnidex SQL Statements that can be entered through OdxSQL.
| Command | Type | Description |
|---|---|---|
| ! <shell command> | Command | Run the command in the OS Shell and return or ! for interactive shell |
| . Redo Command | Command | Execute the last command. |
| n | Command | Execute the numbered OdxSQL History Command or Statement. |
| ATTACH | Statement | Attach an OST file as a table to the currently connected Omnidex Environment. |
| BENCHMARK | Command | Execute Benchmarks to analyze performance. |
| CALC | Command | Perform basic calculator functions. |
| CD | Command | Change Directories in the underlying Operating System. |
| CLOSE CURSOR | Statement | Close a previously opened cursor. |
| CONNECT | Both | Connect to an Omnidex Environment or ODBC/JDBC Datasource File (DSN). |
| CONVERT | Command | Export a file with data conversion. |
| Comment ( ; ) | Command | Use a semicolon as the first character of a line for a comment line. |
| COPY | Command | Copy a fileset. |
| CREATE DATABASE | Statement | (V5.1) Create ad Database within an Omnidex Environment. |
| CREATE DIRECTORY | Statement | Create a directory in the underlying Operating System. |
| CREATE ENVIRONMENT | Statement | (V5.1) Create an Omnidex Environment. |
| CREATE FILE | Command | Create a File in the underlying Operating System. |
| CREATE INDEX | Statement | (V5.1 Create Index |
| CREATE INDEXES | Statement | Create Omnidex Indexes. |
| CREATE TABLE | Statement | (V5.1) Create a Table. |
| DATE | Command | Show the current Date and Time. |
| DELETE | Statement | Issue the SQL DELETE Statement. |
| DETACH | Statement | Detach a previously attached Omnidex Standalone Table (OST) file. |
| DIR | Command | Display a directory of files from the current directory in the underlying Operating System. |
| DISCONNECT | Statement | Disconnect from a previously Connected Omnidex Environment or ODBC or JDBC Datasource (DSN). |
| DROP DATABASE | Statement | Drop Database(V5.1) |
| DROP ENVIRONMENT | Statement | Drop Environment. (V5.1) |
| DROP INDEX | Statement | Drop Index. (V5.1) |
| DROP INDEXES | Statement | Drop Indexes. |
| DROP TABLE | Statement | Drop a previously created temporary table. Expaned in (V5.1) |
| EDIT | Command | Edit a previous command or file. |
| ERROR | Command | Display the text of an error message from the Omnidex Error Message file. |
| EXECSQL | Command | Execute an Omnidex SQL Statement directly with the oaexecsql routine without OdxSQL doing any processing. |
| EXIT | Command | Exit the OdxSQL program. |
| EXPLAIN | Command | Explain the optimization approach to a previous retrieval. |
| EXPORT | Statement | Export rows to a file. |
| EXTRACT | Command | Extracts SQL Statements from a table and writes them to a file. To be removed in 5.1 |
| FETCH | Command | Explicitly fetch data from the database. |
| FETCHKEYS | Command | Explicitly fetch keys from the Omnidex Indexes. |
| FOR | Command | Execute the next one or more commands the specified number of times. |
| FORMAT | Command | Format a file of Omnidex SQL Statements. |
| HELP | Command | Help on COMMANDS, SYNTAX and API. |
| HISTORY | Command | Show the history of OdxSQL Commands and Statements. |
| IF | Command | Form an IF ELIF ELSE ENDIF Construct. |
| INSERT | Statement | Issue an Omnidex SQL INSERT Statement. |
| JOIN | Statement | Join tables using Omnidex Indexes. |
| LOAD OFX | Statement | Load Indexes for a table. |
| OPEN CURSOR | Statement | Open a Cursor. |
| PARTITION | Statement | Partition a table into smaller sub tables based on criteria. |
| PWD | Command | Print (display) the current working directory from the underlying Operating System. |
| QUALIFY | Statement | Qualify rows using Omnidex criteria. |
| QUIT | Command | Quit the OdxSQL program. Same as Exit. |
| REMOVE | Command | Remove (delete or purge) one or more files or directories from the underlying Operating System. |
| RENAME | Command | Rename a file or fileset in the underlying Operating System. |
| RESET HISTORY | Command | Resets the OdxSQL History of Commands and Statements. |
| RESET TIMER | Command | Reset the OdxSQL Cummulative Timer. |
| RESTORE SETTINGS | Command | Restore Settings, optionally from a file that was created with a previous SAVE SETTINGS command. |
| SAVE HISTORY | Command | Save the OdxSQL Command History to a file. |
| SAVE SETTINGS | Command | Save OdxSQL Settings to a file for use with a later RESTORE SETTINGS Command. |
| SELECT | Statement | Issue an Omnidex SQL SELECT Statement. |
| SET | Both | Set an OdxSQL setting. |
| SHOW | Command | Show Omnidex Environment information including Databases, Tables, Column layouts, Query Statistics, etc. |
| SHOW DDL | Show Data Definition Statements. | |
| SIGNAL | Command | Sends a Signal to a process. |
| UPDATE | Statement | Issue an Omnidex SQL UPDATE Statement. |
| UPDATE INDEXES | Statement | Update Omnidex Indexes |
| UPDATE ROLLUP | Statement | Update Rollup Table with summarized information. |
| UPDATE ROLLUPS | Statement | Update all Rollup Tables for an Omnidex Database. |
| UPDATE STATISTICS | Statement | Update Statistics Information. |
| UPDATE TEXT | Statement | Update Textual Statistics. |
| USE | Command | Read and process a file of OdxSQL Commands and Omnidex SQL Statements, optionally with WHERE criteria. |
| VALIDATE | Command | Validate the Omnidex Environment's view of the underlying data. To be removed in 5.1 |
| VERSION | Command | Display current Omnidex Software versions. |
| VIEW | Command | View a text file. |