This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
dev:sql:statements:home [2009/12/03 18:54] 127.0.0.1 external edit |
dev:sql:statements:home [2012/10/26 14:28] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| + | ~~NOTOC~~ | ||
| <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 ====== | ||
| - | ===== Overview ===== | + | {{page>:sql_bar&nofooter&noeditbtn}} |
| + | ===== Omnidex SQL Data Definition Language (DDL) Statements ===== | ||
| - | 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 SQL supports SQL's main Data Manipulation Statements (SELECT, INSERT, UPDATE, and DELETE) as well as several lower level Omnidex SQL routines. |
| - | SQL should be used for all Omnidex application development including ODBC applications, Java applications using the Omnidex JDBC driver, and OmniAccess applications through oaexecsql. The Query Language extensions work in conjunction with the SQL standard syntax, in all types of applications. | + | | [[dev:sql:statements:select:home|SELECT]] | Peform a Query Specification and SELECT rows. | |
| - | + | | [[dev:sql:statements:insert:home|INSERT]] | Insert rows into a Table. | | |
| - | Third party ODBC tools such as Microstratey, Cognos Impromptu, and Microsoft's Query, Access, Excel, and Word communicate with Omnidex via the Omnidex ODBC driver. | + | | [[dev:sql:statements:update:home|UPDATE]] | Update rows in a Table. | |
| - | Development tools such as Microsoft's Interdev, Visual Basic, and Visual C++ can communicate with Omnidex via the OmniAccess API or the Omnidex ODBC driver using object layers such as ActiveX Data Objects (ADO). | + | | [[dev:sql:statements:delete:home|DELETE]] | Delete rows in a Table. | |
| - | + | ||
| - | Java applications such as applets, servlets, and standard console applications can communicate with Omnidex via the Omnidex JDBC driver. | + | |
| - | + | ||
| - | Omnidex SQL is a rich implementation of the SQL2 and SQL99 standards along with extensions specific to Omnidex. | + | |
| - | + | ||
| - | Omnidex SQL Statements are callable via the ODBC and JDBC Interfaces. | + | |
| - | + | ||
| - | Omnidex SQL statements can also be tested and scripted using the [[programs:odxsql:overview|OdxSQL]] program. | + | |
| ===== 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. | ||
| - | | [[dev:sql:select|SELECT]] | Peform a Query Specification and SELECT rows. | | ||
| - | | [[dev:sql:insert|INSERT]] | Insert rows into a Table. | | ||
| - | | [[dev:sql:update|UPDATE]] | Update rows in a Table. | | ||
| - | | [[dev:sql:delete|DELETE]] | Delete rows in a Table. | | ||
| - | | [[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 ^ | ^ DDL Statement ^ Description ^ | ||
| - | | [[dev:sql:create_environment|CREATE ENVIRONMENT]] | Create an Omnidex Environment. | | + | | [[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]] | Create an Omnidex Environment. | |
| - | | [[dev:sql:create_database|CREATE DATABASE]] | Create ad Database within an Omnidex Environment. | | + | | [[dev:sql:statements:create_database:home|CREATE DATABASE]] | Create ad Database within an Omnidex Environment. | |
| - | | [[dev:sql:create_table|CREATE TABLE]] | Create a Table. | | + | | [[dev:sql:statements:create_table:home|CREATE TABLE]] | Create a Table. | |
| - | | [[dev:sql:create_index|CREATE INDEX]] | Create Index | | + | | [[dev:sql:statements:create_index:home|CREATE INDEX]] | Create Index | |
| - | | [[dev:sql:create_indexes|CREATE INDEXES]] | Create Omnidex Indexes. | | + | | [[dev:sql:statements:create_index_group:home | CREATE INDEX GROUP ]] | Create an Index Group. | |
| - | | [[dev:sql:drop_environment|DROP ENVIRONMENT]] | Drop Environment. | | + | | [[dev:sql:statements:create_indexes:home|CREATE INDEXES]] | Create Omnidex Indexes. | |
| - | | [[dev:sql:drop_database|DROP DATABASE]] | Drop Database | | + | | [[dev:sql:statements:drop_environment:home|DROP ENVIRONMENT]] | Drop Environment. | |
| - | | [[dev:sql:drop_table|DROP TABLE]] | Drop a previously created temporary table. | | + | | [[dev:sql:statements:drop_database:home|DROP DATABASE]] | Drop Database | |
| - | | [[dev:sql:drop_index|DROP INDEX]] | Drop Index. | | + | | [[dev:sql:statements:drop_table:home|DROP TABLE]] | Drop a previously created temporary table. | |
| - | | [[dev:sql:drop_indexes|DROP INDEXES]] | Drop Indexes. | | + | | [[dev:sql:statements:drop_index:home|DROP INDEX]] | Drop Index. | |
| - | ===== Omnidex SQL Index Maintenance Statements ===== | + | | [[dev:sql:statements:drop_index_group | DROP INDEX GROUP]] | Drop Index Group. | |
| - | + | | [[dev:sql:statements:drop_indexes:home|DROP INDEXES]] | Drop Indexes. | | |
| - | | [[programs:odxsql:commands:insertindex|INSERTINDEX]] | Insert values directly into the Omnidex Indexes. | | + | ===== Omnidex Detailed DML Statements ===== |
| - | | [[programs:odxsql:commands:updateindex|UPDATEINDEX]] | Update values directly in the Omnidex Indexes. | | + | |
| - | | [[programs:odxsql:commands:deleteindex|DELETEINDEX]] | Delete values directly from the Omnidex Indexes. | | + | |
| + | | [[dev:sql:statements:open_cursor:home |OPEN CURSOR]] | Open a Cursor. | | ||
| + | | [[dev:sql:statements:join:home|JOIN]] | Join tables using Omnidex Indexes. | | ||
| + | | [[dev:sql:statements:qualify:home|QUALIFY]] | Qualify rows using Omnidex criteria. | | ||
| + | | [[dev:sql:statements:close_cursor:home|CLOSE CURSOR]] | Close a previously opened cursor. | | ||
| ===== Omnidex SQL Miscellaneous Statements ===== | ===== Omnidex SQL Miscellaneous Statements ===== | ||
| - | | [[dev:sql:partition|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:update_rollup|UPDATE ROLLUP]] | Update Rollup Table with summarized information. | | + | | [[dev:sql:statements:update_rollup:home|UPDATE ROLLUP]] | Update Rollup Table with summarized information. | |
| - | | [[dev:sql:update_rollups|UPDATE ROLLUPS]] | Update all Rollup Tables for an Omnidex Database. | | + | | [[dev:sql:statements:update_rollups:home|UPDATE ROLLUPS]] | Update all Rollup Tables for an Omnidex Database. | |
| - | | [[dev:sql:update_statistics|UPDATE STATISTICS]] | Update Statistics Information. | | + | | [[dev:sql:statements:update_statistics:home|UPDATE STATISTICS]] | Update Statistics Information. | |
| - | | [[dev:sql:update_text|UPDATE TEXT]] | Update Textual Statistics. | | + | | [[dev:sql:statements:home:update_text|UPDATE TEXT]] | Update Textual Statistics. | |
| - | | [[dev:sql:load ofx|LOAD OFX]] | Load Indexes for a table. | | + | | [[dev:sql:statements:home:load ofx|LOAD OFX]] | Load Indexes for a table. | |
| - | | [[dev:sql:version|VERSION]] | Display current Omnidex Software versions. | | + | ===== Alphabetical List of Omnidex SQL Statements ===== |
| - | ===== Alphabetical List Omnidex SQL Statements ===== | + | |
| ^Statement^ Description ^ | ^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: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:close_cursor|CLOSE CURSOR]] | Close a previously opened cursor. | | ||
| - | | [[dev:sql:command|OS Command]] | Execute an Operating System command. | | + | | [[dev:sql:command| COMMAND]] | Execute an Operating System command. | |
| | [[dev:sql:connect|CONNECT]] | Connect to an Omnidex Environment. | | | [[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_database|CREATE DATABASE]] | Create a Database within an Omnidex Environment. | | ||
| Line 76: | Line 56: | ||
| | [[dev:sql:create_table|CREATE TABLE]] | Create a table within an Omnidex Environment or create a temp table in the underlying RDBMS. | | | [[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: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: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:disconnect|DISCONNECT]] | Disconnect from a connected Omnidex Environment. | | ||
| Line 85: | Line 64: | ||
| | [[dev:sql:drop_table|DROP TABLE]] | Drop a table created with a CREATE TABLE or SELECT INTO statement. | | | [[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: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: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:join|JOIN]] | Manually join two tables for a joined query | | ||
| | [[dev:sql:load_ofx|LOAD OFX]] | Load an OFX file. | | | [[dev:sql:load_ofx|LOAD OFX]] | Load an OFX file. | | ||
| Line 102: | Line 79: | ||
| | [[dev:sql:update_text|UPDATE TEXT]] | Update Omnidex Text information. | | | [[dev:sql:update_text|UPDATE TEXT]] | Update Omnidex Text information. | | ||
| | [[dev:sql:updateindex|UPDATEINDEX]] | Update Omnidex Index values directly. | | | [[dev:sql:updateindex|UPDATEINDEX]] | Update Omnidex Index values directly. | | ||
| - | | [[dev:sql:version|VERSION]] | Retrieve the Omnidex Version from the currently running library. | | + | | [[dev:sql:version|VERSION]] | Retrieve the Omnidex Version from the currently running library. FIXME Doesn't work in ODBC or JDBC - Remove? | |
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||