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?

Redhot

How many rows are you expecting? And which ODBC connection library are you using? – Purdy

full size (300k). I suppose that you meant ODBC Data Source Administrator > System DSN – Redhot

And what kind of driver? – Purdy

im not sure what you mean by that... – Redhot

When I connect to Oracle via RODBC, the System DSN connects via an Oracle driver. It's the DLL Windows uses to manage the connection. – Purdy

NetezzaSQL version 7.00.02 NSQLODBC.DLL – Redhot

Set BelieveNRows=FALSE and rows_at_a_time=1 in your connect statement and try again. hosho.ees.hokudai.ac.jp/~kubo/Rdoc/library/RODBC/html/… – Purdy

Thanks. I added believeNRows=False seems to get me more rows.... Do I need to have rows_at_a_time=1 necessarily? – Redhot

If you look at the docs I linked to, several of the drivers out there don't work well unless you have that set to 1. It can't hurt. – Purdy

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.

Redhot

Correct, just add the argument to your query, for example results = sqlQuery(dbhandle, "SELECT * FROM TABLE", believeNRows=FALSE) – Rockweed

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.

Certiorari

Alex, I've tried this method using nzjdbc.jar on a Mac and am still getting 256 rows back. Any ideas? – Camisole

@FrankP., 256 is the default length of the pre-fetch buffer for the driver. Did you download the data using fetch(res, n = -1)? -1 would force all the records to be retrieved. – Certiorari

When I do fetch(res, n = -1) I still get 256 rows. If I do fetch(res, n = 10) and then fetch(res, n = -1) then I get 10 rows first and then 246 rows. So it seems the query only ever contains 256 rows. – Herrenvolk

This works for me if you change the SQL query to explicitly add a high limit RJDBC::dbGetQuery(conn, "select * from mytable LIMIT 2147483647") – Herrenvolk

Answer 3

I know this is kind of out-dated but the problem is not with the RODBC package. The problem lies in how you set up the ODBC connection if you configure the connection in windows you'll see a last tab in the settings where you can specify the amount of rows it'll fetch. And the default is on 256.

Officialdom