Omnidex SQL Reference

QuickLinks	Statements	Functions	OdxSQL

Omnidex supports a rich set of standard SQL statements along with Omnidex specific extensions to facilitate the development of ODBC and JDBC based applications.

Omnidex SQL Statements can also be used with the console based utility OdxSQL to test and prototype applications.

DML	Select	Insert	Delete	Update	Qualify
Access	Connect	Disconnect	Attach	Detach	Set
DDL Create	Create Environment	Create Database	Create Table	Create Index	Create Index Group
DDL Drop	Drop Environment	Drop Datebase	Drop Table	Drop Index	Drop Index Group
Syncing	Update Indexes	Update Text	Update Statistics	Update Rollups	Update Rollup \| ^ Special ^\| [[dev:sql:statements:export:home \| Export	Partition	Join	Open Cursor	Close Cursor
Other	Install Indexes	Remove Indexes	Load OFX	Command	Extract

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.

Alphabetical List of Omnidex SQL Statements

Statement	Description
ATTACH	Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment for queries and manipulation.
CLOSE CURSOR	Close a previously opened cursor.
COMMAND	Execute an Operating System command.
CONNECT	Connect to an Omnidex Environment.
CREATE DATABASE	Create a Database within an Omnidex Environment.
CREATE ENVIRONMENT	Create an Omnidex Environment.
CREATE INDEX	Create an Index.
CREATE TABLE	Create a table within an Omnidex Environment or create a temp table in the underlying RDBMS.
DELETE	Delete a row or rows of data.
DETACH	Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment.
DISCONNECT	Disconnect from a connected Omnidex Environment.
DROP DATABASE	Drop a Database from within an Omnidex Environment.
DROP ENVIRONMENT	Drop an Omnidex XML Environment and purge the underlying operating system file.
DROP INDEX	Drop an Omnidex Index.
DROP TABLE	Drop a table created with a CREATE TABLE or SELECT INTO statement.
EXPORT	Export rows or rowids to an external file.
INSERT	Insert a row of data.
JOIN	Manually join two tables for a joined query
LOAD OFX	Load an OFX file.
OPEN CURSOR	Open a cursor.
PARTITION	Partition Data based on criteria.
QUALIFY	Qualify rows in a query. Typically superseded by SELECT
SELECT	Select rows , aggregate rows, using Omnidex enhanced criteria.
SET	Set Omnidex SQL processing options.
UPDATE	Update rows in a table.
UPDATE INDEXES	Update all Omnidex Indexes.
UPDATE ROLLUP	Update Rollup Tables with new calculations.
UPDATE ROLLUPS	Update all Rollup Tables with new calculations.
UPDATE STATISTICS	Update the Omnidex Statistics information.
UPDATE TEXT	Update Omnidex Text information.

Back to top

dev/sql/statements/home.1278030718.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes