hello everyone, i’m new in KNIME.
i created a project and i need to connect to an informix DB. i put a “DB connector” node and it seems to work correctly, then i added the “DB table Selector” node, i put my SQL statement but when i evaluate it response is “A syntax error has occurred”.
I already tried to delete the “ideintifier delimiter” but syntax error is still happened.
you don’t need to write custom query if all you want is to select * from specific table. It is enough to specify that table in table filed so uncheck Custom Query option
i tried to write sql statement but unfortunatelly it doesn’t work, i also tried unchecking “custom query” but when i retrieve db data, i get syntax error:
does this happen regardless of what table you select? Can you try DB Query Reader node after connection node?
Also checking or adding your log file would help see what is going on. Log file can be found at "...\knime-workspace\.metadata\knime\knime.log". Please before adding log file check that there are no sensitive information in it.
hi @ipazin
thank for reply, i tried DB Query Reader node and it works. But i don’t understand why when i “evaluate” query i get syantax error (should be “FETCH FIRST 10 ROWS ONLY” string appended after query?)
Hi @giocop,
KNIME Analytics Platform uses sub-queries when automatically creating the statements. It also uses LIMIT when retrieving the metadata of a query. This might cause problems with Informix. According to the documentation the LIMIT statement can not be used in sub-queries. However I’m not sure about this.
To get a better idea of which query fails you can enable the JDBC logger via the Advanced tab of the DB Connector node. This way you can check the statement that KNIME automatically generates and sends to the database by looking at the log file. The Advanced tab also allows you to tweak the statement generation such as the identifier delimiter.
If the automatically generated statements cause problems you can always use the DB Query Reader which sends the unaltered statements to the database for execution.