This is an old revision of the document!
OdxSQL is a console based program used for testing installations, prototyping queries, and exporting data. OdxSQL supports many functions designed to assist in creating fully optimized installations and queries including benchmarking and for executing explain plans to determine query performance.
OdxSQL allows the interactive specification of Omnidex SQL statements as well as OdxSQL specific commands. OdxSQL is primarily used to:
OdxSQL connects to either an Omnidex XML Catalog or an Omnidex Environment Catalog which can either be specified as a run time argument or via the CONNECT command once OdxSQL is running.
Run OdxSQL from the command line with the following syntax:
odxsql [XML Catalog or Environment Catalog] | [-use=use_file]
XML Catalog or Environment Catalog The environment_name parameter is the environment catalog filespec that OdxSql will connect to. OdxSql will try to connect to the specified environment. If this filespec is not passed as a program argument, then a CONNECT statement must be performed to establish a connection to the environment catalog.
-use=use_file The -use=use_file parameter instructs OdxSql to process all the commands contained within the specified use file. The right hand side of the parameter, use_file, is the filespec of the file containing the commands.
Examples:
Running OdxSQL without command line arguments - use the CONNECT command to connect to an XML or Environment Catalog.
os> odxsql
Running OdxSQL with in Environment Catalog.
os> odxsql orders.env
Running OdxSQL with a command file of OdxSQL commands.
os> odxsql -use=commands.txt
Type help at the command prompt with no arguments to see an alphabetical list of ODXSQL commands.
>help
…
Type help with the argument 'syntax' at the command prompt to see a list of commands with their syntax.
>help syntax
…
Type help followed by a specific command to see additional information about that command.
>help connect CONNECT Connect to an environment file Syntax: Connect [TO] filename [AS [user]/[password]] [WITH options]
See HELP in the OdxSQL Commands section for more information.
Commands with an * (asterisk) before the description are also available for use in oaexecsql, ODBC and JDBC applications. All others are available for use ONLY in ODXSQL.
Command | Description |
---|---|
! (Exclamation Point) | Execute an operating system command |
. (Period) | Re-Execute the last command |
;(Semi-Colon) | Comment - any text on the line will be ignored |
ABORT | Cause OdxSQL to terminate |
ASSIGNVALUES | * Assign values for a $VALUES clause |
ATTACH | * Attach an OST file as a table |
BENCHMARK | Perform performance benchmarks |
BIND | Bind a SELECT statement |
CALC | Perform basic calculator functions |
CD | Change directories |
CLOSE CURSOR | * Close a previously opened cursor |
COMMAND | * Execute a shell command via $COMMAND table |
CONNECT | * Connect to an XML or an Environment Catalog |
CONVERT | Export a file with data conversions |
COPY | Copy a fileset |
CREATE DIRECTORY | Create an operating system directory |
CREATE FILE | Create an operating system file |
CREATE TABLE | * Create a table |
DATE | Show the current date and time |
DELETE | * Issue an SQL delete statement |
DELETEINDEX | * Delete values directly from Omnidex indexes |
DESCRIBE | Describe a SELECT statement |
DETACH | * Detach a previously attached OST file |
DIR | Display a directory of files from the operating system |
DISCONNECT | * Disconnect from an XML or Environment Catalog |
DROP | * Drop a table |
DUMP | Dump files, memory or caches |
EDIT | Edit a previous command or file |
ERROR | Display an error message |
EVAL | Evaluate a condition |
EXECDB | Execute an RDBMS statement using the execdb() |
EXIT | Exit Omnidex SQL |
EXPLAIN | Shows an Explain Plan on the retrieval |
EXPORT | * Export records using SELECT statement syntax to a file |
EXTRACT | Extract SQL statements from database |
FETCH | Explicitly fetch data from a database |
FETCHKEYS | Explicitly fetch keys from the indexes |
FLIP | Flip bindary data from one endian to another |
FOR | Form a 'FOR n … END' construct |
FORMAT | Format a file of OdxSQL statements |
GETENV | Display an environment variable |
HELP | Help on OdxSQL commands |
HISTORY | Show history of OdxSQL commands |
IF | Form an 'IF ELIF ELSE ENDIF' construct |
INSERT | * Issue an SQL insert statement to insert data |
INSERTINDEX | * Insert values directly into Omnidex indexes |
IS | Check database conditions |
JOIN | * Join tables using Omnidex |
LOAD | Load indexes for TDF files |
OPEN CURSOR | * Open a cursor |
OXKERNEL | Execute an Omnidex kernel command |
PARSE | Parse a SQL statement |
PARTITION | Partition a table |
PWD | Print or show the current working directory |
QUALIFY | * Qualify rows using the Omnidex QUALIFY statement |
QUIT | Quit Omnidex OdxSQL |
REMOVE | Remove one or more files from the operating system |
RENAME | Rename a file or fileset |
RESET HISTORY | Reset history of ODXSQL commands |
RESET TIMER | Reset the cumulative timer used to verify performance of queries |
RESTORE SETTINGS | Restore settings, optionally from a file |
SAVE HISTORY | Save previously entered OdxSQL commands to a file |
SAVE SETTINGS | Save settings, optionally to a file |
SELECT | * Issue an Omnidex SQL SELECT statement |
SET | Set an OdxSQL option |
SETENV | Set an operating system environment variable |
SHOW | Show information on databases, tables, and columns |
SIGNAL | Send an operating system signal to a process |
SORTMERGE | Sort/merge test system |
UPDATE | * Issue an Omnidex SQL UPDATE statement |
UPDATE STATISTICS | Update statistics for a database or table |
UPDATE TEXT | Enable misspelling searches on Text, FullText or MDK indexed columns. |
UPDATEINDEX | * Update values directly in Omnidex indexes |
USE | Execute a file of OdxSQL commands |
VALIDATE | Validate an Omnidex Environment to determine correct mapping to an underlying database or file and verify index retrievals are working |
VERSION | * Display current software version information |
VIEW | View an external text file |
n | Execute numbered command from the OdxSQL History stack |
n: | Send command to OdxSQL subprocess identified by relative number of the process |
Setting | Option | Description |
---|---|---|
CURSORDISPLAY | ON/OFF |
>SET CURSORDISPLAY ON
>connect /users/garment.env Connected to /users/garment.env on instance 1, cursor 1
>connect / users/orders.env Connected to /users/orders.env on instance 2, cursor 2
SELECT COMPANY, CONTACT, PHONE FROM CUSTOMERS WHERE STATE='CO' ON CURSOR 2 ... INSERT INTO CUSTOMERS (COMPANY, CONTACT) VALUES ('New Wave', 'Mr. Smith') ON CURSOR 1 ...
ODXSQL Environment Variables The following variables can be set before running ODXSQL.
The required variables (above) must also be set prior to running ODXSQL.
ODXSQL_API ODXSQL_API specifies which API to use, OA (default) or ODBC during this ODXSQL session.
If running ODXSQL on a client machine where the Omnidex server software has not been installed, this variable MUST be set to ODBC, since the OA API does not exist on the client.
Unix export ODXSQL_API=“OA”
Windows set ODXSQL_API=“ODBC”
ODXSQLHIST ODXSQL_HIST_FILE points to an ODXSQL History File. By default, the ODXSQL command history is written to .odxsql_history. Use this variable to write the history to a different file.
Unix export ODXSQLHIST=“myhistoryfile.txt”
Windows set ODXSQLHIST=“myhistoryfile.txt”
ODXSQLINIT ODXSQLINIT points to an ODXSQL Init File. This file can contain initialization settings that will be set when ODXSQL starts. This is very useful for set options.
Unix export ODXSQLINIT=“init_file.txt”
Windows set ODXSQLINIT=“init_file.txt”
* Statement is only available from OdxSQL.
Use the CREATE ENVIRONMENT SQL_SYNTAX setting or the SET SQL_SYNTAX setting to activate.
See Also: | List of Datatypes |
---|
Omnidex supports the following standard datatypes:
Datatype | Description |
---|---|
CHARACTER(n) | Space-filled character string of n characters |
CHAR(n) | Synonym for CHARACTER(n) |
STRING(n) | Null-terminated character string of n characters |
VARCHAR(n)* | String of max n characters, with length variable |
CLOB(n)* | Character large object of max n characters, with length variable |
[SIGNED] TINYINT | 1-byte, signed integer ( -128 to 127 ) |
UNSIGNED TINYINT | 1-byte, unsigned integer ( 0 to 255 ) |
[SIGNED] SMALLINT | 2-byte, signed integer ( -32768 to 32767 ) |
UNSIGNED SMALLINT | 2-byte, unsigned integer ( 0 to 65535 ) |
[SIGNED] INTEGER | 4-byte, signed integer ( 2147483648 to 2147483647 ) |
UNSIGNED INTEGER | 4-byte, unsigned integer ( 0 to 4294967295 ) |
[SIGNED] BIGINT | 8-byte, signed integer ( -2^63 to 2^63-1 ) |
UNSIGNED BIGINT | 8-byte, unsigned integer ( 0 to 2^64-1 ) |
FLOAT | IEEE four-byte, single-precision floating point |
DOUBLE | IEEE eight-byte, double-precision floating point |
DATE | 10-byte ANSI date |
TIME | 11-byte ANSI time |
DATETIME | 24-byte ANSI datetime |
ASCII DATE | 8-byte ASCII date ( YYYYMMDD ) |
ASCII DATE(6) | 6-byte ASCII date ( YYMMDD ) |
ODBC DATE | 6-byte ODBC proprietary date |
ODBC TIME | 6-byte ODBC proprietary time |
ODBC DATETIME | 6-byte ODBC proprietary datetime |
ORACLE DATE | 7-byte Oracle proprietary date |
ORACLE TIME | 7-byte Oracle proprietary time |
ORACLE DATETIME | 7-byte Oracle proprietary datetime |
OMNIDEX DATE(n) | Omnidex proprietary date supporting n digits of YYYYMMDD |
OMNIDEX TIME(n) | Omnidex proprietary time supporting n digits of HHMMSSNN |
OMNIDEX DATETIME(n) | Omnidex proprietary datetime supporting n digits of YYYYMMDDHHMMSSNN |
* Omnidex recommends use of the CHARACTER and STRING datatypes rather than VARCHAR and CLOB due to the requirement of a length variable. |
Omnidex also provides partial support for National Character Datatypes (sometimes called Unicode datatypes or wide characters). The Omnidex SQL Engine will process these datatypes, but the Omnidex Indexing Engine will only index the lower 8 bits of these datatypes.
Datatype | Description |
---|---|
NATIONAL CHARACTER(n) | Space-filled character string of n characters |
NATIONAL CHAR(n) | Synonym for CHARACTER(n) |
NATIONAL STRING(n) | Null-terminated character string of n characters |
NATIONAL VARCHAR(n)* | String of max n characters, with length variable |
NATIONAL CLOB(n)* | Character large object of max n characters, with length variable |
* Omnidex recommends use of the NATIONAL CHARACTER and NATIONAL STRING datatypes rather than NATIONAL VARCHAR and NATIONAL CLOB due to the requirement of a length variable. |
os> odxsql > CREATE ENVIRONMENT IN "myenv.xml"; > CREATE DATABASE mydb TYPE FLATFILE IN "myenv.xml"; > CREATE TABLE mytable PHYSICAL "test1" (myseq integer, mytext char(60) quicktext) IN "myenv.xml"; > CONNECT myenv.xml; > UPDATE INDEXES; > SELECT * FROM mytable where mytext = '<some criteria>'; > DISCONNECT; > EXIT os>
Omnidex SQL supports SQL's SELECT and Data Manipulation Statements (INSERT, UPDATE, and DELETE).
Omnidex supports statements to establish connections and set processing options for the connection as well as attach and detach individual files to an Omnidex Environment.
CONNECT | Connect to an Omnidex Environment. |
DISCONNECT | Disconnect from a connected Omnidex Environment. |
SET | Set Omnidex SQL processing options. |
ATTACH | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment for queries and manipulation. |
DETACH | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. |
Omnidex SQL has statements that create and drop objects within an Omnidex Environment.
Also see the OdxSQL Extract command to generate DDL statements from an existing Omnidex Environment.
DDL Statement | Description |
---|---|
CREATE ENVIRONMENT | Create an Omnidex Environment into a named physical operating system file. |
CREATE DATABASE | Create a Database within an Omnidex Environment. |
CREATE TABLE | Create a Table within a Database in the Omnidex metadata or an Omnidex or RDBMS temporary table. |
CREATE INDEX | Specify an Index in the Omnidex metadata but will not physically create the index. |
CREATE INDEX GROUP | Specify an Index Group within the Omnidex metadata. |
DROP ENVIRONMENT | Deletes the Omnidex Metadata Environment by physically deleting the operating system file. |
DROP DATABASE | Remove the named Database from the Omnidex metadata. |
DROP TABLE | Remove a table from the Omnidex metadata or deletes a previously created temporary table. |
DROP INDEX | Removes an index from the Omnidex metadata. |
DROP INDEX GROUP | Removes a named Index Group from the Omnidex metadata. |
ATTACH | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment for queries and manipulation. |
DETACH | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. |
SELECT | Peform a Query Specification and SELECT rows. |
EXPORT | Export rows or rowids to an external file. |
OPEN CURSOR | Open a Cursor. |
CLOSE CURSOR | Close a previously opened cursor. |
Omnidex supports several statements to synchronize the Omnidex indexes, rollup tables and internal statistics.
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. |
PARTITION | Partition a table into smaller sub tables based on criteria. |
LOAD OFX | Load Indexes for a table. |
Install Indexes | Specifies the Omnidex indexes into the Omnidex XML Environment. No indexing is performed. |
Remove Indexes | Removes the Omnidex indexes from the Omnidex XML Environment. |
Omnidex supports lower level QUALIFY and JOIN statements to query the Omnidex indexes directly. These statements are advanced statements used for testing and to support legacy applications. The SELECT statement is now typically used instead.
(c)Copyright Dynamic Information Systems - This document was last updated July 6, 2010