SAS Viya - ODBC Connection

[DionisMotor]

Hi.

I'm trying to force working SAS Viya 3.5 <-> ODBC(Exasol) connection. ( Driver EXASOL_ODBC-6.1.3 )

What I have:

• Table in Exasol DB defined as

CREATE TABLE CITIES (
               CITY_ID      DECIMAL(9,0),
               COUNTRY_CODE VARCHAR(2) UTF8,
               ZIP_CODE     VARCHAR(20) UTF8,
               CITY_NAME    VARCHAR(200) UTF8,
               DISTRICT     VARCHAR(50) UTF8,
               AREA         VARCHAR(50) UTF8,
               AREA_SHORT   VARCHAR(50) UTF8,
               LAT          DECIMAL(9,6),
               LON          DECIMAL(9,6)
           );​

• plus one row of data:

•  Definition of ODBC connection:

caslib Exasol desc="Exasol_Caslib"
datasource=( srctype="odbc"
username="c64"
password="..."
odbc_dsn="exasol"
SCHEMA="C64"
)
libref=Exasol ;

• loading data

proc casutil incaslib="Exasol" outcaslib="Exasol" ;
  droptable casdata="CITIES" quiet;
	load   casdata="CITIES" 
	datasourceoptions=(	SCHEMA="C64" )
	VARS=(	(NAME="CITY_ID" NFL=9 NFD=0)
			(NAME="COUNTRY_CODE")
           (NAME="ZIP_CODE"    )
           (NAME="CITY_NAME"   )
           (NAME="DISTRICT"    )
           (NAME="AREA"        )
           (NAME="AREA_SHORT"  )
			(NAME="LAT" NFL=9 NFD=6)
 			(NAME="LON" NFL=9 NFD=6)
		)
    casout="CITIES";
quit;

as a result I got:

proc sql;
select * from Exasol.CITIES;

ERROR: Invalid characters were present in the data.
ERROR: An error occurred while processing text data.
ERROR: Invalid characters were present in the data.
ERROR: An error occurred while processing text data.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

 After I tried to use CLIENT_ENCODING "UTF-16" parameter

caslib Exasol desc="Exasol_Caslib"
     datasource=( srctype="odbc"
                  username="c64"
                  password="..."
                  odbc_dsn="exasol"
	SCHEMA="C64"
  CLIENT_ENCODING="UTF-16" 
				)
libref=Exasol ;

Data extracted correctly, but I have new ERROR when I'm trying save table:

proc casutil incaslib="Exasol" outcaslib="Exasol" ;
	SAVE  CASDATA="CITIES"
		  CASout="CITIES"
	datasourceoptions=( SCHEMA="C64" )
replace
 ;quit;

83 ! SAVE CASDATA="CITIES"
84 CASout="CITIES"
85 datasourceoptions=( SCHEMA="C64" )
86 replace
87 ;quit;
NOTE: Executing action 'table.save'.
NOTE: Performing serial SaveTable action using SAS Data Connector to ODBC.
ERROR: Invalid attribute/option identifier
NOTE: The target table has been dropped due to a failure during the Save action.
ERROR: Function failed.
ERROR: The action stopped due to errors.
NOTE: Action 'table.save' used (Total process time):
NOTE: The Cloud Analytic Services server processed the request in 0.579997 seconds.
ERROR: Failed to save the table "CITIES" from the "Exasol" caslib to the "Exasol" caslib.
NOTE: The SAS System stopped processing this step because of errors.

So, now, I'm run out of ideas what else I could try.

• Do you know why I need use UTF-16 for fetching data if both Exasol and SAS are using UTF-8 ?
• What wrong with saving data ? (this is more crucial question) Should I use something else for saving table from CAS to ODBC ?

Thanks for any hint.

[joeFurbee]

Hi @DionisMotor.

One of my colleagues suggested contacting Technical Support for this issue. He also referenced and SASGF paper that may shed some light from the ODBC side log. Here is his entire post:

Sounds like a tech support issue to me, this should not happen. At the same time, you might want to reference this SASGF 2017 paper. It has a section explaining how to set debug options for SAS Data Connectors. Just search the paper for the word "debug", the info starts on page 11. It shows how proc cas; action log /…; and proc casutil; contents …; can be used to display what the ODBC driver reports in terms of data types returned to CAS. Wouldn't be surprised if that is one of the first things TS would like to know.

Thanks,

Joe

[Vince_SAS]

Here is the link to the SAS Global Forum 2017 paper:

Introduction to SAS Data Connectors and SAS Data Connect Accelerators on SAS Viya
https://support.sas.com/resources/papers/proceedings17/SAS0331-2017.pdf

Vince DelGobbo

SAS R&D

[DionisMotor]

Thanks a lot for a hint.  Debug information was really helpful.

I'm in contact with SAS support now. Let's hope we could solve this issue.

And side question: according to what I see in logs/documentation - when I'm trying "save table" to ODBC library I can't append data to already existing table.

Either SAS expecting no target table or with replace option SAS just remove it and recreate with own DDL. Did I miss such possibility or it is undocumented function?

Denys

[DionisMotor · Accepted solution]

After discussion with  SAS support  :

Next line in odbc.ini on server could help with loading data

ENCODING =UTF8 

[DionisMotor]

So, FYI, final solution was : do not use ODBC 🙂
We switched to JDBC driver and it works better. I'm still have open question about performance, but I guess this also will be solved.