SAS/ACCESS Interface for MySQL

SQL Pass-Through Facility Specifics for MySQL

For general information about this feature, see Overview of the SQL Pass-Through Facility. MySQL examples are available.

Here are the SQL pass-through facility specifics for MySQL.

The dbms-name is mysql.
If you call MySQL stored procedures that return multiple result sets, SAS returns only the last result set.
Due to a current limitation in the MySQL client library, you cannot run MySQL stored procedures when SAS is running on AIX.
Here are the database-connection-arguments for the CONNECT statement.

USER=<'>MySQL-login-ID<'>

specifies an optional MySQL login ID. If USER= is not specified, the current user is assumed. If you specify USER=, you also must specify PASSWORD=.

PASSWORD=<'>MySQL-password<'>

specifies the MySQL password that is associated with the MySQL login ID. If you specify PASSWORD=, you also must specify USER=.

DATABASE=<'>database-name<'>

specifies the MySQL database.

SERVER=<'>server-name<'>

specifies the name or IP address of the MySQL server to which to connect. If server-name is omitted or set to localhost, a connection to the local host is established.

PORT=port

specifies the port on the server that is used for the TCP/IP connection.

Operating Environment Information: Due to a current limitation in the MySQL client library, you cannot run MySQL stored procedures when SAS is running on AIX. [cautionend]

Examples

This example uses the alias DBCON for the DBMS connection (the connection alias is optional):

proc sql;
   connect to mysql as dbcon
       (user=testuser password=testpass server=mysqlserv
        database=mysqldb port=9876);
quit;

This example connects to MySQL and sends it two EXECUTE statements to process:

proc sql;
   connect to mysql (user=testuser password=testpass server=mysqlserv
        database=mysqldb port=9876);
   execute (create table whotookorders as
      select ordernum, takenby,
             firstname, lastname, phone
         from orders, employees
         where orders.takenby=employees.empid)
      by mysql;
   execute (grant select on whotookorders
            to testuser) by mysql;
   disconnect from mysql;
quit;

This example performs a query, shown in highlighted text, on the MySQL table CUSTOMERS:

proc sql;    
connect to mysql (user=testuser password=testpass server=mysqlserv
        database=mysqldb port=9876);
select *
   from connection to mysql
     (select * from customers
      where customer like '1%');
    disconnect from mysql;
quit;

Top of Page