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

Overview

Open database connectivity (ODBC) standards provide a common interface to a variety of data sources, including dBASE, Microsoft Access, Microsoft SQL Server, Oracle, and Paradox. 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. This means that 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

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. Through a series of dialog boxes, you can create an ODBC data source name (DSN). You can select 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 that 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. To use SAS/ACCESS Interface to ODBC, pair an ODBC driver manager that is based in UNIX with your ODBC driver that is also based in UNIX.. 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.

Key Considerations When Using ODBC Drivers and SAS on UNIX

SAS/ACCESS Interface to ODBC on UNIX allows SAS customers to surface data from a wide variety of external data sources. Many customers using SAS on UNIX have had success using SAS/ACCESS Interface to ODBC with their ODBC client setups. These setups consist of an ODBC driver manager and ODBC drivers for the specific data sources to which customers need access. Critical to this success are the quality and completeness of third-party ODBC client components on UNIX that customers have chosen to use.

To maximize your chances of success, your ODBC driver must comply with the ODBC 3.5 (or later) specification. It must also but support the call sequences that SAS/ACCESS Interface to ODBC sends to the driver. Specifically, your ODBC driver manager and ODBC driver must support these ODBC calls:

SQLAllocConnect	SQLFreeStmt
SQLAllocEnv	SQLGetConnectAttr
SQLAllocHandle	SQLGetConnectOption
SQLAllocStmt	SQLGetCursorName
SQLBindCol	SQLGetDiagRec
SQLBindParameter	SQLGetFunctions
SQLBulkOperations	SQLGetInfo
SQLCancel	SQLGetStmtAttr
SQLColAttribute	SQLGetStmtOption
SQLColumnPrivileges	SQLGetTypeInfo
SQLColumns	SQLMoreResults
SQLConnect	SQLNumResultCols
SQLDataSources	SQLPrepare
SQLDescribeCol	SQLPrepareW
SQLDescribeColW	SQLPrimaryKeys
SQLDisconnect	SQLProcedureColumns
SQLDriverConnect	SQLProcedures
SQLEndTran	SQLRowCount
SQLExecDirect	SQLSetConnectAttr
SQLExecDirectW	SQLSetConnectOption
SQLExecute	SQLSetEnvAttr
SQLExtendedFetch	SQLSetPos
SQLFetch	SQLSetStmtAttr
SQLFetchScroll	SQLSetStmtOption
SQLForeignKeys	SQLSpecialColumns
SQLFreeConnect	SQLStatistics
SQLFreeEnv	SQLTablePrivileges
SQLFreeHandle	SQLTables

SAS/ACCESS Interface to ODBC sends a sequence of ODBC calls to the ODBC driver that you have chosen. The types and sequence in which these calls are made are compliant with the ODBC specification. If your ODBC driver fails to return the correct result or fails to work with SAS/ACCESS Interface to ODBC, here are your options.

Make sure that you are running the current versions of your ODBC client components.
Try to connect using a query tool that is not SAS. Most third-party ODBC driver and driver manager sources include such a query tool with their offerings. However, keep in mind that in some cases you might be able to connect using a query tool (that is not SAS) but not with SAS/ACCESS Interface to ODBC. This is because SAS calls might make a wider range of ODBC calls than an ODBC query tool that is not SAS would make.
SAS Technical Support offers additional tools that can help you identify the root of your ODBC-related client problems and subsequently debug them.
You can address some ODBC client issues by using certain SAS/ACCESS Interface to ODBC options or alternative engines to SAS/ACCESS Interface to ODBC.
Once you have determined that your ODBC client issues are not related to SAS, you need to report your debugging results to your ODBC client providers. If you received your ODBC client components from a commercial ODBC driver vendor, you can work through that vendor’s technical support. If you use freeware or open-source ODBC client components—where formal technical support is not always available—your only recourse might be to communicate with the freeware user community.

SAS has not validated all ODBC drivers on the market and therefore makes no claims of certification or support.