EXTRACT
The EXTRACT command in OdxSQL is used to transform data in a database
into SQL statements. INSERT, UPDATE and DELETE statements are generated
from existing data for each row and output to a file.
The purpose of this command is to allow a database to be restored to
its original state after temporary changes have been made to the data,
without actually making a physical copy of the entire database.
The output file can be executed in ODXSQL as a USE file to restore the
data to its original state.
The default output file is extract.sql.
 The
ODXSQL EXTRACT command is not the same as the EXTRACT
SQL function.
The SQL statement is limited to a maximum of 65536 characters; therefore,
this statement can only be applied against result sets that fit within
this limit. For example, flat file row ids are usually integers, which
would allow about 10000 entries in this statement. However, Oracle row
ids are considerably larger and therefore, limited to considerably fewer
entries. An error will occur if all of the qualified rowids will not fit.
Syntax
EXTRACT sql_statement_type FROM table(s) [WHERE criteria]
[TO filename] [ON [INSTANCE] instancenumber] [WITH options]
EXTRACT
Required.
sql_statement_type
Type of statements to extract. See Statement Types
(below) for more information.
FROM table(s)
Required. table(s) is a comma separated list of table names, as defined
in the Omnidex Environment file. The FROM keyword is required.
WHERE criteria
Optional. Limit the extract to a specific set of records. Criteria is
any supported SQL where clause criteria. The WHERE keyword is required
if a WHERE clause is used.
TO filename
Optional. Filename is the output filespec, fully qualified or relative.
If omitted, the default, extract.sql, will be created in the current working
directory. The TO keyword is required if the output file is specified.
ON INSTANCE instancenumber
Optional. Specify the instance on which to perform the extract. instancenumber
is the integer instance number created when the connection to the Environment
file was opened. If omitted, the current instance contained in the instance
element of the Status array will be used. The INSTANCE keyword is optional.
The ON keyword is required if the instance is specified.
WITH options
Optional. A comma separated list of options to apply to this command.
Statement Types
The OdxSQL EXTRACT command can extract three different types of SQL statements:
INSERTS, UPDATES, and DELETES.
INSERTS causes an SQL INSERT statement
to be generated for each row as determined by the criteria. An SQL INSERT
statement is generated for every row in the table that matches the criteria.
This statement can be used to populate an empty copy of the database,
or restore the data if the rows were deleted.
EXTRACT INSERTS FROM CUSTOMERS
UPDATES causes an SQL UPDATE statement
to be generated for each row as determined by the criteria. An SQL UPDATE
statement is generated for every row in the table that matches the criteria.
This statement can be used to restore rows to their original value.
EXTRACT UPDATES FROM CUSTOMERS
DELETES causes one delete statement to
be generated in the form:
DELETE FROM table WHERE NOT IN (rowid1, rowid2, ...)
The DELETE statement contains the rowid for every row that was in the
table prior to the changes and will delete any row that was added (rowid
not in the list) since the statement was generated.
EXTRACT DELETES FROM CUSTOMERS
Options
Example
The following example extracts all three types of statements to the default
extract.sql, using criteria.
>extract inserts, updates, deletes from customers where
state='co' and company='systems'
>
Three records qualified for this statement. Following are the contents
of extract.sql which was created in the current working directory:
; ***************************************************************
; SQL produced by ODXSQL's EXTRACT command on Tue Nov 06 09:26:28 2001
; ***************************************************************
A connect is included but commented out.
; connect to C:\omnidex\Demodata\orders.env
A single DELETE statement is created to delete every record except the
three qualified by the original extract criteria.
delete from "customers" \
where not $ROWID in ('1', '341', '863')
Three INSERT statements are created, one for each record qualified by
the original extract criteria, to allow the original records to be duplicated.
insert into "customers" \
("CUSTOMER_NO", "COMPANY", "CONTACT", "TITLE",
"SALUTATION", \
"ADDRESS1", "ADDRESS2", "CITY", "STATE",
"POSTAL_CODE", \
"COUNTRY", "PHONE_NO", "FAX_NO", "AKA_COMPANY",
"COMMENTS", \
"LICENSEE") \
values (1, 'Dynamic Information Systems Corporation', \
'Mark S. Trasko', 'President', 'Mr.', '5733 Central Avenue', \
'', 'Boulder', 'CO', '80301', 'USA', '303 444-4000 x1300', \
'303 444-2230', 'DISC MST', \
'Sample prospect record for entry and annotation', 1)
insert into "customers" \
("CUSTOMER_NO", "COMPANY", "CONTACT", "TITLE",
"SALUTATION", \
"ADDRESS1", "ADDRESS2", "CITY", "STATE",
"POSTAL_CODE", \
"COUNTRY", "PHONE_NO", "FAX_NO", "AKA_COMPANY",
"COMMENTS", \
"LICENSEE") \
values (343, 'Summit Information Systems', 'Mark Polando', \
'Sales Rep', 'Mr.', '4130 North 26th Street', '', 'Boulder', \
'CO', '80304', 'USA', '800 937-7500 (656)', '', '', \
'Develop HP3000 credit union application - Corp. in Corvallis',\
0)
insert into "customers" \
("CUSTOMER_NO", "COMPANY", "CONTACT", "TITLE",
"SALUTATION", \
"ADDRESS1", "ADDRESS2", "CITY", "STATE",
"POSTAL_CODE", \
"COUNTRY", "PHONE_NO", "FAX_NO", "AKA_COMPANY",
"COMMENTS", \
"LICENSEE") \
values (867, 'Mini-Computer Systems', 'Phil Norisez', '', 'Ms.', \
'2336 Canyon', 'Suite 201', 'Boulder', 'CO', '80302', 'USA', \
'303 443-3347', '', '', 'Macroscope', 0)
Three UPDATE statements, one for each record qualified by the original
extract criteria, are created to allow these records to be restored to
their original state.
update "customers" \
set "CUSTOMER_NO" = 1, "COMPANY" = \
'Dynamic Information Systems Corporation', "CONTACT" = \
'Mark S. Trasko', "TITLE" = 'President', "SALUTATION"
= 'Mr.', \
"ADDRESS1" = '5733 Central Avenue', "ADDRESS2" = '',
"CITY" = \
'Boulder', "STATE" = 'CO', "POSTAL_CODE" = '80301',
"COUNTRY" \
= 'USA', "PHONE_NO" = '303 444-4000 x1300', "FAX_NO"
= \
'303 444-2230', "AKA_COMPANY" = 'DISC MST', "COMMENTS"
= \
'Sample prospect record for entry and annotation', "LICENSEE"
\
= 1 \
where $ROWID = '1'
update "customers" \
set "CUSTOMER_NO" = 343, "COMPANY" = 'Summit Information
Systems', \
"CONTACT" = 'Mark Polando', "TITLE" = 'Sales Rep',
\
"SALUTATION" = 'Mr.', "ADDRESS1" = '4130 North 26th
Street', \
"ADDRESS2" = '', "CITY" = 'Boulder', "STATE"
= 'CO', \
"POSTAL_CODE" = '80304', "COUNTRY" = 'USA', "PHONE_NO"
= \
'800 937-7500 (656)', "FAX_NO" = '', "AKA_COMPANY"
= '', \
"COMMENTS" = \
'Develop HP3000 credit union application - Corp. in Corvallis',\
"LICENSEE" = 0 \
where $ROWID = '341'
update "customers" \
set "CUSTOMER_NO" = 867, "COMPANY" = 'Mini-Computer
Systems', \
"CONTACT" = 'Phil Norisez', "TITLE" = '', "SALUTATION"
= \
'Ms.', "ADDRESS1" = '2336 Canyon', "ADDRESS2" = 'Suite
201', \
"CITY" = 'Boulder', "STATE" = 'CO', "POSTAL_CODE"
= '80302', \
"COUNTRY" = 'USA', "PHONE_NO" = '303 443-3347', "FAX_NO"
= '', \
"AKA_COMPANY" = '', "COMMENTS" = 'Macroscope', "LICENSEE"
= 0 \
where $ROWID = '863'
A disconnect statement is included but is also commented out.
; disconnect
Top
|
