4

My function, contained in a larger function, uses dbConnect(), dbGetQuery(), and dbDisconnect().

When the function errors out, I fix it and try to rerun it. I can't rerun it though, because I get: Error in mysqlCloseConnection(conn, ...) : connection has pending rows (close open results set first)

(Note 1: I'm not using dbSendQuery() + fetch(), just dbGetQuery(), so this is itself a strange error.)
(Note 2: The errors do NOT occur in the RMySQL code; they occur in other parts of the R code.)

Failed solution 1: dbClearResult(dbListResults(myconnection)[[1]]) does not work because myconnection was created inside the function environment.

Failed solution 2: lapply( dbListConnections( dbDriver( drv = "MySQL")), dbDisconnect) errors out with the same 'pending rows' error message above.

My only solution now is to kill R and start over. However, I know that the connection still exists in the SQL database (because my boss tells me such), so I'm seeking a proper solution to close the result/connection.

Thanks for any help.

StatSandwich
  • 1,134
  • 11
  • 14
  • have you tried a combination of failed solution 1 and 2, i.e.: `lapply( dbListConnections( dbDriver( drv = "MySQL")), function(con) dbClearResult(dbListResults(con)[[1]]))` – Alex Jul 04 '14 at 01:28

1 Answers1

3

I had a similar problem so it might help other people in the future...

pull<- dbSendQuery(con, statement...)
df2 <- fetch(pull, n = -1)
> dbHasCompleted(pull)
[1] TRUE
> dbDisconnect(con)
[1] TRUE
MLavoie
  • 9,671
  • 41
  • 36
  • 56