Show page
Old revisions

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
Next revision Both sides next revision
programs:odxsql:home [2010/07/03 23:14]
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>:​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 -odxnet -odbc | Run OdxSQL and an OdxNET background process and connect to the ODBC File DSN using ODBC | 
-| odxsql -? | Displays OdxSQL command line options | 
-| odxsql -use scriptfile | Run OdxSQL and read and process the scriptfile of OdxSQL Statements and Commands.| 
-^Set Command ^Description ^ 
-|SET| Shows the current settings. | 
-|SET ? | Shows the list of available SET options. | 
-|SET TERMINATION OFF|Will Automatically terminate SQL Statements.| 
-^ Help/Show ^Description^ 
-|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.| 
  
 +===== Commands =====
  
-===== OdxSQL ​Uses =====+The following commands are available in OdxSQL:
  
-OdxSQL is a console application for manually entering Omnidex SQL Statements and OdxSQL Commands or for processing scripts of Omnidex SQL statements. 
  
-Use OdxSQL for processing Omnidex SQL Data Definition Scripts to create Omnidex Environments and Indexes.+==== ATTACH DATABASE ​    ====
  
-Use OdxSQL to test Omnidex SQL SELECTs and ODBC, JDBC and Network Services set up.+Attach a database
  
-Use OdxSQL to prototype Omnidex Queries and use the Explain Command to review optimization.+ATTACH DATABASE database [AS alias] FROM filename [ON [INSTANCE] instance] [WITH options]
  
-Use OdxSQL on a client computer to access an Omnidex Environment on a remote server via OdxNet to test network response times.+==== ATTACH SEGMENT ​     ====
  
-  + * Attach a segment
-===== Common ways to run OdxSQL =====+
  
 + * ATTACH <INDEX | DATA> SEGMENT segment [PHYSICAL physical]
 +     [ON [INSTANCE] instance] [WITH options]
  
-  os> odxsql +==== ATTACH TABLE        ====
-  os> odxsql envfile +
-  os> odxsql dsnfile -odxnet -odbc +
-  os> odxsql -? +
-  os> odxsql -use scriptfile+
  
-Running OdxSQL Explained:  + * Attach a table
-| odxsql | Run OdxSQL. ​ Use the CONNECT command to connect to an Omnidex Environment | +
-| odxsql envfile | Run OdxSQL and connect to the '​envfile'​ | +
-| odxsql dsnfile -odxnet -odbc | Run OdxSQL and an OdxNET background process and connect to the DSN using ODBC | +
-| odxsql -? | Displays OdxSQL command line options | +
-| odxsql -use scriptfile | Run OdxSQL and read and process the scriptfile of OdxSQL Statements and Commands.|+
  
 + * ATTACH TABLE table [PHYSICAL "​physical"​] (column [, column ...])
 +     [ON [INSTANCE] instance>​] [WITH options]
 +     ​column:​ column-name datatype (length)
  
-===== OdxSQL Important Information =====+==== BENCHMARK ​          ====
  
-  * OdxSQL connects to an Omnidex Environment or ODBC or JDBC Omnidex File Datasource (DSN). +   Perform performance benchmarks
-    * connect myenv.xml +
-    * connect mydsn.dsn using odbc  (needs OdxNet running) +
-    * connect mydsn.dsn using jdbc  (needs OdxNet running)+
  
-    * OdxSQL requires a semicolon terminator for SQL Statements and will prompt for additional statement information until a semicolon is found. +   BENCHMARK [<table | select-statement>] [WITH options]
-    * Use SET TERMINATION OFF and OdxSQL will not required the semicolon termination character and will end the statement ​automatically. +
-    * With SET TERMINATION OFF, use the backslash %%[ \ ]%% statement continuation character for multi line statements.  +
-    * Use HELP <cr> to display a list of commands and SQL Statements. +
-    * Use HELP <​command>​ to display the syntax for the command.+
  
-  +==== CALC                ====
-  * Use SHOW <​table>​ to show information on a table with the tables'​s column layout. +
-    * Use SHOW ALL to see all tables, including internal system tables.+
  
