SAS/ACCESS Software for Relational Databases: Reference

SQL Procedure Pass-Through Facility

The SQL Procedure Pass-Through Facility is an extension to the SQL procedure that enables you to send DBMS-specific statements to a database management system and to retrieve DBMS data directly. However, because the SQL Procedure Pass-Through Facility uses a SAS/ACCESS engine to connect to the DBMS, you must have SAS/ACCESS software in order to use it. You specify DBMS syntax instead of SAS SQL syntax when you are using the SQL Procedure Pass-Through Facility.

Beginning in Version 7 of SAS/ACCESS software, there are a number of changes in the way the SQL Procedure Pass-Through Facility works. For more information, see SQL Procedure Pass-Through Facility Statements.

This section provides general reference information for the SQL Procedure Pass-Through Facility. See your DBMS chapter for detailed information about using your database's arguments and options. See Using DBMS Data with the SQL Pass-Through Facility for usage examples.

The SQL Procedure Pass-Through Facility consists of three statements and one component:

The CONNECT statement establishes a connection with the DBMS.
The EXECUTE statement sends dynamic, non-query DBMS-specific SQL statements to the DBMS.
The CONNECTION TO component in the FROM clause of a PROC SQL SELECT statement retrieves data directly from a DBMS.
The DISCONNECT statement terminates the connection with the DBMS.

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

See the SAS Procedures Guide for information on changes and enhancements to the SQL procedure.

Syntax for the SQL Procedure Pass-Through Facility

To connect to a DBMS and send it a DBMS-specific, nonquery SQL statement, use the statements that comprise the SQL Procedure Pass-Through Facility:

PROC SQL;

<CONNECT TO dbms-name <AS alias><
<(connect-statement-argument-1=value
...<connect-statement-argument-n=value>)>>
<(dbms-argument-1=value
...<dbms-argument-n=value>)>>;

EXECUTE (dbms-SQL-statement)
BY dbms-name|alias;

<DISCONNECT FROM dbms-name|alias;>

<QUIT;>

For information about the CONNECT, EXECUTE, and DISCONNECT statements as well as the CONNECTION TO component, see SQL Procedure Pass-Through Facility Statements.

To connect to a DBMS and query the DBMS data, use the SAS/ACCESS LIBNAME statement or use the PROC SQL Pass-Through statement CONNECTION TO in this form:

PROC SQL;

<CONNECT TO dbms-name <AS alias><
<(connect-statement-argument-1=value
...<connect-statement-argument-n=value>)>>
<(dbms-argument-1=value
...<dbms-argument-n=value>)>>;

SELECT column-list

FROM CONNECTION TO dbms-name|alias
(dbms-query)
optional PROC SQL clauses;

<DISCONNECT FROM dbms-name|alias;>

<QUIT;>

Accomplishing Tasks with the SQL Pass-Through Facility
To do this Use this statement

Establish a connection with a DBMS CONNECT

Terminate the connection with a DBMS DISCONNECT

Send a DBMS-specific, non-query SQL statement to a DBMS EXECUTE

Query a DBMS using a DBMS-specific SQL statement SELECT and FROM CONNECTION TO

***Accomplishing Tasks with the SQL Pass-Through Facility***
To do this	Use this statement
Establish a connection with a DBMS	CONNECT
Terminate the connection with a DBMS	DISCONNECT
Send a DBMS-specific, non-query SQL statement to a DBMS	EXECUTE
Query a DBMS using a DBMS-specific SQL statement	SELECT and FROM CONNECTION TO

Chapter Contents
Previous
Next
Top of Page