topic Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics in SAS Visual Analytics

Cannot access ODBC tables from MySQL server in SAS Visual Analytics

HugoVini — Tue, 16 Apr 2019 17:27:04 GMT

I've installed MySQL connector and added the connection on ODBC Data Source Administrator, I did as well the test: "connection successful".

So I went to import data on data preparation -> Server -> ODBC. After filled the fields I tried to find tables but was sended to me that message:

No one table was returned from the library selected

NOTE: This session is executing on the X64_SR12R2 platform.

NOTE: Additional host information:

X64_SR12R2 WIN 6.3.9600 Server

NOTE: SAS Initialization used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

NOTE: The autoexec file, C:\SAS\Config\Lev1\SASApp\WorkspaceServer\autoexec.sas, was executed at server initialization.
1
2 The SAS System 12:08 Tuesday, April 16, 2019

1 The SAS System 12:08 Tuesday, April 16, 2019

1
2
/* *********************************************** */
/* Assign the libref for the data in a ODBC table */
/*
2 ! *********************************************** */

LIBNAME db_odbc ODBC PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES
2 ! DATAsrc="DataBase" USER=adm PASSWORD="{sas002}341F943245F55BDC2B048AB10EEBE51E" ;

NOTE: Libref DB_ODBC was successfully assigned as follows:
Engine: ODBC
Physical Name: DataBase
3 options VALIDVARNAME=ANY VALIDMEMNAME=EXTEND;

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

Creeback — Tue, 16 Apr 2019 17:50:57 GMT

The tables/views contains 32 characters or more? The adm user can access in objects in MYSQL?

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

HugoVini — Wed, 17 Apr 2019 12:17:18 GMT

The table doesn't contains more than 32 characters.

I didnt understand that question: The adm user can access in objects in MYSQL?

I'm using ODBC with mysql driver connector, is it correct? Or should I use SAS connector to be possible acess my data base through the SAS Visual Analytics?

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

alexal — Wed, 17 Apr 2019 12:22:26 GMT

@HugoVini ,

Please run the following program and let me know if you will get anything in the output from proc contents:

LIBNAME db_odbc ODBC PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES DATAsrc="DataBase" USER=adm PASSWORD="{sas002}341F943245F55BDC2B048AB10EEBE51E" ;
proc contents data=db_odbc._all_ nods; run;

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

Creeback — Wed, 17 Apr 2019 12:26:48 GMT

Probably the user does not have grant in objects. That's my doubt..

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

HugoVini — Wed, 17 Apr 2019 12:43:04 GMT

The query ran successfully but generated warnings. See the Messages tab for more details.

WARNING: No matching members in directory.

That was the answer.

Do you have any tutorial, explaining clearly how I access a data base from SAS VA data preparation? Maybe I did something wrong during my process.

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

alexal — Wed, 17 Apr 2019 12:53:51 GMT

@HugoVini ,

Do you know what is weird? I do not see the SCHEMA= option in your LIBNAME statement. Do you know your database schema name? If so, add it to the LIBNAME statement and try again.

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

HugoVini — Wed, 17 Apr 2019 13:01:49 GMT

LIBNAME db_odbc ODBC PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES SCHEMA="bi_teste" DATAsrc="DataBase" USER=adm PASSWORD="{sas002}341F943245F55BDC2B048AB10EEBE51E" ;
proc contents data=db_odbc._all_ nods; run;

I executed that command and received the same answer I told you before

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

alexal — Wed, 17 Apr 2019 13:07:08 GMT

@HugoVini ,

Either schema isn't correct or you are using the wrong database. Did you try to connect to that database from a command line outside of SAS?

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

HugoVini — Wed, 17 Apr 2019 13:10:37 GMT

Yes I did, I'm using Dbeaver to do some queries, everthing is working well and the schema's name is right

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

Creeback — Wed, 17 Apr 2019 13:35:46 GMT

In this case is MS SQL Server:

Data Source (ODBC Name)

work for me.

Pre-requisites:

GRANT and objects names less or equal than 32 characters.

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

HugoVini — Wed, 17 Apr 2019 13:44:21 GMT

@Creeback

Show me your ODBC details, I would like to know which driver are you using.

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

Creeback — Wed, 17 Apr 2019 13:50:17 GMT

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

HugoVini — Wed, 17 Apr 2019 14:00:43 GMT

@Creeback

I did basically the same process but to MySQL.

seems to be correct

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

HugoVini — Wed, 17 Apr 2019 18:27:33 GMT

@Creeback @alexal

I found the problem, there is no SAS odbc driver available, so I cannot connect in no one data base.

Thanks for the help

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

Creeback — Wed, 17 Apr 2019 19:31:19 GMT

Nice!

Re: Cannot access ODBC tables from MySQL server in SAS Visual Analytics

Creeback — Wed, 17 Apr 2019 20:33:39 GMT

I don't have MySQL drivers, only MS SQL Server drivers.