Differences

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

--- dev:sql:home [2010/07/08 22:03]
tdo
+++ dev:sql:home [2016/06/28 22:38]
127.0.0.1 external edit
@@ Line 1: / Line 1: @@
 {{page>:top_add&nofooter&noeditbtn}}
 ~~NOTOC~~
-====== Omnidex SQL Quick Reference ======
+====== Omnidex SQL Statement Quick Reference ======
 [[dev:sql:home|Statements]] | [[dev:sql:functions|Functions]] | [[dev:sql:datatypes|Datatypes]] | [[dev:sql:examples | Examples]]
+|< 95% 50% 50% >|
-^                                           Omnidex SQL Statements                                                                                                                                    ^^^
-|**Connections**                                                   |**Omnidex Index Queries**                                         |**Data Definition**                                               |
-|[[dev:sql:statements:connect:home|CONNECT]]                       |[[dev:sql:statements:qualify:home|QUALIFY]]                       |[[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]] |
-|[[dev:sql:statements:disconnect:home|DISCONNECT]]                 |[[dev:sql:statements:join:home|JOIN]]                             |[[dev:sql:statements:create_database:home|CREATE DATABASE]]       |
-|[[dev:sql:statements:set:home|SET]]                               |                                                                  |[[dev:sql:statements:create_table:home|CREATE TABLE]]             |
-|                                                                  |**Maintenance**                                                   |[[dev:sql:statements:create_index:home|CREATE INDEX]]             |
-|**Queries and Exports**                                           |[[dev:sql:statements:update_indexes:home|UPDATE INDEXES]]         |[[dev:sql:statements:create_index_group:home|CREATE INDEX GROUP]] |
-|[[dev:sql:statements:select:home|SELECT]]                         |[[dev:sql:statements:update_rollups:home|UPDATE ROLLUPS]]         |[[dev:sql:statements:drop_environment:home|DROP ENVIRONMENT]]     |
-|[[dev:sql:statements:export:home|EXPORT]]                         |[[dev:sql:statements:update_statistics:home|UPDATE STATISTICS]]   |[[dev:sql:statements:drop_database:home|DROP DATABASE]]           |
-|[[dev:sql:statements:open_cursor:home|OPEN CURSOR]]               |[[dev:sql:statements:update_text:home|UPDATE TEXT]]               |[[dev:sql:statements:drop_table:home|DROP TABLE]]                 |
-|[[dev:sql:statements:close_cursor:home|CLOSE CURSOR]]             |[[dev:sql:statements:partition:home|PARTITION]]                   |[[dev:sql:statements:drop_index:home|DROP INDEX]]                 |
-|                                                                  |[[dev:sql:statements:load_ofx:home|LOAD OFX]]                     |[[dev:sql:statements:drop_index_group:home|DROP INDEX GROUP]]     |
-|**Updates**                                                       |[[dev:sql:statements:install_indexes:home|INSTALL INDEXES]]       |[[dev:sql:statements:attach:home|ATTACH]]                         |
-|[[dev:sql:statements:insert:home|INSERT]]                         |[[dev:sql:statements:remove_indexes:home|REMOVE INDEXES]]         |[[dev:sql:statements:detatch:home|DETATCH]]                       |
-|[[dev:sql:statements:delete:home|DELETE]]                         |                                                                  |[[dev:sql:statements:extract:home|EXTRACT]]                       |
-|[[dev:sql:statements:update:home|UPDATE]]                         |                                                                  |                                                                  |
-|----------------------------------------------------------|---------------------------------------------------------|----------------------------------------------------------|
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
 ^                                           Omnidex SQL Statements                                                                   ^^
-|**Connections**                                                   |**Data Definition**                                               |
+^**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:detatch:home|DETATCH]]                       |[[dev:sql:statements:create_index_group:home|CREATE INDEX GROUP]] |
+|[[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]]           |
+^**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**                                                   |
+^**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]]                   |
+^**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]]         |
-|----------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------|
 \\
 \\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-\\
