RODBC connection- limited rows

Question

I set up an ODBC connect to a Netezza (SQL database). The connection is fine. However, R only pulls out 256 rows by default and restricts the number of rows it can pull out.

If I ran the query in Netezza, it would return a total number of rows (300k). I am expecting the same number of rows in R. However, it only returned 256 rows quite a bit short from 300k.

The driver I am using NetezzaSQL version 7.00.02 NSQLODBC.DLL

I tried to change the pre-fetch count to zero in the "Drivers Option' from Control Panel > Administrative Tools > Data Sources(OBBC) > System DNS

It didn't work. Any ideas?

Answer 1

I think RODBC acts poorly with Netezza. A solution http://datamining.togaware.com/survivor/Database_Connection.html

just add believeNRows=FALSE to either your sqlQuery or odbcConnect call (use the later if you also use sqlFetch.

Answer 2

You can also try using JDBC driver:

library(RJDBC)
drv <- JDBC("org.netezza.Driver", "nzjdbc.jar", "'")

conn <- dbConnect(drv, "jdbc:netezza://host:5480/database", "user", "password")

res <- dbSendQuery(conn, "select * from mytable")

That way you don't have to deal with DSNs, etc.