-  * Create scripts with an editor and process with the USE <​scriptfile>​ command or the OdxSQL -use scriptfile +   Perform basic calculator functions
-     +
-  * Use EDIT to edit the last statement using Windows Notepad or Linux/Unix vi or emacs. +
-    * Use SET EDIT to change the editor of choice. +
-    * Use EDIT file to edit a script file without leaving OdxSQL and then USE file to run. +
-    * Use EDIT n to edit a command in the Command History Stack.+
  
-  * Edit previous commands by using the UP ARROW and scrolling through the commands. +   CALC expression
-    * Use the Right and Left Arrow, Insert, Delete, Home, and End keys to edit.+
  
-  * Use HISTORY to display a numbered list of previous commands. +==== CD                  ====
-    * Use the history number to re-execute the numbered command. +
-    * Use a period ( . ) to execute the last command. +
- +
  
-===== See Also =====+   ​Change directories
  
-    * [[programs:​odxsql:​overview | OdxSQL Overview ]] +   CD [path]
-    * [[programs:​odxsql:​commands:​home |OdxSQL Commands ]] +
-    * [[programs:​odxsql:​settings:​home |OdxSQL Settings ]] +
-    * [[programs:​odxsql:​commandline |OdxSQL Command Line Options ]] +
-    * OdxSQL Script Examples +
-    * [[ dev:​sql:​statements:​select:​home | SQL SELECT ]] +
-    * SQL SELECT Examples +
-    * [[ dev:​sql:​statements:​home | Omnidex SQL Statements ]]+
  
-===== OdxSQL Internal SET Commands =====+==== CLOSE CURSOR ​       ​====
  
-<​code>​ + * Close a cursor
-Internal +
-x [AUTOPSEUDOCOLUMNS <ON | OFF>] +
-x [AUTORECONNECT <ON | OFF>] +
-x [AUTOSUBPROCESS <n | NONE> +
-x [STATISTICS_COMMENT "​string"​] +
-x [PREPROCESSOR <ON | OFF>] +
-x [PROGRESS <ON | OFF>] +
-x [MEMORY_BASELINE] +
-x [FILE_BASELINE] +
-</​code>​+
  
-===== Level 2 Headline =====+ * CLOSE CURSOR [[ON] CURSOR] cursor] [WITH options]
  
-<​code>​ +==== CONNECT ​            ====
-        SET DEBUG … +
- SET OPTIMIZATION … +
- SET REQUIRED_OPTIMIZATION +
- SET CHAR … +
- SET TIMEOUT … +
- SET REDEFROWIDS …  +
- SET PSEUDOCOLUMNS … +
- SET QUALIFY_MODE <ODXFIND | PARSER > +
- SET AUTOPSEUDOCOLUMNS … +
- SET PREPROCESSOR … +
- SET SQL_LOGGING … +
- SET SQL_LOGFILE … +
-</​code>​+
  
 + * Connect to an environment file
  
-The syntaxes of the SQL Logging SET commands are shown below:+ * 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]
  
-    set sql_logging <on | off> ​+==== CONVERT ​            ====
  
-This command activates SQL Logging for this process. ​ By default, statements are logged to the $SQL_STATEMENTS ​file along with statistics about the execution of the command. ​ The $SQL_STATEMENTS file is named “sqllog.tdf”,​ and is located in the same directory as the compiled environment file.  This file is a standard tab-delimited file, and may be easily imported into other applications. ​ The file may be flushed by simply removing the file; the file will be automatically recreated the next time logging is activated.+   ​Export a file with data conversions
  
-    set sql_logfile <filename>+   ​CONVERT [statement TO filename ​[ON [CURSOR] cursor] [WITH options]
  
-This command redirects the SQL logging for this process to a text file that only contains the statement, and does not contain any statistical information. ​ If the file already exists, it will be appended to rather than overwritten.+==== 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}} {{page>:​bottom_add&​nofooter&​noeditbtn}}
 +
 +
 +
 
Back to top
programs/odxsql/home.txt · Last modified: 2016/06/28 22:38 (external edit)
Sitemap Recent Changes