How to remove warnings in sqldf when using update, delete or alter table

Question

Below is a reproducible example with warnings. I have done some research and some say that RSQLITE version causes this but not sure which version, so is there any way to prevent these warnings in sqldf. Thanks in advance

(mt <- mtcars[1:5,1:5])
sqldf(c('update mt set cyl=5 where cyl>5', 'select * from mt'))

Warning message: In result_fetch(res@ptr, n = n) : SQL statements must be issued with dbExecute() or dbSendStatement() instead of dbGetQuery() or dbSendQuery().

You might like to add your voice to this issue: https://github.com/r-dbi/RSQLite/issues/227 — G. Grothendieck, Mar 16 '20 at 17:12

score 0 · Answer 1 · answered Mar 16 '20 at 13:24

You can suppress warnings globally and then reset after your code has run:

How to suppress warnings globally in an R Script

This appears to work:

oldw <- getOption("warn")
options(warn = -1)
(mt <- mtcars[1:5,1:5])
sqldf(c('update mt set cyl=5 where cyl>5', 'select * from mt'))
options(warn = oldw)

score 0 · Answer 2 · answered Mar 16 '20 at 20:03

This wrapper muffles that particular warning without interfering with others.

sqldf2 <- function(...) {
  withCallingHandlers(sqldf(...), warning = 
    function(w) if (grepl("SQL statements must be issued with dbExecute", w)) 
      invokeRestart("muffleWarning") else w)
}

sql <- c("update BOD set Time = 1 where Time = 2", "select * from BOD")
sqldf2(sql)

giving the following with no warning:

  Time demand
1    1    8.3
2    1   10.3  <-- Time was updated from 2 to 1 on this line
3    3   19.0
4    4   16.0
5    5   15.6
6    7   19.8

How to remove warnings in sqldf when using update, delete or alter table

2 Answers2