SQL Pass-Through Facility Specifics for MySQL

Key Information

For general information about this feature, see SQL Pass-Through Facility.

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.
• The CONNECT statement database-connection-arguments are identical to its LIBNAME connection options.
  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=myusr1 password=mypwd1 server=mysrv1
        database=mysqldb port=9876);
quit;

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

proc sql;
   connect to mysql (user=myusr1 password=mypwd1 server=mysrv1
        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 myusr1) 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=myusr1 password=mypwd1 server=mysrv1
        database=mysqldb port=9876);
select *
   from connection to mysql
     (select * from customers
      where customer like '1%');
    disconnect from mysql;
quit;