Friday, March 16, 2007
From the Edge: Using Enhanced UNLOAD STRUCTURE Command in R:BASE
Section: Enhanced Commands
Chapter: Running R:BASE Your Way!
Platform: R:BASE 7.6 and Turbo V-8 for Windows
Builds: R:BASE 7.6 (7.6.1.30316 or higher ...)
R:BASE C/S:I 7.6 for Windows (Build: 7.6.1.30316 or higher)
R:BASE V-8 Turbo (8.0.15.30316 or higher ...)
Traditionally, when you use the UNLOAD STRUCTURE or UNLOAD ALL
commands, R:BASE engine generates a bunch of R:BASE specific code,
including CHARacter Settings, DISCONNECT statement, STATICDB,
ROWLOCK, FASTLOCK settings, CREATE SCHEMA, CREATE TABLE and so on ...
Then, often we have to edit the text file, clean up the extra stuff
and only leave the SQL commands that are necessary to build the table
or view.
Practically, if you are using the UNLOAD STRUCTURE FOR ... command,
it's because you want to transfer the TABLE or VIEW to a different
database that already exists and that usually has a different name.
All you really want is to transfer the table or view structure.
Did you know that using the enhanced UNLOAD command in the latest
versions of R:BASE (see supported versions above), now you can UNLOAD
the STRUCTURE that would generate only the SQL commands necessary to
rebuild the table or view in question (with all rights, comments,
rules, etc), but none of the extra stuff (CREATE SCHEMA or R:BASE
settings)? That way, you could simply RUN the resulting file without
having to edit it.
Traditional Command: UNLOAD STRUCTURE FOR tablename
Output includes:
Character Settings
DISCONNECT Statement
Define Settings for STATICDB, ROWLOCKS and FASTLOCK
CREATE Database ... statement
CREATE TABLE ... statement
ALTER TABLE statements to ADD PRIMARY KEY, etc ...
CREATE INDEX statements to create INDEXes
COMMENT ON statements for each column
COMMENT ON statement for table
Traditional Command: UNLOAD ALL FOR tablename
Output includes:
Character Settings
DISCONNECT Statement
Define Settings for STATICDB, ROWLOCKS and FASTLOCK
CREATE Database ... statement
CREATE TABLE ... statement
Define Settings for CASE, AUTOSKIP, REVERSE, BELL, NULL, DATE YEAR,
DATE CENTURY, DATE SEQUENCE, TIME SEQUENCE, TOLERANCE, and ZERO
LOAD statement to load data
Define Settings for DATE FORMAT, TIME FORMAT, DATE SEQUENCE,
TIME SEQUENCE
ALTER TABLE statements to ADD PRIMARY KEY, etc ...
CREATE INDEX statements to create INDEXes
COMMENT ON statements for each column
COMMENT ON statement for table
Enhanced Command:
-- To unload Table Definition only
UNLOAD STRUCTURE FOR TABLE tablename
-- To unload Table Definition with Constraints, Indexes and Comments
UNLOAD STRUCTURE FOR tablename NOSCHEMA
Examples:
Example 01:
-- To unload table structure (Traditional)
-- See Appendix A for sample output (Page 6)
CONNECT RRBYW10
OUTPUT Customer.STR
UNLOAD STRUCTURE FOR Customer
OUTPUT SCREEN
Example 02:
-- To unload table definition for one table
-- See Appendix B for sample output (Page 8)
CONNECT RRBYW10
OUTPUT Customer.STR
UNLOAD STRUCTURE FOR TABLE Customer
OUTPUT SCREEN
Example 03:
-- To unload table definition with Constraints, Indexes and Comments
-- See Appendix C for sample output (Page 9)
CONNECT RRBYW10
OUTPUT Customer.STR
UNLOAD STRUCTURE FOR Customer NOSCHEMA
OUTPUT SCREEN
Example 04:
-- To unload table definition, Constraints, Indexes, Comments and data
-- See Appendix D for sample output (Page 11)
CONNECT RRBYW10
OUTPUT Customer.ALL
UNLOAD ALL FOR Customer
OUTPUT SCREEN
Example 05:
-- To unload table definitions for all tables
-- See Appendix E for sample output (Page 14)
CONNECT RRBYW10
OUTPUT AllTables.STR
UNLOAD STRUCTURE FOR TABLEDEF
OUTPUT SCREEN
Example 06:
-- To unload table definitions, Constraints, Indexes and Comments for
-- all tables
-- See Appendix F for sample output (Page 19)
CONNECT RRBYW10
OUTPUT AllTables.STR
UNLOAD STRUCTURE FOR TABLES
OUTPUT SCREEN
Example 07:
-- To unload multiple table definitions, Constraints, Indexes and
-- Comments using wild card
-- See Appendix G for sample output (Page 31)
CONNECT RRBYW10
OUTPUT MulipleTables.STR
UNLOAD STRUCTURE FOR TABLES CO%T
-- CompUsed, Contact and ContactCallNotes
Example 08:
-- To unload definition of a given View
-- See Appendix H for sample output (Page 34)
CONNECT RRBYW10
OUTPUT CustomerView.VIE
UNLOAD STRUCTURE FOR VIEW CustomerView
OUTPUT SCREEN
CONNECT RRBYW10
OUTPUT CustomerView.VIE
UNLOAD STRUCTURE FOR CustomerView NOSCHEMA
OUTPUT SCREEN
Example 09:
-- To unload definition of a given View (Traditional)
-- See Appendix I for sample output (Page 35)
CONNECT RRBYW10
OUTPUT CustomerView.VIE
UNLOAD STRUCTURE FOR CustomerView
OUTPUT SCREEN
Example 10:
-- To unload definition of all views
-- See Appendix J for sample output (Page 36)
CONNECT RRBYW10
OUTPUT AllViews.VIE
UNLOAD STRUCTURE FOR VIEWS
OUTPUT SCREEN
Example 11:
-- To unload definition of multiple views using wild card
-- See Appendix K for sample output (Page 40)
CONNECT RRBYW10
OUTPUT MulipleViews.VIE
UNLOAD STRUCTURE FOR VIEWS New%
OUTPUT SCREEN
-- This will unload structure for all views starting with
-- "New", such as:
-- NewContact, NewCustomer, NewEmployee, NewInvoiceDetails, and
-- NewInvoiceHeader
You may also use the Database Explorer to UNLOAD STRUCTURE using
these enhanced commands.
A complete PDF document (40 pages) with all details is also
available at:
From The Edge: http://www.razzak.com/fte/
Enjoy and make sure to have fun!
Very Best R:egards,
Razzak.
--- RBASE-L
=======================3D=======================3
D=
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
=======================3D=======================3
D=
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
=======================3D=======================3
D=
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
=======================3D=======================3
D=
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
=======================3D=======================3
D=
