This shows you the differences between two versions of the page.
programs:odxsql:commands [2015/06/10 20:17] doc |
programs:odxsql:commands [2016/06/28 22:38] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | {{page>:top_add&nofooter&noeditbtn}} | ||
- | ~~NOTOC~~ | ||
- | |||
- | ====== Programs: OdxSQL ====== | ||
- | |||
- | [[programs:odxsql:home|Overview]] | **[[programs:odxsql:commands|Commands]]** | [[programs:odxsql:options|Options]] | [[programs:odxsql:cmdline|Command-line]] | [[programs:odxsql:files|Command Files]] | ||
- | |||
- | |||
- | ---- | ||
- | |||
- | |||
- | ===== Commands ===== | ||
- | |||
- | The following commands are available in OdxSQL: | ||
- | |||
- | |||
- | ==== ATTACH DATABASE ==== | ||
- | |||
- | The ATTACH DATABASE command attaches a database from one environment into the currently connected environment, thereby allowing SQL statements to access both databases. | ||
- | |||
- | <code> | ||
- | ATTACH DATABASE database [AS alias] FROM filename [ON [INSTANCE] instance] | ||
- | [WITH options] | ||
- | </code> | ||
- | |||
- | ==== ATTACH SEGMENT ==== | ||
- | |||
- | The ATTACH SEGMENT command attaches a previously-created segment into the currently connected environment so that it can be referenced in subsequent SQL statements. Segments are created using the CREATE SEGMENT command. | ||
- | |||
- | <code> | ||
- | ATTACH <INDEX | DATA> SEGMENT segment [PHYSICAL physical] | ||
- | [ON [INSTANCE] instance] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== ATTACH TABLE ==== | ||
- | |||
- | The ATTACH TABLE command attaches a raw data file or a delimited file into 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. | ||
- | |||
- | <code> | ||
- | ATTACH TABLE table [PHYSICAL "physical"] (column [, column ...]) | ||
- | [ON [INSTANCE] instance>] [WITH options] | ||
- | column: column-name datatype (length) | ||
- | </code> | ||
- | |||
- | ==== BENCHMARK ==== | ||
- | |||
- | The BENCHMARK command provides a menu of performance benchmarks that can be performed, focusing on POSIX file I/O. | ||
- | |||
- | <code> | ||
- | BENCHMARK [<table | select-statement>] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== CALC ==== | ||
- | |||
- | The CALC command provides a very basic calculator, allowing formulas like "CALC (123+456)*789". | ||
- | |||
- | <code> | ||
- | CALC expression | ||
- | </code> | ||
- | |||
- | ==== CD ==== | ||
- | |||
- | The CD command changes the current working directory within the OdxSQL session. The current working directory outside of OdxSQL remains unchanged. | ||
- | |||
- | <code> | ||
- | CD [path] | ||
- | </code> | ||
- | |||
- | ==== CLOSE CURSOR ==== | ||
- | |||
- | The CLOSE CURSOR command closes a cursor that was previously opened using the OPEN CURSOR command. | ||
- | |||
- | <code> | ||
- | CLOSE CURSOR [[ON] CURSOR] cursor] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== CONNECT ==== | ||
- | |||
- | 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. | ||
- | |||
- | <code> | ||
- | 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] | ||
- | </code> | ||
- | |||
- | ==== CONVERT ==== | ||
- | |||
- | The CONVERT command allows a SELECT statement to be exported while interactively prompting for data conversions of each retrieved column. | ||
- | |||
- | <code> | ||
- | CONVERT [statement TO filename [ON [CURSOR] cursor] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== CREATE DATABASE ==== | ||
- | |||
- | The CREATE DATABASE command allows a database to be declared in an Omnidex environment file. For details on CREATE DATABASE statements for each database type, consult the [[integration:home|Integration section]] of the documentation. | ||
- | |||
- | <code> | ||
- | CREATE DATABASE database | ||
- | ... | ||
- | IN "filename" [WITH options] | ||
- | </code> | ||
- | |||
- | ==== CREATE ENVIRONMENT ==== | ||
- | |||
- | The CREATE ENVIRONMENT command creates a new Omnidex environment file. | ||
- | |||
- | <code> | ||
- | CREATE ENVIRONMENT environment | ||
- | [OPTIONS options] | ||
- | [NODE node_declaration] | ||
- | IN "filename" | ||
- | </code> | ||
- | |||
- | ==== CREATE INDEX ==== | ||
- | |||
- | The CREATE INDEX and CREATE INDEX GROUP commands create indexes in an Omnidex environment file. For details on CREATE INDEX and CREATE INDEX GROUP statements, consult the [[admin:indexing:creation:home|Index Creation section]] of the documentation. | ||
- | |||
- | <code> | ||
- | 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] | ||
- | </code> | ||
- | |||
- | |||
- | ==== CREATE SEGMENT ==== | ||
- | |||
- | The CREATE SEGMENT command creates a data segment or an index segment, thereby allowing it to be referenced in subsequent queries. | ||
- | |||
- | <code> | ||
- | CREATE [<TEMPORARY | PERMANENT>] <INDEX | DATA> SEGMENT segment | ||
- | [PHYSICAL physical] [AS (select)] [ON [CURSOR] cursor] | ||
- | [WITH options] | ||
- | </code> | ||
- | |||
- | ==== CREATE TABLE ==== | ||
- | |||
- | The CREATE TABLE command creates a table in the current connection. If the IN clause is used, then the CREATE TABLE command creates a table declaration in an Omnidex environment file. For details on CREATE TABLE statements for each database type, consult the [[integration:home|Integration section]] of the documentation. | ||
- | |||
- | <code> | ||
- | CREATE [<[<GLOBAL | LOCAL>] TEMPORARY | PERMANENT>] TABLE table | ||
- | [PHYSICAL "physical"] (column [, column ...]) | ||
- | [<IN "filename" | ON [INSTANCE] instance>] [WITH options] | ||
- | column: column-name datatype (length) | ||
- | </code> | ||
- | |||
- | column: column-name datatype (length) | ||
- | |||
- | ==== DATE ==== | ||
- | |||
- | The DATE command shows the current date and time. | ||
- | |||
- | <code> | ||
- | DATE | ||
- | </code> | ||
- | |||
- | ==== DELETE ==== | ||
- | |||
- | The DELETE command deletes one or more rows from a table. | ||
- | |||
- | <code> | ||
- | DELETE FROM table [WHERE <predicate-list | CURRENT> | ||
- | [<ON | OF> [CURSOR] cursor]] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== DETACH DATABASE ==== | ||
- | |||
- | The DETACH DATABASE command detaches a database that was previously attached using the ATTACH DATABASE command. | ||
- | |||
- | <code> | ||
- | DETACH DATABASE database [ON [INSTANCE] instance] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== DETACH SEGMENT ==== | ||
- | |||
- | The DETACH SEGMENT command detaches a segment that was previously attached using the ATTACH SEGMENT command. | ||
- | |||
- | <code> | ||
- | DETACH SEGMENT segment [ON [INSTANCE] instance] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== DETACH TABLE ==== | ||
- | |||
- | The DETACH TABLE command detaches a table that was previously attached using the ATTACH TABLE command. | ||
- | |||
- | <code> | ||
- | DETACH TABLE table [ON [INSTANCE] instance] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== DIR ==== | ||
- | |||
- | The DIR command lists the files named in the fileset, optionally showing details for each file. | ||
- | |||
- | <code> | ||
- | DIR fileset [WITH options] | ||
- | </code> | ||
- | |||
- | ==== DISCONNECT ==== | ||
- | |||
- | The DISCONNECT command disconnects from the Omnidex environment file. | ||
- | |||
- | <code> | ||
- | DISCONNECT [ALL] [[ON [INSTANCE]] instance] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== DROP DATABASE ==== | ||
- | |||
- | The DROP DATABASE command drops a database from an Omnidex environment file. | ||
- | |||
- | <code> | ||
- | DROP DATABASE [IF EXISTS] database IN "filename" [WITH options] | ||
- | </code> | ||
- | |||
- | ==== DROP ENVIRONMENT ==== | ||
- | |||
- | The DROP ENVIRONMENT command drops and deletes an Omnidex environment file. | ||
- | |||
- | <code> | ||
- | DROP ENVIRONMENT [IF EXISTS] [environment] IN "filename" [WITH options] | ||
- | </code> | ||
- | |||
- | ==== DROP INDEX ==== | ||
- | |||
- | The DROP INDEX and DROP INDEX GROUP commands drop an index or index group from an Omnidex environment file. | ||
- | |||
- | <code> | ||
- | DROP [<OMNIDEX | NATIVE>] INDEX [IF EXISTS] [[(owner)]table.]index [ON table] | ||
- | IN "filename" [WITH options] | ||
- | </code> | ||
- | |||
- | <code> | ||
- | DROP INDEX GROUP [IF EXISTS] [(owner)]group IN "filename" [WITH options] | ||
- | </code> | ||
- | |||
- | ==== DROP SEGMENT ==== | ||
- | |||
- | Drop a segment | ||
- | |||
- | <code> | ||
- | DROP SEGMENT [IF EXISTS] segment [ON [INSTANCE] instance>] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== DROP TABLE ==== | ||
- | |||
- | Drop a table | ||
- | |||
- | <code> | ||
- | DROP [<TEMPORARY | PERMANENT>] TABLE [IF EXISTS] table | ||
- | [<IN "filename" | ON [INSTANCE] instance>] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== EDIT ==== | ||
- | |||
- | Edit a previous command or file | ||
- | |||
- | <code> | ||
- | EDIT [n | filename] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== ERROR ==== | ||
- | |||
- | Display an error message | ||
- | |||
- | <code> | ||
- | ERROR error_code | ||
- | </code> | ||
- | |||
- | ==== EXIT ==== | ||
- | |||
- | Exit Omnidex SQL | ||
- | |||
- | <code> | ||
- | EXIT | ||
- | </code> | ||
- | |||
- | ==== EXPLAIN ==== | ||
- | |||
- | Explain approach to retrieval | ||
- | |||
- | <code> | ||
- | EXPLAIN [statement] [ON [CURSOR] cursor] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== EXPORT ==== | ||
- | |||
- | Export records to a file | ||
- | |||
- | <code> | ||
- | EXPORT [statement] TO filename [ON [CURSOR] cursor] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== EXTRACT ==== | ||
- | |||
- | Extract SQL statements for database | ||
- | |||
- | <code> | ||
- | 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] | ||
- | </code> | ||
- | |||
- | ==== FETCH ==== | ||
- | |||
- | Explicitly fetch data from a database | ||
- | |||
- | <code> | ||
- | FETCH [<n | ALL>] [ON [CURSOR] cursor] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== FOR ==== | ||
- | |||
- | Form a 'FOR n ... END' construct | ||
- | |||
- | <code> | ||
- | FOR n [WITH options] | ||
- | <statements> | ||
- | END | ||
- | </code> | ||
- | |||
- | ==== FORMAT ==== | ||
- | |||
- | Format a file of ODXSQL statements | ||
- | |||
- | <code> | ||
- | FORMAT input-file [INTO output-file] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== HELP ==== | ||
- | |||
- | Help | ||
- | |||
- | <code> | ||
- | HELP [<command | SYNTAX | API>] | ||
- | </code> | ||
- | |||
- | ==== HISTORY ==== | ||
- | |||
- | Show history of ODXSQL commands | ||
- | |||
- | <code> | ||
- | HISTORY | ||
- | </code> | ||
- | |||
- | ==== IF ==== | ||
- | |||
- | Form an 'IF ELIF ELSE ENDIF' construct | ||
- | |||
- | <code> | ||
- | IF condition | ||
- | <statements> | ||
- | ELIF condition | ||
- | <statements> | ||
- | ELSE | ||
- | <statements> | ||
- | ENDIF | ||
- | </code> | ||
- | |||
- | ==== INSERT ==== | ||
- | |||
- | Issue an SQL insert statement | ||
- | |||
- | <code> | ||
- | INSERT INTO table [(column-list)] <VALUES (value-list) | select-stmt> | ||
- | [ON [INSTANCE] instance] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== JOIN ==== | ||
- | |||
- | Join tables using Omnidex | ||
- | |||
- | <code> | ||
- | JOIN [FROM] table USING index TO [(owner)]table USING index | ||
- | [ON [CURSOR] cursor] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== LOAD OFX ==== | ||
- | |||
- | Load indexes for a table | ||
- | |||
- | <code> | ||
- | LOAD OFX [FOR <ENVIRONMENT [environment] | DATABASE database | TABLE table>] | ||
- | [ON [INSTANCE] instance] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== OPEN CURSOR ==== | ||
- | |||
- | Open a cursor | ||
- | |||
- | <code> | ||
- | OPEN CURSOR [ON [INSTANCE] instance] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== PARTITION ==== | ||
- | |||
- | Partition a table | ||
- | |||
- | <code> | ||
- | PARTITION table [INTO n] [BY <column | expression>] [IN path] | ||
- | [ON [INSTANCE] instance] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== PWD ==== | ||
- | |||
- | Print current working directory | ||
- | |||
- | <code> | ||
- | PWD | ||
- | </code> | ||
- | |||
- | ==== QUALIFY ==== | ||
- | |||
- | Qualify rows using Omnidex | ||
- | |||
- | <code> | ||
- | QUALIFY [(owner)]table WHERE [[$QUALIFIED] <AND | OR> [NOT]] | ||
- | predicate [ON [CURSOR] cursor] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== QUIT ==== | ||
- | |||
- | Quit Omnidex SQL | ||
- | |||
- | <code> | ||
- | QUIT | ||
- | </code> | ||
- | |||
- | ==== REGISTER LICENSE ==== | ||
- | |||
- | Register a license code | ||
- | |||
- | <code> | ||
- | REGISTER LICENSE license_code [FOR company] | ||
- | </code> | ||
- | |||
- | ==== REGISTER ODBC ==== | ||
- | |||
- | Register an ODBC driver | ||
- | |||
- | <code> | ||
- | REGISTER ODBC [dir] | ||
- | </code> | ||
- | |||
- | ==== REQUEST ==== | ||
- | |||
- | Request a license code | ||
- | |||
- | <code> | ||
- | REQUEST [<ENTERPRISE | DEVELOPER | TRIAL>] LICENSE | ||
- | </code> | ||
- | |||
- | ==== RESET HISTORY ==== | ||
- | |||
- | Reset history of ODXSQL commands | ||
- | |||
- | <code> | ||
- | RESET HISTORY | ||
- | </code> | ||
- | |||
- | ==== RESET TIMER ==== | ||
- | |||
- | Reset the cumulative timer | ||
- | |||
- | <code> | ||
- | RESET TIMER | ||
- | </code> | ||
- | |||
- | ==== RESTORE SETTINGS ==== | ||
- | |||
- | Restore settings, optionally from a file | ||
- | |||
- | <code> | ||
- | RESTORE SETTINGS [[FROM] filename] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== SAVE HISTORY ==== | ||
- | |||
- | Save commands to file | ||
- | |||
- | <code> | ||
- | SAVE HISTORY [TO] filename] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== SAVE SETTINGS ==== | ||
- | |||
- | Save settings, optionally to a file | ||
- | |||
- | <code> | ||
- | SAVE SETTINGS [TO] filename] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== SELECT ==== | ||
- | |||
- | Issue an SQL select statement | ||
- | |||
- | <code> | ||
- | SELECT statement [ON [CURSOR] cursor] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== SET ==== | ||
- | |||
- | Set an option | ||
- | |||
- | <code> | ||
- | SET [<option | ?>] [ON <INSTANCE | CURSOR> n] | ||
- | </code> | ||
- | |||
- | ==== SETENV ==== | ||
- | |||
- | Set an environment variable | ||
- | |||
- | <code> | ||
- | SETENV variable value | ||
- | </code> | ||
- | |||
- | ==== SHOW ==== | ||
- | |||
- | Describe an object | ||
- | |||
- | <code> | ||
- | 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] | ||
- | </code> | ||
- | |||
- | ==== TRANSFER LICENSE ==== | ||
- | |||
- | Transfer a license to another server | ||
- | |||
- | <code> | ||
- | TRANSFER LICENSE | ||
- | </code> | ||
- | |||
- | ==== UNREGISTER ODBC ==== | ||
- | |||
- | Unregister an ODBC driver | ||
- | |||
- | <code> | ||
- | UNREGISTER ODBC | ||
- | </code> | ||
- | |||
- | ==== UPDATE ==== | ||
- | |||
- | Issue an SQL update statement | ||
- | |||
- | <code> | ||
- | UPDATE table SET column-assignments | ||
- | [WHERE <predicate-list | CURRENT> [<ON | OF> [CURSOR] cursor]] | ||
- | [WITH options] | ||
- | </code> | ||
- | |||
- | ==== UPDATE INDEXES ==== | ||
- | |||
- | Update Omnidex indexes | ||
- | |||
- | <code> | ||
- | UPDATE INDEXES [FOR <ENVIRONMENT [environment] | DATABASE database | | ||
- | TABLE table | COLUMN table.column | INDEX table.index>] | ||
- | [ON [INSTANCE] instance] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== UPDATE ROLLUP ==== | ||
- | |||
- | Update rollup table | ||
- | |||
- | <code> | ||
- | UPDATE ROLLUP TABLE table [ON [INSTANCE] instance] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== UPDATE ROLLUPS ==== | ||
- | |||
- | Update rollup tables | ||
- | |||
- | <code> | ||
- | UPDATE ROLLUPS [FOR <ENVIRONMENT [environment] | DATABASE database | | ||
- | TABLE table>] [ON [INSTANCE] instance] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== UPDATE STATISTICS ==== | ||
- | |||
- | Update statistical information | ||
- | |||
- | <code> | ||
- | UPDATE STATISTICS [FOR <ENVIRONMENT [environment] | DATABASE database | | ||
- | TABLE table | COLUMN table.column | INDEX table.index>] | ||
- | [ON [INSTANCE] instance] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== UPDATE TEXT ==== | ||
- | |||
- | Update textual statistics | ||
- | |||
- | <code> | ||
- | UPDATE TEXT [FOR <ENVIRONMENT [environment] | DATABASE database | | ||
- | TABLE table | COLUMN table.column | INDEX table.index>] | ||
- | [ON [INSTANCE] instance] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== USE ==== | ||
- | |||
- | Execute a file of ODXSQL commands | ||
- | |||
- | <code> | ||
- | USE filename [WHERE criteria] [WITH options] | ||
- | </code> | ||
- | |||
- | ==== VALIDATE ==== | ||
- | |||
- | Validate an environment | ||
- | |||
- | <code> | ||
- | VALIDATE [<ENVIRONMENT [environment] | DATABASE database | TABLE table>] | ||
- | [ON [INSTANCE] instance] [WITH options] | ||
- | </code> | ||
- | |||
- | |||
- | ==== ! ==== | ||
- | |||
- | Execute a shell command | ||
- | |||
- | <code> | ||
- | !<shell command> or ! for interactive shell | ||
- | </code> | ||
- | |||
- | ==== . ==== | ||
- | |||
- | Execute last command | ||
- | |||
- | <code> | ||
- | <. | command number> | ||
- | </code> | ||
- | |||
- | ==== ; ==== | ||
- | |||
- | Comment line | ||
- | |||
- | <code> | ||
- | ; Comment ... | ||
- | </code> | ||
- | |||
- | ==== n ==== | ||
- | |||
- | Execute numbered command | ||
- | |||
- | <code> | ||
- | n (where n is a number from the command history) | ||
- | </code> | ||
- | |||
- | |||
- | ===== ===== | ||
- | |||
- | **[[programs:odxsql:home|Prev]]** | **[[programs:odxsql:options|Next]]** | | ||
- | |||
- | ====== Additional Resources ====== | ||
- | |||
- | See also: | ||
- | |||
- | {{page>:programs:see_also&nofooter&noeditbtn}} | ||
- | |||
- | {{page>:bottom_add&nofooter&noeditbtn}} | ||
- | |||
- | |||