topic Re: How to configure a connection to a MySQL database using SAS/ACCESS Interface to ODBC in Administration and Deployment

How to configure a connection to a MySQL database using SAS/ACCESS Interface to ODBC

pedrosantos_key — Tue, 02 Oct 2018 14:01:15 GMT

Hello,

I need to configure a MySQL connection on the SAS server but only using the SAS/Access Interface to ODBC.

Unfortunately the client doesn't want to get the SAS/Access interface to MySQL, which will be much easier.

Will be this possible to connect via ODBC Connector? If yes, I've already searched in my server for the odbc.ini and I found two:

One placed on /opt/sas/software/ and the other on /opt/sas/software/SASHome/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver/. Should I use the first one?

On the other hand, I need to download to the unix server the MySQL connector right? Should I get the connector from here, https://dev.mysql.com/downloads/connector/odbc/, and place it in a folder on Server? Specific folder or, for example, tmp is fine?

Thanks,

Pedro

Thanks in advance,

Pedro

Re: How to configure a connection to a MySQL database using SAS/ACCESS Interface to ODBC

alexal — Tue, 02 Oct 2018 14:21:29 GMT

Yes, you can install and use unixODBC.

Re: How to configure a connection to a MySQL database using SAS/ACCESS Interface to ODBC

SimonDawson — Wed, 03 Oct 2018 11:13:32 GMT

If you are on RHEL based system the mysql-connector-odbc package in the repos will suck in unixODBC as a dependency. Update an odbc.ini, if this system is only use for SAS /etc/odbc.ini could be used as the global default DSN's.

Sometimes when you use unixODBC there is some confusion for the runtime linked which libodbcinst.so.2 library to load. There is a line inside $SASROOT/bin/sasenv that has the WIP driver path. Comment that line out and your SAS sessions should load all the unixODBC libraries that came out the package and you'll be able to connect to a MySQL data source.

Re: How to configure a connection to a MySQL database using SAS/ACCESS Interface to ODBC

nicocba — Wed, 03 Oct 2018 13:43:52 GMT

You can check which odbc.ini its in use in your sasenv_local file (*SASHOME*/SASFoundation/9.4/bin/sasenv_local
)

Then you can use UnixODBC mysql driver to connect to MySQL databases

Re: How to configure a connection to a MySQL database using SAS/ACCESS Interface to ODBC

JuanS_OCS — Thu, 04 Oct 2018 09:55:53 GMT

Hello @pedrosantos_key,

please refer to:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-build-the-latest-unixODBC-driver-manager-for-Linux/ta-p/231907

and

https://communities.sas.com/t5/SAS-Communities-Library/How-to-configure-SAS-ACCESS-Interface-to-ODBC-on-UNIX-Platforms/ta-p/231900

I think they will help you very much 🙂 Thanks to @KatT_sas for the post in our Library!

Kind regards,

Juan

Re: How to configure a connection to a MySQL database using SAS/ACCESS Interface to ODBC

SimonDawson — Thu, 04 Oct 2018 10:04:31 GMT

For MySQL there are Red Hat built packages in their repository that work. If you can use the software Red Hat compile and ~~manage~~ support for you then that is a good idea. I'd not go down the compiling unixODBC yourself in this specific situation.