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.
