SATTACH

SATTACH (Short Name: SATT)

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.

AS tablealias
Specifies an alias, or temporary name, for the foreign table.

tblname
Specifies the table in the foreign database to attach.

TEMPORARY
Allows you to create a Temporary Table 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 uniquely identify the rows in an attached table if no primary or unique keys are defined.

USING collist
If the foreign table has no primary or unique key, specifies the column(s) that uniquely identify the rows in the table.

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.

After you have exited R:BASE or disconnected the database to which the foreign table is attached, you don't 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 in R:BASE for Windows.

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.

SATTACH requires an owner password if one has been defined, or permission to create tables.

Notes:

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 over 18-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--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.

R:BASE 2000 (version 6.5++), Build:1.847xRT03 and Higher ...
Enhanced Syntax:

SATTACH [TableName [AS TableAlias] [USING ColumnList] [ALIAS AliasList]]

There are no parentheses around the aliaslist, just names separated by commas.

[AS TableAlias] specifies an alias, or temporary name, for the foreign table.

[USING ColumnList] If the foreign table has no primary or unique key, specifies the column(s) that uniquely identify the rows in the table.

[ALIAS AliasList] to specify alias names for columns.

Additional Notes:

Syntax has been extended to allow you to specify only the changed columns. For example, if you only need to alias the second column you can use ...alias ,,location,,

The "missing alias names" mean to use the default name.

If Qualkey defined as "using", it no longer prompts when you supplied already.

If there is a conflicting column name, you will be prompted with a new -ERROR- message (3069). It basically states that: "column xxx will not be attached" When a name is too long it should be obvious why it is asking for an alias.

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:

The Following scenarios will help you understand the use of enhanced SATTACH command in R:BASE 2000 (ver 6.5++), Build:1.847 and higher for Windows.

Scenario A:

When the foreign data source database includes the same table name as in your database, you need to attach the table with a different name.

Commands to Use: SCONNECT and SATTACH

Syntax:

SCONNECT DataSourceName IDENTIFIED BY UserID

Where:

DataSourceName is the foreign data source to connect

IDENTIFIED BY UserID specifies the user identifier. When access rights have been assigned using the GRANT command, you must enter an appropriate user identifier with the SCONNECT command unless permission has been granted to PUBLIC.

Syntax:

SATTACH TableName AS AliasTableName USING collist

Where:

TableName is the table to attach

AS AliasTableName to specifies an alias, or temporary name, for the foreign table.

USING collist, if the foreign table has no primary or unique key, specifies the column(s) that uniquely identify the rows in the table.

Example 01.

SET ERROR MESSAGE 2038 OFF
SDISCONNECT DataSourceName
SCONNECT DataSourceName IDENTIFIED BY NONE
SDETACH tCustomers NOCHECK
SATTACH Customers AS tCustomers USING CustomerID

OR

SET ERROR MESSAGE 2038 OFF
SDISCONNECT DataSourceName
SCONNECT DataSourceName IDENTIFIED BY password
SDETACH tCustomers NOCHECK
SATTACH Customers AS tCustomers USING ALL

Scenario B:

When the foreign data source database includes the same table name as in your database, as well as one of the columns in foreign table conflicts with the data type, you need to attach the table with different name as well as one of the columns as a different column name to avoid any conflict with data type.

Example 02:

SET ERROR MESSAGE 2038 OFF
SDISCONNECT DataSourceName
SCONNECT DataSourceName IDENTIFIED BY NONE
SDETACH tOrders NOCHECK
SATTACH Orders AS tOrders USING +
OrderID ALIAS +
OrderID, +
CustomerID, +
EmployeeID, +
OrderDate, +
RequiredDate, +
ShippedDate, +
ShipVia, +
tFreight, +
ShipName, +
ShipAddress, +
ShipCity, +
ShipRegion, +
ShipPostalCode, +
ShipCountry

Notice that the table Orders will be attached as tOrders, column OrderID will be used AS Primary Key and the column Freight will be an alias tFreight to avoid any conflict.

If you know the exact column sequence and the column you would like to alias, you can use the following simple version of the same command:

Example 02A:

SET ERROR MESSAGE 2038 OFF
SDISCONNECT DataSourceName
SCONNECT DataSourceName IDENTIFIED BY NONE
SDETACH tOrders NOCHECK
SATTACH Orders AS tOrders USING +
OrderID ALIAS ,,,,,,,,tFreight,,,,,,

Notice that the table Orders will be attached as tOrders, column OrderID will be used as the Primary Key and instead of defining individual columns, you can start the list of columns with comma, add comma for each column and the AliasColumn, such a tFreight, to be aliased, and then continue with a comma for each additional column.

In the above example we have eight (8) commas, then AliasColumn and an additional six (6) commas.

There are a total of fourteen (14) columns in tOrders table.

So, start with first comma, seven commas for first seven columns, AliasName, and then additional six commas for columns nine through fourteen.

Scenario C:

When the foreign data source database includes the table name with spaces, you need to attach the table with a different name.

Example 03:

SET ERROR MESSAGE 2038 OFF
SDISCONNECT DataSourceName
SCONNECT DataSourceName IDENTIFIED BY NONE
SDETACH tOrderDetails NOCHECK
SATTACH `Order Details` AS tOrderDetails USING +
OrderID, +
ProductID

Notice that the table `Order Details` with spaces is surrounded by IDQUOTES and will be attached as an alias table tOrderDetails, column OrderID will be used as Primary Key.