MySQL 如何关闭 RMySQL 中的结果集？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4084028/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to close resultset in RMySQL?
提问by lokheart
I used RMySQL for import database, sometimes when I try to close the connection, I receive the following error:
我使用 RMySQL 导入数据库，有时当我尝试关闭连接时，会收到以下错误：
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!
除了重新启动计算机之外，我没有其他方法可以更正此问题，我可以做些什么来解决此问题？谢谢！
回答by Steve Qian
We can use the method dbClearResult.
Example:
我们可以使用 dbClearResult 方法。
例子：
dbClearResult(dbListResults(conn)[[1]])
回答by Dirk Eddelbuettel
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:
DBI 和 RMySQL 等访问器包的文档有时有点挑战性。我试着提醒自己使用dbGetQuery()which 一次抓取整个结果集。以下是 CRANberries 代码的一小段：
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).
在此之后，我可以放心关闭（或执行其他操作）。
回答by stda
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
回答by User 1034
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.
我对 rmysql 了解不多，但尝试先关闭结果集。
回答by Mnl
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 :
如之前的答案中所述，您会收到此错误，因为 RMysql 未返回查询的所有结果。
当结果超过 500 时，我遇到了这个问题，使用：
my_result <- fetch( dbSendQuery(con, query))
my_result <- fetch( dbSendQuery(con, query))
looking at the documentation for fetch I found that you can specify the number of records retrieved :
查看 fetch 的文档，我发现您可以指定检索的记录数：
n = maximum number of records to retrieve per fetch. Use n = -1 or n = Inf to retrieve all pending records.
n = 每次获取要检索的最大记录数。使用 n = -1 或 n = Inf 检索所有未决记录。
Solutions :
解决方案：
1- set the number of record to infinity :
my_result <- fetch( dbSendQuery(con, query), n=Inf)
1-将记录数设置为无穷大：
my_result <- fetch( dbSendQuery(con, query), n=Inf)
2- use dbGetQuery :
my_result <- dbGetQuery(con, query)
2-使用 dbGetQuery ：
my_result <- dbGetQuery(con, query)
回答by koralgooll
You have to remember about result's set yourself. In example below you have how to close/clear results and how to take the rows affected. To solve your problem use last line of code on variable which takes results from any of yours sent statement or query. :)
你必须自己记住结果集。在下面的示例中，您将了解如何关闭/清除结果以及如何处理受影响的行。要解决您的问题，请在变量上使用最后一行代码，该代码从您发送的任何语句或查询中获取结果。:)
statementRes <- DBI::dbSendStatement(conn = db,
"CREATE TABLE IF NOT EXISTS great_dupa_test (
taxonomy_id INTERGER NOT NULL,
scientific_name TEXT);")
DBI::dbGetRowsAffected(statementRes)
DBI::dbClearResult(statementRes)
