Cannot access ODBC tables from MySQL server in SAS Visual Analytics

[HugoVini]

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;

[Creeback]

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

[HugoVini]

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?

[alexal]

@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;

[HugoVini]

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.

[alexal]

@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.

[HugoVini]

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

[alexal]

@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?

[HugoVini]

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

[HugoVini · Accepted solution]

@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

[Creeback]

Nice!

[Creeback]

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

[Creeback]

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.

[HugoVini]

@Creeback 

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

[Creeback]

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