This is an old revision of the document!


Omnidex SQL Quick Reference

Omnidex SQL Statements
Connections Data Definition Omnidex Index Queries
CONNECT CREATE ENVIRONMENT QUALIFY
DISCONNECT CREATE DATABASE JOIN
SET CREATE TABLE
CREATE INDEX
Queries and Exports CREATE INDEX GROUP Maintenance
SELECT DROP ENVIRONMENT UPDATE INDEXES
EXPORT DROP DATABASE UPDATE ROLLUPS
OPEN CURSOR DROP TABLE UPDATE STATISTICS
CLOSE CURSOR DROP INDEX UPDATE TEXT
DROP INDEX GROUP PARTITION
Updates ATTACH LOAD OFX
INSERT DETATCH
DELETE EXTRACT
UPDATE
——————————————————————–——————————————————————-——————————————————————–
Omnidex SQL Statements
Connections Data Definition
CONNECT CREATE ENVIRONMENT
DISCONNECT CREATE DATABASE
SET CREATE TABLE
CREATE INDEX
Queries and Exports CREATE INDEX GROUP
SELECT DROP ENVIRONMENT
EXPORT DROP DATABASE
OPEN CURSOR DROP TABLE
CLOSE CURSOR DROP INDEX
DROP INDEX GROUP
Updates ATTACH
INSERT DETATCH
DELETE EXTRACT
UPDATE
Maintenance
Omnidex Index Queries UPDATE INDEXES
QUALIFY UPDATE ROLLUPS
JOIN UPDATE STATISTICS
UPDATE TEXT
PARTITION
LOAD OFX
—————————————————————————————-—————————————————————————————

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)
C 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 C 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 C 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 C 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>

Learning Omnidex

 
Back to top
dev/sql/home.1278532707.txt.gz · Last modified: 2016/06/28 22:38 (external edit)