Using SAS/ACCESS® Interface to ODBC with MySQL driver version 5.1.7 returns only one row


Using SAS/ACCESS Interface to ODBC with the MySQL ODBC Driver (version 5.1.7) results in the return of only one row of data. A count shows multiple rows, but neither a DATA step nor a SQL procedure returns more than the first row of data.

Workarounds

  1. Move to a later release of the MySQL ODBC driver such as 5.1.8 where the problem appears to be corrected.
  2. Revert to a previous version of the MySQL ODBC driver. The problem does not appear when you use version 5.1.6 or earlier.
  3. If moving to an earlier release is not an option, you can also use dynamic cursors. Enabling dynamic cursors is a two-part step:
    1. Set the dynamic-cursor option on the driver, as follows:
      1. In Windows environments:
        • Open the ODBC Administrator and select or create your MySQL data source name (DSN).
        • Click Details to expand the options.
        • Choose the Cursors/Results tab.
        • Enable (check) the Enable dynamic cursors option.
      2. In UNIX environments:
        • Open the odbc.ini file for editing.
        • Add OPTION=32 or DYNAMIC_CURSOR=1 to the appropriate MySQL DSN section.
    2. Add the CURSOR_TYPE= option to your LIBNAME statement to enable dynamic cursors, as follows:

     

libname mytest odbc dsn=MySQL_DSN user=user-id password=password cursor_type=dynamic;

Note: If performance is affected negatively, you might need to use the READBUFF= option.