This is an old revision of the document!
Omnidex SQL Statements | ||
---|---|---|
Connections | Omnidex Index Queries | Data Definition |
CONNECT | QUALIFY | CREATE ENVIRONMENT |
DISCONNECT | JOIN | CREATE DATABASE |
SET | CREATE TABLE | |
CREATE INDEX | ||
Queries and Exports | Maintenance | CREATE INDEX GROUP |
SELECT | UPDATE INDEXES | DROP ENVIRONMENT |
EXPORT | UPDATE ROLLUPS | DROP DATABASE |
OPEN CURSOR | UPDATE STATISTICS | DROP TABLE |
CLOSE CURSOR | UPDATE TEXT | DROP INDEX |
PARTITION | DROP INDEX GROUP | |
Updates | LOAD OFX | ATTACH |
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 | |
—————————————————————————————- | ————————————————————————————— |
* 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) |
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. |
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. |
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>