SQL Pass-Through Facility Specifics for OLE DB

Key Information

For general information about this feature, see SQL Pass-Through Facility. OLE DB examples are available.

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

The dbms-name is OLEDB.
The CONNECT statement is required.
PROC SQL supports multiple connections to OLE DB. If you use multiple simultaneous connections, you must use an alias to identify the different connections. If you do not specify an alias, the default alias, OLEDB, is used. The functionality of multiple connections to the same OLE DB provider might be limited by a particular provider.
The CONNECT statement database-connection-arguments are identical to the LIBNAME connection options. For some data sources, the connection options have default values and are therefore not required.

Not all OLE DB providers support all connection options. See your provider documentation for more information.
Here are the LIBNAME options that are available with the CONNECT statement:

Examples

This example uses an alias to connect to a Microsoft SQL Server database and select a subset of data from the PAYROLL table. The SAS/ACCESS engine uses OLE DB Services to connect to OLE DB because this is the default action when the OLEDB_SERVICES= option is omitted.

proc sql;
connect to oledb as finance
  (user=username password=password datasource=dwtsrv1
   provider=sqloledb);

select * from connection to finance (select * from payroll
                                     where jobcode='FA3');
quit;

In this example, the CONNECT statement omits the provider name and properties. An OLE DB Services dialog box prompts you for the connection information.

proc sql;
connect to oledb;
quit;

This example uses OLE DB Services to connect to a provider that is configured under the data source name User's Data with the alias USER1. Note that the data source name can contain quotation marks and spaces.

proc sql;
   connect to oledb as user1
   (provider=JET datasource='c:\db1.mdb');;

Special Catalog Queries

Overview

SAS/ACCESS Interface to OLE DB 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. OLE DB 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.

Not all OLE DB providers support all queries. See your provider documentation for more information.

Here is the general format of the special queries:

OLEDB::schema-rowset("parameter 1","parameter n")

OLEDB::

is required to distinguish special queries from regular queries.

schema-rowset

is the specific schema rowset that is being called. All valid schema rowsets are listed under the IDBSchemaRowset Interface in the Microsoft OLE DB Programmer's Reference. Both OLEDB:: and schema-rowset are case sensitive.

"parameter n"

is a quoted string that is enclosed by commas. The values for the special query arguments are specific to each data source. For example, you supply the fully qualified table name for a "Qualifier" argument. In dBase, the value of "Qualifier" might be c:\dbase\tst.dbf, and in SQL Server, the value might be test.customer. In addition, depending on the data source that you use, values for an "Owner" 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 commas to indicate omitted arguments. If you do not specify any parameters, no commas are necessary. These special queries might not be available for all OLE DB providers.

OLE DB supports these special queries:

OLEDB::ASSERTIONS( <"Catalog", "Schema", "Constraint-Name"> )

returns assertions that are defined in the catalog that a given user owns.

OLEDB::CATALOGS( <"Catalog"> )

returns physical attributes that are associated with catalogs that are accessible from the DBMS.

OLEDB::CHARACTER_SETS( <"Catalog", "Schema","Character-Set-Name">)

returns the character sets that are defined in the catalog that a given user can access.

OLEDB::CHECK_CONSTRAINTS(<"Catalog", "Schema", "Constraint-Name">)

returns check constraints that are defined in the catalog and that a given user owns.

OLEDB::COLLATIONS(<"Catalog", "Schema", "Collation-Name">)

returns the character collations that are defined in the catalog and that a given user can access.

OLEDB::COLUMN_DOMAIN_USAGE( <"Catalog", "Schema", "Domain-Name", "Column-Name">)

returns the columns that are defined in the catalog, are dependent on a domain that is defined in the catalog, and a given user owns.

OLEDB::COLUMN_PRIVILEGES( <"Catalog", "Schema", "Table-Name", "Column-Name", "Grantor", "Grantee">)

returns the privileges on columns of tables that are defined in the catalog that a given user grants or can access.

OLEDB::COLUMNS( <"Catalog", "Schema", "Table-Name", "Column-Name">)

returns the columns of tables that are defined in the catalogs that a given user can access.

OLEDB::CONSTRAINT_COLUMN_USAGE(<"Catalog", "Schema", "Table-Name", "Column-Name">)

returns the columns that referential constraints, unique constraints, check constraints, and assertions use that are defined in the catalog and that a given user owns.

OLEDB::CONSTRAINT_TABLE_USAGE(<"Catalog", "Schema", "Table-Name">)

returns the tables that referential constraints, unique constraints, check constraints, and assertions use that are defined in the catalog and that a given user owns.

OLEDB::FOREIGN_KEYS(<"Primary-Key-Catalog", "Primary-Key-Schema", "Primary-Key-Table-Name", "Foreign-Key-Catalog", "Foreign-Key-Schema", "Foreign-Key-Table-Name">)

returns the foreign key columns that a given user defined in the catalog.

OLEDB::INDEXES( <"Catalog", "Schema", "Index-Name", "Type", "Table-Name">)

returns the indexes that are defined in the catalog that a given user owns.

