December 13, 2001
===================================================================== >From the Edge: Understanding SATTACH Command Chapter: Connecting to Foreign Data Sources Section: Running R:BASE Your Way! Platform: R:BASE 2000 (ver 6.5++) DOS/Windows Build: 1.847xRT03 and Higher ... ===================================================================== At the request of the CURRENT R:BASE Community, including Developers, End-Users, Corporations, Educational and Government Customers, a major step was taken at the engine level to include the feature of connecting foreign data sources with longer table names and/or column names with spaces, and also column names which conflict with the name or data types used in your default R:BASE database! Among many new commands, features and bug fixes, the upcoming inline patch-1 for R:BASE 2000 (ver 6.5++) will also enhance functionality when connecting to foreign data sources, such as SQL Server, Informix, DB2 and Oracle. 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. ... Sounds like fun! =========== 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. Summary: Using R:BASE 2000 (ver 6.5++), Build:1.847xRT03 and higher for Windows, you can attach foreign tables and columns as alias tables and columns to avoid any conflict of similar table name and/or similar column names with different data types and still maintain the integrity of Industrial-Strength, Multi-User, Multi-Platfor and a True-Relational Database Management System! Download the PDF version of this article at: http://www.razzak.com/fte Enjoy! Very Best Regards, Razzak. ===================================-============================ Official R:BASE List Server: mailto:[EMAIL PROTECTED] RBTI Events/Training: http://www.rbase2000.com/events R:DCC Members: http://www.rbase2000.com/rdcc ================================================================ R:BASE, Oterro & R:Tango are registered trademarks of RBTI. ==================================-============================= ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l
