The SQL Pass-Through Facility for Relational Databases

CONNECT Statement

Establishes a connection with the DBMS

Valid in:

PROC SQL steps (when accessing DBMS data using SAS/ACCESS software)

Syntax
Arguments
CONNECT Statement Example

Syntax

CONNECT TO dbms-name <AS alias> <(<database-connection-arguments> <connect-statement-arguments> )>;

The CONNECT statement establishes a connection with the DBMS. You establish a connection to send DBMS-specific SQL statements to the DBMS or to retrieve DBMS data. The connection remains in effect until you issue a DISCONNECT statement or terminate the SQL procedure.

Follow these steps to connect to a DBMS using the SQL pass-through facility.

Initiate a PROC SQL step.
Use the SQL pass-through facility CONNECT statement, identify the DBMS (such as Oracle or DB2), and assign an (optional) alias.
Specify any attributes for the connection such as SHARED or UNIQUE.
Specify any arguments that are needed to connect to the database.

The CONNECT statement is optional for some DBMSs. However, if it is not specified, the default values for all database connection arguments are used.

Any return code or message that is generated by the DBMS is available in the macro variables SQLXRC and SQLXMSG after the statement executes. See Macro Variables for Relational Databases for more information about these macro variables.

Arguments

Use these arguments with the CONNECT statement.

dbms-name

identifies the database management system to which you want to connect. You must specify the DBMS name for your SAS/ACCESS interface. You can also specify an optional alias.

alias

specifies for the connection an optional alias that has 1 to 32 characters. If you specify an alias, the keyword AS must appear before the alias. If an alias is not specified, the DBMS name is used as the name of the Pass-Through connection.

database-connection-arguments

specifies the DBMS-specific arguments that PROC SQL needs to connect to the DBMS. These arguments are optional for most databases. However, if you include them, you must enclose them in parentheses. See the documentation for your SAS/ACCESS interface for information about these arguments.

connect-statement-arguments

specifies arguments that indicate whether you can make multiple connections, shared or unique connections, and so on, to the database. These arguments enable the SQL pass-through facility to use some of the LIBNAME statement's connection management features. These arguments are optional, but if they are included, they must be enclosed in parentheses.

CONNECTION= SHARED | GLOBAL

indicates whether multiple CONNECT statements for a DBMS can use the same connection.

The CONNECTION= option enables you to control the number of connections, and therefore transactions, that your SAS/ACCESS engine executes and supports for each Pass-Through CONNECT statement.

When CONNECTION=GLOBAL, multiple CONNECT statements that use identical values for CONNECTION=, CONNECTION_GROUP=, DBCONINIT=, DBCONTERM=, and any database connection arguments can share the same connection to the DBMS.

When CONNECTION=SHARED, the CONNECT statement makes one connection to the DBMS. Only Pass-Through statements that use this alias share the connection. SHARED is the default value for CONNECTION=.

In this example, the two CONNECT statements share the same connection to the DBMS because CONNECTION=GLOBAL. Only the first CONNECT statement actually makes the connection to the DBMS, while the last DISCONNECT statement is the only statement that disconnects from the DBMS.

proc sql;

/*...SQL Pass-Through statements referring to mydbone...*/

connect to oracle as mydbone
   (user=testuser pw=testpass 
     path='myorapath' 
     connection=global);

/*...SQL Pass-Through statements referring to mydbtwo...*/

connect to oracle as mydbtwo
   (user=testuser pw=testpass 
     path='myorapath' 
     connection=global);

disconnect from mydbone;
disconnect from mydbtwo;
quit;

CONNECTION_GROUP=connection-group-name

specifies a connection that can be shared among several CONNECT statements in the SQL pass-through facility.

Default value: none

By specifying the name of a connection group, you can share one DBMS connection among several CONNECT statements. The connection to the DBMS can be shared only if each CONNECT statement specifies the same CONNECTION_GROUP= value and specifies identical DBMS connection arguments.

When CONNECTION_GROUP= is specified, it implies that the value of the CONNECTION= option is GLOBAL.

DBCONINIT=<'>DBMS-user-command<'>

specifies a user-defined initialization command to be executed immediately after the connection to the DBMS.

You can specify any DBMS command that can be passed by the SAS/ACCESS engine to the DBMS and that does not return a result set or output parameters. The command executes immediately after the DBMS connection is established successfully. If the command fails, a disconnect occurs, and the CONNECT statement fails. You must specify the command as a single, quoted string, unless it is an environment variable.

DBCONTERM='DBMS-user-command'

specifies a user-defined termination command to be executed before the disconnect from the DBMS that occurs with the DISCONNECT statement.

Default value: none

