Differences

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

--- programs:odxsql:commands:home [2009/12/08 01:59]
tdo
+++ programs:odxsql:commands:home [2016/06/28 22:38] (current)
@@ Line 2: / Line 2: @@
 <html><div align="center"><span style="color:red">DRAFT</span></div></html>
 ~~NOTOC~~
-====== OdxSQL Commands ======
+====== OdxSQL Commands and Omnidex SQL Statements ======
 {{page>:odxsql_bar&nofooter&noeditbtn}}
-===== Overview =====
-OdxSQL is used to process a wide range of Omnidex SQL Statements and OdxSQL specific commands.
+OdxSQL processes Omnidex SQL statements and OdxSQL specific commands either interactively or in batch oriented script files via the USE command.
-The OdxSQL Commands and Omnidex SQL Statements are organized for quick review as follows:
+The following is a list of Omnidex SQL Statements and OdxSQL commands organized by type with a complete alphabetic list shown below.
-  * [[#Connecting and Disconnecting ]]
-  * [[#Omnidex SQL Data Definition Language (DDL) Statements ]]
-  * [[#Omnidex SQL Data Manipulation Language (DML) Statements ]]
-  * [[#Omnidex Environment Operation Commands ]]
-  * [[#OdxSQl and Omnidex SQL Data Export and Manipulation ]]
-  * [[#OdxSQL Program Operation Commands ]]
-  * [[#OdxSQL Testing and Performance Commands ]]
-  * [[#OdxSQL Operating System Commands ]]
-  * [[#Omnidex Index Direct Maintenance Routines ]]
-  * [[#Alphabetical List of OdxSQL Commands and Omnidex SQL Statements ]]
-OdxSQL Commands and Omnidex SQL Statements can be entered into OdxSQL in ether upper or lower case.
-  > connect myenv
-  > CONNECT myenv
-The syntax for Omnidex SQL Statements entered into OdxSQL or processed in a script file is typically the exact same syntax and returns the exact same results.
-There are a few exceptions such as CONNECT and VERSION.
-In OdxSQL, CONNECT has two additional options of ON host:port and USING api that are not available in the Omnidex SQL CONNECT Statement.  The api is not necessary has it is already known depending on the application type (ODBC or JDBC) and ON
-In OdxSQL, the VERSION command displays the OdxSQL header and multiple lines of version information where the Omnidex SQL VERSION statement returns a single line.
-The OdxSQL EXECSQL command can be used to verify if an Omnidex SQL Statement is processed exactly the same by OdxSQL as the Omnidex SQL parser.
-<code>
-> VERSION
-ODXSQL - Omnidex SQL                                   Wed Dec 02 09:20:41 200
-...
-Omnidex Version                  5.0 Build 2B
-Compilation Timestamp            Oct 13 2009  08:57:09
-Earliest Compatible ODBC Client  4.3 Build 4B
-Earliest Compatible JDBC Client  4.3 Build 4B
-Earliest Compatible Odx Kernel   5.0 Build 1B
-> EXECSQL VERSION
-OmniAccess version 50002
->
-</code>
 ===== Connecting and Disconnecting =====
@@ Line 56: / Line 13: @@
 OdxSQL works by first connecting to an Omnidex Environment or an ODBC or JDBC DataSource (DSN) that references an Omnidex Environment on a remote server.  Although ODBC and JDBC applications use a connection call or object, OdxSQL uses Connect and Disconnect commands.
-| [[programs:odxsql:commands:connect|CONNECT]] | Both | Connect to an Omnidex Environment or ODBC/JDBC Datasource File (DSN). |
+^ Statement ^  Description  ^
-| [[programs:odxsql:commands:disconnect|DISCONNECT]] | Statement |Disconnect from a previously Connected Omnidex Environment or ODBC or JDBC Datasource (DSN). |
+| [[programs:odxsql:commands:connect|CONNECT]] | Connect to an Omnidex Environment or ODBC/JDBC Datasource File (DSN). |
-| [[programs:odxsql:commands:attach|ATTACH]] | Statement | Attach an OST file as a table to the currently connected Omnidex Environment. |
+| [[programs:odxsql:commands:disconnect|DISCONNECT]] | Disconnect from a previously Connected Omnidex Environment or ODBC or JDBC Datasource (DSN). |
-| [[programs:odxsql:commands:detach|DETACH]] | Statement | Detach a previously attached Omnidex Standalone Table (OST) file. |
+| [[programs:odxsql:commands:attach|ATTACH]] | Attach an OST file as a table to the currently connected Omnidex Environment. |
+| [[programs:odxsql:commands:detach|DETACH]] | Detach a previously attached Omnidex Standalone Table (OST) file. |
 ===== Omnidex SQL Data Manipulation Language (DML) Statements =====
@@ Line 65: / Line 23: @@
 OdxSQL can process all Omnidex SQL Data Manipulation Statements (SELECT, INSERT, UPDATE, and DELETE) as well as lower level Omnidex SQL routines.
-| [[programs:odxsql:commands:select|SELECT]] | Statement | Issue an Omnidex SQL SELECT Statement. |
+^ DML Statement ^  Description  ^
-| [[programs:odxsql:commands:insert|INSERT]] | Statement | Issue an Omnidex SQL INSERT Statement. |
+| [[dev:sql:statements:select:home |SELECT]] | Issue an Omnidex SQL SELECT Statement. |
-| [[programs:odxsql:commands:update|UPDATE]] | Statement | Issue an Omnidex SQL UPDATE Statement. |
+| [[dev:sql:statements:insert:home |INSERT]] | Issue an Omnidex SQL INSERT Statement. |
-| [[programs:odxsql:commands:delete|DELETE]] | Statement | Issue the SQL DELETE Statement. |
+| [[dev:sql:statements:update:home |UPDATE]] | Issue an Omnidex SQL UPDATE Statement. |
+| [[dev:sql:statements:delete:home | DELETE]] | Issue the SQL DELETE Statement. |
 ===== Omnidex SQL Data Definition Language (DDL) Statements =====
 ^ DDL Statement ^  Description  ^
-| [[programs:odxsql:commands:create_environment|CREATE ENVIRONMENT]] | Statement |Create an Omnidex Environment. |
+| [[programs:odxsql:commands:create_environment|CREATE ENVIRONMENT]] | Create an Omnidex Environment. |
-| [[programs:odxsql:commands:create_database|CREATE DATABASE]] | Statement | Create ad Database within an Omnidex Environment. |
+| [[programs:odxsql:commands:create_database|CREATE DATABASE]] | Create ad Database within an Omnidex Environment. |
-| [[programs:odxsql:commands:create_table|CREATE TABLE]] | Statement | Create a Table. |
+| [[programs:odxsql:commands:create_table|CREATE TABLE]] | Create a Table. |
-| [[programs:odxsql:commands:create_index|CREATE INDEX]] | Statement | Create Index |
+| [[programs:odxsql:commands:create_index|CREATE INDEX]] | Create Index |
-| [[programs:odxsql:commands:create_indexes|CREATE INDEXES]] | Statement | Create Omnidex Indexes. |
+| [[programs:odxsql:commands:create_index|CREATE INDEX GROUP ]] | Create Index Group |
-| [[programs:odxsql:commands:drop_environment|DROP ENVIRONMENT]] | Statement | Drop Environment. |
+| [[programs:odxsql:commands:create_indexes|CREATE INDEXES]] | Create Omnidex Indexes. |
-| [[programs:odxsql:commands:drop_database|DROP DATABASE]] | Statement | Drop Database |
+| [[programs:odxsql:commands:drop_environment|DROP ENVIRONMENT]] | Drop Environment. |
-| [[programs:odxsql:commands:drop_table|DROP TABLE]] | Statement | Drop a previously created temporary  table. |
+| [[programs:odxsql:commands:drop_database|DROP DATABASE]] | Drop Database |
-| [[programs:odxsql:commands:drop_index|DROP INDEX]] | Statement | Drop Index. |
+| [[programs:odxsql:commands:drop_table|DROP TABLE]] | Drop a previously created temporary  table. |
-| [[programs:odxsql:commands:drop_indexes|DROP INDEXES]] | Statement | Drop Indexes. |
+| [[programs:odxsql:commands:drop_index|DROP INDEX]] | Drop Index. |
+| [[programs:odxsql:commands:drop_indexes|DROP INDEXES]] | Drop Indexes. |
+| [[programs:odxsql:commands:show_ddl|SHOW DDL]] | Show Data Definition Statements. |
-===== Additional Omnidex SQL  Statements =====
+===== Additional Omnidex SQL DML Statements =====
-| [[programs:odxsql:commands:join|JOIN]] | Statement | Join tables using Omnidex Indexes. |
+^ DML Statement ^  Description  ^
-| [[programs:odxsql:commands:qualify|QUALIFY]] | Statement | Qualify rows using Omnidex criteria. |
+| [[programs:odxsql:commands:join|JOIN]] | Join tables using Omnidex Indexes. |
-| [[programs:odxsql:commands:fetch|FETCH]] | Statement | Explicitly fetch data from the database. |
+| [[programs:odxsql:commands:qualify|QUALIFY]] | Qualify rows using Omnidex criteria. |
-| [[programs:odxsql:commands:fetchkeys|FETCHKEYS]] | Statement | Explicitly fetch keys from the Omnidex Indexes. |
+| [[programs:odxsql:commands:fetch|FETCH]] | Explicitly fetch data from the database. |
-| [[programs:odxsql:commands:open_cursor|OPEN CURSOR]] | Statement | Open a Cursor. |
+| [[programs:odxsql:commands:fetchkeys|FETCHKEYS]] | Explicitly fetch keys from the Omnidex Indexes. |
-| [[programs:odxsql:commands:close_cursor|CLOSE CURSOR]] | Statement | Close a previously opened cursor. |
+| [[programs:odxsql:commands:open_cursor|OPEN CURSOR]] | Open a Cursor. |
-===== OdxSQL Environment Operation Commands and Statements =====
+| [[programs:odxsql:commands:close_cursor|CLOSE CURSOR]] | Close a previously opened cursor. |
+===== Omnidex SQL Operational Statements =====
 OdxSQL has commands that operate and update information for an Omnidex Environment and the individual databases contained in the Omnidex Environment.
-| [[programs:odxsql:commands:show|SHOW]] | Command | Show Omnidex Environment information including Databases, Tables, Column layouts, Query Statistics, etc. |
+^ Statement ^  Description  ^
-| [[programs:odxsql:commands:validate|VALIDATE]] | Command | Validate the Omnidex Environment's view of the underlying data. |
+| [[programs:odxsql:commands:partition|PARTITION]] | Partition a table into smaller sub tables based on criteria. |
-| [[programs:odxsql:commands:partition|PARTITION]] | Statement | Partition a table into smaller sub tables based on criteria. |
+| [[programs:odxsql:commands:update_rollup|UPDATE ROLLUP]] | Update Rollup Table with summarized information. |
-| [[programs:odxsql:commands:update_rollup|UPDATE ROLLUP]] | Statement | Update Rollup Table with summarized information. |
+| [[programs:odxsql:commands:update_rollups|UPDATE ROLLUPS]] | Update all Rollup Tables for an Omnidex Database. |
-| [[programs:odxsql:commands:update_rollups|UPDATE ROLLUPS]] | Statement | Update all Rollup Tables for an Omnidex Database. |
+| [[programs:odxsql:commands:update_statistics|UPDATE STATISTICS]] | Update Statistics Information. |
-| [[programs:odxsql:commands:update_statistics|UPDATE STATISTICS]] | Statement | Update Statistics Information. |
+| [[programs:odxsql:commands:update_text|UPDATE TEXT]] | Update Textual Statistics. |
-| [[programs:odxsql:commands:update_text|UPDATE TEXT]] | Statement | Update Textual Statistics. |
+| [[programs:odxsql:commands:load ofx|LOAD OFX]] | Load Indexes for a table. |
-| [[programs:odxsql:commands:load ofx|LOAD OFX]] | Statement | Load Indexes for a table. |
+| [[dev:sql:statements:sql | SET ]] | Set Omnidex SQL Conditions. |
+===== OdxSQL Environment Related Commands =====
-===== OdxSQL and Omnidex SQL Data Export and Manipulation =====
 OdxSQL has commands that can be used to export and partition data.  These can be used to easily pull data from RDBMS data stores into Omnidex Snapshots.
-| [[programs:odxsql:commands:partition|PARTITION]] | Statement | Partition a table into smaller sub tables based on criteria. |
+^ Command ^  Description  ^
-| [[programs:odxsql:commands:convert|CONVERT]] | Command | Export a file with data conversion. |
+| [[programs:odxsql:commands:convert|CONVERT]] | Export a file with data conversion. |
-| [[programs:odxsql:commands:export|EXPORT]] | Statement | Export rows to a file. |
+| [[programs:odxsql:commands:extract|EXTRACT]] | Extracts SQL Statements from a table and writes them to a file. Removed in 5.1 |
-| [[programs:odxsql:commands:extract|EXTRACT]] | Command | Extracts SQL Statements from a table and writes them to a file. |
+| [[programs:odxsql:commands:show|SHOW]] | Show Omnidex Environment information including Databases, Tables, Column layouts, Query Statistics, etc. |
+| [[programs:odxsql:commands:validate|VALIDATE]] | Validate the Omnidex Environment's view of the underlying data. Removed in 5.1 |
 ===== OdxSQL Program Operation Commands =====
 OdxSQL has several commands that facilitate the processing, editing, re-issue of previous commands and processing multiple command blocks.
+^ Command ^  Description  ^
 | [[programs:odxsql:commands:use|USE]] | Read and process a file of OdxSQL Commands and Omnidex SQL Statements, optionally with WHERE criteria. |
 | [[programs:odxsql:commands:period|Redo Command ( . )]] | Execute the last command. |
@@ Line 131: / Line 93: @@
 | [[programs:odxsql:commands:help|HELP]] | Show a list of commands help on individual commands, COMMANDS, SYNTAX and API. |
 | [[programs:odxsql:commands:is|IS]] | Check Table Conditions. |
-| [[programs:odxsql:commands:eval|EVAL]] | Evaluate a condition. |
 | [[programs:odxsql:commands:date|DATE]] | Show the current Date and Time. |
 | [[programs:odxsql:commands:calc|CALC]] |Perform basic calculator functions. |
@@ Line 142: / Line 103: @@
 OdxSQL contains several commands that facilitate the testing and optimization of an Omnidex Environment.
+^ Command ^  Description  ^
 | [[programs:odxsql:commands:explain|EXPLAIN]] | Explain the optimization approach to a previous retrieval. |
-| [[programs:odxsql:commands:validate|VALIDATE]] | Validate the Omnidex Environment's view of the underlying data. |
 | [[programs:odxsql:commands:reset_timer|RESET TIMER]] | Reset the OdxSQL Cummulative Timer. |
 |[[programs:odxsql:commands:benchmark|BENCHMARK]] | Execute Benchmarks to analyze performance. |
-| [[programs:odxsql:commands:extract|EXTRACT]] | Extracts SQL Statements from a table and writes them to a file. |
 | [[programs:odxsql:commands:format|FORMAT]] | Format a file of Omnidex SQL Statements. |
 | [[programs:odxsql:commands:error|ERROR]] | Display the text of an error message from the Omnidex Error Message file. |
-| [[programs:odxsql:commands:execsql|EXECSQL]] | Execute an Omnidex SQL Statement directly with the oaexecsql routine without OdxSQL doing any processing.
+| [[programs:odxsql:commands:execsql|EXECSQL]] | Execute an Omnidex SQL Statement directly with the oaexecsql routine without OdxSQL doing any processing. |
-| [[programs:odxsql:commands:sleep|SLEEP]] | Sleep for a specified number of seconds. |
 ===== OdxSQL Operating System Commands =====
@@ Line 156: / Line 115: @@
 OdxSQL contains several commands that access the underlying operating to facilitate the Omnidex Administrator or Developer staying inside of OdxSQL for many tasks.
+^ Command ^  Description  ^
 | [[programs:odxsql:commands:exclamationpoint|! <shell command> ]] |Run an OS command in the OS Shell and return or just an ( ! ) for an interactive shell. |
 | [[programs:odxsql:commands:command|COMMAND]] | Execute a shell command via the $COMMAND table. |
@@ Line 168: / Line 128: @@
 | [[programs:odxsql:commands:rename|RENAME]] | Rename a file or fileset in the underlying Operating System. |
 | [[programs:odxsql:commands:view |VIEW]] | View a text file. |
-| [[programs:odxsql:commands:setenv|SETENV ]] | Set an Operating System Environment Variable. |
-| [[programs:odxsql:commands:getenv|GETENV]] | Display an Operating System Environment Variable. |
-| [[programs:odxsql:commands:abort|ABORT]] | Issues an Operating System Abort - used to test abnormal conditions. |
-===== Omnidex Index Direct Maintenance Routines =====
-| [[programs:odxsql:commands:insertindex|INSERTINDEX]] | Insert values directly into the Omnidex Indexes. |
-| [[programs:odxsql:commands:updateindex|UPDATEINDEX]] | Update values directly in the Omnidex Indexes. |
-| [[programs:odxsql:commands:deleteindex|DELETEINDEX]] | Delete values directly from the Omnidex Indexes. |
 ===== Alphabetical List of OdxSQL Commands and Omnidex SQL Statements =====
@@ Line 222: / Line 173: @@
 | [[programs:odxsql:commands:for|FOR]] | Command | Execute the next one or more commands the specified number of times. |
 | [[programs:odxsql:commands:format|FORMAT]] | Command | Format a file of Omnidex SQL Statements. |
-| [[programs:odxsql:commands:getenv|GETENV]] | Command | Display an Operating System Environment Variable. To be removed in 5.1 |
 | [[programs:odxsql:commands:help|HELP]] | Command | Help on COMMANDS, SYNTAX and API. |
 | [[programs:odxsql:commands:history|HISTORY]] | Command | Show the history of OdxSQL Commands and Statements. |
@@ Line 243: / Line 193: @@
 | [[programs:odxsql:commands:select|SELECT]] |Statement | Issue an Omnidex SQL SELECT Statement. |
 | [[programs:odxsql:commands:set|SET]] | Both | Set an OdxSQL setting. |
-| [[programs:odxsql:commands:setenv|SETENV ]] | Command |Set an Operating System Environment Variable. Removed in 5.1|
 | [[programs:odxsql:commands:show|SHOW]] | Command |Show Omnidex Environment information including Databases, Tables, Column layouts, Query Statistics, etc. |
+| [[programs:odxsql:commands:show_ddl|SHOW DDL]] | Show Data Definition Statements. |
 | [[programs:odxsql:commands:signal|SIGNAL]] | Command | Sends a Signal to a process. |
-| [[programs:odxsql:commands:sleep|SLEEP]] | Command | Sleep for a specified number of seconds. |
 | [[programs:odxsql:commands:update|UPDATE]] | Statement | Issue an Omnidex SQL UPDATE Statement. |
 | [[programs:odxsql:commands:update_indexes|UPDATE INDEXES]] |Statement | Update Omnidex Indexes |
@@ Line 255: / Line 204: @@
 | [[programs:odxsql:commands:use|USE]] | Command | Read and process a file of OdxSQL Commands and Omnidex SQL Statements, optionally with WHERE criteria. |
 | [[programs:odxsql:commands:validate|VALIDATE]] | Command | Validate the Omnidex Environment's view of the underlying data. To be removed in 5.1 |
-| [[programs:odxsql:commands:version|VERSION]] | Both | Display current Omnidex Software versions. |
+| [[programs:odxsql:commands:version|VERSION]] | Command | Display current Omnidex Software versions. |
 | [[programs:odxsql:commands:view | VIEW]] | Command | View a text file. |

Back to top

programs/odxsql/commands/home.1260237599.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes