R:BASE 11 Help

Zoom Window Out
Larger Text | Smaller Text
Hide Page Header
Show Expanding Text
Printable Version
Save Permalink URL

Navigation: Command Index > U

UNLOAD (Short Name: UNL)

Use the UNLOAD command to copy the data, structure, or data and structure of a database or table to a specified output device.

UNLOAD

Options

ALL

Unloads both the data and the structure.

AS ASCII

Unloads data in ASCII-delimited format. Use only with the UNLOAD DATA command.

AS CSV

Unloads data in a minimally quoted comma separated format. Each field will be separated by the current DELIMIT character (usually the comma). Fields that contain the current DELIMIT character will be enclosed in the current QUOTES character.

DATA

Unloads the table data. For a database unload, the data includes table data and system table data.

DELIMIT=value

Specifies a custom delimiter value for the ASCII or CSV unload formats. The value can be a character or the CHAR function.

FOR ACCESS

Unloads all current access rights in the database.

FOR AUTONUM

Unloads the AUTONUM column formula for all autonumbered database columns in the database.

FOR COMMENTS

Unloads all comments assigned to tables, views, and columns in the database.

FOR CONSTRAINTS

Unloads all primary key, foreign key, and unique key relational constraints in the database, based on their order of creation.

FOR DATABASE

Unloads the character settings, the CREATE SCHEMA command, database comment, and static variables.

FOR DBASE_TABLES

Unloads the structure for all attached dBASE tables. To unload the structure for a single dBASE table, use the UNLOAD STRUCTURE FOR tblname command.

FOR INDEXES

Unloads all indexes in the database.

FOR PROCEDURES

Unloads all defined stored procedures into individual files. A .RMD and .PRO file is created for each stored procedure.

FOR SERVER_TABLES

Unloads the structure for all ODBC attached Server tables. To unload the structure for a single Server table, use the UNLOAD STRUCTURE FOR tblname command.

FOR TABLES

Unloads the complete table structure for all tables in the database. The output includes the table definitions, last modification date/time stamp, relational constraints; based on their order of creation, access rights, autonumbered columns, comments, rules, and triggers. Wildcards can be used to unload the structure for multiple tables/views starting with the same letters (i.e UNLOAD STRUCTURE FOR TABLES CUST%).

FOR TABLEDEF

Unloads the table definition structure and last modification date/time stamp for all tables in the database. The output contains only the SQL command necessary to create all the tables, including SCONNECT and SATTACH commands for Server tables.

FOR tblview

Specifies a single table/view to unload the SQL command necessary to create a specific table/view. The output includes the table definition, last modification date/time stamp, relational constraints; based on their order of creation, access rights, autonumbered columns, comments, rules, and triggers.

FOR TRIGGERS

Unloads all triggers in the database.

FOR RULES

Unloads all rules in the database.

FOR VIEWS

Unloads the SQL command necessary to create all views. The output includes the view definition and the last modification date/time stamp,

NOSCHEMA

Omits the character settings and CREATE SCHEMA syntax from the beginning of the output.

ORDER BY clause

Sorts rows of data. For more information, see ORDER BY.

procname

Specifies the procedure name.

QUOTES=value

Specifies a custom quote character value for the ASCII or CSV unload formats. The value can be a character or the CHAR function.

STRUCTURE

Unloads the database structure, in the following order: schema, database comment, static variables, tables, views, constraints/indexes, users, privileges, autonumber definitions, comments, rules, stored procedure data, triggers, and database events.

STRUCTURE FOR tblview

Unloads the SQL commands necessary to create a specific table/view. The table output includes the table definition, last modification date/time stamp, relational constraints; based on their order of creation, access rights, autonumbered columns, comments, rules, and triggers. The view output includes the structure and the last modification date/time stamp.

TABLEDEF

Unloads the table/view definition structure. The output contains only the SQL command necessary to create the table/view and the last modification date/time stamp.

tblname

Specifies the table name to unload the data, structure, or both.

USING collist

Specifies the column(s) to use with the command. When using ASCII or CSV unloads, text may also be inserted.

UNLOAD DATA FOR Contact USING 'Contact Name:',ContFName,ContLName AS CSV

viewname

Specifies the view name to unload the structure and the last modification date/time stamp. Wildcard are supported to specify multiple views starting with the same letters (i.e UNLOAD STRUCTURE FOR VIEWS Sale%).

