Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
dev:sql:home [2010/07/03 19:09]
tdo
dev:sql:home [2020/11/30 18:42] (current)
doc
Line 1: Line 1:
 {{page>:​top_add&​nofooter&​noeditbtn}} {{page>:​top_add&​nofooter&​noeditbtn}}
-<​html><​div align="​center"><​span style="​color:​red">​DRAFT</​span></​div></​html>​ 
 ~~NOTOC~~ ~~NOTOC~~
-====== Omnidex SQL Quick Reference ====== +====== Omnidex SQL Statement ​Quick Reference ======
-{{page>:​sql_bar&​nofooter&​noeditbtn}} +
-===== Omnidex SQL Statements and OdxSQL Commands ​=====+
  
-^ DML ^| [[dev:sql:​statements:​select:home |Select ​]]| [[dev:sql:statements:​insert:home |Insert ​]] | [[dev:sql:statements:​delete:​home ​|Delete ​]] | [[dev:sql:statements:​update:home |Update ​]] | [[dev:​sql:​statements:​qualify:​home |Qualify ]] | +[[dev:​sql:​home|Statements]] | [[dev:sql:functions:home|Functions]] | [[dev:sql:datatypes|Datatypes]] | [[dev:sql:examples:home|Examples]]
-^ Access ^| [[dev:​sql:​statements:​connect:​home |Connect *]] | [[dev:​sql:​statements:​disconnect:​home | Disconnect *]] | [[dev:​sql:​statements:​attach:​home | Attach ]] | [[dev:​sql:​statements:​detach:​home | Detach ]] | [[dev:​sql:​statements:​set:​home | Set ]] | +
-^ DDL Create ^| [[dev:​sql:​statements:​create_environment:​home | Create Environment ]] | [[dev:​sql:​statements:​create_database:​home | Create Database ]] | [[dev:​sql:​statements:​create_table:​home | Create Table ]] | [[dev:​sql:​statements:​create_index:​home | Create Index ]] | [[dev:​sql:​statements:​create_index_group:​home | Create Index Group ]] | +
-^ DDL Drop  ^| [[dev:​sql:​statements:​drop_environment:​home | Drop Environment ]] | [[dev:​sql:​statements:​drop_database:​home |Drop Datebase ]] | [[dev:​sql:​statements:​drop_table:​home | Drop Table ]] | [[dev:​sql:​statements:​drop_index:​home | Drop Index ]] | [[dev:​sql:​statements:​drop_index_group | Drop Index Group ]] | +
-^ Syncing ^| [[dev:​sql:​statements:​update_indexes:​home | Update Indexes ]] | [[dev:​sql:​statements:​update_text:​home | Update Text ]] | [[dev:​sql:​statements:​update_statistics:​home | Update Statistics ]] | [[dev:​sql:​statements:​update_rollups:​home | Update Rollups ]] | [[dev:​sql:​statements:​update_rollup:​home | Update Rollup ]] | +
-^ Special ^| [[dev:​sql:​statements:​export:​home | Export ]] | [[dev:​sql:​statements:​Partition:​home | Partition ]] | [[dev:​sql:​statements:​join:​home | Join ]] | [[dev:​sql:​statements:​open_cursor:​home | Open Cursor ]] | [[dev:​sql:​statements:​close_cursor:​home | Close Cursor ]]| +
-^ Other ^| [[dev:​sql:​statements:​install_indexes:​home | Install Indexes ]] | [[ dev:​sql:​statements:​remove_indexes:​home | Remove Indexes ]] | [[dev:​sql:​statements:​load_ofx:​home | Load OFX ]]| [[dev:​sql:​statements:​command:​home | Command ]] | [[dev:​odxsql:​commands:​extract:​home | Extract *]]|+
  
