Database Looping no longer working

[zhuma]

I cannot get the database looping (legacy) node to work in an old workflow of mine since the latest update. I’m using the database connector (legacy) node to connect to a database via netezza driver, but when running the database looping node, I get an error of a duplicate row ID “row 0” when this is not the case. Any ideas? I am also still using the legacy nodes because I could not personally get the newer ones to connect to my database at all. Thank you.

[ipazin]

Hello @zhuma,

do you remember what was the issue with connection when using new (current) database framework? I suggest to give it another try and then use following Component:

DB Looping – tobias.koetter

Br,
Ivan

[zhuma]

Hi Ivan. I have the netezza driver installed under Preferences > KNIME. In the old Database Connector node, I just selected the driver as org.netezza.Driver and entered in the database URL and username and password. This still connects. In the new DB Connector node, I have the database type set to default, database dialect as SQL-92 (the only option which appears), driver name (the one I have set as Netezza), and the database URL and username and password. I am getting the following error:

ERROR DB Connector 3:380 Execute failed: DB Session a1b09451-3def-46da-9a1c-911209ed0e87 is invalid. Receiver class org.netezza.sql.NzConnection does not define or inherit an implementation of the resolved method ‘abstract boolean isValid(int)’ of interface java.sql.Connection.

[marvin.kickuth]

Hi @zhuma,

which version of KNIME are you using? You might have to specify the database driver in another place under preferences for the new DB nodes.

Kind regards
Marvin

[zhuma]

Hi Marvin. I am using KNIME 4.4.1 and I have specified it under Preferences > KNIME > Databases. Thank you.

[takbb]

@zhuma

I am getting the following error:

ERROR DB Connector 3:380 Execute failed: DB Session a1b09451-3def-46da-9a1c-911209ed0e87 is invalid. Receiver class org.netezza.sql.NzConnection does not define or inherit an implementation of the resolved method ‘abstract boolean isValid(int)’ of interface java.sql.Connection.

Hi @zhuma, Could this be similar to the issue outlined here on a different jdbc driver?

KNIME error when loading Dremio JDBC Driver

Hi Andreas, it seems as if the Dremio driver does not support the isValid() method. You can force KNIME to use a query to test the connection validity instead of using the not supported API method. To do so open the dialog of the connector node and go to the Advanced tab. In the tab add any valid Dremio query e.g. SELECT 0 that executes successfully as value of the Validation query setting. [image] You might also want to check the other advanced settings to see if they are compatible with Dr…

Basically it tries to call isValid which isn’t supported by the driver, so you need to supply a validation query in the config instead. Might not be, but looks very similar to me.

I came across this same issue recently when assisting with SQL Server too… buried in here… KNIME to SQL Server - #10 by takbb

@takbb

This is what I got…

That had me scratching my head, but fortunately an old post on the KNIME forum came to my rescue. This occurs when the jdbc driver doesn’t have an isvalid method (or something like that). In which case, we have to head to the config for the connector node again and open the Advanced tab, and in the “Validation query” option, type the following:

select 0

then press Apply and OK.

I don’t recall if it has to return zero, or just a value but obviously the query has to be syntactically correct for your database.

[zhuma]

@takbb

select 0

This worked amazingly! Thank you so much!

[zhuma]

Thank you for this! After Brian’s solution, I was able to use this perfectly!

[ipazin]

Hello @zhuma,

glad to see connection issue with “new” database framework is solved and workflow running again

Br,
Ivan