This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
dev:sql:overview [2009/12/03 18:49] tdo |
dev:sql:overview [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 2: | Line 2: | ||
| <html><div align="center"><span style="color:red">DRAFT</span></div></html> | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
| ====== Omnidex SQL Reference ====== | ====== Omnidex SQL Reference ====== | ||
| + | {{page>:sql_bar&nofooter&noeditbtn}} | ||
| ===== Overview ===== | ===== Overview ===== | ||
| + | |||
| + | Omnidex SQL is based on ANSI SQL, but also contains extensions that support Omnidex's extended features. | ||
| + | |||
| + | Omnidex has limited support for Oracle and SQL Server specific SQL extensions such as Oracle decode. | ||
| + | |||
| + | Omnidex SQL is available using standard ODBC and JDBC connections. | ||
| Omnidex provides a high-level SQL interface to access databases enhanced with Omnidex indexes. The SQL supported is a subset of the ANSI SQL2 and ANSI SQL99 standard along with some very powerful extensions specific to Omnidex. | Omnidex provides a high-level SQL interface to access databases enhanced with Omnidex indexes. The SQL supported is a subset of the ANSI SQL2 and ANSI SQL99 standard along with some very powerful extensions specific to Omnidex. | ||
| Line 18: | Line 25: | ||
| Omnidex SQL statements can also be tested and scripted using the [[programs:odxsql:overview|OdxSQL]] program. | Omnidex SQL statements can also be tested and scripted using the [[programs:odxsql:overview|OdxSQL]] program. | ||
| - | |||
| - | ===== Statements by Type ===== | ||
| - | ===== Omnidex SQL Data Definition Language (DDL) Statements ===== | ||
| - | |||
| - | | [[dev:sql:select|SELECT]] | Issue an Omnidex SQL SELECT Statement. | | ||
| - | | [[dev:sql:insert|INSERT]] | Issue an Omnidex SQL INSERT Statement. | | ||
| - | | [[dev:sql:update|UPDATE]] | Issue an Omnidex SQL UPDATE Statement. | | ||
| - | | [[dev:sql:delete|DELETE]] | Issue the SQL DELETE Statement. | | ||
| - | | [[programs:odxsql:commands:open_cursor|OPEN CURSOR]] | Open a Cursor. | | ||
| - | | [[dev:sql:assignvalues | ASSIGNVALUES]] | Assign values for a $VALUES clause to be used in a subsequent SELECT. | | ||
| - | | [[programs:odxsql:commands:join|JOIN]] | Join tables using Omnidex Indexes. | | ||
| - | | [[programs:odxsql:commands:qualify|QUALIFY]] | Qualify rows using Omnidex criteria. | | ||
| - | | [[programs:odxsql:commands:describe|DESCRIBE]] | Describe a SELECT Statement. | | ||
| - | | [[programs:odxsql:commands:bind|BIND]] | Bind a SELECT Statement. | | ||
| - | | [[programs:odxsql:commands:fetch|FETCH]] | Explicitly fetch data from the database. | | ||
| - | | [[programs:odxsql:commands:fetchkeys|FETCHKEYS]] | Explicitly fetch keys from the Omnidex Indexes. | | ||
| - | | [[programs:odxsql:commands:close_cursor|CLOSE CURSOR]] | Close a previously opened cursor. | | ||
| - | ===== Omnidex SQL Data Definition Language (DDL) Statements ===== | ||
| - | ^ DDL Statement ^ Description ^ | ||
| - | | [[dev:sql:create_environment|CREATE ENVIRONMENT]] | Create an Omnidex Environment. | | ||
| - | | [[dev:sql:create_database|CREATE DATABASE]] | Create ad Database within an Omnidex Environment. | | ||
| - | | [[dev:sql:create_table|CREATE TABLE]] | Create a Table. | | ||
| - | | [[dev:sql:create_index|CREATE INDEX]] | Create Index | | ||
| - | | [[dev:sql:create_indexes|CREATE INDEXES]] | Create Omnidex Indexes. | | ||
| - | | [[dev:sql:drop_environment|DROP ENVIRONMENT]] | Drop Environment. | | ||
| - | | [[dev:sql:drop_database|DROP DATABASE]] | Drop Database | | ||
| - | | [[dev:sql:drop_table|DROP TABLE]] | Drop a previously created temporary table. | | ||
| - | | [[dev:sql:drop_index|DROP INDEX]] | Drop Index. | | ||
| - | | [[dev:sql:drop_indexes|DROP INDEXES]] | Drop Indexes. | | ||
| - | ===== Omnidex SQL 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. | | ||
| - | ===== Omnidex SQL Miscellaneous Statements ===== | ||
| - | | [[dev:sql:partition|PARTITION]] | Partition a table into smaller sub tables based on criteria. | | ||
| - | | [[dev:sql:update_rollup|UPDATE ROLLUP]] | Update Rollup Table with summarized information. | | ||
| - | | [[dev:sql:update_rollups|UPDATE ROLLUPS]] | Update all Rollup Tables for an Omnidex Database. | | ||
| - | | [[dev:sql:update_statistics|UPDATE STATISTICS]] | Update Statistics Information. | | ||
| - | | [[dev:sql:update_text|UPDATE TEXT]] | Update Textual Statistics. | | ||
| - | | [[dev:sql:load ofx|LOAD OFX]] | Load Indexes for a table. | | ||
| - | | [[dev:sql:version|VERSION]] | Display current Omnidex Software versions. | | ||
| - | ===== Alphabetical List Omnidex SQL Statements ===== | ||
| - | |||
| - | ^Statement^ Description ^ | ||
| - | | [[dev:sql:assignvalues|ASSIGNVALUES]] | Use an undetermined set of values as criteria in a query. | | ||
| - | | [[dev:sql:attach|ATTACH]] | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment for queries and manipulation. | | ||
| - | | [[dev:sql:close_cursor|CLOSE CURSOR]] | Close a previously opened cursor. | | ||
| - | | [[dev:sql:command|OS Command]] | Execute an Operating System command. | | ||
| - | | [[dev:sql:connect|CONNECT]] | Connect to an Omnidex Environment. | | ||
| - | | [[dev:sql:create_database|CREATE DATABASE]] | Create a Database within an Omnidex Environment. | | ||
| - | | [[dev:sql:create_directory|CREATE DIRECTORY]] | Create a Directory in the underlying operating system. | | ||
| - | | [[dev:sql:create_environment|CREATE ENVIRONMENT]] | Create an Omnidex Environment. | | ||
| - | | [[dev:sql:create_file|CREATE FILE]] | Create a file in the underlying operating system. | | ||
| - | | [[dev:sql:create_index|CREATE INDEX]] | Create an Index. | | ||
| - | | [[dev:sql:create_indexes|CREATE INDEXES]] | Create all Omnidex Indexes | | ||
| - | | [[dev:sql:create_table|CREATE TABLE]] | Create a table within an Omnidex Environment or create a temp table in the underlying RDBMS. | | ||
| - | | [[dev:sql:delete|DELETE]] | Delete a row or rows of data. | | ||
| - | | [[dev:sql:deleteindex|DELETEINDEX]] | Delete an Omnidex Index value. | | ||
| - | | [[dev:sql:detach|DETACH]] | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. | | ||
| - | | [[dev:sql:disconnect|DISCONNECT]] | Disconnect from a connected Omnidex Environment. | | ||
| - | | [[dev:sql:drop_database|DROP DATABASE]] | Drop a Database from within an Omnidex Environment. | | ||
| - | | [[dev:sql:drop_environment|DROP ENVIRONMENT]] | Drop an Omnidex XML Environment and purge the underlying operating system file. | | ||
| - | | [[dev:sql:drop_index|DROP INDEX]] | Drop an Omnidex Index. | | ||
| - | | [[dev:sql:drop_indexes|DROP INDEXES]] | Drop all Omnidex Indexes. | | ||
| - | | [[dev:sql:drop_table|DROP TABLE]] | Drop a table created with a CREATE TABLE or SELECT INTO statement. | | ||
| - | | [[dev:sql:export|EXPORT]] | Export rows or rowids to an external file. | | ||
| - | | [[dev:sql:helper|HELPER]] | Access OAHELPER functions. | | ||
| - | | [[dev:sql:insert|INSERT]] | Insert a row of data. | | ||
| - | | [[dev:sql:insertindex|INSERTINDEX]] | Insert values directly into the Omnidex indexes. | | ||
| - | | [[dev:sql:join|JOIN]] | Manually join two tables for a joined query | | ||
| - | | [[dev:sql:load_ofx|LOAD OFX]] | Load an OFX file. | | ||
| - | | [[dev:sql:open_cursor|OPEN CURSOR]] | Open a cursor. | | ||
| - | | [[dev:sql:partition|PARTITION]] | Partition Data based on criteria. | | ||
| - | | [[dev:sql:qualify|QUALIFY]] | Qualify rows in a query. | | ||
| - | | [[dev:sql:select|SELECT]] | Select rows , aggregate rows, using Omnidex enhanced criteria. | | ||
| - | | [[dev:sql:set|SET]] | Set Omnidex SQL processing options. | | ||
| - | | [[dev:sql:update|UPDATE]] | Update rows in a table. | | ||
| - | | [[dev:sql:update_indexes|UPDATE INDEXES]] | Update all Omnidex Indexes. | | ||
| - | | [[dev:sql:update_rollup|UPDATE ROLLUP]] | Update Rollup Tables with new calculations. | | ||
| - | | [[dev:sql:update_rollups|UPDATE ROLLUPS]] | Update all Rollup Tables with new calculations. | | ||
| - | | [[dev:sql:update_statistics|UPDATE STATISTICS]] | Update the Omnidex Statistics information. | | ||
| - | | [[dev:sql:update_text|UPDATE TEXT]] | Update Omnidex Text information. | | ||
| - | | [[dev:sql:updateindex|UPDATEINDEX]] | Update Omnidex Index values directly. | | ||
| - | | [[dev:sql:version|VERSION]] | Retrieve the Omnidex Version from the currently running library. | | ||
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||