SAS/ACCESS Interface to ODBC

Introduction to SAS/ACCESS Interface to ODBC

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.

ODBC uses SQL syntax for queries and statement execution, or for statements that are executed as commands. However, all databases that support ODBC are not necessarily SQL databases. For example, many databases do not have system tables. Also, the term table can describe a variety of items-including a file, a part of a file, a group of files, a typical SQL table, generated data, or any potential source of data. This is an important distinction. All ODBC data sources respond to a base set of SQL statements such as SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP in their simplest forms. However, some databases do not support other statements and more complex forms of SQL statements.
The ODBC standard allows for various levels of conformance that is generally categorized as low, medium, and high. As previously mentioned, the level of SQL syntax that is supported varies. Also, some driver might not support many programming interfaces. SAS/ACCESS Interface to ODBC works with API calls that conform to the lowest level of ODBC compliance, Level 1. However, it does use some Level 2 API calls if they are available.

SAS programmers or end users must make sure that their particular ODBC driver supports the SQL syntax to be used. If the driver supports a higher level of API conformance, some advanced features are available through the PROC SQL CONNECT statement and special queries that SAS/ACCESS Interface to ODBC supports. For more information, see Special Catalog Queries.
The ODBC manager and drivers return standard operation states and custom text for any warnings or errors. The state variables and their associated text are available through the SAS SYSDBRC and SYSDBMSG macro variables.

Top of Page