LIBNAME Statement for the MySQL Engine

Overview

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

Here is the LIBNAME statement syntax for accessing MySQL.

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.

mysql

specifies the SAS/ACCESS engine name for MySQL 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 these connection options (preceding the list of MySQL LIBNAME statement options) are valid when used in the CONNECT statement with the SQL procedure.

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

specifies the MySQL user logon ID. If this argument is not specified, the current user is assumed. If the user name contains spaces or nonalphanumeric characters, you must enclose the user name in quotation marks.

PASSWORD=<'>MySQL-password<'>

specifies the MySQL password that is associated with the MySQL logon ID. If the password contains spaces or nonalphanumeric characters, you must enclose the password in quotation marks.

DATABASE=<'>MySQL-database<'>

specifies the MySQL database to which you want to connect. If the database name contains spaces or nonalphanumeric characters, you must enclose the database name in quotation marks.

SERVER=<'>MySQL-server<'>

specifies the server name or IP address of the MySQL server. If the server name contains spaces or nonalphanumeric characters, you must enclose the server name in quotation marks.

PORT=port

specifies the port used to connect to the specified MySQL server.

Default: 3306

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 MySQL, with the applicable default values. This table also identifies LIBNAME options that are valid in the CONNECT statement in the SQL procedure. For more information, see LIBNAME Options for Relational Databases.

SAS/ACCESS LIBNAME Options for MySQL

Option	Default Value	Valid in CONNECT
ACCESS=	none
AUTHDOMAIN=	none	●
AUTOCOMMIT=	YES	●
BL_DEFAULT_DIR=	temporary file directory that is specified by the UTILLOC= system option	●
BULKLOAD	NO	●
BL_USE_PIPE=	NO	●
CONNECTION=	SHAREDREAD	●
CONNECTION_GROUP=	none	●
DBCLIENT_MAX_BYTES	SAS session encoding
DBCOMMIT=	1000 (when inserting rows), 0 (when updating, deleting, or appending rows to an existing table)
DBCONINIT=	none	●
DBCONTERM=	none	●
DBCREATE_TABLE_OPTS=	none
DBGEN_NAME=	DBMS	●
DBINDEX=	NO
DBLIBINIT=	none
DBLIBTERM=	none
DBMAX_TEXT=	1024	●
DBMSTEMP=	NO
DBPROMPT=	NO	●
DBSASLABEL=	COMPAT
DEFAULT_AUTH_PLUGIN=	caching_sha2_password
DEFER=	NO	●
DIRECT_EXE=	none
DIRECT_SQL=	YES
ESCAPE_BACKSLASH=	NO
INSERTBUFF=	1
MULTI_DATASRC_OPT=	NONE
PRESERVE_COL_NAMES=	YES
PRESERVE_TAB_NAMES=	YES
QUALIFIER=	none
REREAD_EXPOSURE=	NO	●
RESULTS=	MEMORY
SCHEMA	DBMS specific
SPOOL=	YES
SQL_FUNCTIONS=	none
SQL_FUNCTIONS_COPY=	none
SSL_CA=	none
SSL_CERT=	none
SSL_CIPHER=	none
SSL_KEY=	none
UTILCONN_TRANSIENT=	NO

MySQL LIBNAME Statement Example

In the following example, the libref MYSQLLIB uses SAS/ACCESS Interface to MySQL to connect to a MySQL database. The SAS/ACCESS connection options are USER=, PASSWORD=, DATABASE=, SERVER=, and PORT=.

libname mysqllib mysql user=myusr1 password=mypwd1 database=mysqldb
server=mysrv1 port=9876;

proc print data=mysqllib.employees;
   where dept='CSR010';
run;