Establishes a connection with a DBMS.
| Valid in: | PROC SQL steps (when accessing DBMS data using SAS/ACCESS software) |
|---|
Table of Contents
specifies the DBMS to which you want to connect. You must specify the DBMS name for your SAS/ACCESS interface. See the SQL pass-through section in the DBMS-specific reference section for your SAS/ACCESS interface.
specifies the libref for which a DBMS connection has already been established through the LIBNAME statement.
specifies an alias for the connection that has 1 to 32 characters. The AS keyword must precede alias. If you do not specify an alias, the DBMS name is used as the name of the SQL pass-through connection. Some DBMSs allow more than one connection. You can use the AS clause to name connections so that you can refer to them later.
specifies values for arguments that indicate whether you can make multiple connections, shared or unique connections, and so on, to the database. With these arguments, the SQL pass-through facility can use some of the connection management features of the LIBNAME statement or of SAS system options. Although these arguments are optional, you must enclose them in parentheses if you include any:
indicates that 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 SQL 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. For more information, see Conditions for a Shared DBMS Connection.
When CONNECTION=SHARED, the CONNECT statement makes one connection to the DBMS. Only SQL 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. 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=myusr1 pw=mypwd1 path='mysrv1' connection=global);
/*…SQL Pass-Through statements referring to mydbtwo…*/
connect to oracle as mydbtwo
(user=myusr1 pw=mypwd1 path='mysrv1' connection=global);
disconnect from mydbone;
disconnect from mydbtwo;
quit;specifies a connection that can be shared among several CONNECT statements in the SQL pass-through facility.
Default: 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. For more information, see Conditions for a Shared DBMS Connection.
When CONNECTION_GROUP= is specified, it implies that the value of the CONNECTION= option is GLOBAL.
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.
specifies a user-defined termination command to be executed before the disconnect from the DBMS that occurs with the DISCONNECT statement.
Default: 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.
specifies whether to automatically rename DBMS columns containing characters that SAS does not allow, such as $, to valid SAS variable names.
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 SAP ASE TEXT data type and the Oracle LONG RAW data type.
specifies whether SAS displays a window that prompts the user to enter DBMS connection information before connecting to the DBMS.
Default: 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 window.
The DBPROMPT= option interacts with the DEFER= LIBNAME option to determine when the prompt window appears. If DEFER=NO, the DBPROMPT window appears when the CONNECT statement is executed. If DEFER=YES, the DBPROMPT window appears the first time a pass-through statement is executed. The DEFER= option normally defaults to NO. The option defaults to YES if DBPROMPT=YES. You can override this default by explicitly specifying DEFER=NO.
determines when the connection to the DBMS occurs.
Default: NO
If DEFER=YES, the connection to the DBMS occurs when the first SQL pass-through statement is executed. If DEFER=NO, the connection to the DBMS occurs when the CONNECT statement occurs.
indicates the compatibility mode for variable names for the SQL pass-through facility. The default value is V7.For more information about the V7 or ANY values, see VALIDVARNAME= SAS System Option.
By default, DBMS column names are changed to valid SAS names, following these rules:
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. Specifying this option overrides the value of the SAS system option VALIDVARNAME= during (and only during) the SQL 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 netezza (server='myserver' database='myDB' user=myusr1
password=mypwd1 validvarname=v6)
create table work.foo as
select * from connection to netezza
(select "Amount Budgeted$", "Amount Spent$"
from annual_budget);
quit;
proc contents data=work.foo;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=work.foo; run;The primary reason for using this option would be in a SAS session that is running with option VALIDVARNAME=V7, but in which you have existing SAS code that references variables that use older naming rules.
specifies values for DBMS-specific arguments that PROC SQL needs to connect to the DBMS. Though they are optional for most databases, you must enclose them in parentheses if you include any. For information about these arguments, see the DBMS-specific reference section for your SAS/ACCESS interface.
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.
The CONNECT statement is optional for some DBMSs. However, if you do not specify it, the default values for all database connection arguments are used.
A CONNECT USING statement uses the existing connection that is established in the LIBNAME statement for the specified libref.
Any return code or message that the DBMS generates is available in the SQLXRC and SQLXMSG macro variables after the statement executes. For information about these macro variables, see Macro Variables for Relational Databases.
This example connects to an SAP ASE server and assigns the alias ASECON1 to it. SAP ASE is a case-sensitive database, so database objects are in uppercase, as they were created.
proc sql;
connect to sapase as asecon1
(server=MYSRV1 database=PERSONNEL user=MYUSR1
password=MYPWD1 connection=global);
%put &sqlxmsg &sqlxrc;You might be able to omit the CONNECT statement and implicitly connect to a database by using default values. For details, see the DBMS-specific reference section for your SAS/ACCESS interface.