The SQL Pass-Through Facility

Overview of the SQL Pass-Through Facility

SPD Server uses SQL pass-through commands to access and manipulate data. Enabling SQL pass-through functionality provides SPD Server clients with a new way to establish a connection with an SPD Server host or to directly load from an external database such as Oracle. Users have broader data access in the SPD Server environment and growing connectivity to external databases using the SPD Server engine.

SPD Server SQL Syntax Reference Guide provides reference information about using SPD Server SQL syntax.

Accessing Data Using the SQL Pass-Through Facility

The SQL Pass-Through Facility is an access method that allows SPD Server to connect to a SQL server and manipulate data. An overview of the major steps to use SQL pass-through and examples follow:

Establish a connection from an SPD Server client using a CONNECT statement.
Send SPD Server SQL statements using the EXECUTE statement.
Retrieve data with the CONNECTION TO component in a SELECT statement's FROM clause.
Terminate the connection using the DISCONNECT statement.

SQL Pass-Through Statements

CONNECT Statement

Specifies the SAS I/O engine that provides SQL pass-through access.

Syntax

CONNECT TO dbms-name <AS alias>(dbms-args);

Arguments:

dbms-name (required)

Specifies the name of the engine.

When running SAS and PROC SQL, you must specify sasspds to obtain SQL pass-through to an SPD Server SQL server. You must specify spdseng to obtain SQL pass-through from an SPD Server SQL server. The examples show CONNECT statements specifying these engines.

AS alias (optional)

Specifies an alias or logical name for a connection. When specifying an alias to identify the connection, use a string that is not enclosed in quotation marks. Refer to this logical name in subsequent SQL pass-through statements.

Note: The alias must specify the connection that executes the statement.

Two Examples of Using an Alias

 execute(...)
                                    by alias

select
                                    * from connection to alias(...)

dbms-args (required and optional arguments)

Identifies the SQL server and data source. The following dbms-args arguments are for the SPD Server engines, sasspds and spdseng. SPD Server SQL uses the syntax keyword=value.

DBQ=libname-domain (required)

Specifies the primary SPD Server LIBNAME domain for the SQL pass-through connection. The name that you specify is identical to the LIBNAME domain name that you used when assigning a SAS LIBNAME to sasspds. Enclose the value in single or double quotation marks.

HOST=name-server-host (optional)

Specifies a node name or IP address for a Name Server that is currently running. Enclose the string in single or double quotation marks. If you do not specify a value, SPD Server uses the current value of the SAS macro variable spdshost to determine the node name.

SERVICE=name-server-port (optional)

SERV=name-server-port (optional)

Specifies the network address (port number) for a Name Server that is currently running. Enclose the value in single or double quotation marks. If you do not specify a port number for the Name Server, SPD Server determines the network address from the named service spdsname in the/etc/services file.

USER=SPD Server user ID (required on Windows, but not on UNIX)

Specifies an SPD Server user ID to access an SPD Server SQL server. Enclose the value in single or double quotation marks.

PASSWORD=password (required)

PASSWD=password (required, or use PROMPT=YES unless USER='anonymou')

Specifies an SPD Server user ID password to access an SPD Server. This value is case sensitive. You should not specify a password in a text file that another user can view. You should use this argument in a batch job that is protected by file system permissions, which prohibits other users from reading the text file.

PROMPT=YES (required, or use PASSWD= or PASSWORD= unless USER='anonymou')

Specifies a password prompt to access an SPD Server SQL server. This value is case sensitive.

DISCONNECT Statement

Disconnects you from your DBMS source.

Syntax

DISCONNECT FROM [dbms-name | alias];

Description

When you no longer need the PROC SQL connection, you must disconnect from the DBMS source. This automatically occurs when you exit PROC SQL. However, you can explicitly disconnect from the DBMS source using the DISCONNECT statement.

Arguments

dbms-name

is the name specified in the CONNECT statement that established the connection.

alias

is the alias value specified in the CONNECT statement that established the connection.

EXECUTE Statement

The EXECUTE statement is part of the SQL Pass-Through Facility. It allows the user to use specific SQL statements during a pass-through connection. Before using the EXECUTE statement, the user must establish a connection using the CONNECT statement. After a user has created a pass-through connection, use the EXECUTE statement to submit valid SQL statements (with the exception of the SELECT statement).

Syntax

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

Arguments

(SQL-statement)

A valid SQL statement passed for execution (with the exception of the SELECT statement). This argument is required and must be enclosed within parentheses.

dbms-name (required, or use alias)

Identifies the DBMS to which you want to direct the SQL statement. dbms-name must be preceded by the keyword BY.

alias (optional, or use dbms-name)

Specifies an optional alias used in the CONNECT statement.

CONNECTION TO Statement

CONNECTION TO is an SQL pass-through component that can be used in a SELECT statement's FROM clause as part of the from list. The CONNECTION TO component enables you to make pass-through queries for data, and to use that data in a PROC SQL query or table. PROC SQL treats the results of the query like a virtual table.

Syntax

CONNECTION TO dbms-name(SQL-query)

Arguments

dbms-name (required)

If you have a single connection, dbms-name is the dbms-name specified in your CONNECT statement. If you have multiple connections, use the alias specified in the AS clause of the CONNECT statement.

(SQL-query)

The (SQL-query) specifies the SQL query that you want to send. Your SQL query cannot contain a semicolon because a semicolon represents the end of a statement to SPD Server. Character literals are limited to 32,000 characters. Make sure that your SQL query is enclosed in parentheses.

alias (optional)

Specifies an optional alias used in the CONNECT statement.

Using PROC SQL to Connect to a SQL Server

Execute a CONNECT statement to connect from a SAS session to an SPD Server SQL server. After making the connection, the first EXECUTE statement creates a table named EMPLOYEE_INFO with three columns—EMPLOYEE_NO, EMPLOYEE_NAME, and ANNUAL_SALARY. The second EXECUTE statement inserts an observation into the table where EMPLOYEE_NO equals 1 and EMPLOYEE_NAME equals The Prez.

The subsequent FROM CONNECTION TO statement retrieves all of the records from the new EMPLOYEE_INFO table. (In this example, it retrieves a single observation inserted by the second EXECUTE statement.) The DISCONNECT statement terminates the connection.

    PROC SQL;
    connect to sasspds
     (dbq='mydomain'
      host='workstation1'
      serv='spdsname'
      user='me'
      passwd='noway');

    execute (create table employee_info
      (employee_no num, employee_name char(30),
      annual_salary num) by sasspds;

    execute (insert into employee_info
      values (1, 'The Prez')) by sasspds;

    select * from connection to sasspds
      (select * from employee_info);
    disconnect from sasspds;
    quit;

Nested SQL Pass-Through

SPD Server pass-through access can be nested. Nesting allows access to data that is stored on two different networks or network nodes.

In the following example,, on the DATAGATE host on a local network, SQL pass-through is nested to access the EMPLOYEE_INFO table. This table is available on the PROD host on a remote network. (You must have user access to the PROD host.)

    proc sql;
    connect to sasspds (dbq='domain1'
                        host='datagate' serv='spdsname'
                        user='usr1' passwd='usr1_pw');
    execute (connect to spdseng (dbq='domain2'
                        host='prod' serv='spdsname'
                        user='usr2' passwd='usr2_pw') by sasspds;
    select * from connection to sasspds(
                select * from connection to spdseng(
                    select employee_no, annual_salary
                    from employee_info));
    execute (disconnect from spdseng) by sasspds;
    disconnect from sasspds;
    quit;