Overview | Commands | Options | Command-line | Command Files

The following commands are available in OdxSQL:

The ATTACH DATABASE command allows a database from one environment to be attached into the currently connected environment, thereby allowing SQL statements to access both databases.

ATTACH DATABASE database [AS alias] FROM filename [ON [INSTANCE] instance]
  [WITH options]

The ATTACH SEGMENT command allows a previously-created segment to be attached into the currently connected environment and used in subsequent SQL statements. Segments are created using the CREATE SEGMENT command.

ATTACH <INDEX | DATA> SEGMENT segment [PHYSICAL physical]
  [ON [INSTANCE] instance] [WITH options]

The ATTACH TABLE command allows a raw data file or a delimited file to be attached to the currently connected environment, thereby allowing SQL statements to access both databases. Note that attached tables are not indexed with Omnidex. For attaching indexed tables, use the ATTACH DATABASE command.

ATTACH TABLE table [PHYSICAL "physical"] (column [, column ...])
  [ON [INSTANCE] instance>] [WITH options]
  column: column-name datatype (length)

The BENCHMARK command allows a series of performance benchmarks to be performed, focusing on POSIX file I/O.

BENCHMARK [<table | select-statement>] [WITH options]

The CALC command provides a very basic calculator, allowing formulas like “CALC (123+456)*789”.

CALC expression

The CD command changes the current working directory within the OdxSQL session. The current working directory outside of OdxSQL remains unchanged.

CD [path]

The CLOSE CURSOR command closes a cursor that was previously opened using the OPEN CURSOR command.

CLOSE CURSOR [[ON] CURSOR] cursor] [WITH options]

The CONNECT command connects to an Omnidex environment file. The USING clause allows the connection to be performed using the ODBC or JDBC client interfaces.

CONNECT [TO] connection_string [USING api] [WITH options]
  Connection string syntax:
    [ host:port:pool ] filename [ node ] &options
  Examples:
    CONNECT example.xml
    CONNECT [server1:7555]example.xml
    CONNECT [server1:7555:pool1]

The CONVERT command allows a SELECT statement to be exported while interactively prompting for data conversions of each retrieved column.

CONVERT [statement TO filename [ON [CURSOR] cursor] [WITH options]

The CREATE DATABASE command allows a database to be declared in an Omnidex environment file.

CREATE DATABASE database node [node ...] IN "filename" [WITH options]

Create an environment file

CREATE ENVIRONMENT environment [attribute [attribute ...]]

Create an index or index group

CREATE <OMNIDEX | QUICKTEXT | FULLTEXT | CUSTOM | NATIVE> INDEX
  [[(owner)]table.]index [ON table]
  (<column | substring> [, <column | substring> ...])]
  [attribute [attribute ...]] IN "filename" [WITH options]

CREATE INDEX GROUP [(owner)]group
  (table.index [, table.index ...])] IN "filename" [WITH options]

Create a segment

CREATE [<TEMPORARY | PERMANENT>] <INDEX | DATA> SEGMENT segment
  [PHYSICAL physical] [AS (select)] [ON [CURSOR] cursor]
  [WITH options]

Create a table

CREATE [<[<GLOBAL | LOCAL>] TEMPORARY | PERMANENT>] TABLE table
  [PHYSICAL "physical"] (column [, column ...])
  [<IN "filename" | ON [INSTANCE] instance>] [WITH options]
  column: column-name datatype (length)

column: column-name datatype (length)

Show the current date and time

DATE

Issue an SQL delete statement

DELETE FROM table [WHERE <predicate-list | CURRENT>
  [<ON | OF> [CURSOR] cursor]] [WITH options]

Detach a previously attached database

DETACH DATABASE database [ON [INSTANCE] instance] [WITH options]

Detach a previously attached segment

DETACH SEGMENT segment [ON [INSTANCE] instance] [WITH options]

Detach a previously attached table

DETACH TABLE table [ON [INSTANCE] instance] [WITH options]

Display a directory of files

DIR fileset [WITH options]

Disconnect from an environment file

DISCONNECT [ALL] [[ON [INSTANCE]] instance] [WITH options]

Drop a database

DROP DATABASE [IF EXISTS] database IN "filename" [WITH options]

Drop an environment

DROP ENVIRONMENT [IF EXISTS] [environment] IN "filename" [WITH options]

Drop an index or index group

DROP [<OMNIDEX | NATIVE>] INDEX [IF EXISTS] [[(owner)]table.]index [ON table]
  IN "filename" [WITH options]

DROP INDEX GROUP [IF EXISTS] [(owner)]group IN "filename" [WITH options]

Drop a segment

DROP SEGMENT [IF EXISTS] segment [ON [INSTANCE] instance>] [WITH options]

Drop a table

DROP [<TEMPORARY | PERMANENT>] TABLE [IF EXISTS] table 
  [<IN "filename" | ON [INSTANCE] instance>] [WITH options]

Edit a previous command or file

EDIT [n | filename] [WITH options]

Display an error message

ERROR error_code

Exit Omnidex SQL

EXIT

Explain approach to retrieval

EXPLAIN [statement] [ON [CURSOR] cursor] [WITH options]

Export records to a file

EXPORT [statement] TO filename [ON [CURSOR] cursor] [WITH options]

Extract SQL statements for database

EXTRACT DDL [FOR <ENVIRONMENT [environment] | DATABASE database>]
  [TO filename] [ON [INSTANCE] instance] [WITH options]
