CONNECTION TO Component

CONNECTION TO Component

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

Optional component

Syntax
Arguments
Syntax
Examples
Informix Command Restrictions for the Pass-Through Facility

Syntax

CONNECTION TO INFORMIX | alias (DBMS-SQL-query)

Arguments

alias

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

(DBMS-SQL-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. However, the query cannot contain a semicolon because to the SAS System a semicolon represents the end of a statement.

You must specify a dbms-SQL-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 type it; therefore, if your DBMS is case-sensitive, you must use the correct case for DMBS object names, enclosing them in quotes, if necessary. Quoted character strings are limited to 200 characters.

On some DBMSs, the dbms-SQL-query argument can be a DBMS-specific SQL EXECUTE statement that executes a DBMS stored procedure. However, if the stored procedure contains more than one query, only the first query is processed.

The CONNECTION TO component specifies the DBMS connection that you want to use or that you want to establish (if you have omitted the CONNECT statement). CONNECTION TO 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-SQL-query) other optional PROC SQL clauses;

CONNECTION TO can be used in any FROM clause, including those that are in nested queries (that is, subqueries).

You can store a Pass-Through query in a PROC SQL view and then use that view in SAS programs. When you create a PROC SQL view, any options that you specify in the corresponding CONNECT statement are stored too. Thus, when the PROC SQL view is used in a SAS program, the SAS System can establish the appropriate connection to the DBMS.

On many DBMSs, you can issue a CONNECTION TO component in a PROC SQL SELECT statement directly without first connecting to a DBMS. 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 connection arguments.

Because DBMSs and the SAS System have different naming conventions, some DBMS column names may be truncated when you retrieve DBMS data through the CONNECTION TO component. Default SAS variable names follow these rules:

If the column name is longer than 32 characters, the SAS System uses only the first 32 characters. If truncating results in duplicate names, sequential numbers (starting with zero) are appended to the ends of the names.
If the column name contains characters that are invalid in SAS names (such as national characters), the SAS System replaces the invalid characters with underscores (_). For example, the column name func$ becomes the SAS variable name func_.

Examples

The following example sends an SQL query, shown in italics, to the database for processing. The results from the SQL query serve as a virtual table for the PROC SQL FROM clause. In this example, DBCON is a connection alias.

proc sql;
connect to informix as dbcon
   (user=testuser using=testpass db=testdb
    server=testserver);

select *
   from connection to dbcon
      (select empid, lastname, firstname,
      hiredate, salary
          from employees
          where hiredate>='31JAN88');

disconnect from dbcon;
quit;

The following example gives the previous query a name and stores it as the PROC SQL view SLIB.HIRES88. The CREATE VIEW statement appears in italics.

libname slib 'SAS-data-library';

proc sql;
connect to informix as mycon
   (user=testuser using=testpass db=testdb
     server=testserver);

create view slib.hires88 as
  select *
    from connection to mycon
      (select empid, lastname, firstname,
       hiredate, salary from employees
       where hiredate>='31JAN88');

disconnect from mycon;
quit;

The next example connects to Informix and executes the stored procedure testproc. The select * clause displays the results from the stored procedure.

proc sql;
   connect to informix as mydb
      (database='//dbserver/corpdb');
   select * from connection to mydb
      (execute procedure testproc('123456'));
   disconnect from mydb;
quit;

Informix Command Restrictions for the Pass-Through Facility

Informix SQL contains extensions to the ANSI-89 standards. Some of these extensions, such as LOAD FROM and UNLOAD TO, are restricted from use by any applications other than the Informix DB-Access product. Specifying these extensions in the PROC SQL EXECUTE statement generates this error: -201 A syntax error has occurred.

Chapter Contents
Previous
Next
Top of Page