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!

Solution 1

We can use the method dbClearResult.
Example:

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

Solution 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).

Solution 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)

Solution 4

rs<- dbGetQuery(dbcon, sql)
data<-dbFetch(rs)
dbClearResult(rs)

last line removed the following error when continuing querying

Error in .local(conn, statement, ...) : 
  connection with pending rows, close resultSet before continuing

Solution 5

You need to close the resultset before closing the connection. If you try to close the connection before closing the resultset which has pending rows then sometimes it lead to hang the machine.

I don't know much about rmysql but try to close the resultset first.

View more solutions

Comments

[JD Long]

I apparently have been doing this properly not by design but by luck. What command is it that does not return all of the results but leaves the resultset open?

[Dirk Eddelbuettel]

E.g. dbSendQuery() followed by fetch() with a fixed number of rows.

[Maiasaura]

I do what Dirk does, on a daily basis, and it works just fine.

[Diego-MX]

This is an old thread, however since I ran into it I wanted to mention the function sprintf with which the above code can be written more friendly as: sql <- sprintf("select count(*) from packages where package='%s' and version='%s';" , curPkg, curVer)

[Backlin]

Downvoted because of the reasons explained in Dirks post: If you have pending rows you've probably done a dbSendQuery("SELECT ...") without fetching all of the results, which is probably not what you want. Always use dbGetQuery with SELECT statements unless you have specific reasons not to.

[Liang Zhang]

dbExecute is preferred in case that the query results are not of interest.

[Marcelo Ventura]

This is an old thread, however since I ran into it I wanted to update it by mentioning the function glue::glue() with which the above code can be written more friendly as: sql <- glue("select count(*) from packages where package={curPkg} and version={curVer};") [ Imitation intended for comedic purposes XD ]