This is an old revision of the document!


DRAFT

OdxSQL Commands

Overview

OdxSQL is used to process a wide range of Omnidex SQL Statements and OdxSQL specific commands.

The OdxSQL Commands and Omnidex SQL Statements are organized for quick review as follows:

OdxSQL Commands and Omnidex SQL Statements can be entered into OdxSQL in ether upper or lower case.

> connect myenv
> CONNECT myenv

The syntax for Omnidex SQL Statements entered into OdxSQL or processed in a script file is typically the exact same syntax and returns the exact same results.

There are a few exceptions such as CONNECT and VERSION.

In OdxSQL, CONNECT has the additional options of ON host:port and USING api that would not make sense in an ODBC or JDBC application.

In OdxSQL, the VERSION command displays the OdxSQL header and multiple lines of version information where the Omnidex SQL VERSION statement returns a single line.

The OdxSQL EXECSQL command can be used to verify if an Omnidex SQL Statement is processed exactly the same by OdxSQL as the Omnidex SQL parser.

> VERSION
ODXSQL - Omnidex SQL                                   Wed Dec 02 09:20:41 200
...
Omnidex Version                  5.0 Build 2B
Compilation Timestamp            Oct 13 2009  08:57:09
Earliest Compatible ODBC Client  4.3 Build 4B
Earliest Compatible JDBC Client  4.3 Build 4B
Earliest Compatible Odx Kernel   5.0 Build 1B

> EXECSQL VERSION
OmniAccess version 50002
> 

Connecting and Disconnecting

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.

CONNECT Both Connect to an Omnidex Environment or ODBC/JDBC Datasource File (DSN).
DISCONNECT Statement Disconnect from a previously Connected Omnidex Environment or ODBC or JDBC Datasource (DSN).
ATTACH Statement Attach an OST file as a table to the currently connected Omnidex Environment.
DETACH Statement Detach a previously attached Omnidex Standalone Table (OST) file.

Omnidex SQL Data Definition Language (DDL) Statements

DDL Statement Description
CREATE ENVIRONMENT Statement Create an Omnidex Environment.
CREATE DATABASE Statement Create ad Database within an Omnidex Environment.
CREATE TABLE Statement Create a Table.
CREATE INDEX Statement Create Index
CREATE INDEXES Statement Create Omnidex Indexes.
DROP ENVIRONMENT Statement Drop Environment.
DROP DATABASE Statement Drop Database
DROP TABLE Statement Drop a previously created temporary table.
DROP INDEX Statement Drop Index.
DROP INDEXES Statement Drop Indexes.

Omnidex SQL Data Manipulation Language (DML) Statements

OdxSQL can process all Omnidex SQL Data Manipulation Statements (SELECT, INSERT, UPDATE, and DELETE) as well as lower level Omnidex SQL routines.

SELECT Statement Issue an Omnidex SQL SELECT Statement.
INSERT Statement Issue an Omnidex SQL INSERT Statement.
UPDATE Statement Issue an Omnidex SQL UPDATE Statement.
DELETE Statement Issue the SQL DELETE Statement.

Additional Omnidex SQL Statements

JOIN Statement Join tables using Omnidex Indexes.
QUALIFY Statement Qualify rows using Omnidex criteria.
FETCH Statement Explicitly fetch data from the database.
FETCHKEYS Statement Explicitly fetch keys from the Omnidex Indexes.
OPEN CURSOR Statement Open a Cursor.
CLOSE CURSOR Statement Close a previously opened cursor.

OdxSQL Environment Operation Commands and Statements

OdxSQL has commands that operate and update information for an Omnidex Environment and the individual databases contained in the Omnidex Environment.

SHOW Command Show Omnidex Environment information including Databases, Tables, Column layouts, Query Statistics, etc.
VALIDATE Command Validate the Omnidex Environment's view of the underlying data.
PARTITION Statement Partition a table into smaller sub tables based on criteria.
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.
LOAD OFX Statement Load Indexes for a table.

