RODBC Cannot allocate memory

5
votes

Simple R script

library(RODBC)
odbChannel <- odbcConnect(dsn = "CTPRD03", uid = "BD_RPT_RO", pwd = "****")
df.test <- sqlQuery(channel = odbChannel, query = "select * from DUAL;")
df.test
close(odbChannel)

Produces the following error

shiny@narc07shiny1dev:~/software> Rscript ./RODBC_SIMPLE_TEST.r
Error in odbcQuery(channel, query, rows_at_time) :
'Calloc' could not allocate memory (18446744073709551616 of 22816 bytes)
Calls: sqlQuery -> odbcQuery -> .Call
Execution halted
Warning message:
closing unused RODBC handle 1

Tested odbc outside of R and was able to get results. I'm not sure where the problem is at this point. I'm thinking it is RODBC.

Already uninstalled RODBC and reinstalled the RODBC package and there were no errors during that process, but still have the same results.

Found one case out there with the same problem but there wasn't any resolution. What would be next steps in isolating the problem, any suggestions?

3
runixodbcrodbc
Are you using 32bit or 64bit R? – Matthew Plourde
It does appear that there might be an issue x86 and x64 ODBC and R unixODBC-32bit-2.2.12-198.17 R version 3.1.2 (2014-10-31) -- "Pumpkin Helmet" Copyright (C) 2014 The R Foundation for Statistical Computing Platform: x86_64-suse-linux-gnu (64-bit) – klaasb01

3 Answers

10
votes

I had a similar problem and fixed by adding rows_at_time = 1 to the connection.

odbChannel <- odbcConnect(dsn = "CTPRD03", uid = "BD_RPT_RO", pwd = "****", rows_at_time = 1)

I also found this blurb in the documentation:

https://cran.r-project.org/web/packages/RODBC/RODBC.pdf

Several errors which have been reported as bugs in RODBC 1.3-0 which were in fact ODBC driver errors that can be circumvented by setting rows_at_time = 1 (and the warning under that argument has always been there). The drivers involved have been third-party Oracle drivers and old SQL Server drivers.

0
votes

What type of database architecture are you connecting to? If it doesn't match the version of R you're using, the query won't run. For example, if you're using 64-bit R but you are trying to connect to a Microsoft Access 2007 or earlier database (which is 32-bit) you won't be able to run the query.

If you're using R Studio, go to the 'Tools' menu then 'Global Options' and you can change the version of R you're using to match that of the database.

0
votes

I kept having this problem for large tables, worked perfectly for smaller tables but not more than 10,000 rows. I was using: con <- odbcDriverConnect ("driver={SQL Server}; server=SERVERName; database=dBName; uid=Name; pwd=password")

I simply changed the driver from 'SQL Server' to 'ODBC Driver 17 for SQL Server' and now I can work with table of any size.