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 | ||
|
dev:sql:statements:home [2010/06/14 16:35] tdo |
dev:sql:statements:home [2010/07/08 22:23] tdo |
||
|---|---|---|---|
| Line 4: | Line 4: | ||
| ====== Omnidex SQL Reference ====== | ====== Omnidex SQL Reference ====== | ||
| {{page>:sql_bar&nofooter&noeditbtn}} | {{page>:sql_bar&nofooter&noeditbtn}} | ||
| - | The following Omnidex SQL Statements can be used by OdxSQL or an ODBC or JDBC application. | + | ===== Omnidex SQL Statements (alphabetical order) ===== |
| + | |||
| + | ^Statement^ Description ^ | ||
| + | | [[dev:sql:statements:attach:home|ATTACH]] | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment for queries and manipulation. | | ||
| + | | [[dev:sql:statements:close_cursor:home|CLOSE CURSOR]] | Close a previously opened cursor. | | ||
| + | | [[dev:sql:statements:command:home| COMMAND]] | Execute an Operating System command. | | ||
| + | | [[dev:sql:statements:connect:home|CONNECT]] | Connect to an Omnidex Environment. | | ||
| + | | [[dev:sql:statements:create_database:home|CREATE DATABASE]] | Create a Database within an Omnidex Environment. | | ||
| + | | [[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]] | Create an Omnidex Environment. | | ||
| + | | [[dev:sql:statements:create_index:home|CREATE INDEX]] | Create an Index. | | ||
| + | | [[dev:sql:statements:create_index_group:home|CREATE INDEX GROUP]] | Create an Omnidex Index Group. | | ||
| + | | [[dev:sql:statements:create_table:home|CREATE TABLE]] | Create a table within an Omnidex Environment or create a temp table in the underlying RDBMS. | | ||
| + | | [[dev:sql:statements:delete:home|DELETE]] | Delete a row or rows of data. | | ||
| + | | [[dev:sql:statements:detach:home|DETACH]] | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. | | ||
| + | | [[dev:sql:statements:disconnect:home|DISCONNECT]] | Disconnect from a connected Omnidex Environment. | | ||
| + | | [[dev:sql:statements:drop_database:home|DROP DATABASE]] | Drop a Database from within an Omnidex Environment. | | ||
| + | | [[dev:sql:statements:drop_environment:home|DROP ENVIRONMENT]] | Drop an Omnidex XML Environment and purge the underlying operating system file. | | ||
| + | | [[dev:sql:statements:drop_index:home|DROP INDEX]] | Drop an Omnidex Index. | | ||
| + | | [[dev:sql:statements:drop_index_group:home|DROP INDEX GROUP]] | Drop an Omnidex Index Group. | | ||
| + | | [[dev:sql:statements:drop_table:home|DROP TABLE]] | Drop a table created with a CREATE TABLE or SELECT INTO statement. | | ||
| + | | [[dev:sql:statements:export:home|EXPORT]] | Export rows or rowids to an external file. | | ||
| + | | [[dev:sql:statements:insert:home|INSERT]] | Insert a row of data. | | ||
| + | | [[dev:sql:statements:install_indexes:home | INSTALL INDEXES ]]| Specify the Omnidex indexes but do not synchronize the indexes to the underlying data.| | ||
| + | | [[dev:sql:statements:join:home|JOIN]] | Manually join two tables for a joined query | | ||
| + | | [[dev:sql:statements:load_ofx:home|LOAD OFX]] | Load an OFX file. | | ||
| + | | [[dev:sql:statements:open_cursor:home|OPEN CURSOR]] | Open a cursor. | | ||
| + | | [[dev:sql:statements:partition:home|PARTITION]] | Partition Data based on criteria. | | ||
| + | | [[dev:sql:statements:qualify:home|QUALIFY]] | Qualify rows in a query. Typically superseded by SELECT| | ||
| + | | [[dev:sql:statements:remove_indexes:home | REMOVE INDEXES ]] | Remove the Omnidex Indexes from the the Omnidex Envrionment.| | ||
| + | | [[dev:sql:statements:select:home|SELECT]] | Select rows , aggregate rows, using Omnidex enhanced criteria. | | ||
| + | | [[dev:sql:statements:set:home|SET]] | Set Omnidex SQL processing options. | | ||
| + | | [[dev:sql:statements:update:home|UPDATE]] | Update rows in a table. | | ||
| + | | [[dev:sql:statements:update_indexes:home|UPDATE INDEXES]] | Update all Omnidex Indexes. | | ||
| + | | [[dev:sql:statements:update_rollup_table:home|UPDATE ROLLUP TABLE]] | Update Rollup Tables with new calculations. | | ||
| + | | [[dev:sql:statements:update_rollups:home|UPDATE ROLLUPS]] | Update all Rollup Tables with new calculations. | | ||
| + | | [[dev:sql:statements:update_statistics:home|UPDATE STATISTICS]] | Update the Omnidex Statistics information. | | ||
| + | | [[dev:sql:statements:update_text:home|UPDATE TEXT]] | Update Omnidex Text information. | | ||
| + | |||
| ===== Omnidex SQL SELECT and Data Manipulation Statements ===== | ===== Omnidex SQL SELECT and Data Manipulation Statements ===== | ||
| Line 14: | Line 52: | ||
| | [[dev:sql:statements:delete:home|DELETE]] | Delete rows in a Table. | | | [[dev:sql:statements:delete:home|DELETE]] | Delete rows in a Table. | | ||
| ===== Omnidex Connection Statements ===== | ===== Omnidex Connection Statements ===== | ||
| - | Omnidex supports statements to establish connections and set processing options for the connection. | + | Omnidex supports statements to establish connections and set processing options for the connection as well as attach and detach individual files to an Omnidex Environment. |
| | [[dev:sql:statements:connect:home|CONNECT]] | Connect to an Omnidex Environment. | | | [[dev:sql:statements:connect:home|CONNECT]] | Connect to an Omnidex Environment. | | ||
| | [[dev:sql:statements:disconnect:home|DISCONNECT]] | Disconnect from a connected Omnidex Environment. | | | [[dev:sql:statements:disconnect:home|DISCONNECT]] | Disconnect from a connected Omnidex Environment. | | ||
| | [[dev:sql:statements:set:home|SET]] | Set Omnidex SQL processing options. | | | [[dev:sql:statements:set:home|SET]] | Set Omnidex SQL processing options. | | ||
| + | | [[dev:sql:statements:attach:home|ATTACH]] | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment for queries and manipulation. | | ||
| + | | [[dev:sql:statements:detach:home|DETACH]] | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. | | ||
| + | |||
| ===== Omnidex SQL Data Definition Language (DDL) Statements ===== | ===== Omnidex SQL Data Definition Language (DDL) Statements ===== | ||
| Omnidex SQL has statements that create and drop objects within an Omnidex Environment. | Omnidex SQL has statements that create and drop objects within an Omnidex Environment. | ||
| + | |||
| + | Also see the OdxSQL Extract command to generate DDL statements from an existing Omnidex Environment. | ||
| ^ DDL Statement ^ Description ^ | ^ DDL Statement ^ Description ^ | ||
| - | | [[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]] | Create an Omnidex Environment. | | + | | [[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]] | Create an Omnidex Environment into a named physical operating system file. | |
| | [[dev:sql:statements:create_database:home|CREATE DATABASE]] | Create a Database within an Omnidex Environment. | | | [[dev:sql:statements:create_database:home|CREATE DATABASE]] | Create a Database within an Omnidex Environment. | | ||
| - | | [[dev:sql:statements:create_table:home|CREATE TABLE]] | Create a Table in the Omnidex metadata or an Omnidex or RDBMS temporary table. | | + | | [[dev:sql:statements:create_table:home|CREATE TABLE]] | Create a Table within a Database in the Omnidex metadata or an Omnidex or RDBMS temporary table. | |
| | [[dev:sql:statements:create_index:home|CREATE INDEX]] | Specify an Index in the Omnidex metadata but will not physically create the index. | | | [[dev:sql:statements:create_index:home|CREATE INDEX]] | Specify an Index in the Omnidex metadata but will not physically create the index. | | ||
| - | | [[dev:sql:statements:create_index_group:home | CREATE INDEX GROUP ]] | Create an Index Group. | | + | | [[dev:sql:statements:create_index_group:home | CREATE INDEX GROUP ]] | Specify an Index Group within the Omnidex metadata. | |
| - | | [[dev:sql:statements:drop_environment:home|DROP ENVIRONMENT]] | Drop Environment. | | + | | [[dev:sql:statements:drop_environment:home|DROP ENVIRONMENT]] | Deletes the Omnidex Metadata Environment by physically deleting the operating system file. | |
| - | | [[dev:sql:statements:drop_database:home|DROP DATABASE]] | Drop Database | | + | | [[dev:sql:statements:drop_database:home|DROP DATABASE]] | Remove the named Database from the Omnidex metadata. | |
| - | | [[dev:sql:statements:drop_table:home|DROP TABLE]] | Drop a previously created temporary table. | | + | | [[dev:sql:statements:drop_table:home|DROP TABLE]] | Remove a table from the Omnidex metadata or deletes a previously created temporary table. | |
| - | | [[dev:sql:statements:drop_index:home|DROP INDEX]] | Drop Index. | | + | | [[dev:sql:statements:drop_index:home|DROP INDEX]] | Removes an index from the Omnidex metadata. | |
| - | | [[dev:sql:statements:drop_index_group | DROP INDEX GROUP]] | Drop Index Group. | | + | | [[dev:sql:statements:drop_index_group | DROP INDEX GROUP]] | Removes a named Index Group from the Omnidex metadata. | |
| | [[dev:sql:statements:attach:home|ATTACH]] | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment for queries and manipulation. | | | [[dev:sql:statements:attach:home|ATTACH]] | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment for queries and manipulation. | | ||
| | [[dev:sql:statements:detach:home|DETACH]] | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. | | | [[dev:sql:statements:detach:home|DETACH]] | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. | | ||
| - | | [[dev:sql:statements:extract:home | EXTRACT]] | Extract DDL statements | | + | |
| ===== Omnidex SQL Query and Export Statements ===== | ===== Omnidex SQL Query and Export Statements ===== | ||
| Line 42: | Line 85: | ||
| | [[dev:sql:statements:open_cursor:home |OPEN CURSOR]] | Open a Cursor. | | | [[dev:sql:statements:open_cursor:home |OPEN CURSOR]] | Open a Cursor. | | ||
| | [[dev:sql:statements:close_cursor:home|CLOSE CURSOR]] | Close a previously opened cursor. | | | [[dev:sql:statements:close_cursor:home|CLOSE CURSOR]] | Close a previously opened cursor. | | ||
| - | + | ===== Omnidex SQL Index and Processing Maintenance Statements ===== | |
| - | ===== Omnidex SQL Index Query Statements ===== | + | Omnidex supports several statements to synchronize the Omnidex indexes, rollup tables and internal statistics. |
| - | | [[dev:sql:statements:join:home|JOIN]] | Join tables using Omnidex Indexes. | | + | |
| - | | [[dev:sql:statements:qualify:home|QUALIFY]] | Qualify rows using Omnidex criteria. | | + | |
| - | ===== Omnidex SQL Maintenance Statements ===== | + | |
| | [[dev:sql:statements:update_indexes:home|UPDATE INDEXES]] | Update Omnidex Indexes. | | | [[dev:sql:statements:update_indexes:home|UPDATE INDEXES]] | Update Omnidex Indexes. | | ||
| Line 55: | Line 95: | ||
| | [[dev:sql:statements:partition:home|PARTITION]] | Partition a table into smaller sub tables based on criteria. | | | [[dev:sql:statements:partition:home|PARTITION]] | Partition a table into smaller sub tables based on criteria. | | ||
| | [[dev:sql:statements:load_ofx:home|LOAD OFX]] | Load Indexes for a table. | | | [[dev:sql:statements:load_ofx:home|LOAD OFX]] | Load Indexes for a table. | | ||
| + | | Install Indexes | Specifies the Omnidex indexes into the Omnidex XML Environment. No indexing is performed. | | ||
| + | | Remove Indexes | Removes the Omnidex indexes from the Omnidex XML Environment. | | ||
| + | ===== Omnidex SQL Index Query Statements ===== | ||
| + | Omnidex supports lower level QUALIFY and JOIN statements to query the Omnidex indexes directly. These statements are advanced statements used for testing and to support legacy applications. The SELECT statement is now typically used instead. | ||
| + | |||
| + | | [[dev:sql:statements:qualify:home|QUALIFY]] | Qualify index counts using Omnidex criteria. | | ||
| + | | [[dev:sql:statements:join:home|JOIN]] | Join tables using Omnidex Indexes. | | ||
| - | ===== Alphabetical List of Omnidex SQL Statements ===== | ||
| - | ^Statement^ Description ^ | ||
| - | | [[dev:sql:statements:attach:home|ATTACH]] | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment for queries and manipulation. | | ||
| - | | [[dev:sql:statements:close_cursor:home|CLOSE CURSOR]] | Close a previously opened cursor. | | ||
| - | | [[dev:sql:statements:command:home| COMMAND]] | Execute an Operating System command. | | ||
| - | | [[dev:sql:statements:connect:home|CONNECT]] | Connect to an Omnidex Environment. | | ||
| - | | [[dev:sql:statements:create_database:home|CREATE DATABASE]] | Create a Database within an Omnidex Environment. | | ||
| - | | [[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]] | Create an Omnidex Environment. | | ||
| - | | [[dev:sql:statements:create_index:home|CREATE INDEX]] | Create an Index. | | ||
| - | | [[dev:sql:statements:create_table:home|CREATE TABLE]] | Create a table within an Omnidex Environment or create a temp table in the underlying RDBMS. | | ||
| - | | [[dev:sql:statements:delete:home|DELETE]] | Delete a row or rows of data. | | ||
| - | | [[dev:sql:statements:detach:home|DETACH]] | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. | | ||
| - | | [[dev:sql:statements:disconnect:home|DISCONNECT]] | Disconnect from a connected Omnidex Environment. | | ||
| - | | [[dev:sql:statements:drop_database:home|DROP DATABASE]] | Drop a Database from within an Omnidex Environment. | | ||
| - | | [[dev:sql:statements:drop_environment:home|DROP ENVIRONMENT]] | Drop an Omnidex XML Environment and purge the underlying operating system file. | | ||
| - | | [[dev:sql:statements:drop_index:home|DROP INDEX]] | Drop an Omnidex Index. | | ||
| - | | [[dev:sql:statements:drop_table:home|DROP TABLE]] | Drop a table created with a CREATE TABLE or SELECT INTO statement. | | ||
| - | | [[dev:sql:statements:export:home|EXPORT]] | Export rows or rowids to an external file. | | ||
| - | | [[dev:sql:statements:insert:home|INSERT]] | Insert a row of data. | | ||
| - | | [[dev:sql:statements:join:home|JOIN]] | Manually join two tables for a joined query | | ||
| - | | [[dev:sql:statements:load_ofx:home|LOAD OFX]] | Load an OFX file. | | ||
| - | | [[dev:sql:statements:open_cursor:home|OPEN CURSOR]] | Open a cursor. | | ||
| - | | [[dev:sql:statements:partition:home|PARTITION]] | Partition Data based on criteria. | | ||
| - | | [[dev:sql:statements:qualify:home|QUALIFY]] | Qualify rows in a query. Typically superseded by SELECT| | ||
| - | | [[dev:sql:statements:select:home|SELECT]] | Select rows , aggregate rows, using Omnidex enhanced criteria. | | ||
| - | | [[dev:sql:statements:set:home|SET]] | Set Omnidex SQL processing options. | | ||
| - | | [[dev:sql:statements:update:home|UPDATE]] | Update rows in a table. | | ||
| - | | [[dev:sql:statements:update_indexes:home|UPDATE INDEXES]] | Update all Omnidex Indexes. | | ||
| - | | [[dev:sql:statements:update_rollup:home|UPDATE ROLLUP]] | Update Rollup Tables with new calculations. | | ||
| - | | [[dev:sql:statements:update_rollups:home|UPDATE ROLLUPS]] | Update all Rollup Tables with new calculations. | | ||
| - | | [[dev:sql:statements:update_statistics:home|UPDATE STATISTICS]] | Update the Omnidex Statistics information. | | ||
| - | | [[dev:sql:statements:update_text:home|UPDATE TEXT]] | Update Omnidex Text information. | | ||
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||