804.TXT
=====================================================================
ODBC Connectivity in R:BASE 5.1
=====================================================================
PRODUCT: R:BASE VERSION: 5.1 or Higher
=====================================================================
CATALOG: Programming In R:BASE AREA : General Information
=====================================================================
ODBC (Open DataBase Connectivity) is a standard set forth by
Microsoft. ODBC allows different database management systems (DBMS) to
speak with each other and share data. For the past two and a half
years, Microrim has supported the ODBC standard by producing and
supporting the R:BASE SQL Engine. The SQL Engine is an ODBC back end
that works with ODBC-compliant interfaces to provide connectivity with
R:BASE databases. Visual Basic, Access, and MS Query are examples of
ODBC front end interfaces. Microrim is proud to announce that R:BASE
version 5.1 will contain both ODBC front and back end capability.
What does this mean for R:BASE users? The ODBC connectivity provided
in R:BASE 5.1 opens a whole new avenue of database support. R:BASE 5.1
can have as many as five ODBC data sources attached to the same
database at the same time. For example, open your main R:BASE database
and then using ODBC, attach a second R:BASE database, and then an
Access database. After establishing the SQL data source connections,
you can attach tables from the other data sources and include the
tables in R:BASE queries, forms, views, reports, and labels.
Making the Connection
You connect to an ODBC data source using either the Utilities menu or
commands in the R> prompt window. From the Utilities menu, select
Connect SQL Data Source. The ODBC Administrator program starts and
displays a list of valid data sources. Select the data source to
establish a connection to and enter any necessary passwords. Or, from
the R> prompt window, use the SCONNECT command to establish a
connection to an ODBC data source. Following are the valid forms of
the SCONNECT command:
SCONNECT_Opens the ODBC "SQL Data Sources" dialog box.
SCONNECT data_source_name_Connects the specified data source without
opening the ODBC "SQL Data Sources" dialog box. The user name and
password are not prompted for_PUBLIC is assumed.
SCONNECT data_source_name IDENTIFIED BY login_name password_Connects
the specified data source with the user name and password.
After establishing a data source connection, you are ready to attach
specific tables from the connected data source. You can attach SQL
tables using either the Utilities menu or commands in the R> prompt
window. From the Utilities menu, select Attach SQL Database Tables.
Choose the appropriate data table. The dialog box also has an option
for attaching the table with an alias name, which is useful if the
foreign table has a name that is considered illegal in R:BASE or if
the foreign table name already exists in the main R:BASE database.
From the R> prompt window use one of the following syntax options:
SATTACH_Opens the "Attach Table(s)" dialog box.
SATTACH tablename_Attaches the specified foreign table.
SATTACH tablename AS Alias_table_Name_Attaches the specified foreign
table with an alias name.
ODBC requires a method for uniquely identifying rows in a table when
performing data updates. Foreign tables should have a primary key or
unique key defined to uniquely identify a row of data. If the foreign
table does not have either constraint type defined, then R:BASE opens
a dialog box with a list of columns in the foreign table during the
attach process. You must select the column or set of columns that
uniquely identifies a row in the table.
Working with Foreign Tables
After attaching the desired foreign tables, you can begin working with
them in R:BASE. Reports, forms, labels, and views can use attached
foreign tables in addition to regular R:BASE tables. Depending on the
access rights you have on the foreign table, you can perform inserts,
updates, and deletes. You can even modify the schema of a foreign
table or attached data source.
Modifying the schema of a foreign data source requires that the ODBC
back end engine process the command. For example, the following
command creates an index on an R:BASE table:
CREATE INDEX Ind1 ON tablename (column name)
To create an index on an attached foreign table, you must tell R:BASE
not to process the CREATE INDEX command, but rather pass it on to the
ODBC engine for processing. You do this using the SSQL command. Simply
precede the CREATE INDEX command with SSQL as follows:
SSQL CREATE INDEX Ind1 ON tablename (column name)
You should exercise caution when attempting schema modifications to
foreign tables. Whenever possible, use the native software package to
make schema modifications to foreign tables. For example, use
Microsoft Access to modify Access tables.
Severing the Connection
If you disconnect from a database that has attached foreign tables,
R:BASE remembers the attached foreign tables the next time R:BASE
connects to the same database. R:BASE does this by storing the foreign
table information in the system table SYS_SERVERS. To drop an attached
foreign table, select the Detach SQL Database Tables option from the
Utilities menu, or use the SDETACH command in the R> prompt window:
SDETACH tablename
To sever an ODBC data source connection, select the Disconnect SQL
Data Source option from the Utilities menu, or use the SDISCONNECT
command in the R> prompt window:
SDISCONNECT data_source_name
Disconnecting an ODBC data source, or server, does not detach foreign
tables.