OdxSQL and Omnidex SQL Data Export and Manipulation

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.

PARTITION Statement Partition a table into smaller sub tables based on criteria.
CONVERT Command Export a file with data conversion.
EXPORT Statement Export rows to a file.
EXTRACT Command Extracts SQL Statements from a table and writes them to a file.

OdxSQL Program Operation Commands

OdxSQL has several commands that facilitate the processing, editing, re-issue of previous commands and processing multiple command blocks.

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.
EVAL Evaluate a condition.
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 Testing and Performance Commands

OdxSQL contains several commands that facilitate the testing and optimization of an Omnidex Environment.

EXPLAIN Explain the optimization approach to a previous retrieval.
VALIDATE Validate the Omnidex Environment's view of the underlying data.
RESET TIMER Reset the OdxSQL Cummulative Timer.
BENCHMARK Execute Benchmarks to analyze performance.
EXTRACT Extracts SQL Statements from a table and writes them to a file.
FORMAT Format a file of Omnidex SQL Statements.
n: Send the numbered OdxSQL History Command to a son process.
ERROR Display the text of an error message from the Omnidex Error Message file.
MESSAGE Display an Omnidex Environment Message.
EXECSQL
NETWORKACCESS Execute an Omndiex Network Access Command.
SLEEP Sleep for a specified number of seconds.
ABORT Issues an Operating System Abort - used to test abnormal conditions.
SIGNAL Sends a Signal to a process.
SLEEP Sleep for a specified number of seconds.
DUMP Dump files, memory or caches.
OXKERNEL Execute an Omnidex Kernel Command. Should be used for Debugging with DISC Personnel.
PARSE Parse an Omnidex SQL Statement to a file. This would only be used by DISC Personnel.
SORTMERGE Internal: Activates the Sort/Merge Test System.

OdxSQL Operating System Commands

OdxSQL contains several commands that access the underlying operating to facilitate the Omnidex Administrator or Developer staying inside of OdxSQL for many tasks.

! <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.
SETENV Set an Operating System Environment Variable.
GETENV Display an Operating System Environment Variable.
ABORT Issues an Operating System Abort - used to test abnormal conditions.

Omnidex Index Direct Maintenance Routines

INSERTINDEX Insert values directly into the Omnidex Indexes.
UPDATEINDEX Update values directly in the Omnidex Indexes.
DELETEINDEX Delete values directly from the Omnidex Indexes.

Alphabetical List of OdxSQL Commands and Omnidex SQL Statements

The following is a complete list of all OdxSQL Commands and all Omnidex SQL Statements that can be entered through OdxSQL.

