This is an old revision of the document!
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. |
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. |