SAS/ACCESS Interface for MySQL

LIBNAME Statement Specifics for MySQL

This section describes the LIBNAME statements that SAS/ACCESS Interface to MySQL supports and includes examples. For details about this feature, see Overview of the LIBNAME Statement for Relational Databases.

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, while 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 rows, deleting rows, 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
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;

Top of Page