LIBNAME Statement Specifics for MySQL

Overview

This section describes the LIBNAME statements that SAS/ACCESS Interface to MySQL supports and includes examples. For details about this feature, see the LIBNAME statement.

Here is the LIBNAME statement syntax for accessing MySQL.

LIBNAME libref mysql <connection-options ><LIBNAME-options>;

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.

USER=<'>user name<'>

specifies the MySQL user login 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=<'>password<'>

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

DATABASE=<'>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=<'>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. If you do not specify a value, 3306 is used.

LIBNAME-options

define 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. For more detail about these options, see LIBNAME Options for Relational Databases.

SAS/ACCESS LIBNAME Options for MySQL

Option	Default Value
ACCESS=	none
AUTHDOMAIN=	none
AUTOCOMMIT=	YES
CONNECTION=	SHAREDREAD
CONNECTION_GROUP=	none
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
DEFER=	NO
DIRECT_EXE=	none
DIRECT_SQL=	YES
ESCAPE_BACKSLASH=
INSERTBUFF=	0
MULTI_DATASRC_OPT=	none
PRESERVE_COL_NAMES=	YES
PRESERVE_TAB_NAMES=	YES
QUALIFIER=	none
REREAD_EXPOSURE=	NO
RESULTS=	MEMORY
SPOOL=	YES
SQL_FUNCTIONS=	none
SQL_FUNCTIONS_COPY=	none
UTILCONN_TRANSIENT=	NO

MySQL LIBNAME Statement Examples

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=testuser password=testpass database=mysqldb
server=mysqlserv port=9876;

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