Help with Database Reader node

Hi all

I'm setting up a workflow that reads some data from a BMC Remedy instance via JDBC, using a vendor provided driver (https://developer.bmc.com/legal/arjdbc10/ARSystem_JDBCdriver.pdf)

I've successfully set-up the driver into KNIME v3.2.1 and I'm able to retrieve some data using the Database Reader node, but I'm noticing a quite strange behavior: only odd-numbered ROWIDs are returned at every time (Row1, Row3, Row5 and so on), and I end up getting only a half of the expected query results...

The BMC JDBC driver has no commercial support, so I don't discard a bug on the driver's getrow method that I suppose the node relies on, but I performed a quick test using a general purpose Java SQL Client (Squirrel SQL) using the same driver and the results are OK with complete query results everytime, so I'm not sure about why this happens with KNIME.

Is there any node/tool setting I can fiddle with? From my understanding of what I've read,  some other nodes offer the possibility to use an internal row numbering (Database Connection Table Reader), but it seems that the other nodes involved on this approach lead to the use of SQL subqueries, not supported by my JDBC driver...

Best Regards and, once again, thank you for your help,

--

Jorge

Hello Jorge,

this is indeed a strange that using the rowid affects the number of rows that are returned from the database. Only the Database Connection Table Reader has the option to not use the row id from the database to use this node you need one of the other db nodes which all use subqueries to get the table specification from the db. So I do not see a workaround for this problem with the DB nodes. What you could do is to write the data with another tool e.g. SquirrelSQL into a csv file and read it into KNIME with the File Reader.

Bye

Tobias

OK thank you Tobias, I will do so.

Best Regards,

 

--

Jorge

Hi all, just in case someone is using the same JDBC driver as me  (BMC ARSystem JDBC Driver)

I have been examining the differences between  the JDBC calls coming from KNIME and SquirrelSQL, and found that the driver incorrectly shifts the cursor one row forward  each time the JDBC getRow function is called (the Database Reader node does in order to get the RowID after retrieving each row, but Squirrel doesn't rely on it). That would explain why the results excludes one out of each two rows when I connect from KNIME..

 I've managed to modify the JDBC driver method and now it works as expected from KNIME....

It's clear for me that this is not KNIME's fault but , wouldn't make sense to add the possibility to use internal row numbering to the Database Reader node (as in Database Connection Table Reader node) in order to make it more resilient?

Once again, thank you for your help and for this great tool, I've begun to work with KNIME earlier this year and it has significantly simplified my life since then :-D

Best Regards,

--

Jorge

 

Hi Jorge,

this is indeed a strange behavior of the JDBC driver. I'm glad you could fix the problem. We will add the internal row numbering option to the Database Reader node with one of the next releases as well.

Bye

Tobias

Excellent, thank you Tobias!

Best Regards,

--

Jorge

We have just released a preview of the new database framework which is available via the labs update site. This comes with a new DB Query Reader node which no longer uses the db row id and thus should solve your problem. Give it a try and let us know if you encounter any problems.
Bye
Tobias