Avoiding warning message “There is a result object still in use” when using dbSendQuery to create table on database

Question

Background:

I use dbplyr and dplyr to extract data from a database, then I use the command dbSendQuery() to build my table.

---

Issue:

After the table is built, if I run another command I get the following warning:

Warning messages: 1. In new_result(connection@ptr, statement): Cancelling previous query 2. In connection_release(conn@ptr) :
 There is a result object still in use. The connection will be automatically released when it is closed.

---

Question:

Because I don’t have a result to fetch (I am sending a command to build a table) I’m not sure how to avoid this warning. At the moment I disconnect after building a table and the error goes away. Is there anything I can do do to avoid this warning?

Currently everything works, I just have this warning. I'd just like to avoid it as I assume I should be clearing something after I've built my table.

---

Code sample

# establish connection con = DBI::dbConnect(<connection stuff here>)

# connect to table and database transactions = tbl(con,in_schema(“DATABASE_NAME”,”TABLE_NAME”))

# build query string query_string = “SELECT * FROM some_table”

# drop current version of table DBI::dbSendQuery(con,paste('DROP TABLE MY_DB.MY_TABLE'))

# build new version of table DBI::dbSendQuery(con,paste('CREATE TABLE PABLE MY_DB.MY_TABLE AS (‘,query_string,’) WITH DATA'))

Answer 1

Even though you're not retrieving stuff with a SELECT clause, DBI still allocates a result set after every call to DBI::dbSendQuery(). Give it a try with DBI::dbClearResult() in between of DBI::dbSendQuery() calls.

DBI::dbClearResult() does:

Clear A Result Set
Frees all resources (local and remote) associated with a 
result set. In some cases (e.g., very large result sets) this 
can be a critical step to avoid exhausting resources 
(memory, file descriptors, etc.)

The example of the man page should give a hint how the function should be called:

con <- dbConnect(RSQLite::SQLite(), ":memory:")

rs <- dbSendQuery(con, "SELECT 1")
print(dbFetch(rs))

dbClearResult(rs)
dbDisconnect(con)