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.
... View more