Command Description
! <shell command> Run the command in the OS Shell and return or ! for interactive shell
. Redo Command Execute the last command.
ABORT Issues an Operating System Abort - used to test abnormal conditions.
ASSIGNVALUES Assign values for a $VALUES clause to be used in a subsequent SELECT.
ATTACH Attach an OST file as a table to the currently connected Omnidex Environment.
BENCHMARK Execute Benchmarks to analyze performance.
BIND Bind a SELECT Statement.
CALC Perform basic calculator functions.
CD Change Directories in the underlying Operating System.
CLOSE CURSOR Close a previously opened cursor.
COMMAND Execute a shell command via the $COMMAND table.
Comment ( ; ) Use a semicolon as the first character of a line for a comment line.
CONNECT Connect to an Omnidex Environment or ODBC/JDBC Datasource File (DSN).
CONVERT Export a file with data conversion.
COPY Copy a fileset.
CREATE DATABASE (V5.1) Create ad Database within 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 Index
CREATE INDEXES Create Omnidex Indexes.
CREATE TABLE (V5.1) Create a Table.
DATE Show the current Date and Time.
DELETE Issue the SQL DELETE Statement.
DELETEINDEX Delete values directly from the Omnidex Indexes.
DESCRIBE Describe a SELECT Statement.
DETACH Detach a previously attached Omnidex Standalone Table (OST) file.
DIR Display a directory of files from the current directory in the underlying Operating System.
DISCONNECT Disconnect from a previously Connected Omnidex Environment or ODBC or JDBC Datasource (DSN).
DROP DATABASE Drop Database(V5.1)
DROP ENVIRONMENT Drop Environment. (V5.1)
DROP INDEX Drop Index. (V5.1)
DROP INDEXES Drop Indexes.
DROP TABLE Drop a previously created temporary table. Expaned in (V5.1)
DUMP Dump files, memory or caches.
EDIT Edit a previous command or file.
ERROR Display the text of an error message from the Omnidex Error Message file.
EVAL Evaluate a condition.
EXECSQL Execute an Omnidex SQL Statement directly with the oaexecsql routine without OdxSQL doing any processing.
EXIT Exit the OdxSQL program.
EXPLAIN Explain the optimization approach to a previous retrieval.
EXPORT Export rows to a file.
EXTRACT Extracts SQL Statements from a table and writes them to a file.
FETCH Explicitly fetch data from the database.
FETCHKEYS Explicitly fetch keys from the Omnidex Indexes.
FLIP Change the endian byte order from one endian to the other.
FOR Execute the next one or more commands the specified number of times.
FORMAT Format a file of Omnidex SQL Statements.
GETENV Display an Operating System Environment Variable.
HELP Help on COMMANDS, SYNTAX and API.
HELPER Access OAHELPER functions for generating Environment Catalogs.
HISTORY Show the history of OdxSQL Commands and Statements.
IF Form an IF ELIF ELSE ENDIF Construct.
INSERT Issue an Omnidex SQL INSERT Statement.
INSERTINDEX Insert values directly into the Omnidex Indexes.
IS Check Table Conditions.
JOIN Join tables using Omnidex Indexes.
LOAD OFX Load Indexes for a table.
LOOKUP Lookup Text M etadata and display it.
MESSAGE Display an Omnidex Environment Message.
NETWORKACCESS Execute an Omndiex Network Access Command.
OPEN CURSOR Open a Cursor.
OXKERNEL Execute an Omnidex Kernel Command. Should be used for Debugging with DISC Personnel.
PARSE Parse an Omnidex SQL Statement to a file. This would only be used by DISC Personnel.
PARTITION Partition a table into smaller sub tables based on criteria.
PWD Print (display) the current working directory from the underlying Operating System.
QUALIFY Qualify rows using Omnidex criteria.
QUIT Quit the OdxSQL program. Same as Exit.
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.
RESET HISTORY Resets the OdxSQL History of Commands and Statements.
RESET TIMER Reset the OdxSQL Cummulative Timer.
RESTORE SETTINGS Restore Settings, optionally from a file that was created with a previous SAVE SETTINGS command.
SAVE HISTORY Save the OdxSQL Command History to a file.
SAVE SETTINGS Save OdxSQL Settings to a file for use with a later RESTORE SETTINGS Command.
SELECT Issue an Omnidex SQL SELECT Statement.
SET Set an OdxSQL setting.
SETENV Set an Operating System Environment Variable.
SHOW Show Omnidex Environment information including Databases, Tables, Column layouts, Query Statistics, etc.
SIGNAL Sends a Signal to a process.
SLEEP Sleep for a specified number of seconds.
SORTMERGE Internal: Activates the Sort/Merge Test System.
UPDATE Issue an Omnidex SQL UPDATE Statement.
UPDATE INDEXES Update Omnidex Indexes
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.
UPDATEINDEX Update values directly in the Omnidex Indexes.
USE Read and process a file of OdxSQL Commands and Omnidex SQL Statements, optionally with WHERE criteria.
VALIDATE Validate the Omnidex Environment's view of the underlying data.
VERSION Display current Omnidex Software versions.
VIEW View a text file.
n Execute the numbered OdxSQL History Command or Statement.
n: Send the numbered OdxSQL History Command to the a son process.
 
Back to top
programs/odxsql/commands/home.1260229354.txt.gz · Last modified: 2016/06/28 22:38 (external edit)