A syntax errore has occurred in "DB table selector"

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.

thank for you support.

You would have to use an SQL Statement like

SELECT * FROM informix.my_table

You might want to check out the:

https://docs.knime.com/2020-07/db_extension_guide/index.html

And an example of the usage of SQL/DB databses

https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_h2_database/m_001_h2_db

Hi @giocop,

I assume your SQL statement will need to read -

Select
  *
From
  ute_utente

This should work.

Matt

1 Like

Hi @giocop,

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 :wink:

welcome to KNIME Community!

Br,
Ivan

1 Like

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:

Hi @giocop,

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.

Br,
Ivan

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,

not exactly sure in which cases you get syntax error. Would guess it can be driver/DB related…

Br,
Ivan

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.

Bye
Tobias

2 Likes

hi all,
DB query reader works fine, thanks a lot

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.