topic Re: Connect my mysql to SAS using ODBC in New SAS User

Connect my mysql to SAS using ODBC

adilar39 — Wed, 19 Jan 2022 07:38:44 GMT

Hello everyone,

My office just installed SAS Viya 3.5 last November and my team leader ask me to connect the database to SAS.

The database is in MySQL format, and our SAS doesn't have data connector for MySQL, instead, we have ODBC connector. I've tried the solution from this post below, and still doesn't work.
https://communities.sas.com/t5/SAS-Programming/Suggest-me-the-Connectivity-code-for-SQL-in-SAS/td-p/471985

This is the code that I use:

Libname New_lib ODBC
NoPrompt="Driver=SQL Server;Server=10.15.36.146;
Database=mysql;Uid=root;pwd=********";

And the error that showed up:

ERROR: CLI error trying to establish connection: [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found

ERROR: Error in the LIBNAME statement.

I suspect that I need to install this connector

https://dev.mysql.com/downloads/connector/odbc/

But maybe is there any other way to connect the database without installing that? I don't have access to the server-side of SAS Viya and I'm not familiar with Linux too.

Thanks in advance.

Re: Connect my mysql to SAS using ODBC

LinusH — Wed, 19 Jan 2022 12:48:29 GMT

First, I don't you can use a SQL Server driver to access MySQL, you need a specific driver for that.

Second, are you sure the rest of the parameters are correct, is your MySQL database really called mysql?

Third, you should never use root password for this kind of work, should be exclusive for server maintenece personnel only.

Re: Connect my mysql to SAS using ODBC

Sajid01 — Thu, 20 Jan 2022 12:38:19 GMT

Your question is

"I suspect that I need to install this connector https://dev.mysql.com/downloads/connector/odbc/

But maybe is there any other way to connect the daabase without installing that? I don't have access to the server-side of SAS Viya and I'm not familiar with Linux too. "

Well you need to install the SAS Access interface to ODBC or SAS Access interface to MySQL. Both are licensed products.

SAS Access interface to ODBC helps you to connect to more databases, but will require little more configuration.

You can have more details here https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/titlepage.htm

Re: Connect my mysql to SAS using ODBC

adilar39 — Mon, 24 Jan 2022 08:22:42 GMT

For the first point, I thought from the post that I linked in the question, "SQL Server" is only the default name in the installation, so I just used the same. But of course, it didn't work.
The second point, yes, the database is named mysql, but it only contains dummy data. I just want to see if I can connect it to SAS.
The third point, that's the id and password that was given to me.

Thank you for answering my question. But it looks like I'm the one whos confused with the job that was given to me.
I already found another way from this paper:
https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2020/4209-2020.pdf

Re: Connect my mysql to SAS using ODBC

adilar39 — Mon, 24 Jan 2022 08:29:27 GMT

Our SAS have SAS Access interface to ODBC, and from the post that I linked in the question, it showed that you can use that interface to connect MySQL format database to connect it to SAS, but I still can't figure it out.

I've read the documentation that you provided, and it's a bit complex for me.

Thank you for answering my question. But it looks like I'm the one whos confused with the job that was given to me.
I already found another way from this paper:
https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2020/4209-2020.pdf