WHERE clause

Limits rows of data. For more information, see WHERE.

About the UNLOAD Command

Use UNLOAD to transfer tables or views from one database to another, or to backup a database. Forms, reports, labels, and stored procedures may also be unloaded.

The UNLOAD ALL and UNLOAD STRUCTURE commands require the database owner's user identifier if the database has had access rights granted with the GRANT command. R:BASE places the owner's user identifier and all the granted access rights in the file created by UNLOAD to ensure that the restored database file continues to be protected. Be sure to protect this file from unauthorized users.

The UNLOAD command creates a file with a .LOB extension for binary large objects, and the originating file that you specify for the data and/or structure. Your originating file can NOT have a .LOB file extension, otherwise, R:BASE will not be able to continue with the UNLOAD process.

The UNLOAD command unloads defined database events (ON CONNECT/PACK, ON ERROR, ON DISCONNECT/EXIT) if an UNLOAD ALL or UNLOAD STRUCTURE is performed.

Transferring Tables and Views

UNLOAD does not change the data or structure of the original database, and it does not unload computed column values unless you use the AS ASCII or AS CSV option.

You can also unload and transfer the data and structure of a view. If a table or view needed to define a view cannot be found, after the error message about the missing item, you will get another message to identify the view that R:BASE was trying to create. To input the data from an unloaded view into a table, create a table to match the view's structure and use the LOAD tblname FROM filespec command, since views do not contain data. The UNLOAD command is useful when you want to create a file to transfer data to another database as a table. Within the unloaded table and view structure output is the RESET command, which preserves the last modification date/time stamp.

To be able to reliably load data back into R:BASE from an unloaded file:

•SET NULL to -0- (the R:BASE default) before unloading data.

•Do not set a special character to the same setting as another special character.

•Precede UNLOAD with an OUTPUT command to direct the data to be unloaded to a file. You can edit the unloaded file with any ASCII text editor.

•Ensure the IDQUOTES setting for the unloaded output is identical to the destination database.

•Use the DELIMIT and QUOTES options to specify custom values.

To transfer unloaded information back into R:BASE, use the RUN, RESTORE, or GATEWAY commands, if transferring only data. For example, you can use the UNLOAD DATA command to unload data, then the RUN command to transfer the data to a different database.

If the UNLOAD AS CSV syntax has been used you can use the LOAD AS CSV command to restore the data.

Backing up a Database

R:BASE unloads data in ASCII delimited format: values are separated by the current delimiter and all text strings are enclosed in quotation marks. UNLOAD creates a file containing commands that set special characters, such as commas and quotation marks. The setting of the SET WIDTH condition affects the width of data lines in the unloaded file.

If the database has columns defined as binary or text large objects, then UNLOAD creates two files, one file containing the R:BASE commands, and a second file (with a .LOB extension) containing the large object data. Both files are needed to transfer the information back into R:BASE. Your originating file can NOT have a .LOB file extension, otherwise, R:BASE will not be able to continue with the UNLOAD process.

Note: The unloaded file cannot span multiple floppy disks.

The UNLOAD STRUCTURE or UNLOAD ALL commands write all the commands necessary to define the database or table, starting with CREATE SCHEMA AUTHORIZATION dbname near the beginning of the file. Before you input or restore the data or structure into a different database, you can use a text editor to change the database name in the unloaded file. If you use UNLOAD STRUCTURE, you can copy the table structure after you change the database name in the unloaded file.

Unloading Temporary Tables

If there are temporary tables/views in a database when an UNLOAD ALL or UNLOAD STRUCTURE is performed, the temporary table/view definitions will be unloaded.

Use the UNLOAD tblname command to backup individual temporary tables created when STATICDB is set on-which activates a read-only schema mode. When UNLOAD is used to backup temporary tables, it generates a SET STATICDB OFF command to be executed prior to the CREATE SCHEMA command.

Note:

•UNLOAD ALL and UNLOAD STRUCTURE will include the definitions for temporary tables/views.

•Wildcards can be used to unload the structure for multiple tables/views starting with the same letters (i.e UNLOAD STRUCTURE FOR TABLES CUST%).

Unloading Computed Columns

