[R-sig-DB] RODBC Error when fetching tables: 'Calloc' could not allocate memory

Discussion:

Brad P

2015-07-23 01:50:46 UTC

Hello,

I have used R for a while in Linux, but am trying to become familiar with
using R in Windows as well as using MS SQL and the RODBC package. I have a
problem described below when trying to fetch SOME tables.

I am using:

OS:
Windows 7 64-bit
Intel quad i5 ***@320GHz
12GB RAM

R:
Rstudio Version 0.99.467
Revolution R Open 3.2.0
Using CRAN snapshot taken on 2015-05-01

sessionInfo()

R version 3.2.0 (2015-04-16)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

locale:
[1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United
States.1252
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C

[5] LC_TIME=English_United States.1252

attached base packages:
[1] stats graphics grDevices utils datasets methods base

other attached packages:
[1] SOAR_0.99-11 pryr_0.1.2 RODBC_1.3-12

loaded via a namespace (and not attached):
[1] magrittr_1.5 tools_3.2.0 Rcpp_0.11.6 stringi_0.5-5
[5] codetools_0.2-11 stringr_1.0.0

Sys.getlocale()

[1] "LC_COLLATE=English_United States.1252;LC_CTYPE=English_United
States.1252;LC_MONETARY=English_United
States.1252;LC_NUMERIC=C;LC_TIME=English_United States.1252"

SQL:
MS SQL server 2012
Example database: AdventureWorksDW2012

# Here is some code and the problem I am having:

library(RODBC)
con <- odbcDriverConnect('driver={SQL Server}; server=SQLEXPRESS;
Database=AdventureWorksDW2012; uid=BPS; trusted_connection=true')

# The problem is that some tables are fetched OK, while some tables lead to
the following error:

# increasing memory
memory.limit(size=10000000000)
round(memory.limit()/2^30, 2)
[1] 9.31 # GB

dat <- sqlFetch(con, DimEmployee)
Error in odbcQuery(channel, query, rows_at_time) :
'Calloc' could not allocate memory (214748364800 of 1 bytes)

traceback()
4: .Call(C_RODBCQuery, attr(channel, "handle_ptr"), as.character(query),
as.integer(rows_at_time))
3: odbcQuery(channel, query, rows_at_time)
2: sqlQuery(channel, paste("SELECT * FROM", dbname), ...)
1: sqlFetch(con, DimEmployee)

# Here are 2 examples of databases that can be fetched and can not:

# DimAccount does work, here is some info regarding its size:
sqlQuery(con, "exec sp_spaceused DimCustomer")
name rows reserved data index_size unused
1 DimCustomer 18484 13608 KB 12552 KB 920 KB 136 KB

# DimEmployee (example given above) does not work, here is some info
regarding its size:
sqlQuery(con, "exec sp_spaceused DimEmployee")
name rows reserved data index_size unused
1 DimEmployee 296 18992 KB 18856 KB 48 KB 88 KB

# 4 of 31 tables in this SQL database give this exact error when attempting
to fetch them as shown above, the rest are fetched without error.

# Also, please know that when I run this in regular 64-bit R (not
Revolution R) it does the same thing, and when I do it in 32-bit R, it
literally crashes

Please let me know if you have any suggestions.
-Patrick

[[alternative HTML version deleted]]

Brad P

2015-07-23 05:41:09 UTC

Permalink

Sorry if replying to this is not OK.

I now see that RODBC can not handle cases where length is huge, evidently >
8000, see this:

http://stackoverflow.com/questions/18477994/rodbc-does-not-save-greater-than-8k-varchar-text-from-a-data-frame

I also noticed that one column in the table previously mentioned had a
length of 2147483647

Anyway, my goal was to pull all tables in and saving them as .rds files.
I wrote a quick work around that identifies which tables have these long
lengths and selects all columns but those columns. This is not ideal, but
at least I get most of the data.

##################################
# get list of all table names
tables <- sqlQuery(con, "SELECT * FROM information_schema.tables")
tables <- subset(tables, subset=TABLE_TYPE!="VIEW")
tables <- as.character(tables$TABLE_NAME)

# for loop to pull in tables 1 at a time
for(i in 1:length(tables) ){
x <- sqlQuery(con, paste("exec sp_columns", tables[i], sep=" ") )
if(length(which(x$LENGTH > 8000))>0){
drop.cols <- as.character( x[which(x$LENGTH > 8000),]$COLUMN_NAME )
print ( paste(tables[i], "had", paste(drop.cols, collapse=","),
"column(s) dropped", sep=" ") )
keep.cols <- as.character( x[which(x$LENGTH < 8000),]$COLUMN_NAME )
dat <- sqlQuery(con, paste( "SELECT", paste(keep.cols, collapse=","),
"FROM", tables[i], sep=" ") )
}else{
dat <- try( sqlFetch(con, tables[i]), silent=F)
}
print(i)
print( paste(tables[i]) )
print( object.size(dat), units = "auto" )
saveRDS(dat, paste(tables[i], "rds", sep=".") )
}
##################################

##################################

Cheers!
Patrick

Post by Brad P
Hello,
I have used R for a while in Linux, but am trying to become familiar with
using R in Windows as well as using MS SQL and the RODBC package. I have a
problem described below when trying to fetch SOME tables.
Windows 7 64-bit
12GB RAM
Rstudio Version 0.99.467
Revolution R Open 3.2.0
Using CRAN snapshot taken on 2015-05-01

sessionInfo()

R version 3.2.0 (2015-04-16)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1
[1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United
States.1252
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C
[5] LC_TIME=English_United States.1252
[1] stats graphics grDevices utils datasets methods base
[1] SOAR_0.99-11 pryr_0.1.2 RODBC_1.3-12
[1] magrittr_1.5 tools_3.2.0 Rcpp_0.11.6 stringi_0.5-5
[5] codetools_0.2-11 stringr_1.0.0

Sys.getlocale()

[1] "LC_COLLATE=English_United States.1252;LC_CTYPE=English_United
States.1252;LC_MONETARY=English_United
States.1252;LC_NUMERIC=C;LC_TIME=English_United States.1252"
MS SQL server 2012
Example database: AdventureWorksDW2012
library(RODBC)
con <- odbcDriverConnect('driver={SQL Server}; server=SQLEXPRESS;
Database=AdventureWorksDW2012; uid=BPS; trusted_connection=true')
# The problem is that some tables are fetched OK, while some tables lead
# increasing memory
memory.limit(size=10000000000)
round(memory.limit()/2^30, 2)
[1] 9.31 # GB
dat <- sqlFetch(con, DimEmployee)
'Calloc' could not allocate memory (214748364800 of 1 bytes)
traceback()
4: .Call(C_RODBCQuery, attr(channel, "handle_ptr"), as.character(query),
as.integer(rows_at_time))
3: odbcQuery(channel, query, rows_at_time)
2: sqlQuery(channel, paste("SELECT * FROM", dbname), ...)
1: sqlFetch(con, DimEmployee)
sqlQuery(con, "exec sp_spaceused DimCustomer")
name rows reserved data index_size unused
1 DimCustomer 18484 13608 KB 12552 KB 920 KB 136 KB
# DimEmployee (example given above) does not work, here is some info
sqlQuery(con, "exec sp_spaceused DimEmployee")
name rows reserved data index_size unused
1 DimEmployee 296 18992 KB 18856 KB 48 KB 88 KB
# 4 of 31 tables in this SQL database give this exact error when
attempting to fetch them as shown above, the rest are fetched without error.
# Also, please know that when I run this in regular 64-bit R (not
Revolution R) it does the same thing, and when I do it in 32-bit R, it
literally crashes
Please let me know if you have any suggestions.
-Patrick

[[alternative HTML version deleted]]

Philippi, Tom

2015-07-23 06:47:38 UTC

Permalink

memo fields are an issue for MS ODBC and MS Access or SQL Server,
especially with sqlSave() creating or appending to tables. ODBC reports
them as 1G 2-byte characters per value, VARCHAR with length of 1070M or so.
As far as I know, this is an all-MS issue, between their ODBC and their
databases, and on most if not all platforms.

sqlColumns(mdb,table) is another way to accomplish what you do with
x <- sqlQuery(con, paste("exec sp_columns", tables[i], sep=" ") )
x <- sqlColumns(con,tables[i])

I may be brain-dead, but I don't think you need the which() in your
subsetting:
drop.cols <- as.character( x[which(x$LENGTH > 8000),]$COLUMN_NAME )
drop.cols <- as.character( x$COLUMN_NAME[x$LENGTH > 8000] )

Tom 2

Post by Brad P
Sorry if replying to this is not OK.
I now see that RODBC can not handle cases where length is huge, evidently >
http://stackoverflow.com/questions/18477994/rodbc-does-not-save-greater-than-8k-varchar-text-from-a-data-frame
I also noticed that one column in the table previously mentioned had a
length of 2147483647
Anyway, my goal was to pull all tables in and saving them as .rds files.
I wrote a quick work around that identifies which tables have these long
lengths and selects all columns but those columns. This is not ideal, but
at least I get most of the data.
##################################
# get list of all table names
tables <- sqlQuery(con, "SELECT * FROM information_schema.tables")
tables <- subset(tables, subset=TABLE_TYPE!="VIEW")
tables <- as.character(tables$TABLE_NAME)
# for loop to pull in tables 1 at a time
for(i in 1:length(tables) ){
x <- sqlQuery(con, paste("exec sp_columns", tables[i], sep=" ") )
if(length(which(x$LENGTH > 8000))>0){
drop.cols <- as.character( x[which(x$LENGTH > 8000),]$COLUMN_NAME )
print ( paste(tables[i], "had", paste(drop.cols, collapse=","),
"column(s) dropped", sep=" ") )
keep.cols <- as.character( x[which(x$LENGTH < 8000),]$COLUMN_NAME )
dat <- sqlQuery(con, paste( "SELECT", paste(keep.cols, collapse=","),
"FROM", tables[i], sep=" ") )
}else{
dat <- try( sqlFetch(con, tables[i]), silent=F)
}
print(i)
print( paste(tables[i]) )
print( object.size(dat), units = "auto" )
saveRDS(dat, paste(tables[i], "rds", sep=".") )
}
##################################
##################################
Cheers!
Patrick

Post by Brad P
Hello,
I have used R for a while in Linux, but am trying to become familiar with
using R in Windows as well as using MS SQL and the RODBC package. I have

Post by Brad P
problem described below when trying to fetch SOME tables.
Windows 7 64-bit
12GB RAM
Rstudio Version 0.99.467
Revolution R Open 3.2.0
Using CRAN snapshot taken on 2015-05-01

sessionInfo()

Sys.getlocale()

[1] "LC_COLLATE=English_United States.1252;LC_CTYPE=English_United
States.1252;LC_MONETARY=English_United
States.1252;LC_NUMERIC=C;LC_TIME=English_United States.1252"
MS SQL server 2012
Example database: AdventureWorksDW2012
library(RODBC)
con <- odbcDriverConnect('driver={SQL Server}; server=SQLEXPRESS;
Database=AdventureWorksDW2012; uid=BPS; trusted_connection=true')
# The problem is that some tables are fetched OK, while some tables lead
# increasing memory
memory.limit(size=10000000000)
round(memory.limit()/2^30, 2)
[1] 9.31 # GB
dat <- sqlFetch(con, DimEmployee)
'Calloc' could not allocate memory (214748364800 of 1 bytes)
traceback()
4: .Call(C_RODBCQuery, attr(channel, "handle_ptr"), as.character(query),
as.integer(rows_at_time))
3: odbcQuery(channel, query, rows_at_time)
2: sqlQuery(channel, paste("SELECT * FROM", dbname), ...)
1: sqlFetch(con, DimEmployee)
sqlQuery(con, "exec sp_spaceused DimCustomer")
name rows reserved data index_size unused
1 DimCustomer 18484 13608 KB 12552 KB 920 KB 136 KB
# DimEmployee (example given above) does not work, here is some info
sqlQuery(con, "exec sp_spaceused DimEmployee")
name rows reserved data index_size unused
1 DimEmployee 296 18992 KB 18856 KB 48 KB 88 KB
# 4 of 31 tables in this SQL database give this exact error when
attempting to fetch them as shown above, the rest are fetched without

error.

Post by Brad P
# Also, please know that when I run this in regular 64-bit R (not
Revolution R) it does the same thing, and when I do it in 32-bit R, it
literally crashes
Please let me know if you have any suggestions.
-Patrick

[[alternative HTML version deleted]]
_______________________________________________
R-sig-DB mailing list -- R Special Interest Group
https://stat.ethz.ch/mailman/listinfo/r-sig-db

[[alternative HTML version deleted]]

Edward Vanden Berghe

2015-07-23 11:25:48 UTC

Permalink

Hi,

I had some similar issues, working with a PostgreSQL/PostGIS database and
RODBC - RODBC seems to have problems dealing with long fields. I tried with
R on both Ubuntu and Windows (PostGIS was always on Ubuntu), but larger
polygons always got truncated. Problems disappeared when I switched over to
DBI and RPostregeSQL. Don't forget to also set 'stringsAsFactors' to false
if you want to read into R instead of just writing out to the database.

HTH

Edward

-----Original Message-----
From: R-sig-DB [mailto:r-sig-db-***@r-project.org] On Behalf Of Brad P
Sent: Thursday, July 23, 2015 07:41 AM
To: r-sig-***@r-project.org
Subject: Re: [R-sig-DB] RODBC Error when fetching tables: 'Calloc' could not
allocate memory

Sorry if replying to this is not OK.

I now see that RODBC can not handle cases where length is huge, evidently >
8000, see this:

http://stackoverflow.com/questions/18477994/rodbc-does-not-save-greater-than
-8k-varchar-text-from-a-data-frame

I also noticed that one column in the table previously mentioned had a
length of 2147483647

Anyway, my goal was to pull all tables in and saving them as .rds files.
I wrote a quick work around that identifies which tables have these long
lengths and selects all columns but those columns. This is not ideal, but at
least I get most of the data.

##################################
# get list of all table names
tables <- sqlQuery(con, "SELECT * FROM information_schema.tables") tables <-
subset(tables, subset=TABLE_TYPE!="VIEW") tables <-
as.character(tables$TABLE_NAME)

# for loop to pull in tables 1 at a time for(i in 1:length(tables) ){
x <- sqlQuery(con, paste("exec sp_columns", tables[i], sep=" ") )
if(length(which(x$LENGTH > 8000))>0){
drop.cols <- as.character( x[which(x$LENGTH > 8000),]$COLUMN_NAME )
print ( paste(tables[i], "had", paste(drop.cols, collapse=","),
"column(s) dropped", sep=" ") )
keep.cols <- as.character( x[which(x$LENGTH < 8000),]$COLUMN_NAME )
dat <- sqlQuery(con, paste( "SELECT", paste(keep.cols, collapse=","),
"FROM", tables[i], sep=" ") )
}else{
dat <- try( sqlFetch(con, tables[i]), silent=F)
}
print(i)
print( paste(tables[i]) )
print( object.size(dat), units = "auto" )
saveRDS(dat, paste(tables[i], "rds", sep=".") ) }
##################################

##################################

Cheers!
Patrick

Post by Brad P
Hello,
I have used R for a while in Linux, but am trying to become familiar
with using R in Windows as well as using MS SQL and the RODBC package.
I have a problem described below when trying to fetch SOME tables.
Windows 7 64-bit
12GB RAM
Rstudio Version 0.99.467
Revolution R Open 3.2.0
Using CRAN snapshot taken on 2015-05-01

sessionInfo()

R version 3.2.0 (2015-04-16)
Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows 7 x64
(build 7601) Service Pack 1
[1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United
States.1252
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C
[5] LC_TIME=English_United States.1252
[1] stats graphics grDevices utils datasets methods base
[1] SOAR_0.99-11 pryr_0.1.2 RODBC_1.3-12
[1] magrittr_1.5 tools_3.2.0 Rcpp_0.11.6 stringi_0.5-5
[5] codetools_0.2-11 stringr_1.0.0

Sys.getlocale()

[1] "LC_COLLATE=English_United States.1252;LC_CTYPE=English_United
States.1252;LC_MONETARY=English_United
States.1252;LC_NUMERIC=C;LC_TIME=English_United States.1252"
MS SQL server 2012
Example database: AdventureWorksDW2012
library(RODBC)
con <- odbcDriverConnect('driver={SQL Server}; server=SQLEXPRESS;
Database=AdventureWorksDW2012; uid=BPS; trusted_connection=true')
# The problem is that some tables are fetched OK, while some tables
# increasing memory
memory.limit(size=10000000000)
round(memory.limit()/2^30, 2)
[1] 9.31 # GB
dat <- sqlFetch(con, DimEmployee)
'Calloc' could not allocate memory (214748364800 of 1 bytes)
traceback()
4: .Call(C_RODBCQuery, attr(channel, "handle_ptr"), as.character(query),
as.integer(rows_at_time))
3: odbcQuery(channel, query, rows_at_time)
2: sqlQuery(channel, paste("SELECT * FROM", dbname), ...)
1: sqlFetch(con, DimEmployee)
sqlQuery(con, "exec sp_spaceused DimCustomer")
name rows reserved data index_size unused
1 DimCustomer 18484 13608 KB 12552 KB 920 KB 136 KB
# DimEmployee (example given above) does not work, here is some info
sqlQuery(con, "exec sp_spaceused DimEmployee")
name rows reserved data index_size unused
1 DimEmployee 296 18992 KB 18856 KB 48 KB 88 KB
# 4 of 31 tables in this SQL database give this exact error when
attempting to fetch them as shown above, the rest are fetched without error.
# Also, please know that when I run this in regular 64-bit R (not
Revolution R) it does the same thing, and when I do it in 32-bit R, it
literally crashes
Please let me know if you have any suggestions.
-Patrick

[[alternative HTML version deleted]]

_______________________________________________
R-sig-DB mailing list -- R Special Interest Group R-sig-***@r-project.org
https://stat.ethz.ch/mailman/listinfo/r-sig-db