You can translate the question and the replies:

Error updating data from SAS via ODBC

We have connected a SAS Server to Denodo via the postgresql ODBC connection. We can access the views created in Denodo as a SAS library and read data. The views appear in SAS as SAS datasets. However, when attempting to update or insert data, the following error is returned in SAS: ERROR: CLI execute error: ERROR: Syntax error: Exception parsing query near '"' java.sql.SQLException: Syntax error: Exception parsing query near '"'; Error while executing the query We have tried both PROC SQL statements in SAS, and also APPEND statements in a DATA step, but we get the same error. We put in some logging of the ODBC driver, and it is sending the following SQL statement to Denodo: INSERT INTO TableA ("column_a") VALUES ( $1 ) We have tested reading and writing data via the postgresql ODBC driver from Excel (using VBA code), and the INSERT statement works fine when there are no double quotes around the column name. When we add the double quotes to the column name in the SQL statement, we get the same error that we see in SAS above. So it appears that Denodo does not like the double quotes around the column names in the INSERT statement. Is there a setting in Denodo to accept ANSI quoted identifiers (i.e. double quotes around column names) in INSERT and UPDATE statements? We are using Denodo Express 5.5 and the south-bound database is MS SQL Server.
user
09-12-2015 20:48:43 -0500

4 Answers

I have been able to reproduce the same problem using the UNICODE driver so I assume you are using the UNICODE driver too. After that I tried using the ANSI driver and I was able to run the insert query surrounding the column names with double quotes and it works fine. You may try to use the Postgresql ODBC ANSI driver in order to allow the double quotes in insert queries. Hope this helps.
Denodo Team
10-12-2015 06:52:10 -0500
Hi, Thanks for your response. Yes we are using the UNICODE driver. Your answer led us to another solution that fixed the problem. By setting the quote_char option in the libname statement, we can get it to work with the UNICODE driver i.e. libname denodo odbc dsn=denodo_dsn quote_char= ""; Thanks for your help.
user
10-12-2015 16:27:05 -0500
Denodo Team We are also facing same issue but a a step back as we are trying to connect Denodo from SAS and not being able to. So far we have tried: libname test odbc datasrc=Denodo_Dev; ERROR: CLI error trying to establish connection: [unixODBC]invalid connection option "krbsrvname" Note: We removed the krbsrvname option from the ini file and still returned the same error “invalid connection option "krbsrvname"”. Also,connection thru Unix ( server hosting SAS) ODBC ISQL works but connection using SAS manager doesn't as shown above.
user
04-05-2017 17:02:25 -0400
Hi, After reading the following article about setting an ODBC connection from SAS at http://www.ryslander.com/sas-odbc-connection-directly-in-sas/, I think you should check if you are using the "TRUSTED_CONNECTION=yes" option. If you want to create the connection providing a user name and password instead of using the Active Directory you should try disabling that option. Hope this helps!
Denodo Team
24-05-2017 11:51:11 -0400
You must sign in to add an answer. If you do not have an account, you can register here