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:[email protected]] On Behalf Of Brad P Sent: Thursday, July 23, 2015 07:41 AM To: [email protected] 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 On Wed, Jul 22, 2015 at 8:50 PM, Brad P <[email protected]> wrote: > 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 650@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]] _______________________________________________ R-sig-DB mailing list -- R Special Interest Group [email protected] https://stat.ethz.ch/mailman/listinfo/r-sig-db _______________________________________________ R-sig-DB mailing list -- R Special Interest Group [email protected] https://stat.ethz.ch/mailman/listinfo/r-sig-db
