R help - Oct 2012 - Java Exception error while reading large data in R from DB using RJDBC.

Dear List,

Java Exception error while reading large data in R from DB using RJDBC.
I am trying to read large data from DB table(Vectorwise), using RJDBC
connection.
I have tested the connection with small size data and was able to fetch DB
tables using same connection(conn as in my code).  

Please suggest where  am i going wrong or alternate option to solve such
issues  while reading large DB table.

drv <- JDBC(paste(db_driver,  sep = ""),
           paste(db_jar_file,  sep = ""),
           identifier.quote="`")

conn <<- dbConnect(drv, paste(db_server,  sep = ""),
					paste(db_server_lgn,  sep = ""), 
					paste(db_server_pwd,  sep = ""))
s <- sprintf("select * from  cypress_modeldev_account_info")
 temp <- dbGetQuery(conn, s) 
Error in .jcheck() : 
  Java Exception <no description because toString() failed>.jcall(rp,
"I",
"fetch", stride)<S4 object of class "jobjRef">




--
View this message in context:
http://r.789695.n4.nabble.com/Java-Exception-error-while-reading-large-data-in-R-from-DB-using-RJDBC-tp4647844.html
Sent from the R help mailing list archive at Nabble.com.

I didn't have the same exception yet, but please make sure you have a stable
connection (avoid wlan).
I personally ran out of memory very often and had to make several queries,
combining the results in R instead.

This is probably not the best thing to do, but worked for me to fetch large
amounts of data, though it takes a while:
	
res<-dbSendQuery(conn,query)
result<-list()
i=1
result[[i]]<-fetch(res,n=100000)
while(nrow(chunk <- fetch(res, n = 100000))>0){
	i<-i+1
	result[[i]]<-chunk
}
allResults<-do.call(rbind,result)

On 30.10.2012, at 12:32, aajit75 wrote:
> Dear List,
> 
> Java Exception error while reading large data in R from DB using RJDBC.
> I am trying to read large data from DB table(Vectorwise), using RJDBC
> connection.
> I have tested the connection with small size data and was able to fetch DB
> tables using same connection(conn as in my code).  
> 
> Please suggest where  am i going wrong or alternate option to solve such
> issues  while reading large DB table.
> 
> drv <- JDBC(paste(db_driver,  sep = ""),
>           paste(db_jar_file,  sep = ""),
>           identifier.quote="`")
> 
> conn <<- dbConnect(drv, paste(db_server,  sep = ""),
> 					paste(db_server_lgn,  sep = ""), 
> 					paste(db_server_pwd,  sep = ""))
> s <- sprintf("select * from  cypress_modeldev_account_info")
> temp <- dbGetQuery(conn, s) 
> Error in .jcheck() : 
>  Java Exception <no description because toString() failed>.jcall(rp,
"I",
> "fetch", stride)<S4 object of class "jobjRef">
> 
> 
> 
> 
> --
> View this message in context:
http://r.789695.n4.nabble.com/Java-Exception-error-while-reading-large-data-in-R-from-DB-using-RJDBC-tp4647844.html
> Sent from the R help mailing list archive at Nabble.com.
> 
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

Sir,

On 30 October 2012 04:32, aajit75 <aajit75@yahoo.co.in>
wrote:
>
> Please suggest where  am i going wrong or alternate option to solve such
> issues  while reading large DB table.
>
You might consider setting the fetch size. I haven't used RJDBC in a few
months, but hopefully, the hints on
http://www.rforge.net/doc/packages/RJDBC/html/JDBCResult-methods.html point
you in the right direction? If not, email me off-list and I'll try to come
up with some sample code to help you. -- H

	[[alternative HTML version deleted]]