CONNECT Statement

CONNECT Statement

Establishes a connection with the DBMS

Contains DBMS-specific arguments

Optional statement for some DBMSs

Syntax
Arguments
CONNECT Statement Arguments
Example

Syntax

CONNECT TO dbms-name <AS alias> <(<connect-statement-arguments> <database-connection-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.

Using the CONNECT statement is optional for some DBMSs. However, if it is not specified, the default values for all of the 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 SQL Procedure Pass-Through Facility Return Codes for more information on these macro variables.

Arguments

You use the following arguments with the CONNECT statement:

dbms-name: identifies the database management system to which you want to connect. You must specify a DBMS name, which is listed in your DBMS chapter. You may also specify an optional alias in the CONNECT statement.
alias: specifies an optional alias for the Pass-Through connection 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.
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 are optional but if they are included, they must be enclosed in parentheses. For details, see CONNECT Statement Arguments.
database-connection-arguments: specifies the DBMS-specific arguments that are needed by PROC SQL to connect to the DBMS. These arguments are optional for most databases, but if they are included, they must be enclosed in parentheses. See your DBMS chapter for information about these arguments.

CONNECT Statement Arguments

The following list describes the general arguments that are used for the PROC SQL Pass-Through CONNECT statement.

CONNECTION= argument
CONNECTION_GROUP= argument
DBCONINIT= argument
DBCONTERM= argument
DBPROMPT= argument
DEFER= argument
VALIDVARNAME=

CONNECTION=SHARED | GLOBAL

indicates whether multiple connect statements for a DBMS can use the same connection. See your DBMS chapter for DBMS-specific arguments for CONNECTION=.

Default value: SHARED

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

This option is supported by the SAS/ACCESS engines that support multiple, simultaneous connections to the DBMS. For most of these SAS/ACCESS engines, there must be a connection, also known as an attach, to the DBMS server before any data can be accessed. Typically, each DBMS connection has one transaction, or work unit, active in the connection. This transaction is affected by any SQL COMMITs or ROLLBACKs that are performed within the connection while executing the Pass-Through statements.

The values for CONNECTION= are as follows:

SHARED

When CONNECTION=SHARED, the CONNECT statement makes one connection to the DBMS. All Pass-Through statements that use this alias share this connection.

SHARED is the default value for CONNECTION= .

GLOBAL

When CONNECTION=GLOBAL, multiple CONNECT statements that use identical values for all options can share the same connection to the DBMS.

In the following example, the two CONNECT statements, MYDBONE and MYDBTWO, 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;

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

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

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

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

disconnect from mydbone;
disconnect from mydbtwo;

CONNECTION_GROUP=connection_group_name

specifies a connection that can be shared among several CONNECT statements in the SQL Procedure Pass-Through Facility.

Default value: none

By specifying the name of a connection group, you can share one DBMS connection among several different 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 options.

When CONNECTION_GROUP= is specified, it implies that the value of the CONNECTION= option will be 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.

See SAS/ACCESS LIBNAME Statement for an example.

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.

See SAS/ACCESS LIBNAME Statement for an example.

DBPROMPT=YES | NO

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

Default value: NO

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

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

The DBPROMPT= option interacts with the DEFER= 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.

See SAS/ACCESS LIBNAME Statement for an example.

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 those variable names considered valid SAS variable names in Version 6 of SAS software are considered valid. Specify this connection argument if you want Pass-Through to operate in Version 6 compatibility mode.

When V6 is specified in SQL Pass-Through code, the SAS/ACCESS engine for the DBMS truncates column names to eight characters, as it did in Version 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 SQL Pass-Through connection.

The following example shows how the 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;

In this example, if you had omitted VALIDVARNAME=V6 as a connection argument, you would have had to have added 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.

Example

The following example connects to a SYBASE server and assigns the alias SYBCON1 to it. SYBASE is a case sensitive database; therefore, the 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;

Chapter Contents
Previous
Next
Top of Page

	Contains DBMS-specific arguments
	Optional statement for some DBMSs