-Statement is only available from OdxSQL.+|< 95% 50% 50% >| 
 +^                                           ​Omnidex SQL Statements ​                                                                  ^^ 
 +^**Connections** ​                                                  ​^**Data Definition** ​                                              ^ 
 +|[[dev:​sql:​statements:​connect:​home|CONNECT]] ​                      ​|[[dev:​sql:​statements:​create_environment:​home|CREATE ENVIRONMENT]] | 
 +|[[dev:​sql:​statements:​disconnect:​home|DISCONNECT]] ​                ​|[[dev:​sql:​statements:​create_database:​home|CREATE DATABASE]] ​      | 
 +|[[dev:​sql:​statements:​set:​home|SET]] ​                              ​|[[dev:​sql:​statements:​create_table:​home|CREATE TABLE]] ​            | 
 +|[[dev:​sql:​statements:​attach:​home|ATTACH]] ​                        ​|[[dev:​sql:​statements:​create_index:​home|CREATE INDEX]] ​            | 
 +|[[dev:​sql:​statements:​detach:​home|DETACH]] ​                      ​|[[dev:​sql:​statements:​create_index_group:​home|CREATE INDEX GROUP]] | 
 +|                                                                  |[[dev:​sql:​statements:​drop_environment:​home|DROP ENVIRONMENT]] ​    | 
 +^**Queries and Exports** ​                                          ​|[[dev:​sql:​statements:​drop_database:​home|DROP DATABASE]] ​          | 
 +|[[dev:​sql:​statements:​select:​home|SELECT]] ​                        ​|[[dev:​sql:​statements:​drop_table:​home|DROP TABLE]] ​                | 
 +|[[dev:​sql:​statements:​export:​home|EXPORT]] ​                        ​|[[dev:​sql:​statements:​drop_index:​home|DROP INDEX]] ​                | 
 +|[[dev:​sql:​statements:​open_cursor:​home|OPEN CURSOR]] ​              ​|[[dev:​sql:​statements:​drop_index_group:​home|DROP INDEX GROUP]] ​    | 
 +|[[dev:​sql:​statements:​close_cursor:​home|CLOSE CURSOR]] ​            ​|[[dev:​sql:​statements:​extract:​home|EXTRACT]] ​                      | 
 +|                                                               ​| ​                                                                 | 
 +^**Updates** ​                                                      ​^**Maintenance** ​                                                  ^ 
 +|[[dev:​sql:​statements:​insert:​home|INSERT]] ​                        ​|[[dev:​sql:​statements:​update_indexes:​home|UPDATE INDEXES]] ​        | 
 +|[[dev:​sql:​statements:​delete:​home|DELETE]] ​                        ​|[[dev:​sql:​statements:​update_rollups:​home|UPDATE ROLLUPS]] ​        | 
 +|[[dev:​sql:​statements:​update:​home|UPDATE]] ​                        ​|[[dev:​sql:​statements:​update_statistics:​home|UPDATE STATISTICS]] ​  | 
 +|                                                                  |[[dev:​sql:​statements:​update_text:​home|UPDATE TEXT]] ​              | 
 +^**Omnidex Index Queries** ​                                        ​|[[dev:​sql:​statements:​partition:​home|PARTITION]] ​                  | 
 +|[[dev:​sql:​statements:​qualify:​home|QUALIFY]] ​                      ​|[[dev:​sql:​statements:​load_ofx:​home|LOAD OFX]]                     | 
 +|[[dev:​sql:​statements:​join:​home|JOIN]] ​                            ​|[[dev:​sql:​statements:​install_indexes:​home|INSTALL INDEXES]] ​      | 
 +|                                                                  |[[dev:​sql:​statements:​remove_indexes:​home|REMOVE INDEXES]] ​        ​| ​                                                                  
 +\\ 
 +\\
  
-===== Omnidex SQL Standard Functions ​===== +====== Omnidex SQL Statement Descriptions =======
-| [[ dev:​sql:​functions:​abs | ABS ]]| [[ dev:​sql:​functions:​case | CASE ]] | [[ dev:​sql:​functions:​cast | CAST ]] |[[ dev:​sql:​functions:​character_length:​home | CHAR[ACTER]_LENGTH ]] |[[ dev:​sql:​functions:​coalesce | COALESCE ]] | [[ dev:​sql:​functions:​concatenation | || (Concatenation) ]] |  +
-|[[ dev:​sql:​functions:​current_date | CURRENT_DATE ]] | [[ dev:​sql:​functions:​current_time | CURRENT_TIME]] | [[ dev:​sql:​functions:​current_timestamp | CURRENT_TIMESTAMP]] |[[ dev:​sql:​functions:​current_user | CURRENT_USER]] | [[ dev:​sql:​functions:​extract | EXTRACT]] | [[ dev:​sql:​functions:​lower | LOWER]] |  +
-| [[ dev:​sql:​functions:​mod | MOD ]] | [[ dev:​sql:​functions:​position | POSITION]] | [[ dev:​sql:​functions:​power | POWER ]] | [[ dev:​sql:​functions:​round | ROUND ]] | [[ dev:​sql:​statements:​select:​sample | SAMPLE ]] | [[ dev:​sql:​functions:​substring | SUBSTRING]] |  +
-| [[ dev:​sql:​functions:​session_user | SESSION_USER]] | [[ dev:​sql:​functions:​system_user | SYSTEM_USER ]] | [[ dev:​sql:​functions:​trim | TRIM ]] | [[ dev:​sql:​functions:​trunc | TRUNC ]] | [[ dev:​sql:​functions:​upper | UPPER ]] | [[ dev:​sql:​functions:​user | USER ]] | +
  