OLEDB::KEY_COLUMN_USAGE(<"Constraint-Catalog", "Constraint-Schema", "Constraint-Name", "Table-Catalog", "Table-Schema", "Table-Name", "Column-Name">)

returns the columns that are defined in the catalog and that a given user has constrained as keys.

OLEDB::PRIMARY_KEYS(<"Catalog", "Schema", "Table-Name">)

returns the primary key columns that a given user defined in the catalog.

OLEDB::PROCEDURE_COLUMNS(<"Catalog", "Schema", "Procedure-Name", "Column-Name">)

returns information about the columns of rowsets that procedures return.

OLEDB::PROCEDURE_PARAMETERS(<"Catalog", "Schema", "Procedure-Name", "Parameter-Name">)

returns information about the parameters and return codes of the procedures.

OLEDB::PROCEDURES(<"Catalog", "Schema", "Procedure-Name", "Procedure-Type">)

returns procedures that are defined in the catalog that a given user owns.

OLEDB::PROVIDER_INFO()

returns output that contains these columns: PROVIDER_NAME, PROVIDER_DESCRIPTION, and PROVIDER_PROPERTIES. The PROVIDER_PROPERTIES column contains a list of all properties that the provider supports. A semicolon (;) separates the properties. See yExamples of Special OLE DB Queries.

OLEDB::PROVIDER_TYPES(<"Data Type", "Best-Match">)

returns information about the base data types that the data provider supports.

OLEDB::REFERENTIAL_CONSTRAINTS(<"Catalog", "Schema", "Constraint-Name">)

returns the referential constraints that are defined in the catalog that a given user owns.

OLEDB::SCHEMATA(<"Catalog", "Schema", "Owner">)

returns the schemas that a given user owns.

OLEDB::SQL_LANGUAGES()

returns the conformance levels, options, and dialects that the SQL implementation processing data supports and that is defined in the catalog.

OLEDB::STATISTICS(<"Catalog", "Schema", "Table-Name">)

returns the statistics that is defined in the catalog that a given user owns.

OLEDB::TABLE_CONSTRAINTS(<"Constraint-Catalog", "Constraint-Schema", "Constraint-Name", "Table-Catalog", "Table-Schema", "Table-Name", "Constraint-Type">)

returns the table constraints that is defined in the catalog that a given user owns.

OLEDB::TABLE_PRIVILEGES(<"Catalog", "Schema", "Table-Name", "Grantor", "Grantee">)

returns the privileges on tables that are defined in the catalog that a given user grants or can access.

OLEDB::TABLES(<"Catalog", "Schema", "Table-Name", "Table-Type">)

returns the tables defined in the catalog that a given user grants and can access.

OLEDB::TRANSLATIONS(<"Catalog", "Schema", "Translation-Name">)

returns the character translations that are defined in the catalog and that are accessible to a given user.

OLEDB::USAGE_PRIVILEGES(<"Catalog", "Schema", "Object-Name", "Object-Type", "Grantor", "Grantee">)

returns the USAGE privileges on objects that are defined in the catalog and that a given user grants or can access.

OLEDB::VIEW_COLUMN_USAGE(<"Catalog", "Schema", "View-Name">)

returns the columns on which viewed tables depend that are defined in the catalog and that a given user owns.

OLEDB::VIEW_TABLE_USAGE(<"Catalog", "Schema", "View-Name">)

returns the tables on which viewed tables depend that are defined in the catalog and that a given user owns.

OLEDB::VIEWS(<"Catalog", "Schema", "Table-Name">)

returns the viewed tables that are defined in the catalog and that a given user can access.

For a complete description of each rowset and the columns that are defined in each rowset, see the Microsoft OLE DB Programmer's Reference.

Examples of Special OLE DB Queries

This example retrieves a rowset that displays all tables that the HRDEPT schema accesses:

proc sql;
  connect to oledb(provider=sqloledb properties=("User ID"=testuser
                   Password=testpass
     "Data Source"='dwtsrv1'));
  select * from connection to oledb
       (OLEDB::TABLES(,"HRDEPT"));
quit;

It uses the special query OLEDB::PROVIDER_INFO() to produce this output:

proc sql;
  connect to oledb(provider=msdaora properties=("User ID"=testuser
                   Password=testpass
     "Data Source"="Oraserver"));
  select * from connection to oledb
       (OLEDB::PROVIDER_INFO());
quit;

Provider and Properties Output

PROVIDER_NAME   PROVIDER_DESCRIPTION   PROVIDER_PROPERTIES
-------------   --------------------   -------------------
MSDAORA         Microsoft OLE DB       Password;User ID;Data
                Provider for Oracle    Source;Window Handle;Locale
                                       Identifier;OLE DB Services;
                                       Prompt; Extended Properties;

SampProv        Microsoft OLE DB       Data Source;Window Handle;
                Sample Provider        Prompt;

You could then reference the output when automating a connection to the provider. For the previous result set, you could write this SAS/ACCESS LIBNAME statement:

libname mydblib oledb provider=msdaora
   props=('Data Source'=OraServer 'User ID'=scott 'Password'=tiger);