DBMS Access Using the SQL Procedure Pass-Through Facility (1)

Accessing Other Vendors' DBMS Data

DBMS Access Using the SQL Procedure Pass-Through Facility

Although SAS software can efficiently process DBMS data through the LIBNAME engine, you may sometimes wish to have explicit control over the SQL statements that are passed to the DBMS. SAS/ACCESS software provides the SQL Procedure Pass-Through Facility, an extension to PROC SQL that enables you to send DBMS-specific SQL statements directly to a DBMS.

The SQL Procedure Pass-Through Facility provides the statements and component shown in the table below.

Statement or Component Function

CONNECT statement establishes a connection to the DBMS

CONNECTION TO component
(in the FROM clause of the PROC SQL SELECT statement) retrieves data directly from the DBMS

EXECUTE statement sends dynamic, non-query, SQL statements to the DBMS

DISCONNECT statement terminates the connection to the DBMS

Statement or Component	Function
CONNECT statement	establishes a connection to the DBMS
CONNECTION TO component (in the FROM clause of the PROC SQL SELECT statement)	retrieves data directly from the DBMS
EXECUTE statement	sends dynamic, non-query, SQL statements to the DBMS
DISCONNECT statement	terminates the connection to the DBMS

You can embed Pass-Through statements in a PROC SQL query, or you can store them in a PROC SQL view. This lesson focuses on embedding Pass-Through statements in a query.

Sample Program
Before we examine the SQL Procedure Pass-Through Facility in detail, let's take a look at a sample program. The PROC SQL step shown below does the following:

connects to an ORACLE database and assigns it the alias Dblink
uses this connection to read data from an ORACLE table named EMPLOYEES
grants UPDATE and INSERT authority for the table to the user
disconnects from the ORACLE database.

Sample program

Let's start by seeing how you establish a connection to a DBMS by using the CONNECT statement.