November 26, 2001
==================================================================== >From the Edge: Using New SATTACH Command Section: Connecting a Foreign Data Source via ODBC Chapter: Running R:BASE Your Way! Platform: R:BASE 2000 (ver 6.5++) for Windows Build: 1.845xRT03 and Higher ... ==================================================================== The Open Database Connectivity (ODBC) allows R:BASE developer to write one application that has the ability to access data from databases created with different ODBC-compliant products. For example, you could write an application that would select data from SQL Server, Oracle, DB2, Informix, and R:BASE databases, separately or all at the same time, depending on which database drivers you have installed. To work with a foreign data source, you must first open a database in R:BASE, then you can SCONNECT the data source and SATTACH the table(s) with which you want to work. You can connect to as many as five other Open Database Connectivity (ODBC) data sources at one time. You can SATTACH as many tables as you like, but you can only use up to twenty in a single query or transaction. When R:BASE SATTACHes a foreign table, R:BASE only includes those columns in the table that have recognizable data types. Also, if the foreign tables have names that are illegal in R:BASE, or if a foreign table name is the same as one in the R:BASE database, R:BASE prompts you to specify an alias that will be used for the foreign table while that table is attached. Using R:BASE 2000 (version 6.5++) for Windows, Inline Patch-1, Build:1.845xRT03 and higher, now you CAN alias the columns with long names, column names with spaces or column names with reserved names, such as ZIP, DATE, NAME, etc. Syntax: SCONNECT datasource IDENTIFIED BY userid <password> 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 CONNECT command unless permission has been granted to PUBLIC. Optional <password> verifies the correct user identifier of the current user of the database. 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. Enjoy the ENHANCEMENTS in R:BASE 2000 (ver 6.5++), Inline Patch-1, to be released shortly! Download the PDF version of this article at: http://www.razzak.com/fte 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. ==================================-=============================
