SAS/ACCESS Interface to ODBC

SQL Pass-Through Facility Specifics for ODBC

For general information about this feature, see Overview of the SQL Pass-Through Facility. ODBC examples are available.

Here are the SQL pass-through facility specifics for the ODBC interface.

The dbms-name is ODBC.
The CONNECT statement is required.
PROC SQL supports multiple connections to ODBC. If you use multiple simultaneous connections, you must use the alias argument to identify the different connections. If you do not specify an alias, the default odbc alias is used. The functionality of multiple connections to the same ODBC data source might be limited by the particular data source driver.
The CONNECT statement database-connection-arguments are identical to its LIBNAME connection-options. Not all ODBC drivers support all of these arguments. See your driver documentation for more information.
On some DBMSs, the DBMS-SQL-query argument can be a DBMS-specific SQL EXECUTE statement that executes a DBMS stored procedure. However, if the stored procedure contains more than one query, only the first query is processed.
These options are available with the CONNECT statement. For information, see the LIBNAME Statement section.

AUTOCOMMIT=

CURSOR_TYPE=

KEYSET_SIZE=

QUERY_TIMEOUT=

READBUFF=

READ_ISOLATION_LEVEL=

TRACE=

TRACEFILE=

USE_ODBC_CL=

UTILCONN_TRANSIENT=

These examples use ODBC to connect to a data source that is configured under the data source name User's Data using the alias USER1. The first example uses the connection method that is guaranteed to be present at the lowest level of ODBC conformance. DATASRC= names can contain quotation marks and spaces.

proc sql;
   connect to ODBC as user1
   (datasrc="User's Data" user=testuser password=testpass);

This example uses the connection method that represents a more advanced level of ODBC conformance. It uses the input dialog box that is provided by the driver. The DATASRC= and USER= arguments are within the connection string. The SQL pass-through facility therefore does not parse them but instead passes them to the ODBC manager.

proc sql;
   connect to odbc as user1
   (required = "dsn=User's Data;uid=testuser");

This example enables you to select any data source that is configured on your machine. The example uses the connection method that represents a more advanced level of ODBC conformance, Level 1. When connection succeeds, the connection string is returned in the SQLXMSG and SYSDBMSG macro variables and can be stored if this method is used to configure a connection for later use.

proc sql;
   connect to odbc (required);

This next example prompts you to specify the information that is required to make a connection to the DBMS. You are prompted to supply the data source name, user ID, and password in the dialog boxes that display.

proc sql;
   connect to odbc (prompt);

Connection to Component Examples

This example sends an Oracle SQL query (presented in highlighted text) to the Oracle database for processing. The results from the query serve as a virtual table for the PROC SQL FROM clause. In this example MYCON is a connection alias.

proc sql;
connect to odbc as mycon
   (datasrc=ora7 user=testuser password=testpass);

select *
   from connection to mycon
      (select empid, lastname, firstname,
      hiredate, salary
      from sasdemo.employees 
         where hiredate>='31.12.1988');

disconnect from mycon;
quit;

This next example gives the previous query a name and stores it as the SQL view Samples.Hires88. The CREATE VIEW statement appears highlighted.

libname samples 'SAS-data-library';

proc sql;
connect to odbc as mycon
   (datasrc=ora7 user=testuser password=testpass);

create view samples.hires88 as
 select *
    from connection to mycon
      (select empid, lastname, firstname,
       hiredate, salary from sasdemo.employees
       where hiredate>='31.12.1988');

disconnect from mycon;
quit;

This example connects to Microsoft Access and creates a view NEWORDERS from all columns in the ORDERS table.

proc sql;
   connect to odbc as mydb
      (datasrc=MSAccess7);
   create view neworders as
    select * from connection to mydb
      (select * from orders);
disconnect from mydb;
quit;

This next example sends an SQL query to Microsoft SQL Server, configured under the data source name SQL Server, for processing. The results from the query serve as a virtual table for the PROC SQL FROM clause.

proc sql;
   connect to odbc as mydb
      (datasrc="SQL Server" user=testuser password=testpass);
   select * from connection to mydb
      (select CUSTOMER, NAME, COUNTRY
          from CUSTOMERS
          where COUNTRY <> 'USA');
quit;

This example returns a list of the columns in the CUSTOMERS table.

proc sql;
   connect to odbc as mydb
      (datasrc="SQL Server" user=testuser password=testpass);
   select * from connection to mydb
      (ODBC::SQLColumns (, , "CUSTOMERS"));
