How to use Netezza jdbc driver in KNIME client tool

KNIME Analytics Platform
1

I am new to Knime and I could test SQL Server and Oracle connections fine using KNIME client tool. But, though I have Netezza jdbc driver installed in my PC, I don't see that in the client tool in the drop down list of database driver in the 'Database Reader' node. Any help to make Netezza connection to work is greatly appreciated.

2

Have you registered the driver in the Databases pane of your KNIME Preferences?  If not, from the KNIME Preferences page search for database and follow the instructions there to register additional drivers.  Keep us posted on your progress. 

3

Thanks for your reply.

I have NZjdbc.jar installed in my PC. Thanks to your reply, I registered the driver using preferences and I could see this driver in 'database reader' now.

I used the connection string of the format: jdbc:netezza://<HOST>:<PORT>/<DATABASE_NAME>

When I execute the node, I am getting the following error:

execution failed: java.lang.AbstractMethodError:org.netezza.sql.nzresultset.getNString(I)LJava/lang/string

May be I am not using the right version of the driver. I will check that.

4

Yes, this very much looks like your JDBC driver is too old. The driver must support at least JDBC 4.0.

5

I got this error with 7.0.4.3 jdbc driver, later came to know that Netezza server version is 7.0.0.11. So I uninstalled 7.0.4.3 and installed 7.0.0.11 Netezza jdbc driver in my PC to match with the server version. I am getting the same error still. 'Fetch metadata' works in the 'Database reader'. Only the query seems to fail.

6

It seems there is no JDBC 4.0-compliant driver for Netezza. Without this you cannot use it with KNIME.

7

Thanks a lot for explaining why Netezza JDBC driver is not working fully. I was trying to check about JDBC 4.0 compliance. But somehow I couldn't do that, that is why the delay in replying.

Thanks a lot for your help and it is much appreciated!

8

It's been awhile since this last post so I wanted to see if anyone has successfully connected KNIME to Netezza. Thi sis our primary datasource and clearly a show stopper for us using KNIME if we can't get it working. Currently I get the following error when trying to read data from the DB:

ERROR Database Reader 0:7 Execute failed: org.netezza.sql.NzResultSet.getNString(I)Ljava/lang/String;

 

9

This is not a KNIME issue but the Netezza-JDBC driver that is not JDBC 4.0-compliant (this version was released already in 2007). You should probably ask the vendor about their plans for an updated driver.

10

Maybe it is a bit late but I want to send my solution here if someone in future ran into the same problem. I noticed the same problem working with netezza driver. I was using the latest netezza driver and for reading from some netezza tables I would get the same error :

Could not fetch data from database, reason: Method org/netezza/sql/NzResultSet.getNString(I)Ljava/lang/String; is abstract

So I did some investigation then I released that the problem is with tables that have NVARCHAR data type columns. If you filter those columns everything works fine. Another solution would be to cast your NVARCHAR columns to VARCHAR and that would solve the problem. So instead of:

SELECT NVARCHAR_COLUMN FROM TABLE

use

SELECT CAST(NVARCHAR_COLUMN AS VARCHAR(50)) AS VARCHAR_COLUMN FROM TABLE

and you should be good.

Hope this helps.

4 Likes
11

Not too late at all. Just right in time. Will test it out. Was waiting on this.

Thanks!

12

Thanks for posting! you saved my bacon :)

13

Oh thanks so much. It worked for me too.