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?

How many rows are you expecting? And which ODBC connection library are you using? — Maurice Reeves, Jun 18 '13 at 23:55
full size (300k). I suppose that you meant ODBC Data Source Administrator > System DSN — Luo Lei, Jun 19 '13 at 00:00
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. — Maurice Reeves, Jun 19 '13 at 00:10
Set `BelieveNRows=FALSE` and `rows_at_a_time=1` in your connect statement and try again. http://hosho.ees.hokudai.ac.jp/~kubo/Rdoc/library/RODBC/html/odbcConnect.html — Maurice Reeves, Jun 19 '13 at 01:33
Thanks. I added believeNRows=False seems to get me more rows.... Do I need to have rows_at_a_time=1 necessarily? — Luo Lei, Jun 19 '13 at 01:48
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. — Maurice Reeves, Jun 19 '13 at 02:52

score 8 · Answer 1 · edited Feb 10 '16 at 17:18

8

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.

edited Feb 10 '16 at 17:18

Daniel Sparing

2,163
15
20

answered Jun 19 '13 at 01:43

Luo Lei

649
2
9
19

Correct, just add the argument to your query, for example `results = sqlQuery(dbhandle, "SELECT * FROM TABLE", believeNRows=FALSE)` – DavidJ Aug 26 '14 at 14:29

score 2 · Answer 2 · answered Jun 19 '13 at 13:16

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.

answered Jun 19 '13 at 13:16

Alex Vorobiev

4,349
21
29

2

Alex, I've tried this method using nzjdbc.jar on a Mac and am still getting 256 rows back. Any ideas? – Frank P. Aug 04 '14 at 20:32
@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. – Alex Vorobiev Aug 06 '14 at 15:51
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. – Stuart Apr 10 '20 at 10:26
This works for me if you change the SQL query to explicitly add a high limit RJDBC::dbGetQuery(conn, "select * from mytable LIMIT 2147483647") – Stuart Apr 10 '20 at 13:15

score 1 · Answer 3 · answered Dec 13 '17 at 15:35

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.

RODBC connection- limited rows

3 Answers3