Greetings - have just upgraded to 4.0 and am generally very happy!
I’m trying to convert my database operations off of the legacy nodes, but am running into an issue with DB table selector. I read from two databases currently - one uses MS SQL, and I converted this one without issue.
The issue I have comes from using the general DB connector and the Informix JDBC driver. The output of the connector gives the correct connection string and executes OK. When I try to replicate my legacy selector node, even a simple table select built using the graphical dialogues gives the error “java.sql.SQLSyntaxErrorException: A syntax error has occurred.”
When I try the generated SQL in DBeaver, it also gives a SQL error - if I remove the quotes from the temptable name, it can execute. Is this behavior configurable, and if so how do I stop it from quoting the temptable names?
Hello ebogart,
Welcome to the KNIME community and thanks for using the new database framework.
The problem with Informix might be due to the default settings of the DB Connector node. Bye default the new framework uses " to quote all identifier. I read the documentation but I’m not sure if I understand it correctly. It seems you need to enable identifier quoting via a DB_LOCAL environment variable but I don’t know how. However you can also disable the identifier quoting in KNIME by simply removing the two delimiter in the Advanced tab of the DB Connector node (see below). You can also change them if the default quote character isn’t " for Informix.
I hope this helps. If not it would be great if you could enable the JDBC logging also via the Advanced tab (see above) and then send me the log file via private message with the full error message. To view the log file in KNIME simply go to View->Open KNIME log and then to File->Save as to save it on your hard disc.
Bye
Tobias
Tobias, thank you so much for the very specific guidance. I can confirm that the quoted identifier setting is exactly the problem, and by eliminating that setting I was able to proceed.
It does seem that in Informix databases, quoted identifiers require an environment variable to be set in the database and our particular instance was not set to allow it.
I will also say a big thanks for the performance boost 
