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:overview [2009/11/29 22:55] tdo |
dev:sql:overview [2010/07/02 18:03] tdo |
||
|---|---|---|---|
| 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 a rich implementation of the SQL2 and SQL99 standards along with extensions specific to Omnidex. | ||
| - | Omnidex SQL statements can be tested and scripted using the [[programs:odxsql:overview|ODXSQL]] program. | + | Omnidex SQL is based on ANSI SQL, but also contains extensions that support Omnidex's extended features. |
| - | ===== Statements by Type ===== | + | Omnidex has limited support for Oracle and SQL Server specific SQL extensions such as Oracle decode. |
| - | ==== Data Manipulation Statements ==== | + | |
| - | | SELECT | Select data | | + | |
| - | | INSERT | Insert data | | + | |
| - | | DELETE | Delete data | | + | |
| - | | UPDATE | Update data | | + | |
| - | | QUALIFY | Qualify rows using Omnidex criteria | | + | |
| - | ==== Index Manipulation Statements ==== | + | Omnidex SQL is available using standard ODBC and JDBC connections. |
| - | | | | | + | |
| - | ==== Miscellaneous Statements ==== | + | |
| - | ===== All Statements ===== | + | |
| - | ^Statement^ Description ^ | + | 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. |
| - | | [[dev:sql:assignvalues|ASSIGNVALUES]] |Assigns Values | | + | |
| - | | [[dev:sql:attach|ATTACH]] | Attach an OST | | + | 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:close_cursor|CLOSE CURSOR]] | Closes a cursor | | + | |
| - | | [[dev:sql:command|command]] | Issue an operating system command | | + | 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:connect|CONNECT]] | Connect to an Omnidex Environment | | + | 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:create_database|CREATE DATABASE]] | Create a Database | | + | |
| - | | [[dev:sql:create_directory|CREATE DIRECTORY]] | Create a Directory in the underlying operating system. | | + | Java applications such as applets, servlets, and standard console applications can communicate with Omnidex via the Omnidex JDBC driver. |
| - | | [[dev:sql:create_environment|CREATE ENVIRONMENT]] | Create an Omnidex XML Environment | | + | |
| - | | [[dev:sql:create_file|CREATE FILE]] | Create a file in the underlying operating system. | | + | Omnidex SQL is a rich implementation of the SQL2 and SQL99 standards along with extensions specific to Omnidex. |
| - | | [[dev:sql:create_index|CREATE INDEX]] | Create an Index. | | + | |
| - | | [[dev:sql:create_indexes|CREATE INDEXES]] | ??? | | + | |
| - | | [[dev:sql:create_table|CREATE TABLE]] | Create a table within an Omnidex XML Environment. | | + | |
| - | | [[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 | | + | |
| - | | [[dev:sql:disconnect|DISCONNECT]] | Disconnect from an Omnidex Environment | | + | |
| - | | [[dev:sql:drop_database|DROP DATABASE]] | Drop a Database within an Omnidex Environment. | | + | |
| - | | [[dev:sql:drop_environment|DROP ENVIRONMENT]] | Drop and Omnidex XML Environment and purge the underlying operating system file. | | + | |
| - | | [[dev:sql:drop_index|DROP INDEX]] | Drop an Omnidex Index from an Omnidex XML Environment. | | + | |
| - | | [[dev:sql:drop_indexes|DROP INDEXES]] ??? | | + | |
| - | | [[dev:sql:drop_table|DROP TABLE]] | Drop a table. | | + | |
| - | | [[dev:sql:export|EXPORT]] | Export data. | | + | |
| - | | [[dev:sql:helper|HELPER]] | ??? | | + | |
| - | | [[dev:sql:insert|INSERT]] | Insert a row of data. | | + | |
| - | | [[dev:sql:insertindex|INSERTINDEX]] | Insert a value in an Omnidex Index. | | + | |
| - | | [[dev:sql:join|JOIN]] | Join tables | | + | |
| - | | [[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 using Omnidex criteris. | | + | |
| - | | [[dev:sql:select|SELECT]] | Select data | | + | |
| - | | [[dev:sql:set|SET]] | Set Omnidex SQL processing options. | | + | |
| - | | [[dev:sql:update|UPDATE]] | Update data. | | + | |
| - | | [[dev:sql:update_indexes|UPDATE INDEXES]] | populate Omnidex indexes. | | + | |
| - | | [[dev:sql:update_rollup|UPDATE ROLLUP]] | Update Rollup Tables with new calculations. | | + | |
| - | | [[dev:sql:update_rollups|UPDATE ROLLUPS]] | ??? | | + | |
| - | | [[dev:sql:update_statistics|UPDATE STATISTICS]] | Update the Omnidex Statistics information. | [[dev:sql:update_text|UPDATE TEXT]] Update Text information. | | + | |
| - | | [[dev:sql:updateindex|UPDATEINDEX]] | Update Omnidex Index Information. | | + | |
| - | | [[dev:sql:version|VERSION]] | Retrieve Omnidex Version Information. | | + | |
| - | ===== Functions ===== | + | 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 Statements ===== | ||
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||