This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Next revision Both sides next revision | ||
programs:odxsql:home [2010/07/04 01:46] tdo |
programs:odxsql:home [2015/06/10 18:08] doc |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | |||
+ | |||
+ | ====== Programs: OdxSQL ====== | ||
+ | |||
+ | [[programs:odxsql:home|Overview]] | **[[programs:odxsql:commands|Commands]]** | [[programs:odxsql:options|Options]] | [[programs:odxsql:cmdline|Command-line]] | [[programs:odxsql:files|Command Files]] | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
{{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
- | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
- | ~~NOTOC~~ | ||
- | ====== OdxSQL Quick Reference ====== | ||
- | {{page>:sql_odxsql_bar&nofooter&noeditbtn}} | ||
- | ^ Command Line Options ^ Description ^ | ||
- | | odxsql | Run OdxSQL. Use the CONNECT command to connect to an Omnidex Environment | | ||
- | | odxsql env_filespec | Run OdxSQL and connect to the env_filespec. | | ||
- | | odxsql dsn_filespec -odbc |Run OdxSQL and connect to the ODBC File DSN using the Omnidex ODBC driver.| | ||
- | | odxsql env_filespec -odxnet -jdbc |Run OdxSQL and start an OdxNet background process and connect to the env_filespec using the Omnidex JDBC driver.| | ||
- | | odxsql -? | Displays OdxSQL command line options | | ||
- | | odxsql -use scriptfile | Run OdxSQL and read and process the scriptfile of OdxSQL Statements and Commands.| | ||
- | ^Set Command ^ ^ | ||
- | |SET| Shows the current settings. | | ||
- | |SET ? | Shows the list of available SET options. | | ||
- | |SET TERMINATION OFF|Will process SQL Statements without requiring a semi-colon terminator.| | ||
- | |SET TIMER ON|Useful to show how long a statement takes to process.| | ||
- | ^ Help/Show ^ ^ | ||
- | |HELP|Shows a list of OdxSQL commands and SQL Statements.| | ||
- | |HELP <command>|Provides specific help on the specified command or SQL Statement.| | ||
- | |SHOW <table>|Provides information on the specified table.| | ||
- | |SHOW ALL|Provides information on all tables.| | ||
- | ^Editing^ ^ | ||
- | |EDIT| Launch editor and send current SQL Statement to either Windows Notedpad or UNIX/Linux vi or emacs. | | ||
- | |SET EDITOR <filespec>|Change the default editor. Can also be NOTEPAD, WORDPAD, VI or EMACS.| | ||
- | |EDIT filespec|Typically used to edit a script file and then use the USE file to run the script.| | ||
- | |EDIT n|Edits the command in the Command History Stack|. | ||
- | ^Other Commands^ ^ | ||
- | |HISTORY|Shows a numbered list of previously entered commands.| | ||
- | |<command_number>|Execute the numbered command from the Command History Stack.| | ||
- | |Period(.)|Execute the last command.| | ||
- | |USE <filespec>|Processes the OdxSQL commands in a script file.| | ||
- | |EXPLAIN|Shows the Explain Plan for the previous SELECT Statement.| | ||
- | |SET AUTOEXPLAIN ON|Automatically show the Explain after a SELECT Statement.| | ||
- | ^ OdxSQL Settings ^ ^ ^ ^ ^ ^ | + | ===== Commands ===== |
- | |API |AUTODISPLAY |AUTOEXPLAIN |AUTOFETCH |AUTOPSEUDOCOLUMNS |AUTORECONNECT| | + | |
- | |AUTOSUBPROCESS|CHAR|CURSOR|CURSORDISPLAY|DEFAULTS|EDITOR| | + | The following commands are available in OdxSQL: |
- | |ENDIAN |ERRORDISPLAY|ERRORS|EXPLAIN SET|FILE_BASELINE|HEADERS| | + | |
- | |HEX|HISTORY|INTERRUPTS|MEMORY_BASELINE|MULTICONNECT|MULTILINE| | + | |
- | |NULL_INDICATORS|OMNIDEX_DEBUG|OPTIMIZATION|ODXSQL_DEBUG|PAGELENGTH|PAGEWIDTH| | + | ==== ATTACH DATABASE ==== |
- | |PREPROCESSOR|PROGRESS|PROMPT|PSEUDOCOLUMNS|QUALIFY_COUNTS|REDEFROWIDS| | + | |
- | |REQUIRED_OPTIMIZATION|SILENT_MODE|SQL_LOGFILE|SQL_LOGGING|SQL_SYNTAX|STATISTICS| | + | Attach a database |
- | |STATISTICS_COMMENT|STATUSDISPLAY|TERMINATION|TIMEOUT|TIMER| | + | |
+ | ATTACH DATABASE database [AS alias] FROM filename [ON [INSTANCE] instance] [WITH options] | ||
+ | |||
+ | ==== ATTACH SEGMENT ==== | ||
+ | |||
+ | * Attach a segment | ||
+ | |||
+ | * ATTACH <INDEX | DATA> SEGMENT segment [PHYSICAL physical] | ||
+ | [ON [INSTANCE] instance] [WITH options] | ||
+ | |||
+ | ==== ATTACH TABLE ==== | ||
+ | |||
+ | * Attach a table | ||
+ | |||
+ | * ATTACH TABLE table [PHYSICAL "physical"] (column [, column ...]) | ||
+ | [ON [INSTANCE] instance>] [WITH options] | ||
+ | column: column-name datatype (length) | ||
+ | |||
+ | ==== BENCHMARK ==== | ||
+ | |||
+ | Perform performance benchmarks | ||
+ | |||
+ | BENCHMARK [<table | select-statement>] [WITH options] | ||
+ | |||
+ | ==== CALC ==== | ||
+ | |||
+ | Perform basic calculator functions | ||
+ | |||
+ | CALC expression | ||
+ | |||
+ | ==== CD ==== | ||
+ | |||
+ | Change directories | ||
+ | |||
+ | CD [path] | ||
+ | |||
+ | ==== CLOSE CURSOR ==== | ||
+ | |||
+ | * Close a cursor | ||
+ | |||
+ | * CLOSE CURSOR [[ON] CURSOR] cursor] [WITH options] | ||
+ | |||
+ | ==== CONNECT ==== | ||
+ | |||
+ | * Connect to an environment file | ||
+ | |||
+ | * 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] | ||
+ | |||
+ | ==== CONVERT ==== | ||
+ | |||
+ | Export a file with data conversions | ||
+ | |||
+ | CONVERT [statement TO filename [ON [CURSOR] cursor] [WITH options] | ||
+ | |||
+ | ==== CREATE DATABASE ==== | ||
+ | |||
+ | * Create a database | ||
+ | |||
+ | * CREATE DATABASE database node [node ...] IN "filename" [WITH options] | ||
+ | node: [NODE node] TYPE type [attribute [attribute ...]] | ||
+ | |||
+ | ==== CREATE ENVIRONMENT ==== | ||
+ | |||
+ | * Create an environment file | ||
+ | |||
+ | * CREATE ENVIRONMENT environment [attribute [attribute ...]] | ||
+ | [node [node ...]] IN "filename" [WITH options] | ||
+ | node: [NODE node] [attribute [attribute ...]] | ||
+ | |||
+ | ==== CREATE INDEX ==== | ||
+ | |||
+ | * 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 SEGMENT ==== | ||
+ | |||
+ | * Create a segment | ||
+ | |||
+ | * CREATE [<TEMPORARY | PERMANENT>] <INDEX | DATA> SEGMENT segment | ||
+ | [PHYSICAL physical] [AS (select)] [ON [CURSOR] cursor] | ||
+ | [WITH options] | ||
+ | |||
+ | ==== CREATE TABLE ==== | ||
+ | |||
+ | * 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) | ||
+ | |||
+ | ==== DATE ==== | ||
+ | |||
+ | Show the current date and time | ||
+ | |||
+ | DATE | ||
+ | |||
+ | ==== DELETE ==== | ||
+ | |||
+ | * Issue an SQL delete statement | ||
+ | |||
+ | * DELETE FROM table [WHERE < predicate-list | CURRENT> | ||
+ | [<ON | OF> [CURSOR] cursor]] [WITH options] | ||
+ | |||
+ | ==== DETACH DATABASE ==== | ||
+ | |||
+ | * Detach a previously attached database | ||
+ | |||
+ | * DETACH DATABASE database [ON [INSTANCE] instance] [WITH options] | ||
+ | |||
+ | ==== DETACH SEGMENT ==== | ||
+ | |||
+ | * Detach a previously attached segment | ||
+ | |||
+ | * DETACH SEGMENT segment [ON [INSTANCE] instance] [WITH options] | ||
+ | |||
+ | ==== DETACH TABLE ==== | ||
+ | |||
+ | * Detach a previously attached table | ||
+ | |||
+ | * DETACH TABLE table [ON [INSTANCE] instance] [WITH options] | ||
+ | |||
+ | ==== DIR ==== | ||
+ | |||
+ | Display a directory of files | ||
+ | |||
+ | DIR fileset [WITH options] | ||
+ | |||
+ | ==== DISCONNECT ==== | ||
+ | |||
+ | * Disconnect from an environment file | ||
+ | |||
+ | * DISCONNECT [ALL] [[ON [INSTANCE]] instance] [WITH options] | ||
+ | |||
+ | ==== DROP DATABASE ==== | ||
+ | |||
+ | * Drop a database | ||
+ | |||
+ | * DROP DATABASE [IF EXISTS] database IN "filename" [WITH options] | ||
+ | |||
+ | ==== DROP ENVIRONMENT ==== | ||
+ | |||
+ | * Drop an environment | ||
+ | |||
+ | * DROP ENVIRONMENT [IF EXISTS] [environment] IN "filename" [WITH options] | ||
+ | |||
+ | ==== DROP INDEX ==== | ||
+ | |||
+ | * 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 SEGMENT ==== | ||
+ | |||
+ | * Drop a segment | ||
+ | |||
+ | * DROP SEGMENT [IF EXISTS] segment [ON [INSTANCE] instance>] [WITH options] | ||
+ | |||
+ | ==== DROP TABLE ==== | ||
+ | |||
+ | * Drop a table | ||
+ | |||
+ | * DROP [<TEMPORARY | PERMANENT>] TABLE [IF EXISTS] table | ||
+ | [<IN "filename" | ON [INSTANCE] instance>] [WITH options] | ||
+ | |||
+ | ==== EDIT ==== | ||
+ | |||
+ | Edit a previous command or file | ||
+ | |||
+ | EDIT [n | filename] [WITH options] | ||
+ | |||
+ | ==== ERROR ==== | ||
+ | |||
+ | Display an error message | ||
+ | |||
+ | ERROR error_code | ||
+ | |||
+ | ==== EXIT ==== | ||
+ | |||
+ | Exit Omnidex SQL | ||
+ | |||
+ | EXIT | ||
+ | |||
+ | ==== EXPLAIN ==== | ||
+ | |||
+ | Explain approach to retrieval | ||
+ | |||
+ | EXPLAIN [statement] [ON [CURSOR] cursor] [WITH options] | ||
+ | |||
+ | ==== EXPORT ==== | ||
+ | |||
+ | * Export records to a file | ||
+ | |||
+ | * EXPORT [statement] TO filename [ON [CURSOR] cursor] [WITH options] | ||
+ | |||
+ | ==== EXTRACT ==== | ||
+ | |||
+ | * 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] | ||
+ | |||
+ | ==== FETCH ==== | ||
+ | |||
+ | Explicitly fetch data from a database | ||
+ | |||
+ | FETCH [<n | ALL>] [ON [CURSOR] cursor] [WITH options] | ||
+ | |||
+ | ==== FOR ==== | ||
+ | |||
+ | Form a 'FOR n ... END' construct | ||
+ | |||
+ | FOR n [WITH options] | ||
+ | <statements> | ||
+ | END | ||
+ | |||
+ | ==== FORMAT ==== | ||
+ | |||
+ | Format a file of ODXSQL statements | ||
+ | |||
+ | FORMAT input-file [INTO output-file] [WITH options] | ||
+ | |||
+ | ==== HELP ==== | ||
+ | |||
+ | Help | ||
+ | |||
+ | HELP [<command | SYNTAX | API>] | ||
+ | |||
+ | ==== HISTORY ==== | ||
+ | |||
+ | Show history of ODXSQL commands | ||
+ | |||
+ | HISTORY | ||
+ | |||
+ | ==== IF ==== | ||
+ | |||
+ | Form an 'IF ELIF ELSE ENDIF' construct | ||
+ | |||
+ | IF condition | ||
+ | <statements> | ||
+ | ELIF condition | ||
+ | <statements> | ||
+ | ELSE | ||
+ | <statements> | ||
+ | ENDIF | ||
+ | |||
+ | ==== INSERT ==== | ||
+ | |||
+ | * Issue an SQL insert statement | ||
+ | |||
+ | * INSERT INTO table [(column-list)] <VALUES (value-list) | select-stmt> | ||
+ | [ON [INSTANCE] instance] [WITH options] | ||
+ | |||
+ | ==== JOIN ==== | ||
+ | |||
+ | * Join tables using Omnidex | ||
+ | |||
+ | * JOIN [FROM] table USING index TO [(owner)]table USING index | ||
+ | [ON [CURSOR] cursor] [WITH options] | ||
+ | |||
+ | ==== LOAD OFX ==== | ||
+ | |||
+ | * Load indexes for a table | ||
+ | |||
+ | * LOAD OFX [FOR <ENVIRONMENT [environment] | DATABASE database | TABLE table>] | ||
+ | [ON [INSTANCE] instance] [WITH options] | ||
+ | |||
+ | ==== OPEN CURSOR ==== | ||
+ | |||
+ | * Open a cursor | ||
+ | |||
+ | * OPEN CURSOR [ON [INSTANCE] instance] [WITH options] | ||
+ | |||
+ | ==== PARTITION ==== | ||
+ | |||
+ | * Partition a table | ||
+ | |||
+ | * PARTITION table [INTO n] [BY <column | expression>] [IN path] | ||
+ | [ON [INSTANCE] instance] [WITH options] | ||
+ | |||
+ | ==== PWD ==== | ||
+ | |||
+ | Print current working directory | ||
+ | |||
+ | PWD | ||
+ | |||
+ | ==== QUALIFY ==== | ||
+ | |||
+ | * Qualify rows using Omnidex | ||
+ | |||
+ | * QUALIFY [(owner)]table WHERE [[$QUALIFIED] <AND | OR> [NOT]] | ||
+ | predicate [ON [CURSOR] cursor] [WITH options] | ||
+ | |||
+ | ==== QUIT ==== | ||
+ | |||
+ | Quit Omnidex SQL | ||
+ | |||
+ | QUIT | ||
+ | |||
+ | ==== REGISTER LICENSE ==== | ||
+ | |||
+ | Register a license code | ||
+ | |||
+ | REGISTER LICENSE license_code [FOR company] | ||
+ | |||
+ | ==== REGISTER ODBC ==== | ||
+ | |||
+ | Register an ODBC driver | ||
+ | |||
+ | REGISTER ODBC [dir] | ||
+ | |||
+ | ==== REQUEST ==== | ||
+ | |||
+ | Request a license code | ||
+ | |||
+ | REQUEST [<ENTERPRISE | DEVELOPER | TRIAL>] LICENSE | ||
+ | [FOR COMPANY "company" | ||
+ | CONTACT "contact" | ||
+ | PHONE "phone" | ||
+ | EMAIL "email"] | ||
+ | |||
+ | ==== RESET HISTORY ==== | ||
+ | |||
+ | Reset history of ODXSQL commands | ||
+ | |||
+ | RESET HISTORY | ||
+ | |||
+ | ==== RESET TIMER ==== | ||
+ | |||
+ | Reset the cumulative timer | ||
+ | |||
+ | RESET TIMER | ||
+ | |||
+ | ==== RESTORE SETTINGS ==== | ||
+ | |||
+ | Restore settings, optionally from a file | ||
+ | |||
+ | RESTORE SETTINGS [[FROM] filename] [WITH options] | ||
+ | |||
+ | ==== SAVE HISTORY ==== | ||
+ | |||
+ | Save commands to file | ||
+ | |||
+ | SAVE HISTORY [[TO] filename] [WITH options] | ||
+ | |||
+ | ==== SAVE SETTINGS ==== | ||
+ | |||
+ | Save settings, optionally to a file | ||
+ | |||
+ | SAVE SETTINGS [[TO] filename] [WITH options] | ||
+ | |||
+ | ==== SELECT ==== | ||
+ | |||
+ | * Issue an SQL select statement | ||
+ | |||
+ | * SELECT statement [ON [CURSOR] cursor] [WITH options] | ||
+ | |||
+ | ==== SET ==== | ||
+ | |||
+ | Set an option | ||
+ | |||
+ | SET [<option | ?>] [ON <INSTANCE | CURSOR> n] | ||
+ | |||
+ | ==== SETENV ==== | ||
+ | |||
+ | * Set an environment variable | ||
+ | |||
+ | * SETENV variable value | ||
+ | |||
+ | ==== SHOW ==== | ||
+ | |||
+ | 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> | ||
+ | |||
+ | ==== TRANSFER LICENSE ==== | ||
+ | |||
+ | Transfer a license to another server | ||
+ | |||
+ | TRANSFER LICENSE | ||
+ | |||
+ | ==== UNREGISTER ODBC ==== | ||
+ | |||
+ | Unregister an ODBC driver | ||
+ | |||
+ | UNREGISTER ODBC | ||
+ | |||
+ | ==== UPDATE ==== | ||
+ | |||
+ | * Issue an SQL update statement | ||
+ | |||
+ | * UPDATE table SET column-assignments | ||
+ | [WHERE <predicate-list | CURRENT> [<ON | OF> [CURSOR] cursor]] | ||
+ | [WITH options] | ||
+ | |||
+ | ==== UPDATE INDEXES ==== | ||
+ | |||
+ | * 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 ==== | ||
+ | |||
+ | * Update rollup table | ||
+ | |||
+ | * UPDATE ROLLUP TABLE table [ON [INSTANCE] instance] [WITH options] | ||
+ | |||
+ | ==== UPDATE ROLLUPS ==== | ||
+ | |||
+ | * Update rollup tables | ||
+ | |||
+ | * UPDATE ROLLUPS [FOR <ENVIRONMENT [environment] | DATABASE database | | ||
+ | TABLE table>] [ON [INSTANCE] instance] [WITH options] | ||
+ | |||
+ | ==== UPDATE STATISTICS ==== | ||
+ | |||
+ | * Update statistical information | ||
+ | |||
+ | * UPDATE STATISTICS [FOR <ENVIRONMENT [environment] | DATABASE database | | ||
+ | TABLE table | COLUMN table.column | INDEX table.index>] | ||
+ | [ON [INSTANCE] instance] [WITH options] | ||
+ | |||
+ | ==== UPDATE TEXT ==== | ||
+ | |||
+ | * Update textual statistics | ||
+ | |||
+ | * UPDATE TEXT [FOR <ENVIRONMENT [environment] | DATABASE database | | ||
+ | TABLE table | COLUMN table.column | INDEX table.index>] | ||
+ | [ON [INSTANCE] instance] [WITH options] | ||
+ | |||
+ | ==== USE ==== | ||
+ | |||
+ | Execute a file of ODXSQL commands | ||
+ | |||
+ | USE filename [WHERE criteria] [WITH options] | ||
+ | criteria: SQL syntax referencing SECTION, TEST and/or LINE | ||
+ | |||
+ | ==== VALIDATE ==== | ||
+ | |||
+ | 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 | ||
+ | |||
+ | ; [<directive [directive ...]>] text | ||
+ | <SUSPEND> <RESUME> <VERBOSE> <QUIET> | ||
+ | <COMMON> <END_COMMON> <SECTION> <END_SECTION> <TEST> <END_TEST> | ||
+ | |||
+ | |||
+ | ==== n ==== | ||
+ | |||
+ | Execute numbered command | ||
+ | |||
+ | n (where n is a number from the command history) | ||
+ | |||
+ | |||
+ | ===== ===== | ||
+ | |||
+ | **[[programs:odxsql:home|Prev]]** | **[[programs:odxsql:options|Next]]** | | ||
+ | |||
+ | ====== Additional Resources ====== | ||
+ | |||
+ | See also: | ||
+ | |||
+ | {{page>:programs:see_also&nofooter&noeditbtn}} | ||
+ | |||
+ | {{page>:bottom_add&nofooter&noeditbtn}} | ||
+ | |||