Kbase P43278: SQL-92: How To Dump and Load Data Definitions of an SQL-92 VIEW
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/28/2011 |
|
Status: Verified
GOAL:
SQL-92: How To Dump and Load Data Definitions of an SQL-92 VIEW
GOAL:
How To Dump and Load Data Definitions File of an SQL-92 VIEW using SQLSCHEMA utility.
FACT(s) (Environment):
OpenEdge Category: SQL
Progress 9.1x
OpenEdge 10.x
All Supported Operating Systems
FIX:
Progress SQL-92 VIEW and TABLE data definitions may be dumped and loaded at will. This Solution demonstrates the use of the Progress SQLSCHEMA command line utility to dump and load an SQL-92 VIEW data definition. Similar syntax may be used to dump and load the schema definition of any other SQL-92 table or component.
SQLSCHEMA is a Progress command-line utility that selectively writes SQL-92 database schema components to the screen or to an output file.
For additional details on this utility and the related SQLDUMP and SQLLOAD Utilities, refer to the "Database Administration Utilities" Chapter in the Version 9 "Progress Database Administration Guide and Reference".
Example:
1) Create a new sports2000 database.
2) Start the database in multi-user mode.
3) Create one user with Name and Password using the Data Dictionary.
4) Connect to the database using the Progress SQL Explorer Tool.
5) Using the Progress SQL Explorer Tool, create a VIEW by executing the following two statements:
CREATE VIEW myview (
NAME,
CITY
) AS SELECT NAME , CITY FROM PUB.CUSTOMER;
COMMIT WORK;
6) Confirm that you have indeed created 'myview', by executing the following statement in the Progress SQL Explorer Tool:
SELECT * FROM myview;
A listing of the VIEW's data will be displayed.
7) From the command line, invoke the SQLSCHEMA utility to dump the 'myview' data definition into an output file:
sqlschema -o myOutputFile -t myview -u <UserName> -a <UserPassword> progress:T:localhost:<cServiceName>:sports2000
8) SQLSCHEMA will generate 'myOutputFile.dfsql' that includes the 'myview' data definition code:
CREATE VIEW myview (
NAME,
CITY
) AS SELECT NAME , CITY FROM PUB.CUSTOMER;
COMMIT WORK;
9) To load 'myview' into a load target of sports2000, execute the code generated using an ODBC or JDBC client like WinSQL, the Progress SQL Explorer Tool against the load target database.