RODBC Cannot allocate memory

Question

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?

Ismaelisman

Are you using 32bit or 64bit R? – Tabasco

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) – Ismaelisman

Answer 1

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.

Median

Just tried your suggestion and unfortunately it did not resolve the problem. I get the same error. We have switched to using JDBC and it is working fine for us. – Ismaelisman

That is good to know. In my case it worked for SQL, I didn't try Oracle. – Median

this worked for me ... using odbcDriverConnect(...,rows_at_time = 1). Though I got a warning which I don't know if it is related: Warning message: In .Internal(sys.call(which)) : closing unused RODBC handle 2 – Muriel

Answer 2

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.

Fructify

Answer 3

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.

Elongation

The target DB is Oracle 11.2.0.4 on Windows x86. In this case we were just testing R locally on a SUSE Enterprise Linux x64 ultimately we will be using Shiny Server Pro. Testing unixODBC using isql worked fine. I will try using and instance of Oracle running on x64 SUSE to see if I get the same error. – Ismaelisman

Did a test using Oracle x64 11.2.0.4 running on an SUSE. Got the same error message – Ismaelisman