This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
programs:odxsql:overview [2009/12/04 06:10] tdo |
programs:odxsql:overview [2012/10/26 14:26] (current) |
||
|---|---|---|---|
| Line 63: | Line 63: | ||
| See HELP in the OdxSQL Commands section for more information. | See HELP in the OdxSQL Commands section for more information. | ||
| - | |||
| ===== Commands ===== | ===== Commands ===== | ||
| + | ===== Overview ===== | ||
| + | |||
| + | OdxSQL is used to process a wide range of Omnidex SQL Statements and OdxSQL specific commands. | ||
| + | |||
| + | The OdxSQL Commands and Omnidex SQL Statements are organized for quick review as follows: | ||
| + | |||
| + | * [[#Connecting and Disconnecting ]] | ||
| + | * [[#Omnidex SQL Data Definition Language (DDL) Statements ]] | ||
| + | * [[#Omnidex SQL Data Manipulation Language (DML) Statements ]] | ||
| + | * [[#Omnidex Environment Operation Commands ]] | ||
| + | * [[#OdxSQl and Omnidex SQL Data Export and Manipulation ]] | ||
| + | * [[#OdxSQL Program Operation Commands ]] | ||
| + | * [[#OdxSQL Testing and Performance Commands ]] | ||
| + | * [[#OdxSQL Operating System Commands ]] | ||
| + | * [[#Omnidex Index Direct Maintenance Routines ]] | ||
| + | * [[#Alphabetical List of OdxSQL Commands and Omnidex SQL Statements ]] | ||
| + | |||
| + | OdxSQL Commands and Omnidex SQL Statements can be entered into OdxSQL in ether upper or lower case. | ||
| + | |||
| + | > connect myenv | ||
| + | |||
| + | > CONNECT myenv | ||
| + | |||
| + | The syntax for Omnidex SQL Statements entered into OdxSQL or processed in a script file is typically the exact same syntax and returns the exact same results. | ||
| + | |||
| + | There are a few exceptions such as CONNECT and VERSION. | ||
| + | |||
| + | In OdxSQL, CONNECT has two additional options of ON host:port and USING api that are not available in the Omnidex SQL CONNECT Statement. The api is not necessary has it is already known depending on the application type (ODBC or JDBC) and ON | ||
| + | |||
| + | In OdxSQL, the VERSION command displays the OdxSQL header and multiple lines of version information where the Omnidex SQL VERSION statement returns a single line. | ||
| + | |||
| + | The OdxSQL EXECSQL command can be used to verify if an Omnidex SQL Statement is processed exactly the same by OdxSQL as the Omnidex SQL parser. | ||
| + | |||
| + | <code> | ||
| + | > VERSION | ||
| + | ODXSQL - Omnidex SQL Wed Dec 02 09:20:41 200 | ||
| + | ... | ||
| + | Omnidex Version 5.0 Build 2B | ||
| + | Compilation Timestamp Oct 13 2009 08:57:09 | ||
| + | Earliest Compatible ODBC Client 4.3 Build 4B | ||
| + | Earliest Compatible JDBC Client 4.3 Build 4B | ||
| + | Earliest Compatible Odx Kernel 5.0 Build 1B | ||
| - | Commands with an * (asterisk) before the description are also available for use in oaexecsql, ODBC and JDBC applications. All others are available for use ONLY in ODXSQL. | + | > EXECSQL VERSION |
| - | ^Command ^ Description ^ | + | OmniAccess version 50002 |
| - | | ! (Exclamation Point) | Execute an operating system command | | + | > |
| - | | . (Period) | Re-Execute the last command | | + | </code> |
| - | | ;(Semi-Colon) | Comment - any text on the line will be ignored | | + | |
| - | | ABORT | Cause OdxSQL to terminate | | + | |
| - | | [[programs:odxsql:commands:assignvalues|ASSIGNVALUES]] | * Assign values for a $VALUES clause | | + | |
| - | | ATTACH | * Attach an OST file as a table | | + | |
| - | | BENCHMARK |Perform performance benchmarks | | + | |
| - | | BIND | Bind a SELECT statement | | + | |
| - | | CALC | Perform basic calculator functions | | + | |
| - | | CD | Change directories | | + | |
| - | | CLOSE CURSOR | * Close a previously opened cursor | | + | |
| - | | COMMAND | * Execute a shell command via $COMMAND table | | + | |
| - | | CONNECT | * Connect to an XML or an Environment Catalog | | + | |
| - | | CONVERT | Export a file with data conversions | | + | |
| - | | COPY | Copy a fileset | | + | |
| - | | CREATE DIRECTORY | * Create an operating system directory | | + | |
| - | | CREATE DATABASE | * Create a database within an Omnidex Environment | | + | |
| - | | CREATE ENVIRONMENT | * Create an Omnidex Environment | | + | |
| - | | CREATE FILE | * Create an operating system file | | + | |
| - | | CREATE INDEX | * Create an index or a column or group of columsn | | + | |
| - | | CREATE TABLE | * Create a table | | + | |
| - | | DATE | Show the current date and time | | + | |
| - | | DELETE | * Issue an SQL delete statement | | + | |
| - | | DELETEINDEX |* Delete values directly from Omnidex indexes | | + | |
| - | | DESCRIBE | Describe a SELECT statement | | + | |
| - | | DETACH |* Detach a previously attached OST file | | + | |
| - | | DIR | Display a directory of files from the operating system | | + | |
| - | | DISCONNECT | * Disconnect from an XML or Environment Catalog | | + | |
| - | | DROP DATABASE | | | + | |
| - | | DROP ENVIRONMENT | | | + | |
| - | | DROP INDEX | | | + | |
| - | | DROP TABLE | | | + | |
| - | | DROP | * Drop a table | | + | |
| - | | DUMP | Dump files, memory or caches | | + | |
| - | | EDIT | Edit a previous command or file | | + | |
| - | | ERROR | Display an error message | | + | |
| - | | EVAL | Evaluate a condition | | + | |
| - | | EXECDB | Execute an RDBMS statement using the execdb() | | + | |
| - | | EXIT | Exit Omnidex SQL | | + | |
| - | | EXPLAIN | Shows an Explain Plan on the retrieval | | + | |
| - | | EXPORT | * Export records using SELECT statement syntax to a file | | + | |
| - | | EXTRACT | Extract SQL statements from database | | + | |
| - | | FETCH | Explicitly fetch data from a database | | + | |
| - | | FETCHKEYS | Explicitly fetch keys from the indexes | | + | |
| - | | FLIP | Flip bindary data from one endian to another | | + | |
| - | | FOR | Form a 'FOR n ... END' construct | | + | |
| - | | FORMAT | Format a file of OdxSQL statements | | + | |
| - | | GETENV | Display an environment variable | | + | |
| - | | HELP | Help on OdxSQL commands | | + | |
| - | | HISTORY | Show history of OdxSQL commands | | + | |
| - | | IF | Form an 'IF ELIF ELSE ENDIF' construct | | + | |
| - | | INSERT | * Issue an SQL insert statement to insert data | | + | |
| - | | INSERTINDEX | * Insert values directly into Omnidex indexes | | + | |
| - | | IS | Check database conditions | | + | |
| - | | JOIN | * Join tables using Omnidex | | + | |
| - | | LOAD | Load indexes for TDF files | | + | |
| - | | OPEN CURSOR | * Open a cursor | | + | |
| - | | OXKERNEL | Execute an Omnidex kernel command | | + | |
| - | | PARSE | Parse a SQL statement | | + | |
| - | | PARTITION | Partition a table | | + | |
| - | | PWD | Print or show the current working directory | | + | |
| - | | QUALIFY |* Qualify rows using the Omnidex QUALIFY statement| | + | |
| - | | QUIT | Quit Omnidex OdxSQL | | + | |
| - | | REMOVE | Remove one or more files from the operating system | | + | |
| - | | RENAME | Rename a file or fileset | | + | |
| - | | RESET HISTORY | Reset history of ODXSQL commands | | + | |
| - | | RESET TIMER | Reset the cumulative timer used to verify performance of queries | | + | |
| - | | RESTORE SETTINGS | Restore settings, optionally from a file | | + | |
| - | | SAVE HISTORY | Save previously entered OdxSQL commands to a file | | + | |
| - | | SAVE SETTINGS | Save settings, optionally to a file | | + | |
| - | | SELECT | * Issue an Omnidex SQL SELECT statement | | + | |
| - | | SET command | Set an OdxSQL option | | + | |
| - | | SET statement | Set and Omnidex SQL setting | | + | |
| - | | SETENV | Set an operating system environment variable | | + | |
| - | | SHOW | Show information on databases, tables, and columns | | + | |
| - | | SIGNAL | Send an operating system signal to a process | | + | |
| - | | SORTMERGE | Sort/merge test system | | + | |
| - | | UPDATE | * Issue an Omnidex SQL UPDATE statement | | + | |
| - | | UPDATE STATISTICS | Update statistics for a database or table | | + | |
| - | | UPDATE TEXT | Enable misspelling searches on Text, FullText or MDK indexed columns. | | + | |
| - | | UPDATEINDEX | * Update values directly in Omnidex indexes | | + | |
| - | | USE | Execute a file of OdxSQL commands | | + | |
| - | | VALIDATE | Validate an Omnidex Environment to determine correct mapping to an underlying database or file and verify index retrievals are working | | + | |
| - | | VERSION | * Display current software version information | | + | |
| - | | VIEW | View an external text file | | + | |
| - | | n | Execute numbered command from the OdxSQL History stack | | + | |
| - | | n: | Send command to OdxSQL subprocess identified by relative number of the process | | + | |
| ===== Settings ===== | ===== Settings ===== | ||