-===== Omnidex ​Extended Functions ===== +Omnidex SQL Statements can be issued through any standard ODBC and JDBC connection, and through the OdxSQL ​program 
-| [[ dev:​sql:​functions:​calc_date | $CALC_DATE ]] | [[ dev:​sql:​functions:​col_length | $COL[UMN]_LENGTH ]] | [[ dev:​sql:​functions:​contains:​home | $CONTAINS ]] | [[ dev:​sql:​functions:​context:​home | $CONTEXT ]] | [[ dev:​sql:​functions:​convert | $CONVERT ]] | [[ dev:​sql:​functions:​compare_dates | $COMPARE_DATES]] | +
-|[[ dev:​sql:​functions:​current_row | $CURRENT_ROW ]] | [[ dev:​sql:​functions:​distance | $DISTANCE ]] | [[ dev:​sql:​functions:​external | $EXTERNAL ]] | [[ [[ dev:​sql:​functions:​highlight_criteria:​home | $HIGHLIGHT_CRITERIA ]] | [[ dev:​sql:​functions:​highlight_keywords:​home | $HIGHLIGHT_KEYWORDS ]] | [[ dev:​sql:​functions:​ifnull | $IFNULL ]] | +
-| [[ dev:​sql:​functions:​lj | $LJ ]] |[[ [[ dev:​sql:​functions:​lookup:​home | $LOOKUP ]] | [[ dev:​sql:​functions:​lpad | $LPAD ]] | [[ dev:​sql:​functions:​mod | $MOD ]]  | [[ dev:​sql:​functions:​proper | $PROPER ]] | [[ dev:​sql:​functions:​random | $RANDOM ]] | +
-| [[ dev:​sql:​functions:​rj | $RJ ]]  | [[ dev:​sql:​functions:​rpad | $RPAD ]] | [[ dev:​sql:​functions:​score | $SCORE ]] | [[ dev:​sql:​functions:​soundex | $SOUNDEX ]] |  +
-===== Oracle Functions ===== +
-| [[ dev:​sql:​orafunctions:​chr | CHR ]] | [[ dev:​sql:​orafunctions:​instr | INSTR ]] | [[ dev:​sql:​orafunctions:​new_time | NEW_TIME ]] | [[ dev:​sql:​orafunctions:​nvl | NVL ]] | [[ SUBSTR ]] | [[ SYS_EXTRACT_UTC ]] | [[ SYSDATE ]] | [[ dev:​sql:​orafunctions:​systimestamp | SYSTIMESTAMP ]] | [[ dev:​sql:​orafunctions:​to_date | TO_DATE ]] | [[ dev:​sql:​orafunctions:​to_number | TO_NUMBER ]] | +
-===== SETTINGS that affect ​SQL ===== +
-| SET | SET SQL_SYNTAX=<​ORACE%%|%%OMNIDEX%%|%%SQLSERVER>​ | Allow Oracle ​and SqlServer extensions. | +
-OdxSQL ​| SET TERMINATION [ ON %%|%% OFF ] | Set auto termination of statements|+
  
 +|< 95% 25% 75% >|
 +^Statement ​ ^Description ​ ^
 +|  ||
 +^**Connections** ​ ^^
 +| [[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:​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 File. |
 +| [[dev:​sql:​statements:​detach:​home|DETACH]] | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. |
 +|  ||
 +^**Queries and Exports** ​ ^|
 +| [[dev:​sql:​statements:​select:​home|SELECT]] | Peform a Query Specification and SELECT rows. |
 +| [[dev:​sql:​statements:​export:​home|EXPORT]] | Export rows or rowids to an external file. |
 +| [[dev:​sql:​statements:​open_cursor:​home |OPEN CURSOR]] | Open a Cursor. |
 +| [[dev:​sql:​statements:​close_cursor:​home|CLOSE CURSOR]] | Close a previously opened cursor. |
 +|  ||
 +^**Updates** ​ ^|
 +| [[dev:​sql:​statements:​insert:​home|INSERT]] | Insert rows into a Table. |
 +| [[dev:​sql:​statements:​update:​home|UPDATE]] | Update rows in a Table. |
 +| [[dev:​sql:​statements:​delete:​home|DELETE]] | Delete rows in a Table. |
 +|  ||
 +^**Omnidex Index Queries** ​ ^|
 +| [[dev:​sql:​statements:​qualify:​home|QUALIFY]] | Qualify index counts using Omnidex criteria. |
 +| [[dev:​sql:​statements:​join:​home|JOIN]] | Join tables using Omnidex Indexes. |
 +|  ||
 +^**Data Definition** ​ ^|
 +| [[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_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_group:​home | CREATE INDEX GROUP ]] | Specify an Index Group within the Omnidex metadata. |
 +| [[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]] | Remove the named Database from the Omnidex metadata. |
 +| [[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]] | Removes an index from the Omnidex metadata. |
 +| [[dev:​sql:​statements:​drop_index_group:​home | DROP INDEX GROUP]] | Removes a named Index Group from the Omnidex metadata. |
 +|  ||
 +^**Maintenance** ​ ^|
 +| [[dev:​sql:​statements:​update_indexes:​home|UPDATE INDEXES]] | Update Omnidex Indexes. |
 +| [[dev:​sql:​statements:​update_rollup_table:​home|UPDATE ROLLUP TABLE]] | Update Rollup Table with summarized information. |
 +| [[dev:​sql:​statements:​update_rollups:​home|UPDATE ROLLUPS]] | Update all Rollup Tables for an Omnidex Database. | 
 +| [[dev:​sql:​statements:​update_statistics:​home|UPDATE STATISTICS]] | Update Statistics Information. |
 +| [[dev:​sql:​statements:​home:​update_text|UPDATE TEXT]] | Update Textual Statistics. |
 +| [[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:​install_indexes:​home | INSTALL INDEXES ]]| Specifies the Omnidex indexes into the Omnidex XML Environment. No indexing is performed. |
 +| [[ dev:​sql:​statements:​remove_indexes:​home | REMOVE INDEXES]]| Removes the Omnidex indexes from the Omnidex XML Environment. |
  
-===== Create a simple functioning Environment with an Omnidex index ===== 
-<​code>​ 
-os> odxsql 
-> CREATE ENVIRONMENT IN "​myenv.xml";​ 
-> CREATE DATABASE mydb TYPE FLATFILE IN "​myenv.xml";​ 
-> CREATE TABLE mytable PHYSICAL "​test1" ​ 
-       ​(myseq integer, mytext char(60) quicktext) ​ 
-       IN "​myenv.xml";​ 
-> CONNECT myenv.xml; 
-> UPDATE INDEXES; 
-> SELECT * FROM mytable where mytext = '<​some criteria>';​ 
-> DISCONNECT; 
-> EXIT 
-os> 
-</​code>​ 
-===== Learning Omnidex ===== 
-  * [[ tutorials:​simple_flatfile | Creating a very simple flat file Environment and doing retrievals. ]] 
-  * [[ tutorials:​simple_flatfile_java_JDBC | Writing a simple JDBC program to access the simple flatfile Environment. ]] 
-  
- 
-===== Special Omnidex SQL tokens ===== 
-  
-| $omnidex | special table of one row and one column. ​ Good to test functions, etc. | 
-| $uniquekey | | 
-| $ROWID | | 
  
  
 {{page>:​bottom_add&​nofooter&​noeditbtn}} {{page>:​bottom_add&​nofooter&​noeditbtn}}
 
Back to top
dev/sql/home.1278184152.txt.gz · Last modified: 2016/06/28 22:38 (external edit)