Omnidex SQL Statements and OdxSQL Commands

Omnidex SQL Standard Functions

Omnidex Extended Functions

Oracle Functions

Use the CREATE ENVIRONMENT SQL_SYNTAX setting or the SET SQL_SYNTAX setting to activate.

Omnidex Data Types

Standard 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.

National Character Datatypes

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.

Create a simple functioning Environment with an Omnidex index

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>

SQL Statements by Category

Omnidex SQL SELECT and Data Manipulation Statements

Omnidex SQL supports SQL's SELECT and Data Manipulation Statements (INSERT, UPDATE, and DELETE).

SELECT Peform a Query Specification and SELECT rows.
INSERT Insert rows into a Table.
UPDATE Update rows in a Table.
DELETE Delete rows in a Table.

Omnidex Connection Statements

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 Data Definition Language (DDL) Statements

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.

Omnidex SQL Query and Export Statements

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 SQL Index and Processing Maintenance Statements

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 SQL Index Query Statements

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.

QUALIFY Qualify index counts using Omnidex criteria.
JOIN Join tables using Omnidex Indexes.

Learning Omnidex

 
Back to top
quicklinks.txt ยท Last modified: 2020/11/30 18:56 by doc