The termination command that you select can be a script, stored procedure, or any DBMS SQL language statement that might provide additional control over the interaction between the SAS/ACCESS engine and the DBMS. You can specify any valid DBMS command that can be passed by the SAS/ACCESS engine to the DBMS and that does not return a result set or output parameters. The command executes immediately before SAS terminates each connection to the DBMS. If the command fails, SAS provides a warning message but the disconnect still occurs. You must specify the command as a quoted string.

DBGEN_NAME= DBMS | SAS

specifies whether to automatically rename DBMS columns containing characters that SAS does not allow, such as $, to valid SAS variable names. See DBGEN_NAME= LIBNAME Option for more information.

DBMAX_TEXT=integer

determines the length of any very long DBMS character data type that is read into SAS or written from SAS when using a SAS/ACCESS engine. This option applies to reading, appending, and updating rows in an existing table. It does not apply when you are creating a table.

Examples of a long DBMS data type are the SYBASE TEXT data type or the Oracle LONG RAW data type.

DBPROMPT=YES | NO

specifies whether SAS displays a window that prompts the user to enter DBMS connection information before connecting to the DBMS.

Default value: NO

Interaction: DEFER= LIBNAME option

If you specify DBPROMPT=YES, SAS displays a window that interactively prompts you for the DBMS connection arguments when the CONNECT statement is executed. Therefore, it is not necessary to provide connection arguments with the CONNECT statement. If you do specify connection arguments with the CONNECT statement and you specify DBPROMPT=YES, the connection argument values are displayed in the window. These values can be overridden interactively.

If you specify DBPROMPT=NO, SAS does not display the prompting indow.

The DBPROMPT= option interacts with the DEFER= LIBNAME option to determine when the prompt window appears. If DEFER=NO, the DBPROMPT window opens when the CONNECT statement is executed. If DEFER=YES, the DBPROMPT window opens the first time a pass-through statement is executed. The DEFER= option normally defaults to NO, but defaults to YES if DBPROMPT=YES. You can override this default by explicitly setting DEFER=NO.

DEFER=NO | YES

determines when the connection to the DBMS occurs.

Default value: NO

If DEFER=YES, the connection to the DBMS occurs when the first Pass-Through statement is executed. If DEFER=NO, the connection to the DBMS occurs when the CONNECT statement occurs.

VALIDVARNAME=V6

indicates that only SAS 6 variable names are considered valid. Specify this connection argument if you want the SQL pass-through facility to operate in SAS 6 compatibility mode.

By default, DBMS column names are changed to valid SAS names, following these rules:

Up to 32 mixed-case alphanumeric characters are allowed.
Names must begin with an alphabetic character or an underscore.
Characters that are not permitted are changed to underscores.
Any column name that is not unique when it is normalized is made unique by appending a counter (0,1,2,...) to the name.

When VALIDVARNAME=V6 is specified, the SAS/ACCESS engine for the DBMS truncates column names to eight characters, as it does in SAS 6. If required, numbers are appended to the ends of the truncated names to make them unique. Setting this option overrides the value of the SAS system option VALIDVARNAME= during (and only during) the Pass-Through connection.

This example shows how the SQL pass-through facility uses VALIDVARNAME=V6 as a connection argument. Using this option causes the output to show the DBMS column "Amount Budgeted$" as AMOUNT_B and "Amount Spent$" as AMOUNT_S.

proc sql;
connect to oracle (user=gloria password=teacher 
                   validvarname=v6)
create view budget2000 as
 select amount_b, amount_s
 from connection to oracle
     (select "Amount Budgeted$", "Amount Spent$"
      from annual_budget);
quit;
proc contents data=budget2000;
run;

For this example, if you omit VALIDVARNAME=V6 as a connection argument, you must add it in an OPTIONS= statement in order for PROC CONTENTS to work:

options validvarname=v6; 
proc contents data=budget2000;
run;

Thus, using it as a connection argument saves you coding later.

Note: In addition to the arguments listed here, several other LIBNAME options are available for use with the CONNECT statement. See the section about the SQL pass-through facility in the documentation for your SAS/ACCESS interface to determine which LIBNAME options are available in the SQL pass-through facility for your DBMS. When used with the SQL pass-through facility CONNECT statement, these options have the same effect as they do in a LIBNAME statement. [cautionend]

CONNECT Statement Example

This example connects to a Sybase server and assigns the alias SYBCON1 to it. Sybase is a case-sensitive database, so database objects are in uppercase, as they were created.

proc sql;
connect to sybase as sybcon1
   (server=SERVER1 database=PERSONNEL
    user=testuser password=testpass
    connection=global);
%put &sqlxmsg &sqlxrc;

Note: You might be able to omit the CONNECT statement and implicitly connect to a database by using default settings. See the documentation for your SAS/ACCESS interface for more information. [cautionend]

Top of Page