-{{page>:sql_odxsql_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 ]] |
-^ 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_table:home | Update Rollup Table ]] |
-^ 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.
-===== Omnidex SQL Standard Functions =====
-| [[ dev:sql:functions:abs | ABS ]]| [[ dev:sql:functions:case | CASE ]] | [[ dev:sql:functions:cast | CAST ]] |[[ dev:sql:functions:character_length | 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 =====
-| [[ 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 =====
-Use the CREATE ENVIRONMENT SQL_SYNTAX setting or the SET SQL_SYNTAX setting to activate.
-| [[ dev:sql:orafunctions:chr | CHR ]] | [[ dev:sql:orafunctions:instr | INSTR ]] | [[ dev:sql:orafunctions:new_time | NEW_TIME ]] | [[ dev:sql:orafunctions:nvl | NVL ]] | [[ dev:sql:orafunctions:substr | SUBSTR ]] | [[ dev:sql:orafunctions:sys_extract_utc | SYS_EXTRACT_UTC ]] | [[ dev:sql:orafunctions:sysdate | SYSDATE ]] | [[ dev:sql:orafunctions:systimestamp | SYSTIMESTAMP ]] | [[ dev:sql:orafunctions:to_date | TO_DATE ]] | [[ dev:sql:orafunctions:to_number | TO_NUMBER ]] |
-===== Omnidex Data Types =====
-^See Also:^[[dev:sql:datatypes:home | List of Datatypes]]^
-=== Standard Datatypes ===
-Omnidex supports the following standard datatypes:
-^ Datatype                ^ Description                                                  ^
-| CHARACTER(//n//)        | Space-filled character string of //n// characters            |
-| CHAR(//n//)             | Synonym for CHARACTER(//n//)                                 |
-| C STRING(//n//)         | Null-terminated character string of //n// characters         |
-| VARCHAR(//n//)*         | String of max //n// characters, with length variable         |
-| CLOB(//n//)*            | Character large object of max //n// characters, with length variable |
-| [SIGNED] TINYINT        | 1-byte, signed integer ( -128 to 127 )                       |
-| UNSIGNED TINYINT        | 1-byte, unsigned integer ( 0 to 255 )                        |
-| [SIGNED] SMALLINT       | 2-byte, signed integer ( -32768 to 32767 )                   |
-| UNSIGNED SMALLINT       | 2-byte, unsigned integer ( 0 to 65535 )                      |
-| [SIGNED] INTEGER        | 4-byte, signed integer ( 2147483648 to 2147483647 )          |
-| UNSIGNED INTEGER        | 4-byte, unsigned integer ( 0 to 4294967295 )                 |
-| [SIGNED] BIGINT         | 8-byte, signed integer ( %%-2^63 to 2^63-1%% )               |
-| UNSIGNED BIGINT         | 8-byte, unsigned integer ( %%0 to 2^64-1%% )                 |
-| FLOAT                   | IEEE four-byte, single-precision floating point              |
-| DOUBLE                  | IEEE eight-byte, double-precision floating point             |
-| DATE                    | 10-byte ANSI date                                            |
-| TIME                    | 11-byte ANSI time                                            |
-| DATETIME                | 24-byte ANSI datetime                                        |
-| ASCII DATE              | 8-byte ASCII date ( YYYYMMDD )                               |
-| ASCII DATE(6)           | 6-byte ASCII date ( YYMMDD )                                 |
-| ODBC DATE               | 6-byte ODBC proprietary date                                 |
-| ODBC TIME               | 6-byte ODBC proprietary time                                 |
-| ODBC DATETIME           | 6-byte ODBC proprietary datetime                             |
-| ORACLE DATE             | 7-byte Oracle proprietary date                               |
-| ORACLE TIME             | 7-byte Oracle proprietary time                               |
-| ORACLE DATETIME         | 7-byte Oracle proprietary datetime                           |
-| OMNIDEX DATE(//n//)     | Omnidex proprietary date supporting //n// digits of YYYYMMDD |
-| OMNIDEX TIME(//n//)     | Omnidex proprietary time supporting //n// digits of HHMMSSNN |
-| OMNIDEX DATETIME(//n//) | Omnidex proprietary datetime supporting //n// digits of YYYYMMDDHHMMSSNN |
-| * Omnidex recommends use of the CHARACTER and C STRING datatypes rather than VARCHAR and CLOB due to the requirement of a length variable. ||
-=== National Character Datatypes ===
-Omnidex also provides partial support for National Character Datatypes (sometimes called
+====== Omnidex SQL Statement Descriptions =======
-Unicode datatypes or wide characters).  The Omnidex SQL Engine will process these
-datatypes, but the Omnidex Indexing Engine will only index the lower 8 bits of these
-datatypes.
-^ Datatype                  ^ Description                                                ^
+Omnidex SQL Statements can be issued through any standard ODBC and JDBC connection, and through the OdxSQL program.
-| NATIONAL CHARACTER(//n//) | Space-filled character string of //n// characters          |
-| NATIONAL CHAR(//n//)      | Synonym for CHARACTER(//n//)                               |
-| NATIONAL C STRING(//n//)  | Null-terminated character string of //n// characters       |
-| NATIONAL VARCHAR(//n//)*  | String of max //n// characters, with length variable       |
-| NATIONAL CLOB(//n//)*     | Character large object of max //n// characters, with length variable |
-| * Omnidex recommends use of the NATIONAL CHARACTER and NATIONAL C STRING datatypes rather than NATIONAL VARCHAR and NATIONAL CLOB due to the requirement of a length variable.  ||
+|< 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. ]]
 {{page>:bottom_add&nofooter&noeditbtn}}

Back to top

dev/sql/home.txt · Last modified: 2020/11/30 18:42 by doc

Sitemap Recent Changes