EXTRACT DDL FOR <MYSQL | ORACLE | ODBC | SQLSERVER>
  [TO filename] [ON [INSTANCE] instance] [WITH options]
EXTRACT DML FOR table-list [WHERE criteria]
  [TO filename] [ON [INSTANCE] instance] [WITH options]
EXTRACT [<ALL | n | LONGEST n | ERRORING>] QUERIES
  [BY <CONNECTION | STATEMENT>] [WHERE criteria]
  [TO filename] [ON [INSTANCE] instance] [WITH options]

Explicitly fetch data from a database

FETCH [<n | ALL>] [ON [CURSOR] cursor] [WITH options]

Form a 'FOR n … END' construct

FOR n [WITH options]
<statements>
END

Format a file of ODXSQL statements

FORMAT input-file [INTO output-file] [WITH options]

Help

HELP [<command | SYNTAX | API>]

Show history of ODXSQL commands

HISTORY

Form an 'IF ELIF ELSE ENDIF' construct

IF condition
  <statements>
ELIF condition
  <statements>
ELSE
  <statements>
ENDIF

Issue an SQL insert statement

INSERT INTO table [(column-list)] <VALUES (value-list) | select-stmt>
  [ON [INSTANCE] instance] [WITH options]

Join tables using Omnidex

JOIN [FROM] table USING index TO [(owner)]table USING index
  [ON [CURSOR] cursor] [WITH options]

Load indexes for a table

LOAD OFX [FOR <ENVIRONMENT [environment] | DATABASE database | TABLE table>]
  [ON [INSTANCE] instance] [WITH options]

Open a cursor

OPEN CURSOR [ON [INSTANCE] instance] [WITH options]

Partition a table

PARTITION table [INTO n] [BY <column | expression>] [IN path]
  [ON [INSTANCE] instance] [WITH options]

Print current working directory

PWD

Qualify rows using Omnidex

QUALIFY [(owner)]table WHERE [[$QUALIFIED] <AND | OR> [NOT]]
  predicate [ON [CURSOR] cursor] [WITH options]

Quit Omnidex SQL

QUIT

Register a license code

REGISTER LICENSE license_code [FOR company]

Register an ODBC driver

REGISTER ODBC [dir]

Request a license code

REQUEST [<ENTERPRISE | DEVELOPER | TRIAL>] LICENSE 

Reset history of ODXSQL commands

RESET HISTORY

Reset the cumulative timer

RESET TIMER

Restore settings, optionally from a file

RESTORE SETTINGS [[FROM] filename] [WITH options]

Save commands to file

SAVE HISTORY [TO] filename] [WITH options]

Save settings, optionally to a file

SAVE SETTINGS [TO] filename] [WITH options]

Issue an SQL select statement

SELECT statement [ON [CURSOR] cursor] [WITH options]

Set an option

SET [<option | ?>] [ON <INSTANCE | CURSOR> n]

Set an environment variable

SETENV variable value

Describe an object

SHOW <ENVIRONMENT [environment] | DATABASE database | TABLE table |
  COLUMN column> [ON [INSTANCE] instance] [WITH options]
SHOW [<USER_ | DATA_ | METADATA_ | SYSTEM_ | ALL_>]
  <DATABASES | TABLES | COLUMNS>
  [ON [INSTANCE] instance] [WITH options]
SHOW ALL [database] [ON [INSTANCE] instance] [WITH options]
SHOW <INSTANCE | CURSOR> [object] [WITH options]
SHOW LICENSE
SHOW <CONNECTIONS | STATEMENTS> [WITH options]

Transfer a license to another server

TRANSFER LICENSE

Unregister an ODBC driver

UNREGISTER ODBC

Issue an SQL update statement

UPDATE table SET column-assignments
  [WHERE <predicate-list | CURRENT> [<ON | OF> [CURSOR] cursor]]
  [WITH options]

Update Omnidex indexes

UPDATE INDEXES [FOR <ENVIRONMENT [environment] | DATABASE database |
  TABLE table | COLUMN table.column | INDEX table.index>]
  [ON [INSTANCE] instance] [WITH options]

Update rollup table

UPDATE ROLLUP TABLE table [ON [INSTANCE] instance] [WITH options]

Update rollup tables

UPDATE ROLLUPS [FOR <ENVIRONMENT [environment] | DATABASE database |
   TABLE table>] [ON [INSTANCE] instance] [WITH options]

Update statistical information

UPDATE STATISTICS [FOR <ENVIRONMENT [environment] | DATABASE database |
  TABLE table | COLUMN table.column | INDEX table.index>]
  [ON [INSTANCE] instance] [WITH options]

Update textual statistics

UPDATE TEXT [FOR <ENVIRONMENT [environment] | DATABASE database |
  TABLE table | COLUMN table.column | INDEX table.index>]
  [ON [INSTANCE] instance] [WITH options]

Execute a file of ODXSQL commands

USE filename [WHERE criteria] [WITH options]

Validate an environment

VALIDATE [<ENVIRONMENT [environment] | DATABASE database | TABLE table>]
  [ON [INSTANCE] instance] [WITH options]

Execute a shell command

!<shell command> or ! for interactive shell

Execute last command

<. | command number>

Comment line

; Comment ...

Execute numbered command

   n (where n is a number from the command history)

Prev | Next |

See also: