LIBNAME Statement for the JDBC Engine

Overview

This section describes the LIBNAME statement that SAS/ACCESS Interface to JDBC supports. For general information about this feature, see LIBNAME Statement for Relational Databases.

Here is the LIBNAME statement syntax for accessing JDBC.

For general information about the LIBNAME statement that is not specific to SAS/ACCESS, see LIBNAME Statement in SAS Global Statements: Reference.

Arguments

libref

specifies any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views.

JDBC

specifies the SAS/ACCESS engine name for the JDBC interface.

connection-options

provide connection information and control how SAS manages the timing and concurrence of the connection to the DBMS.

Here is how these options are defined.

Note: All of the following connection options are also valid in the CONNECT statement when you use the SQL pass-through facility (SQL procedure) to connect to your DBMS.

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

lets you connect to a JDBC database with a user ID that is different from the default ID. USER= is optional.

Note: USER= must be specified as a connection option and cannot be specified via the URL= option.

Alias: UID=

PASSWORD=<'>JDBC-password<'>

specifies the JDBC password that is associated with your user ID. PASSWORD= is optional. If the password contains spaces or nonalphanumeric characters, enclose it in quotation marks. If you do not want to enter your JDBC password in uncoded text on this statement, see PROC PWENCODE in the Base SAS Procedures Guide for a method to encode it.

Note: PASSWORD= must be specified as a connection option and cannot be specified via the URL= option.

Alias: PWD=

DRIVERCLASS="value"

specifies the JDBC driver to use for your database connection.

Alias: CLASS=

Example: driverclass="org.postgresql.Driver"

CLASSPATH="directory-path"

specifies additional resources, including JDBC drivers, to add to the JDBC class path. In SAS 9.4M9, third-party JDBC driver files must be installed in a required location. CLASSPATH= enables you to specify an alternate class path if you need a different class path for some LIBNAME statements. The directory-path must be a subdirectory of the required location. Most users do not need the CLASSPATH= option. When used, the CLASSPATH= option must be used in conjunction with the DRIVERCLASS= option.

• On UNIX, this is the required location in SAS 9.4M9:

  /SASHOME/AccessClients/9.4/DataDrivers/jdbc

  or, if SASHOME is not set:

  /usr/local/SAS/AccessClients/9.4/DataDrivers/jdbc

• On Windows, this is the required location:

  SASHOME\AccessClients\9.4\DataDrivers\jdbc

  or, if SASHOME is not set:

  C:\Progam Files\SASHome\AccessClients\9.4\DataDrivers\jdbc

URL="jdbc:driver-name://driver-connection-options"

specifies the JDBC connection string to use for your database connection.

Example: url="jdbc:postgresql://<server>:<port>/test"

See your JDBC driver documentation for a list of the JDBC connection options that your JDBC driver supports.

LIBNAME-options

specify how SAS processes DBMS objects. Some LIBNAME options can enhance performance, and others determine locking or naming behavior. The following table describes the LIBNAME options for SAS/ACCESS Interface to JDBC, with the applicable default values. This table also identifies LIBNAME options that are valid in the CONNECT statement in the SQL procedure.For details, see LIBNAME Options for Relational Databases.

SAS/ACCESS LIBNAME Options for JDBC

Option	Default Value	Valid in CONNECT Statement
ACCESS=	none
AUTHDOMAIN=	none
BATCH_UPDATE=	YES if BULKLOAD=NO and your JDBC driver supports batch updates NO otherwise
BULKLOAD=	YES	●
CONNECTION=	SHAREDREAD	●
DBCLIENT_MAX_BYTES=	matches the maximum number of bytes per single character of the SAS session encoding
DBCONINIT=	none	●
DBCONTERM=	none	●
DBCREATE_TABLE_OPTS=	none
DBGEN_NAME=	DBMS	●
DBLIBINIT=	none
DBLIBTERM=	none
DBMAX_TEXT=	1024	●
DBMAX_TEXT_TYPES=	LONG
DBMSTEMP=	NO
DBSASLABEL=	COMPAT
DEFER=	NO	●
DIRECT_SQL=	YES
DRIVER_TRACE=	none
DRIVER_TRACEFILE=	none
DRIVER_TRACEOPTIONS=	The trace log is overwritten and includes no time stamps or thread identification.
INSERTBUFF=	based on row length
LOGIN_TIMEOUT=	0	●
MULTI_DATASRC_OPT=	NONE
POST_STMT_OPTS=	none
PRESERVE_COL_NAMES=	NO (see Naming Conventions for JDBC)
PRESERVE_TAB_NAMES=	NO see (Naming Conventions for JDBC)
QUERY_TIMEOUT=	0	●
QUOTE_CHAR=	none
READBUFF=	automatically calculated based on row length	●
SCHEMA=	none
SPOOL=	YES
SQL_FUNCTIONS=	none
SQL_FUNCTIONS_COPY=	none
SQLGENERATION=	none
SUB_CHAR=	none
TRANSCODE_FAIL=	ERROR	●

JDBC LIBNAME Statement Examples

In this example, USER=, PASSWORD=, URL=, and DRIVERCLASS= are connection options.

libname mydblib JDBC user=myusr1 password=mypwd1 
        url="jdbc:postgresql://<server>:<port>/myDB" 
        driverclass="org.postgresql.Driver";

In this next example, the libref MYLIB uses the JDBC engine to connect to a PostgreSQL database. The connection options are DRIVERCLASS=, URL=, USER=, PASSWORD=, and CLASSPATH=. The CLASSPATH= LIBNAME option indicates that the vendor’s JDBC driver JAR file is contained in the C:\Progam Files\SASHome\AccessClients\9.4\DataDrivers\jdbc\postgresql directory.

libname x JDBC driverclass="org.postgresql.Driver"
   user=myuser 
   password="mypwd1" 
   classpath="C:\Progam Files\SASHome\AccessClients\9.4\DataDrivers\jdbc\postgresql"
   URL="jdbc:postgresql://myserver:myport/<postgres-database>";

proc print data=x.customers;
   where state='CA';
run;