BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DionisMotor
Fluorite | Level 6

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:

1.png

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

2.png

 

 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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
DionisMotor
Fluorite | Level 6

After discussion with  SAS support  :

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

ENCODING =UTF8 

 

View solution in original post

5 REPLIES 5
joeFurbee
Community Manager

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


Join us for SAS Community Trivia
SAS Bowl XLIII, The New SAS Developer Portal
Wednesday, August 14, 2024, at 10 a.m. ET | #SASBowl

Vince_SAS
Rhodochrosite | Level 12

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
Fluorite | Level 6

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
Fluorite | Level 6

After discussion with  SAS support  :

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

ENCODING =UTF8 

 

DionisMotor
Fluorite | Level 6
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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 3904 views
  • 4 likes
  • 3 in conversation