Syntax error - Informix - DB Query Reader - Preview/Evaluate

Hi Knimers,

I have to use an Informix DB for my back-end ETL processing. All is fine except when I need to use the DB Query Reader. In the Preview/Evaluate function, I get “A syntax error has occurred”. This is because Informix syntax for TOP/LIMIT is “SELECT FIRST nn * FROM TABLE_NAME”.

Hs anyone found a way around this type of error with other DBs which have a strange TOP/LIMIT syntax? Is there a setting in KNIME that I have not found which allows mapping of Key Words on SQL injection? Rather than rely on language dialects, would it be possible to use the JS Statement.setMaxRows() function (like SQL-Squirrel does) which passes the workload to the JDBC driver, or to have a choice of rownum-paramater(s)?

For ref: Select (SQL) - Wikipedia

Corporate Drifter.

Hi @acrey1, and welcome to the KNIME Community!

Are you saying that it is just the evaulate/preview that is not working with Informix on the db query node but that the node will return results when it is executed?

Hey,

The actual query works fine. The problem is that to do the preview, KNIME wraps the SQL in a “select * from (my query) limit 10” construct. This borks the Informix syntax parser (or, in fact any parser which does not support that type of record-sampling construct). I think Teradata may complain also, but I haven’t tried this. I believe that there is a myth in SQL-land that SQL-92 standardised the “LIMIT nn” clause, but this is not true.

CD.

Ah ok. That’s interesting. I haven’t used Informix but what intrigues me is that KNIME does evidently handle different databases in different ways but this is probably where there is specific dedicated connector node. (e.g. Oracle Connector) For example Oracle works fine, using the but doesn’t have a this “LIMIT nn” clause in its syntax either. Although later versions of Oracle now support the most recent ansi standardisation for limiting rows (the addition of fetch first n rows only ) KNIME applies the old Oracle syntax (as it would work across all Oracle versions) when I press the evaluate button, adding WHERE rownum <= 10.
I can only assume that the LIMIT nn clause it what is being applied by default on the generic nodes.

For Informix, I assume you are using the generic “DB Connector” node, as I don’t see a dedicated Informix Connector node? Unfortunately I don’t know if this is configurable anywhere. I’ve not seen any settings. Sorry I can’t be of more help at the moment.

1 Like