CONNECTION TO Component

Retrieves and uses DBMS data in a PROC SQL query or view

Valid in:

PROC SQL step SELECT statements (when accessing DBMS data using SAS/ACCESS software)

Syntax

Details

Examples

Example 1: Send an Oracle SQL Query to the Oracle Database

Example 2: Name and Store the Query as an SQL View

Syntax

CONNECTION TO dbms-name | alias | (dbms-query)

Required Arguments

dbms-name: identifies the database management system to which you direct the DBMS-specific SQL statement. See the documentation for your SAS/ACCESS interface for the name for your DBMS.

alias: specifies an alias, if one was defined in the CONNECT statement.

(dbms-query): specifies the query that you are sending to the DBMS. The query can use any DBMS-specific SQL statement or syntax that is valid for the DBMS.

You must specify a query argument in the CONNECTION TO component, and the query must be enclosed in parentheses. The query is passed to the DBMS exactly as you enter it. Therefore, if your DBMS is case sensitive, you must use the correct case for DBMS object names.

On some DBMSs, the dbms-query argument can be a DBMS stored procedure. However, stored procedures with output parameters are not supported in the SQL pass-through facility. Furthermore, if the stored procedure contains more than one query, only the first query is processed.

Details

The CONNECTION TO component specifies the DBMS connection that you want to use or that you want to create (if you have omitted the CONNECT statement). CONNECTION TO then enables you to retrieve DBMS data directly through a PROC SQL query.

You use the CONNECTION TO component in the FROM clause of a PROC SQL SELECT statement:

PROC SQL;

SELECT column-list

FROM CONNECTION TO dbms-name (dbms-query)other optional PROC SQL clauses

QUIT;

You can use CONNECTION TO in any FROM clause, including those in nested queries—that is, subqueries.

You can store an SQL pass-through facility query in an SQL view and then use that view in SAS programs. When you create an SQL view, any options that you specify in the corresponding CONNECT statement are stored too. So when the SQL view is used in a SAS program, SAS can establish the appropriate connection to the DBMS.

On many relational databases, you can issue a CONNECTION TO component in a PROC SQL SELECT statement directly without first connecting to a DBMS. (See CONNECT Statement.) If you omit the CONNECT statement, an implicit connection is performed when the first PROC SQL SELECT statement that contains a CONNECTION TO component is passed to the DBMS. Default values are used for all DBMS connection arguments. See the documentation for your SAS/ACCESS interface for details.

Because relational databases and SAS have different naming conventions, some DBMS column names might be changed when you retrieve DBMS data through the CONNECTION TO component. See SAS Names and Support for DBMS Names for more information.

Examples

Example 1: Send an Oracle SQL Query to the Oracle Database

After you connect (explicitly using the CONNECT statement or implicitly using default settings) to a DBMS, you can send a DBMS-specific SQL query to the DBMS using the facilities CONNECTION TO component. You issue a SELECT statement (to indicate which columns you want to retrieve), identify your DBMS (such as Oracle or DB2), and issue your query by using the SQL syntax of your DBMS.

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

proc sql;
connect to oracle as mycon (user=testuser
   password=testpass path='myorapath');
%put &sqlxmsg;
select *
   from connection to mycon
    (select empid, lastname, firstname,
            hiredate, salary
     from employees where 
          hiredate>='31-DEC-88');
%put &sqlxmsg;
disconnect from mycon;
quit;

The SAS %PUT macro displays the &SQLXMSG macro variable for error codes and information from the DBMS. See Macro Variables for Relational Databases for more information.

Example 2: Name and Store the Query as an SQL View

This example gives the query a name and stores it as the SQL view samples.

libname samples 'SAS-library';
proc sql;
connect to oracle as mycon (user=testuser
   password=testpass path='myorapath');
%put &sqlxmsg;
create view samples.hires88 as
   select *
      from connection to mycon
         (select empid, lastname, firstname,
           hiredate, salary
          from employees where
               hiredate>='31-DEC-88');
%put &sqlxmsg;
disconnect from mycon;
quit;