Hi.
I'm trying to force working SAS Viya 3.5 <-> ODBC(Exasol) connection. ( Driver EXASOL_ODBC-6.1.3 )
What I have:
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)
);
caslib Exasol desc="Exasol_Caslib"
datasource=( srctype="odbc"
username="c64"
password="..."
odbc_dsn="exasol"
SCHEMA="C64"
)
libref=Exasol ;
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.
Thanks for any hint.
After discussion with SAS support :
Next line in odbc.ini on server could help with loading data
ENCODING =UTF8
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
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
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
After discussion with SAS support :
Next line in odbc.ini on server could help with loading data
ENCODING =UTF8
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.