RODBC Limited Rows

Question

I have established an ODBC connection with Netezza (SQL Database). The connection is beautiful. However, R by default fetches 256 rows and limits the number of rows that it can pull.

If I run the query in Netezza, it will return the total number of rows (300k). I expect the same number of lines in R. However, he only returned 256 lines, slightly reduced from 300,000.

In the driver I am using NetezzaSQL version 7.00.02 NSQLODBC.DLL

I tried changing the prefetch account to zero in "Driver Options" from Control Panel> Administrative Tools> Data Sources (OBBC)> System DNS

This did not work. Any ideas?

Answer 1

I think RODBC does not work well with Netezza. Solution http://datamining.togaware.com/survivor/Database_Connection.html

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

Answer 2

You can also try using the 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") 

This way you do not need to deal with DSN, etc.

Answer 3

I know this is partly outdated, but the problem is not in the RODBC package. The problem is how you establish the ODBC connection, if you configure the connection in windows, you will see the last tab in the settings, where you can specify the number of rows that it will retrieve. And the default value is 256.