SQL Pass-Through Facility Specifics for Sybase

Key Information

For general information about this feature, see SQL Pass-Through Facility. A Sybase example is available.

Here are the SQL pass-through facility specifics for the Sybase interface.

The dbms-name is SYBASE.
The CONNECT statement is optional. If you omit the CONNECT statement, an implicit connection is made using the default values for all connection options.
The interface can connect multiple times to one or more servers.
Here are the database-connection-arguments for the CONNECT statement.

USER=<'>SYBASE-user-name<'>

specifies the Sybase user name (also called the login name) that you use to connect to your database. If the user name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.

PASSWORD=<'>SYBASE-password<'>

specifies the password that is associated with the Sybase user name.

If you omit the password, a default password of NULL is used. If the password contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.

PASSWORD= can also be specified with the SYBPW=, PASS=, and PW= aliases. If you do not wish to enter your Sybase password in uncoded text, see PROC PWENCODE in the Base SAS Procedures Guide for a method to encode it.

DATABASE=<'>database-name<'>

specifies the name of the Sybase database that contains the tables and views that you want to access.

If the database name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks. If you omit DATABASE=, the default database for your Sybase user name is used.

Alias: DB=

SERVER=<'>server-name<'>

specifies the server that you want to connect to. This server accesses the database that contains the tables and views that you want to access.

If the server name contains lowercase, spaces, or nonalphanumeric characters, you must enclose it in quotation marks.

If you omit SERVER=, the default action for your operating system occurs. On UNIX systems, the value of the environment variable DSQUERY is used if it has been set.

INTERFACE=filename

specifies the name and location of the Sybase interfaces file. The interfaces file contains the names and network addresses of all available servers on the network.

If you omit this statement, the default action for your operating system occurs. INTERFACE= is not used in some operating environments. Contact your database administrator to determine whether it applies to your operating environment.

SYBBUFSZ=number-of-rows

specifies the number of rows of DBMS data to write to the buffer. If this statement is used, the SAS/ACCESS interface view engine creates a buffer that is large enough to hold the specified number of rows. This buffer is created when the associated database table is read. The interface view engine uses SYBBUFSZ= to improve performance.

If you omit this statement, no data is written to the buffer.

Connection options for Sybase are all case sensitive. They are passed to Sybase exactly as you enter them.
Here are the LIBNAME options that are available with the CONNECT statement.
- DBMAX_TEXT=
- MAX_CONNECTS=
- READBUFF=
- PACKETSIZE=

Example

This example retrieves a subset of rows from the Sybase INVOICE table. Because the WHERE clause is specified in the DBMS query (the inner SELECT statement), the DBMS processes the WHERE expression and returns a subset of rows to SAS.

proc sql;
connect to sybase(server=SERVER1
                  database=INVENTORY
                  user=testuser password=testpass);
%put &sqlxmsg;

select * from connection to sybase
   (select * from INVOICE where BILLEDBY=457232);
%put &sqlxmsg;

The SELECT statement that is enclosed in parentheses is sent directly to the database and therefore must be specified using valid database variable names and syntax.