dbGetQuery results in Error in new_result(connection@ptr, statement, immediate) : std::bad_alloc error

[nbfcdatascience]

I get a bad_alloc error when trying to connect to a DB2 database (called ODS below) via 64bit DB2 drivers via 64bit R/Rstudio installed in Linux (Ubuntu 18.04)

Code

library(odbc) con <- dbConnect(odbc::odbc(), "ODS", uid = "sample_user",pwd="password",timeout = 10) --connects successfully

a <- dbGetQuery(con,'SELECT * FROM SAMPLE_DATABASE') --Error in new_result(connection@ptr, statement, immediate) : --std::bad_alloc

On searching, RODBC driver also results in a similar error:

library(RODBC) myconn <-odbcConnect("ODS", uid="sample_user", pwd="password") sqlQuery(myconn, "SELECT * FROM SAMPLE_DATABASE") --Error in odbcFetchRows(channel, max = max, buffsize = buffsize, nullstring = nullstring, : --negative length vectors are not allowed

which is resolved by adding the believeNRows=FALSE option as below:

myconn <-odbcConnect("ODS", uid="sample_user", pwd="password", believeNRows=FALSE) sqlQuery(myconn, "SELECT * FROM SAMPLE_DATABASE") --Works successfully

As per a stackoverflow post, this is caused by SQLRowCount to be 4294967295 (even if there's just one row) on 64bit Linux while it reports -1 on 32 bit Linux. This is circumvented in RODBC by the believeNRows=FALSE option.

Is the odbc running into the issue because of some different behaviour between 32bit and 64bit? Is there any way to resolve this for the odbc library as well?

I have confirmed via db2cli tool that sql output of the above query in command-line via the DB2 driver works perfectly.

[aryoda]

@nbfcdatascience I may have the same error message on Windows with a MS SQL Server 2016 om a so,oöar scenario. Could you please add a link to the mentioned stackoverflow post? THX

[nbfcdatascience]

@nbfcdatascience I may have the same error message on Windows with a MS SQL Server 2016 om a so,oöar scenario. Could you please add a link to the mentioned stackoverflow post? THX

https://stackoverflow.com/questions/3407015/querying-oracle-db-from-revolution-r-using-rodbc

Have not been able to solve the problem with r-dbi/odbc for DB2. Do update if you find a solution which works for you.

[aryoda]

@nbfcdatascience Would it be possible for you to provide a minimal reproducible example here, mainly:

• the table create SQL statement
• insert SQL statements to populate the table with as many rows as needed to provoke the error

Also: Please provide the

• sessionInfo() of the loaded packages and versions
• the DB2 driver version and the odbc configuration files (eg. odbc.ini and odbcinst.ini files from /etc/ and ~

Important: Please make sure to NOT leaking credentials or private information if providing this information here!

[nbfcdatascience]

Issue1.txt

I have done all of the requested and added with appropriate comments in the attached. Summarizing below:

• create SQL - this works
• insert SQL - I tried inserting 20000 rows which worked. Will get back on trying to get the error with a larger loop.
• while checking, noticed that a 'select count(*) from table' works - tried with multiple other tables. However, a 'select column from table' or 'select * from table' fails immediately with same titled error - fails with small tables with less than 10 rows as well.
• sessionInfo() provided
• odbc.ini and odbcinst.ini details are also listed
• DB2 driver version ibm_data_server_driver_package_linuxx64_v11.5

Let me know if anything missing. Will try a larger insert as well.

[aryoda]

I have tried to reproduce your error using your code from Issue1.txt on a Windows 10 64 bit machine with Microsoft SQL Server 2016 (I had no other infrastructure so this is the best I could do) and was not able to provoke the error std::bad_alloc even though i have inserted 200,002 instead of only 20,002 rows into the table.

Nevertheless I was able to reproduce my error and will provide a new issue with an MRE here soon.

Perhaps your problem is related to mine. You could test if your code works with odbc v1.2.3 (which is the last working version in my case, v1.3.0 introduce the memory leak) by using one of these commands in R:

devtools::install_github("r-dbi/odbc", ref = "v1.2.3")  # works without an error in my case
devtools::install_version("odbc", "1.2.3")#474 

PS: I could not test the effect of the with ur SQL syntax which is DB2-specific (you could try to remove this and see if it works then) but I wouldn't expect this as problem...

[philippleppert]

Thank you @nbfcdatascience for your explanation.

We have the same issue with a Firebird DB on Linux (Debian 11). We recently switched from R package RODBC where we likewise needed the believeNRows argument set to FALSE in order for our queries to work with odbcConnect. The error occurs sporadically and seems unrelated to actual RAM consumption and/or size of the queried table.

Then again, the error does not occur on Windows.

package versions:

• DBI 1.1.3
• odbc 1.3.3

[hadley]

Hi all, I'm closing this because it's rather old and lacks a good reprex. If you're still experiencing the problem, please open a new issue with reprex.