Previous Page | Next Page

SAS/ACCESS Interface to ODBC

Introduction to SAS/ACCESS Interface to ODBC


Overview

This section describes SAS/ACCESS Interface to ODBC. For a list of SAS/ACCESS features that are available in this interface, see SAS/ACCESS Interface to ODBC: Supported Features.


ODBC Concepts

Open database connectivity (ODBC) standards provide a common interface to a variety of data sources. The goal of ODBC is to enable access to data from any application, regardless of which DBMS handles the data. ODBC accomplishes this by inserting a middle layer--consisting of an ODBC driver manager and an ODBC driver--between an application and the target DBMS. The purpose of this layer is to translate application data queries into commands that the DBMS understands. Specifically, ODBC standards define application programming interfaces (APIs) that enable applications such as SAS software to access a database. For all of this to work, both the application and the DBMS must be ODBC-compliant, meaning the application must be able to issue ODBC commands and the DBMS must be able to respond to these.

Here are the basic components and features of ODBC.

Three components provide ODBC functionality: the client interface, the ODBC driver manager, and the ODBC driver for the data source with which you want to work, as shown below.

The ODBC Interface to SAS

[ODBC Interface to SAS]

For PC and UNIX environments, SAS provides SAS/ACCESS Interface to ODBC as the client interface. Consisting of the ODBC driver manager and the ODBC driver, the client setup with which SAS/ACCESS Interface to ODBC works is quite different between the two platforms.


ODBC on a PC Platform

On the PC side, the Microsoft ODBC Data Source Administrator is the ODBC driver manager. You can open the ODBC Data Source Administrator from the Windows control panel. Working through a series of dialog boxes, you can create an ODBC data source name (DSN) by selecting a particular ODBC driver for the database with which you want to work from the list of available drivers. You can then provide specific connection information for the database that the specific driver can access.

USER DSN

specific to an individual user. It is available only to the user who creates it.

SYSTEM DSN

not specific to an individual user. Anyone with permission to access the data source can use it.

FILE DSN

not specific to an individual user. It can be shared among users even though it is created locally. Because this DSN is file-based, it contains all information that is required to connect to a data source.

You can create multiple DSNs in this way and then reference them in your PC-based SAS/ACCESS Interface to ODBC code.

When you use the ODBC Data Source Administrator on the PC to create your ODBC data sources, the ODBC drivers for the particular databases from which you want to enable access to data are often in the list of available drivers, especially those for the more common databases. If the ODBC driver you want is not listed, you must work to obtain one.


ODBC on a UNIX Platform

ODBC on UNIX works a bit differently. The ODBC driver manager and ODBC drivers on the PC are available by default, so you need only plug them in. Because these components are not generally available on UNIX, you must instead work with third-party vendors to obtain them.

When you submit SAS/ACCESS Interface to ODBC code, SAS looks first for an ODBC driver manager. It checks the directories that are listed in such environment variables settings as LD_LIBRARY_PATH, LIBPATH, or SHLIB_PATH, depending on your UNIX platform. It uses the first ODBC driver manager that it finds.

The ODBC driver manager then checks .INI files--either a stand-alone ODBC.INI file, or a combination of ODBC.INI and ODBCINST.INI files--for the DSNs that you specified in your code. To make sure that the intended .INI files are referenced, you can use such environment variables settings as ODBCINI or ODBCSYSINI, depending on how your .INI files are set up. You can set up global .INI files for all your users, or you can set up .INI files for single users or groups of users. This is similar to using the ODBC Data Source Administrator to create either SYSTEM or USER DSNs for PC platforms. One or more .INI files include a section for each DSN, and each section includes specific connection information for each data source from which you ultimately want to enable access to data. Some ODBC driver vendors provide tools with which you can build one or more of your .INI files. However, editing a sample generic .INI file that is provided with the ODBC driver is often done manually.

Most database vendors--such as Sybase, Oracle, or DB2--include ODBC drivers for UNIX platforms. However, to use SAS/ACCESS Interface to ODBC, you must pair a UNIX-based ODBC driver manager with your UNIX-based ODBC driver. Freeware ODBC driver managers for UNIX such as unixODBC are generally available for download. Another alternative is to obtain the required ODBC client components for UNIX platforms from third-party vendors who market both ODBC drivers for various databases and an ODBC driver manager that works with these drivers. To use SAS/ACCESS Interface to ODBC, you can select any ODBC client solution that you want as long as it is ODBC-compliant.


ODBC for PC and UNIX Platforms

These concepts are common across both PC and UNIX platforms.

Previous Page | Next Page | Top of Page