Use the SATTACH command to attach a specified table from a foreign database to a connected R:BASE database.
Options
ALIAS AliasList
To specify alias names for columns. The alias list is separated by commas. Only the changed column names can be specified in the alias list, with other column name to be retained as is left blank.
AS tablealias
Specifies an alias, or temporary name, for the foreign table. A table alias is sometimes required when attaching foreign data sources that do not follow the same table name restrictions as R:BASE.
tblname
Specifies the table in the foreign database to attach.
TEMPORARY
Allows you to create a temporary name with the SATTACH command. The temporary tables will disappear when the database is disconnected. NOTE: Any changes made to the temporary table will not be reflected upon the original SQL data source.
USING ALL
Specifies all columns that uniquely identify the rows in an attached table if no primary or unique keys are defined. With USING ALL, the QUALCOLS setting is ignored to determine the number of columns to identify the rows.
When performing a direct UPDATE to the foreign table, the USING ALL approach is the slowest in processing, which means that to qualify a row for updating, all of the column values must match. Rather, if a primary key exists, specify the column with USING PrimaryKeyColName instead, as this way, only the primary key value must match (which is all that should be needed). When updating a row on the foreign table, R:BASE must count how many rows match that row, and there should only be one matching row.
USING collist
If the foreign table has no primary or unique key, specify the column(s) that uniquely identify the rows in the table. The primary key should be specified as the collist value. The collist is not limited to a single column, but the more columns that are specified, then those column values must also match.
USING ONLY collist
Specifies that only the columns listed will be attached. When using the ONLY option the word "ONLY" must immediately follow the word "USING". R:BASE will determine the key columns by querying the special columns of source table, to find primary key or unique key columns. If the query fails, then all columns will be used.
WHERE clause
Limits rows of data. For more information, see the WHERE Clause.
About the SATTACH Command
Before you can attach a foreign data source table, an R:BASE database must be connected. Also, your workstation must be connected to the data source.
If you use SATTACH without the tblname option, R:BASE displays the "Attach Table(s)" dialog box with names of tables in the data source. You can then select a table to attach.
When attaching external tables by selecting "Utilities" > "Attach SQL Database Tables" from the menu bar, or using the SATTACH command (without the "USING ALL/collist" keywords), the QUALOCOLS setting is used to assign what columns uniquely identifies a row. If a primary key or unique key was not found for the table being SATTACHed, and the USING collist clause was not used to specify what columns uniquely identifies a row, then R:BASE assigns primary and unique key qualkeys for the attached table. R:BASE assigns a set of columns to identify the rows starting with the first column of the table. The number of columns used is limited by the value for QUALCOLS. The (CVAL('QUALKEYS')) function may be used to capture the columns assigned as a QualKeys for the current database. The (CVAL('QUALKEY TABLES')) function may be used to capture the tables assigned with QualKey columns.
After you have exited R:BASE or disconnected the database to which the foreign table is attached, you do not need to reconnect to the table's data source when you open the database again. The data source is connected when you use the attached table. The data source table remains attached until you detach it with the SDETACH command, or use the Utilities: Detach SQL Database Table menu option.
When a foreign table is attached, R:BASE writes a table description that identifies the table as a data source table and names its data source. Use the LIST TABLES tblname command to review table descriptions.
Notes:
•SATTACH requires an owner password if one has been defined, or permission to create tables.
•When you attach a foreign table, R:BASE only includes the columns with legal names. For example, R:BASE does not include columns that have spaces in the name, or column names that exceed the character limit for the R:BASE version installed. Table and column names are limited to 128 characters.
•When you attach a foreign table and select the columns that uniquely identify its rows in the "Select Column Set" dialog box, do not select columns that have LOB data types, as unpredictable results might occur.
•When running applications that connect to foreign data sources, you should always disconnect from the R:BASE database before running the application again.
•When using the keyword "ONLY", to limit the columns attached with a table, the ability to SATTACH temporary tables and column alias names is supported.
Notes for [Alias] Parameters:
•Syntax has been extended to specify only the changed columns. For example, if you only need to alias the second column out of four columns you can use ... ALIAS ,,location,,
•Any missing alias names will use the default name.
•If there is a conflicting column name, a warning will be displayed you will be prompted for a new column name.
•If the name conflicts with another name then you get the error message first explaining the conflict, then the dialog box.
•If no qualkey is specified, automatic qualkeys will be assigned based on information from the ODBC source
Examples:
Example 01:
Attaches a foreign data source table using an alias table name
SATTACH CustomerDetails AS tCustomerDetails
Example 02:
Attaches a foreign data source table using an alias table name, and specifies the unique column name for the source table
SATTACH CustomerDetails AS tCustomerDetails USING CustomerID
Example 03:
Attaches a foreign data source table using alias names for the table and columns, and specifies the unique column name for the source table
SATTACH Orders AS tOrders USING +
OrderID ALIAS +
OrderID, +
CustomerID, +
EmployeeID, +
OrderDate, +
RequiredDate, +
ShippedDate, +
ShipVia, +
tFreight, +
ShipName, +
ShipAddress, +
ShipCity, +
ShipRegion, +
ShipPostalCode, +
ShipCountry
Example 04:
Attaches a foreign data source table using alias names for the table and only the Freight column, and specifies the unique column name for the source table
SATTACH Orders AS tOrders USING +
OrderID ALIAS ,,,,,,,,tFreight,,,,,,
Example 05:
Attaches a foreign data source table using an alias name for the table which contains spaces, and specifies only two columns to be included. The table `Order Details` is surrounded by IDQUOTES.
SATTACH `Order Details` AS tOrderDetails USING ONLY +
OrderID, ProductID
Example 06:
Attaches a foreign data source table using an alias name for the table, and specifies a WHERE clause for limited results
SATTACH Artists AS tArtists WHERE ALastName = 'Ford'
