This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
dev:odxsql:commands:extract:home [2010/07/04 15:32] tdo |
dev:odxsql:commands:extract:home [2016/06/28 22:38] (current) |
||
---|---|---|---|
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> | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
- | ====== Omnidex SQL: EXTRACT DDL ====== | + | ====== Omnidex SQL: EXTRACT (DDL and DML Statements) ====== |
{{page>:sql_bar&nofooter&noeditbtn}} | {{page>:sql_bar&nofooter&noeditbtn}} | ||
+ | |||
===== Description ===== | ===== Description ===== | ||
- | The OdxSQL EXTRACT Command will Extract DDL statements (CREATE ENVIRONMENT, CREATE DATABASE, CREATE TABLE, CREATE INDEX and CREATE INDEX GROUP) or INSERT statements to a file. The extracted file can the be modified with an editor and used to build another Omnidex Environment. | + | The OdxSQL EXTRACT command will Extract DDL statements (CREATE ENVIRONMENT, CREATE DATABASE, CREATE TABLE, CREATE INDEX and CREATE INDEX GROUP) from the currently connected Omnidex Environment to a file. The extracted file can then be modified with an editor and used to build another Omnidex Environment. |
+ | |||
+ | The EXTRACT command is particularly helpful in converting Omnidex Environments from previous versions that used the original Omnidex Environment Catalog syntax. | ||
+ | |||
+ | The EXTRACT command can also be used to generate INSERT, DELETE, and UPDATE statements from the existing data. This would typically be used in a testing environment. | ||
===== Syntax ===== | ===== Syntax ===== | ||
Line 13: | Line 19: | ||
FROM tables [WHERE criteria] [TO filename] | FROM tables [WHERE criteria] [TO filename] | ||
[ON [INSTANCE] instance] [WITH options] | [ON [INSTANCE] instance] [WITH options] | ||
- | |||
===== Discussion ===== | ===== Discussion ===== | ||
+ | ==== DDL ==== | ||
+ | Decompiles a connected Omnidex Environment and generates the DDL CREATE ENVIRONMENT, CREATE DATABASE, CREATE TABLE, CREATE INDEX, and CREATE INDEX GROUP statements. | ||
+ | |||
+ | === FOR DATABASE database_name === | ||
+ | The FOR DATABASE clause can be used to extract the DDL for a specific database should the Omnidex Environment contain multiple databases. | ||
+ | ==== DML Statement Clauses ==== | ||
+ | The INSERT, UPDATE and DELETE SQL Data Manipulation Language statements can be generated with the Extract command. | ||
+ | === CLUSTERED === | ||
+ | The CLUSTERED option requires tables to be one parent table and any number of linked children. | ||
+ | |||
+ | === DML Type (INSERTS | UPDATES | DELETE_WHERES | DELETE_EXCEPTS) === | ||
+ | The DML Type specifies which type of DML statement is to be generated. Note that on DELETE statements, there are two types. DELETE_WHERE and DELETE_EXCEPTS. | ||
+ | |||
+ | === FROM table_specs ==== | ||
+ | FROM table_specs is one or more tables to use to generate the DML statements. | ||
+ | |||
+ | |||
+ | ==== TO filespec ==== | ||
+ | The TO filespec clause specifies the directory location and filename of the extracted statements. | ||
+ | |||
+ | If the TO filespec clause is omitted, then the file will be written to extract.sql in the current working directory. | ||
+ | ==== WITH options ==== | ||
+ | |||
+ | == DELETE == | ||
+ | Deletes the file specified in the TO filespec clause or the default extract.sql file if no filespec is specified in order to create a new file with the current SQL statement information. | ||
- | (Note: CLUSTERED requires tables to be one parent and any linked children) | + | extract ddl to myenv.sql with delete; |
+ | ===== Examples ===== | ||
+ | Extract DDL CREATE statements from a connected Environment. | ||
+ | connect myenv.xml; | ||
+ | extract ddl to myenvnew.sql; | ||
+ | Extract DDL to default extract.sql file. The extract.sql file must not exist. | ||
+ | extract ddl; | ||
+ | Extract INSERT statements from a named table. | ||
+ | extract inserts from table1 to table1_inserts.sql; | ||
{{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} |