how to close resultset in RMySQL?

Question

I used RMySQL for import database, sometimes when I try to close the connection, I receive the following error:

Error in mysqlCloseConnection(conn, ...) : 
  connection has pending rows (close open results set first)

I have no other ways of correcting this other than restarting the computer, anything I can do so solve this? Thanks!

Answer 1

We can use the method dbClearResult.
Example:

dbClearResult(dbListResults(conn)[[1]])

Answer 2

As Multiplexer noted, you are probably doing it wrong by leaving parts of the result set behind.

DBI and the accessor packages like RMySQL have documentation that is a little challenging at times. I try to remind myself to use dbGetQuery() which grabs the whole result set at once. Here is a short snippet from the CRANberries code:

sql <- paste("select count(*) from packages ",
             "where package='", curPkg, "' ",
             "and version='", curVer, "';", sep="")
nb <- dbGetQuery(dbcon, sql)

After this I can close without worries (or do other operations).

Answer 3

As explained in previous answers, you get this error because RMysql didn't return all the results of the query.
I had this problem when the results where over 500 ,using :

my_result <- fetch( dbSendQuery(con, query))

looking at the documentation for fetch I found that you can specify the number of records retrieved :

n = maximum number of records to retrieve per fetch. Use n = -1 or n = Inf to retrieve all pending records.

Solutions :

1- set the number of record to infinity : my_result <- fetch( dbSendQuery(con, query), n=Inf)

2- use dbGetQuery : my_result <- dbGetQuery(con, query)