quit;

Special Catalog Queries

SAS/ACCESS Interface to ODBC supports the following special queries. Many databases provide or use system tables that allow queries to return the list of available tables, columns, procedures, and other useful information. ODBC provides much of this functionality through special application programming interfaces (APIs) to accommodate databases that do not follow the SQL table structure. You can use these special queries on SQL and non-SQL databases.

Here is the general format of the special queries:

ODBC::SQLAPI "parameter 1","parameter n"

ODBC::: is required to distinguish special queries from regular queries.
SQLAPI: is the specific API that is being called. Neither ODBC:: nor SQLAPI are case sensitive.
"parameter n": is a quoted string that is delimited by commas.

Within the quoted string, two characters are universally recognized: the percent sign (%) and the underscore (_). The percent sign matches any sequence of zero or more characters; the underscore represents any single character. Each driver also has an escape character that can be used to place characters within the string. See the driver documentation to determine the valid escape character.

The values for the special query arguments are DBMS-specific. For example, you supply the fully qualified table name for a "Catalog" argument. In dBase, the value of "Catalog" might be c:\dbase\tst.dbf and in SQL Server, the value might be test.customer . In addition, depending on the DBMS that you are using, valid values for a "Schema" argument might be a user ID, a database name, or a library. All arguments are optional. If you specify some but not all arguments within a parameter, use a comma to indicate the omitted arguments. If you do not specify any parameters, commas are not necessary. Special queries are not available for all ODBC drivers.

ODBC supports these special queries:

ODBC::SQLColumns <"Catalog", "Schema", "Table-name", "Column-name">: returns a list of all columns that match the specified arguments. If no arguments are specified, all accessible column names and information are returned.
ODBC::SQLColumnPrivileges <"Catalog", "Schema", "Table-name", "Column-name">: returns a list of all column privileges that match the specified arguments. If no arguments are specified, all accessible column names and privilege information are returned.
ODBC::SQLDataSources: returns a list of database aliases to which ODBC is connected.
ODBC::SQLDBMSInfo: returns a list of DB2 databases (DSNs) to which ODBC is connected. It returns one row with two columns that describe the DBMS name (such as SQL Server or Oracle) and the corresponding DBMS version.
ODBC::SQLForeignKeys <"PK-catalog", "PK-schema", "PK-table-name", "FK-catalog", "FK-schema", "FK-table-name">: returns a list of all columns that comprise foreign keys that match the specified arguments. If no arguments are specified, all accessible foreign key columns and information are returned.
ODBC::SQLGetTypeInfo: returns information about the data types that are supported in the data source.
ODBC::SQLPrimaryKeys <"Catalog", "Schema", "Table-name">: returns a list of all columns that compose the primary key that matches the specified table. A primary key can be composed of one or more columns. If no table name is specified, this special query fails.
ODBC::SQLProcedures <"Catalog", "Schema", "Procedure-name">: returns a list of all procedures that match the specified arguments. If no arguments are specified, all accessible procedures are returned.
ODBC::SQLProcedureColumns <"Catalog", "Schema", "Procedure-name", "Column-name">: returns a list of all procedure columns that match the specified arguments. If no arguments are specified, all accessible procedure columns are returned.
ODBC::SQLSpecialColumns <"Identifier-type", "Catalog-name", "Schema-name", "Table-name", "Scope", "Nullable">: returns a list of the optimal set of columns that uniquely identify a row in the specified table.
ODBC::SQLStatistics <"Catalog", "Schema", "Table-name">: returns a list of the statistics for the specified table name, with options of SQL_INDEX_ALL and SQL_ENSURE set in the SQLStatistics API call. If the table name argument is not specified, this special query fails.
ODBC::SQLTables <"Catalog", "Schema", "Table-name", "Type">: returns a list of all tables that match the specified arguments. If no arguments are specified, all accessible table names and information are returned.
ODBC::SQLTablePrivileges <"Catalog", "Schema", "Table-name">: returns a list of all tables and associated privileges that match the specified arguments. If no arguments are specified, all accessible table names and associated privileges are returned.

Top of Page

	AUTOCOMMIT=
	CURSOR_TYPE=
	KEYSET_SIZE=
	QUERY_TIMEOUT=
	READBUFF=
	READ_ISOLATION_LEVEL=
	TRACE=
	TRACEFILE=
	USE_ODBC_CL=
	UTILCONN_TRANSIENT=