What Is the Pass-Through Facility?

What Is the Pass-Through Facility?

The SQL Procedure Pass-Through Facility enables you to send DBMS-specific SQL statements directly to a DBMS for execution. The Pass-Through Facility uses a SAS/ACCESS interface engine to connect to the DBMS. Therefore, you must have SAS/ACCESS software installed for your DBMS.

You submit SQL statements that are DBMS-specific. For example, you pass Transact-SQL statements to a Sybase database. The Pass-Through Facility's basic syntax is the same for all the DBMSs. Only the statements that are used to connect to the DBMS and the SQL statements are DBMS-specific.

With the Pass-Through Facility, you can perform the following tasks:

       Establish a connection with the DBMS by using a CONNECT statement and terminate the connection with the DISCONNECT statement.

       Send nonquery DBMS-specific SQL statements to the DBMS by using the EXECUTE statement.

       Retrieve data from the DBMS to be used in a PROC SQL query with the CONNECTION TO component in a SELECT statement's FROM clause.

You can use the Pass-Through Facility statements in a query, or you can store them in a PROC SQL view. When a view is stored, any options that are specified in the corresponding CONNECT statement are also stored. Thus, when the PROC SQL view is used in a SAS program, SAS can automatically establish the appropriate connection to the DBMS.

Note:   SAS procedures that perform multipass processing cannot operate on PROC SQL views that store Pass-Through Facility statements, because the Pass-Through Facility does not allow reopening of a table after the first record has been retrieved. To work around this limitation, create a SAS data set from the view and use the SAS data set as the input data set.  

Return Codes

As you use PROC SQL statements that are available in the Pass-Through Facility, any errors are written to the SAS log. The return codes and messages that are generated by the Pass-Through Facility are available to you through the SQLXRC and SQLXMSG macro variables. Both macro variables are described in Using the PROC SQL Automatic Macro Variables.

Pass-Through Example

In this example, SAS/ACCESS connects to an ORACLE database by using the alias ora2 , selects all rows in the STAFF table, and displays the first 15 rows of data by using PROC SQL.

proc sql outobs=15;

   connect to oracle as ora2 (user=user-id password=password);

   select * from connection to ora2 (select lname, fname, state from staff);

   disconnect from ora2;

quit;

Output from the Pass-Through Facility Example

 LNAME            FNAME          STATE

---------------------------------------

ADAMS            GERALD           CT

ALIBRANDI        MARIA            CT

ALHERTANI        ABDULLAH         NY

ALVAREZ          MERCEDES         NY

ALVAREZ          CARLOS           NJ

BAREFOOT         JOSEPH           NJ

BAUCOM           WALTER           NY

BANADYGA         JUSTIN           CT

BLALOCK          RALPH            NY

BALLETTI         MARIE            NY

BOWDEN           EARL             CT

BRANCACCIO       JOSEPH           NY

BREUHAUS         JEREMY           NY

BRADY            CHRISTINE        CT

BREWCZAK         JAKOB            CT