SQL Pass-Through Facility Specifics for MySQL

Key Information

For general information about this feature, see 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.
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.

Note: Due to a current limitation in the MySQL client library, you cannot run MySQL stored procedures when SAS is running on AIX.

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;