This shows you the differences between two versions of the page.
programs:odxsql:commands [2015/06/10 18:29] doc |
programs:odxsql:commands [2016/06/28 22:38] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | {{page>:top_add&nofooter&noeditbtn}} | ||
- | |||
- | |||
- | ====== 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 ==== | ||
- | |||
- | Attach a database | ||
- | |||
- | <nowiki> | ||
- | ATTACH DATABASE database [AS alias] FROM filename [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== ATTACH SEGMENT ==== | ||
- | |||
- | Attach a segment | ||
- | |||
- | <nowiki> | ||
- | ATTACH <INDEX | DATA> SEGMENT segment [PHYSICAL physical] [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== ATTACH TABLE ==== | ||
- | |||
- | Attach a table | ||
- | |||
- | <nowiki> | ||
- | ATTACH TABLE table [PHYSICAL "physical"] (column [, column ...]) [ON [INSTANCE] instance>] [WITH options] | ||
- | |||
- | column: column-name datatype (length) | ||
- | </nowiki> | ||
- | |||
- | ==== BENCHMARK ==== | ||
- | |||
- | Perform performance benchmarks | ||
- | |||
- | <nowiki> | ||
- | BENCHMARK [<table | select-statement>] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== CALC ==== | ||
- | |||
- | Perform basic calculator functions | ||
- | |||
- | <nowiki> | ||
- | CALC expression | ||
- | </nowiki> | ||
- | |||
- | ==== CD ==== | ||
- | |||
- | Change directories | ||
- | |||
- | <nowiki> | ||
- | CD [path] | ||
- | </nowiki> | ||
- | |||
- | ==== CLOSE CURSOR ==== | ||
- | |||
- | Close a cursor | ||
- | |||
- | <nowiki> | ||
- | CLOSE CURSOR [[ON] CURSOR] cursor] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== CONNECT ==== | ||
- | |||
- | Connect to an environment file | ||
- | |||
- | <nowiki> | ||
- | CONNECT [TO] connection_string [USING api] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== CONVERT ==== | ||
- | |||
- | Export a file with data conversions | ||
- | |||
- | <nowiki> | ||
- | CONVERT [statement TO filename [ON [CURSOR] cursor] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== CREATE DATABASE ==== | ||
- | |||
- | Create a database | ||
- | |||
- | <nowiki> | ||
- | CREATE DATABASE database node [node ...] IN "filename" [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== CREATE ENVIRONMENT ==== | ||
- | |||
- | Create an environment file | ||
- | |||
- | <nowiki> | ||
- | CREATE ENVIRONMENT environment [attribute [attribute ...]] | ||
- | </nowiki> | ||
- | |||
- | ==== CREATE INDEX ==== | ||
- | |||
- | Create an index or index group | ||
- | |||
- | <nowiki> | ||
- | CREATE <OMNIDEX | QUICKTEXT | FULLTEXT | CUSTOM | NATIVE> INDEX [[(owner)]table.]index [ON table] (<column | substring> [, <column | substring> ...])] [attribute [attribute ...]] IN "filename" [WITH options] | ||
- | </nowiki> | ||
- | |||
- | <nowiki> | ||
- | CREATE INDEX GROUP [(owner)]group (table.index [, table.index ...])] IN "filename" [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== CREATE SEGMENT ==== | ||
- | |||
- | Create a segment | ||
- | |||
- | <nowiki> | ||
- | CREATE [<TEMPORARY | PERMANENT>] <INDEX | DATA> SEGMENT segment [PHYSICAL physical] [AS (select)] [ON [CURSOR] cursor] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== CREATE TABLE ==== | ||
- | |||
- | Create a table | ||
- | |||
- | <nowiki> | ||
- | CREATE [<[<GLOBAL | LOCAL>] TEMPORARY | PERMANENT>] TABLE table [PHYSICAL "physical"] (column [, column ...]) [<IN "filename" | ON [INSTANCE] instance>] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | column: column-name datatype (length) | ||
- | |||
- | ==== DATE ==== | ||
- | |||
- | Show the current date and time | ||
- | |||
- | <nowiki> | ||
- | DATE | ||
- | </nowiki> | ||
- | |||
- | ==== DELETE ==== | ||
- | |||
- | Issue an SQL delete statement | ||
- | |||
- | <nowiki> | ||
- | DELETE FROM table [WHERE < predicate-list | CURRENT> [<ON | OF> [CURSOR] cursor]] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== DETACH DATABASE ==== | ||
- | |||
- | Detach a previously attached database | ||
- | |||
- | <nowiki> | ||
- | DETACH DATABASE database [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== DETACH SEGMENT ==== | ||
- | |||
- | Detach a previously attached segment | ||
- | |||
- | <nowiki> | ||
- | DETACH SEGMENT segment [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== DETACH TABLE ==== | ||
- | |||
- | Detach a previously attached table | ||
- | |||
- | <nowiki> | ||
- | DETACH TABLE table [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== DIR ==== | ||
- | |||
- | Display a directory of files | ||
- | |||
- | <nowiki> | ||
- | DIR fileset [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== DISCONNECT ==== | ||
- | |||
- | Disconnect from an environment file | ||
- | |||
- | <nowiki> | ||
- | DISCONNECT [ALL] [[ON [INSTANCE]] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== DROP DATABASE ==== | ||
- | |||
- | Drop a database | ||
- | |||
- | <nowiki> | ||
- | DROP DATABASE [IF EXISTS] database IN "filename" [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== DROP ENVIRONMENT ==== | ||
- | |||
- | Drop an environment | ||
- | |||
- | <nowiki> | ||
- | DROP ENVIRONMENT [IF EXISTS] [environment] IN "filename" [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== DROP INDEX ==== | ||
- | |||
- | Drop an index or index group | ||
- | |||
- | <nowiki> | ||
- | DROP [<OMNIDEX | NATIVE>] INDEX [IF EXISTS] [[(owner)]table.]index [ON table] IN "filename" [WITH options] | ||
- | </nowiki> | ||
- | |||
- | <nowiki> | ||
- | DROP INDEX GROUP [IF EXISTS] [(owner)]group IN "filename" [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== DROP SEGMENT ==== | ||
- | |||
- | Drop a segment | ||
- | |||
- | <nowiki> | ||
- | DROP SEGMENT [IF EXISTS] segment [ON [INSTANCE] instance>] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== DROP TABLE ==== | ||
- | |||
- | Drop a table | ||
- | |||
- | <nowiki> | ||
- | DROP [<TEMPORARY | PERMANENT>] TABLE [IF EXISTS] table [<IN "filename" | ON [INSTANCE] instance>] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== EDIT ==== | ||
- | |||
- | Edit a previous command or file | ||
- | |||
- | <nowiki> | ||
- | EDIT [n | filename] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== ERROR ==== | ||
- | |||
- | Display an error message | ||
- | |||
- | <nowiki> | ||
- | ERROR error_code | ||
- | </nowiki> | ||
- | |||
- | ==== EXIT ==== | ||
- | |||
- | Exit Omnidex SQL | ||
- | |||
- | <nowiki> | ||
- | EXIT | ||
- | </nowiki> | ||
- | |||
- | ==== EXPLAIN ==== | ||
- | |||
- | Explain approach to retrieval | ||
- | |||
- | <nowiki> | ||
- | EXPLAIN [statement] [ON [CURSOR] cursor] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== EXPORT ==== | ||
- | |||
- | Export records to a file | ||
- | |||
- | <nowiki> | ||
- | EXPORT [statement] TO filename [ON [CURSOR] cursor] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== EXTRACT ==== | ||
- | |||
- | Extract SQL statements for database | ||
- | |||
- | <nowiki> | ||
- | EXTRACT DDL [FOR <ENVIRONMENT [environment] | DATABASE database>] [TO filename] [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | <nowiki> | ||
- | EXTRACT DDL FOR <MYSQL | ORACLE | ODBC | SQLSERVER> [TO filename] [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | <nowiki> | ||
- | EXTRACT DML FOR table-list [WHERE criteria] [TO filename] [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | <nowiki> | ||
- | EXTRACT [<ALL | n | LONGEST n | ERRORING>] QUERIES [BY <CONNECTION | STATEMENT>] [WHERE criteria] [TO filename] [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== FETCH ==== | ||
- | |||
- | Explicitly fetch data from a database | ||
- | |||
- | <nowiki> | ||
- | FETCH [<n | ALL>] [ON [CURSOR] cursor] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== FOR ==== | ||
- | |||
- | Form a 'FOR n ... END' construct | ||
- | |||
- | <nowiki> | ||
- | FOR n [WITH options] | ||
- | <statements> | ||
- | END | ||
- | </nowiki> | ||
- | |||
- | ==== FORMAT ==== | ||
- | |||
- | Format a file of ODXSQL statements | ||
- | |||
- | <nowiki> | ||
- | FORMAT input-file [INTO output-file] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== HELP ==== | ||
- | |||
- | Help | ||
- | |||
- | <nowiki> | ||
- | HELP [<command | SYNTAX | API>] | ||
- | </nowiki> | ||
- | |||
- | ==== HISTORY ==== | ||
- | |||
- | Show history of ODXSQL commands | ||
- | |||
- | <nowiki> | ||
- | HISTORY | ||
- | </nowiki> | ||
- | |||
- | ==== IF ==== | ||
- | |||
- | Form an 'IF ELIF ELSE ENDIF' construct | ||
- | |||
- | <nowiki> | ||
- | IF condition | ||
- | <statements> | ||
- | ELIF condition | ||
- | <statements> | ||
- | ELSE | ||
- | <statements> | ||
- | ENDIF | ||
- | </nowiki> | ||
- | |||
- | ==== INSERT ==== | ||
- | |||
- | Issue an SQL insert statement | ||
- | |||
- | <nowiki> | ||
- | INSERT INTO table [(column-list)] <VALUES (value-list) | select-stmt> [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== JOIN ==== | ||
- | |||
- | Join tables using Omnidex | ||
- | |||
- | <nowiki> | ||
- | JOIN [FROM] table USING index TO [(owner)]table USING index [ON [CURSOR] cursor] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== LOAD OFX ==== | ||
- | |||
- | Load indexes for a table | ||
- | |||
- | <nowiki> | ||
- | LOAD OFX [FOR <ENVIRONMENT [environment] | DATABASE database | TABLE table>] [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== OPEN CURSOR ==== | ||
- | |||
- | Open a cursor | ||
- | |||
- | <nowiki> | ||
- | OPEN CURSOR [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== PARTITION ==== | ||
- | |||
- | Partition a table | ||
- | |||
- | <nowiki> | ||
- | PARTITION table [INTO n] [BY <column | expression>] [IN path] [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== PWD ==== | ||
- | |||
- | Print current working directory | ||
- | |||
- | <nowiki> | ||
- | PWD | ||
- | </nowiki> | ||
- | |||
- | ==== QUALIFY ==== | ||
- | |||
- | Qualify rows using Omnidex | ||
- | |||
- | <nowiki> | ||
- | QUALIFY [(owner)]table WHERE [[$QUALIFIED] <AND | OR> [NOT]] predicate [ON [CURSOR] cursor] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== QUIT ==== | ||
- | |||
- | Quit Omnidex SQL | ||
- | |||
- | <nowiki> | ||
- | QUIT | ||
- | </nowiki> | ||
- | |||
- | ==== REGISTER LICENSE ==== | ||
- | |||
- | Register a license code | ||
- | |||
- | <nowiki> | ||
- | REGISTER LICENSE license_code [FOR company] | ||
- | </nowiki> | ||
- | |||
- | ==== REGISTER ODBC ==== | ||
- | |||
- | Register an ODBC driver | ||
- | |||
- | <nowiki> | ||
- | REGISTER ODBC [dir] | ||
- | </nowiki> | ||
- | |||
- | ==== REQUEST ==== | ||
- | |||
- | Request a license code | ||
- | |||
- | <nowiki> | ||
- | REQUEST [<ENTERPRISE | DEVELOPER | TRIAL>] LICENSE [FOR COMPANY "company" CONTACT "contact" PHONE "phone" EMAIL "email"] | ||
- | </nowiki> | ||
- | |||
- | ==== RESET HISTORY ==== | ||
- | |||
- | Reset history of ODXSQL commands | ||
- | |||
- | <nowiki> | ||
- | RESET HISTORY | ||
- | </nowiki> | ||
- | |||
- | ==== RESET TIMER ==== | ||
- | |||
- | Reset the cumulative timer | ||
- | |||
- | <nowiki> | ||
- | RESET TIMER | ||
- | </nowiki> | ||
- | |||
- | ==== RESTORE SETTINGS ==== | ||
- | |||
- | Restore settings, optionally from a file | ||
- | |||
- | <nowiki> | ||
- | RESTORE SETTINGS [[FROM] filename] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== SAVE HISTORY ==== | ||
- | |||
- | Save commands to file | ||
- | |||
- | <nowiki> | ||
- | SAVE HISTORY [[TO] filename] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== SAVE SETTINGS ==== | ||
- | |||
- | Save settings, optionally to a file | ||
- | |||
- | <nowiki> | ||
- | SAVE SETTINGS [[TO] filename] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== SELECT ==== | ||
- | |||
- | Issue an SQL select statement | ||
- | |||
- | <nowiki> | ||
- | SELECT statement [ON [CURSOR] cursor] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== SET ==== | ||
- | |||
- | Set an option | ||
- | |||
- | <nowiki> | ||
- | SET [<option | ?>] [ON <INSTANCE | CURSOR> n] | ||
- | </nowiki> | ||
- | |||
- | ==== SETENV ==== | ||
- | |||
- | Set an environment variable | ||
- | |||
- | <nowiki> | ||
- | SETENV variable value | ||
- | </nowiki> | ||
- | |||
- | ==== SHOW ==== | ||
- | |||
- | Describe an object | ||
- | |||
- | <nowiki> | ||
- | SHOW <ENVIRONMENT [environment] | DATABASE database | TABLE table | COLUMN column> [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | <nowiki> | ||
- | SHOW [<USER_ | DATA_ | METADATA_ | SYSTEM_ | ALL_>] <DATABASES | TABLES | COLUMNS> [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | <nowiki> | ||
- | SHOW ALL [database] [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | <nowiki> | ||
- | SHOW <INSTANCE | CURSOR> [object] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | <nowiki> | ||
- | SHOW LICENSE | ||
- | </nowiki> | ||
- | |||
- | <nowiki> | ||
- | SHOW <CONNECTIONS | STATEMENTS> | ||
- | </nowiki> | ||
- | |||
- | ==== TRANSFER LICENSE ==== | ||
- | |||
- | Transfer a license to another server | ||
- | |||
- | <nowiki> | ||
- | TRANSFER LICENSE | ||
- | </nowiki> | ||
- | |||
- | ==== UNREGISTER ODBC ==== | ||
- | |||
- | Unregister an ODBC driver | ||
- | |||
- | <nowiki> | ||
- | UNREGISTER ODBC | ||
- | </nowiki> | ||
- | |||
- | ==== UPDATE ==== | ||
- | |||
- | Issue an SQL update statement | ||
- | |||
- | <nowiki> | ||
- | UPDATE table SET column-assignments [WHERE <predicate-list | CURRENT> [<ON | OF> [CURSOR] cursor]] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== UPDATE INDEXES ==== | ||
- | |||
- | Update Omnidex indexes | ||
- | |||
- | <nowiki> | ||
- | UPDATE INDEXES [FOR <ENVIRONMENT [environment] | DATABASE database | TABLE table | COLUMN table.column | INDEX table.index>] [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== UPDATE ROLLUP ==== | ||
- | |||
- | Update rollup table | ||
- | |||
- | <nowiki> | ||
- | UPDATE ROLLUP TABLE table [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== UPDATE ROLLUPS ==== | ||
- | |||
- | Update rollup tables | ||
- | |||
- | <nowiki> | ||
- | UPDATE ROLLUPS [FOR <ENVIRONMENT [environment] | DATABASE database | TABLE table>] [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== UPDATE STATISTICS ==== | ||
- | |||
- | Update statistical information | ||
- | |||
- | <nowiki> | ||
- | UPDATE STATISTICS [FOR <ENVIRONMENT [environment] | DATABASE database | TABLE table | COLUMN table.column | INDEX table.index>] [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== UPDATE TEXT ==== | ||
- | |||
- | Update textual statistics | ||
- | |||
- | <nowiki> | ||
- | UPDATE TEXT [FOR <ENVIRONMENT [environment] | DATABASE database | TABLE table | COLUMN table.column | INDEX table.index>] [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== USE ==== | ||
- | |||
- | Execute a file of ODXSQL commands | ||
- | |||
- | <nowiki> | ||
- | USE filename [WHERE criteria] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | ==== VALIDATE ==== | ||
- | |||
- | Validate an environment | ||
- | |||
- | <nowiki> | ||
- | VALIDATE [<ENVIRONMENT [environment] | DATABASE database | TABLE table>] [ON [INSTANCE] instance] [WITH options] | ||
- | </nowiki> | ||
- | |||
- | |||
- | ==== ! ==== | ||
- | |||
- | Execute a shell command | ||
- | |||
- | <nowiki> | ||
- | !<shell command> or ! for interactive shell | ||
- | </nowiki> | ||
- | |||
- | ==== . ==== | ||
- | |||
- | Execute last command | ||
- | |||
- | <nowiki> | ||
- | <. | command number> | ||
- | </nowiki> | ||
- | |||
- | ==== ; ==== | ||
- | |||
- | Comment line | ||
- | |||
- | |||
- | ==== n ==== | ||
- | |||
- | Execute numbered command | ||
- | |||
- | <nowiki> | ||
- | n (where n is a number from the command history) | ||
- | </nowiki> | ||
- | |||
- | |||
- | ===== ===== | ||
- | |||
- | **[[programs:odxsql:home|Prev]]** | **[[programs:odxsql:options|Next]]** | | ||
- | |||
- | ====== Additional Resources ====== | ||
- | |||
- | See also: | ||
- | |||
- | {{page>:programs:see_also&nofooter&noeditbtn}} | ||
- | |||
- | {{page>:bottom_add&nofooter&noeditbtn}} | ||
- | |||
- | |||