When the original computed values must be preserved with an UNLOAD and LOAD, the NOCALC setting must be set ON. The UNLOAD output generated will also have a NOCALC after the LOAD command so that the original values are loaded.

Examples

Example 01:

The following command lines unload only the data from the product table to a file named MYFILE.DBS. The data is in ASCII delimited format. The OUTPUT SCREEN command redirects the output back to the screen and closes the file.

OUTPUT myfile.dbs

UNLOAD DATA FOR product AS ASCII

OUTPUT SCREEN

Example 02:

In the example below the a file will be created that contains Comma Separated Values with no headings and no page breaks.

SET HEADINGS OFF

SET LINES 0

SET WIDTH 200

OUTPUT myfile.csv

UNLOAD DATA FOR Employees AS CSV

OUTPUT SCREEN

The commands above might create the file below. Notice that Jane Dough has Quotes surrounding her address. This is because the text contains an embedded comma.

Robert,Smith,123 Main St,Denver,CO,Support

Jane,Dough,'98 Folk St, Apt 1',Pittsburgh,PA,Sales

Matt,Follows,14 Arrowhead Ln,Portsmouth,RI,Services

Example 03:

The following command unloads the complete structure for the Contact table within the RRBYW20 sample database.

R>UNLOAD STRUCTURE FOR Contact

SET QUOTES=NULL

SET QUOTES='

SET DELIMIT=NULL

SET DELIMIT=','

SET LINEEND=NULL

SET LINEEND='^'

SET SEMI=NULL

SET SEMI=';'

SET PLUS=NULL

SET PLUS='+'

SET SINGLE=NULL

SET SINGLE='_'

SET MANY=NULL

SET MANY='%'

SET IDQUOTES=NULL

SET IDQUOTES='`'

SET CURRENCY '$' PREF 2 B

DISCONNECT

SET STATICDB OFF

SET ROWLOCKS ON

SET FASTLOCK OFF

CREATE SCHEMA AUTHOR RRBYW20 NONE

CREATE TABLE `Contact` +

(`CustID` INTEGER, +

`ContID` INTEGER NOT NULL +

('Value for column ContID cannot be null') , +

`ContFName` TEXT (10), +

`ContLName` TEXT (16), +

`ContPhone` TEXT (14), +

`ContFax` TEXT (14), +

`ContCell` TEXT (14), +

`ContPager` TEXT (14), +

`ContEMail` TEXT (60), +

`ContInfo` NOTE, +

`ContPhoto` LONG VARBIT, +

`LastContactDate` DATE)

RESET Contact LAST_MOD TO '10/06/2018 21:24:51'

ALTER TABLE `Contact` ADD PRIMARY KEY (`ContID` ) +

('Values for rows in Contact must be unique',+

'Cannot delete - values exist in another table',+

'Cannot update - values exist in another table')

ALTER TABLE `Contact` ADD FOREIGN KEY +

( `CustID` )+

REFERENCES `Customer`

AUTONUM `ContID` IN `Contact` USING 1047. 1. NONUM

COMMENT ON `CustID` IN `Contact` IS 'Customer Identification Number'

COMMENT ON `ContID` IN `Contact` IS 'Contact Automated ID'

COMMENT ON `ContFName` IN `Contact` IS 'Customer Contact First Name'

COMMENT ON `ContLName` IN `Contact` IS 'Customer Contact Last Name'

COMMENT ON `ContPhone` IN `Contact` IS 'Customer Contact Phone Number'

COMMENT ON `ContFax` IN `Contact` IS 'Customer Contact Fax Number'

COMMENT ON `ContCell` IN `Contact` IS 'Customer Contact Cellular Number'

COMMENT ON `ContPager` IN `Contact` IS 'Customer Contact Pager Number'

COMMENT ON `ContEMail` IN `Contact` IS 'Customer Contact E-Mail Address'

COMMENT ON `ContInfo` IN `Contact` IS 'Customer Contact Notes'

COMMENT ON `ContPhoto` IN `Contact` IS 'Customer Contact Photo'

COMMENT ON `LastContactDate` IN `Contact` IS 'Last Contact Date'

COMMENT ON TABLE `Contact` IS 'Customer Contact Information'

Example 04:

The following command unloads the structure for the Contact table within the RRBYW20 sample database, without any database schema at the beginning.

R>UNLOAD STRUCTURE FOR contact NOSCHEMA