Database Table Selector with Oracle DB and comments

Hi there,

I use KNIME 3.7.1 and an Oracle Database. When I execute

select 1 from dual
--comment

it gives me an error (ORA-00907) in the Database Table Selector node. However, it does not give me an error when I execute it in a Database Reader node (no matter whether connected to a Database Connector node or not). The other comment syntax

select 1 from dual
/*comment*/

works in both nodes by the way.

Hey Agaunt,

Thanks for your post. I was also able to reproduce your issue for the inline comment database table selector (across different db types), so I am passing this on to our engineering team. I suppose for now the best thing to do if you need to use comments and database table selector is to use block comment formatting.

Cheers,
Gamble

1 Like

Hi Agaunt,
I was testing this in the new database framework which we released with KNIME 4.0 and had the same problem when I executed the data preview in the DB outport view. The reason for this is that KNIME creates a lot of sub-queries where the selected table is used within the query so your query

select 1 from dual
–comment

ends up in a query like this

SELECT * FROM (select 1 from dual
–comment) “tempTable_492387802718279087” WHERE rownum <= 100

where the the temp table alias and the rownum get commented out causing the exception. However when you add a line break at the end of your query you no longer get the error because this will result in the following query

SELECT * FROM (select 1 from dual
–comment
) “tempTable_2469039946033833958” WHERE 1 = 0 ;

which no longer causes any problems.
To get a better idea of which queries are send to the database you can enable the JDBC logger in the Advanced tab of the corresponding connector node e.g. the Oracle Connector as shown below:


All queries that are send to the database are then also logged into the KNIME log with debug level.
Bye
Tobias

1 Like