SAS/ACCESS Software for Relational Databases: Reference

Overview of ODBC

Open database connectivity (ODBC) standards provide a common interface to a variety of databases, including AS/400, dBASE, Microsoft Access, ORACLE, Paradox, and SQL Server databases. Specifically, ODBC standards define application programming interfaces (APIs) that enable an application to access a database if both the application and the database adhere to the specification. ODBC also provides a mechanism to enable dynamic selection of a database that an application is accessing, so end users have the flexibility of selecting databases other than those that are specified by the application developer.

The basic components and features of ODBC include the following:

ODBC functionality is provided by three components: the client interface, the ODBC driver manager, and the ODBC driver. SAS Institute provides the SAS/ACCESS interface to ODBC, which is the client interface. For PC platforms, Microsoft developed the ODBC Administrator, which is used from the Windows Control Panel to perform software administration and maintenance activities. The ODBC driver manager also manages the interaction between the client interface and the ODBC driver. Other software vendors provide the ODBC manager with their ODBC drivers, which process requests for external data. These drivers also either directly manipulate and retrieve the data or they pass the request to a native library for the specific DBMS. The ODBC interface to the SAS System is illustrated in .
The ODBC Interface to the SAS System
The ODBC administrator defines a data source as the data that is used in an application and the operating system and network that are used to access the data. You create a data source by using the ODBC administrator in the Windows Control Panel, selecting an ODBC driver, and providing the information (for example, data source name, userid, password, description, server name) required by the driver to make a connection to the desired data. The driver displays dialog boxes in which you enter this information. In operation, a client application usually requests a connection to a named data source, not just to a specific ODBC driver. In a UNIX environment such as HP-UX, AIX, or Solaris, no ODBC Administrator exists. During an install, the driver creates a generic .odbc.ini file that can be edited to create your own data source names.
For more information on customizing your SAS application, refer to your vendor-specific documentation.
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, and the term tables may be used to describe a variety of items, including a file, parts of files, groups of files, typical SQL tables, generated data, or any potential source of data. This distinction is important because although 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, some databases do not support other statements and more complex forms of the SQL statements.
The ODBC standard allows for various levels of conformance, generally categorized as low, medium, and high. As mentioned previously, the level of SQL syntax that is supported varies. There are also many programming interfaces that might not be supported by some drivers. The SAS/ACCESS Interface to ODBC is designed to work with API calls that conform to the lowest level of ODBC compliance, Level 1. However, the SAS/ACCESS Interface to ODBC does use some Level 2 API calls if they are available.
However, it is the responsibility of the SAS programmer or end user to ensure that the SQL syntax that is used is supported by the particular driver that is being used. If the ODBC driver supports a higher level of API conformance, some of the advanced features are made available through the PROC SQL CONNECT statement and special queries supported by the SAS/ACCESS Interface to ODBC. For more information, see Special ODBC 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 system macro variables SYSDBRC and SYSDBMSG.
The SAS/ACCESS interface supports the ODBC 3.0 specifications that are part of the ODBC drivers that are provided with Microsoft Office 97. There are three types of data source names that can be specified. A User DSN is specific to an individual user and is available only to the user who creates it. A System DSN can be used by anyone who has permission to access the data source. A File DSN can be shared among users even though it is created locally. Since it is file based, it contains all the information that is required to connect to a data source.
In addition to the information provided in this document, you need to refer to the documentation provided with your ODBC driver. Most ODBC drivers supply a help file that you can access online. In the Windows Control Panel, double click the ODBC icon to start the ODBC Administrator application. Within the ODBC Data Source Administrator, double click the data source name from the User DSN, System DSN, or File DSN tabbed dialog. This will bring up the ODBC driver setup dialog box for this specific ODBC driver. Clicking the Help button will provide information you will need to configure the ODBC data source for this driver.

Chapter Contents
Previous
Next
Top of Page