6

I've recently upgraded R, RSQLite, and sqldf (versions below).

Normally:

sqldf('create table foo as select * from bar', db = 'test.db')

should create a table called 'foo' in an attached sqlite database, using data frame 'bar' if it exists to load the new table.

Instead, I'm getting a 'no such table' error, and also when I look at the database there are both 'foo' and 'bar' tables created.

Reproducible example:

library(RSQLite)
library(sqldf)
mydb = 'test.db'
## remove file if it exists                                                                                                                                                                                       
system(paste('rm', mydb))
## open connection                                                                                                                                                                                                
##con <- dbConnect(SQLite(), dbname=mydb)                                                                                                                                                                         
system(paste('ls -l', mydb))
sqldf( paste0( 'attach "', mydb, '" as new' ) )
system(paste('ls -l', mydb))
class(mtcars)
sqldf( 'create table mycars as select * from mtcars', dbname = mydb )
sqldf('select * from sqlite_master', dbname = mydb)
sqldf('select * from main.mycars limit 1', dbname = mydb)
sqldf('select * from main.mtcars limit 1', dbname = mydb)
sessionInfo()

which produces two tables and throws an error (to add insult to injury):

    > library(RSQLite)                                                                                                                                                                                               
     > library(sqldf)                                                                                                                                                                                                 
     Loading required package: gsubfn
     Loading required package: proto
     > mydb = 'test.db'                                                                                                                                                                                               
     > ## remove file if it exists                                                                                                                                                                                    
     > system(paste('rm', mydb))                                                                                                                                                                                      
     > ## open connection                                                                                                                                                                                             
     > ##con <- dbConnect(SQLite(), dbname=mydb)                                                                                                                                                                      
     > system(paste('ls -l', mydb))                                                                                                                                                                                   
     ls: test.db: No such file or directory
     > sqldf( paste0( 'attach "', mydb, '" as new' ) )                                                                                                                                                                
     Loading required package: tcltk
     data frame with 0 columns and 0 rows
     > system(paste('ls -l', mydb))                                                                                                                                                                                   
     -rwxrwxrwx  1 nathan  staff  1 Jan  6 10:01 test.db
     > class(mtcars)                                                                                                                                                                                                  
     [1] "data.frame"
     > sqldf( 'create table mycars as select * from mtcars', dbname = mydb )                                                                                                                                          
     Error in rsqlite_send_query(conn@ptr, statement) :
       no such table: `mtcars`
     In addition: Warning message:
     Quoted identifiers should have class SQL, use DBI::SQL() if the caller performs the quoting.
     > sqldf('select * from sqlite_master', dbname = mydb)                                                                                                                                                            
        type   name tbl_name rootpage
     1 table mtcars   mtcars        2
     2 table mycars   mycars        5
                                                                                                                                                                                                   sql
     1 CREATE TABLE `mtcars` (\n  "mpg" REAL,\n  "cyl" REAL,\n  "disp" REAL,\n  "hp" REAL,\n  "drat" REAL,\n  "wt" REAL,\n  "qsec" REAL,\n  "vs" REAL,\n  "am" REAL,\n  "gear" REAL,\n  "carb" REAL\n)
     2                          CREATE TABLE mycars(\n  mpg REAL,\n  cyl REAL,\n  disp REAL,\n  hp REAL,\n  drat REAL,\n  wt REAL,\n  qsec REAL,\n  vs REAL,\n  am REAL,\n  gear REAL,\n  carb REAL\n)
     > sqldf('select * from main.mycars limit 1', dbname = mydb)                                                                                                                                                      
       mpg cyl disp  hp drat   wt  qsec vs am gear carb
     1  21   6  160 110  3.9 2.62 16.46  0  1    4    4
     > sqldf('select * from main.mtcars limit 1', dbname = mydb)                                                                                                                                                      
       mpg cyl disp  hp drat   wt  qsec vs am gear carb
     1  21   6  160 110  3.9 2.62 16.46  0  1    4    4
     > sessionInfo()                                                                                                                                                                                                  
     R version 3.3.2 (2016-10-31)
     Platform: x86_64-apple-darwin13.4.0 (64-bit)
     Running under: OS X El Capitan 10.11.6

     locale:
     [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

     attached base packages:
     [1] tcltk     stats     graphics  grDevices utils     datasets  methods   base

     other attached packages:
     [1] sqldf_0.4-10    gsubfn_0.6-6    proto_1.0.0     RSQLite_1.1-1   devtools_1.12.0

     loaded via a namespace (and not attached):
     [1] DBI_0.5-1     withr_1.0.2   Rcpp_0.12.8   memoise_1.0.0 digest_0.6.11 chron_2.3-48

Is this a bug or some new intended behavior?  Thanks for your help.
5
  • You might be mixing up environments (R vs SQLite). Why not use RSQLite's dbWriteTable() and leave sqldf() to only manipulate R dfs? Commented Jan 6, 2017 at 19:00
  • 2
    RSLQite 1.1-1 was recently released and I suspect it has introduced some incompatibility with existing software. Suggest you downgrade back to RSQLite 1.0.0: devtools::install_url("https://cran.r-project.org/src/contrib/Archive/RSQLite/RSQLite_1.0.0.tar.gz") Commented Jan 6, 2017 at 19:52
  • 1
    @G.Grothendieck: ?dbWriteTable and ?dbRemoveTable says that name is "[a] character string specifying a DBMS table name", it says nowhere that name should be quoted, this is what sqldf seems to be doing internally. I'd argue that sqldf operates out of spec here. That said, I'm releasing RSQLite 1.1-2 very soon to improve sqldf compatibility. Commented Jan 7, 2017 at 17:57
  • The last statement before sessionInfo() fails also with RSQLite 1.0.0. Please confirm. The following works: sqldf('select * from mtcars limit 1', dbname = mydb) . Commented Jan 7, 2017 at 17:58
  • I either had to quote them to get it to work with an earlier version of RSQLite or it might have been that this was needed to get it work with all 4 back ends that sqldf supports. At any rate, sqldf("select * from mtcars limit 1", dbname = "x.db") works under RSQLite 1.0.0 whether or not x.db exists but fails in both cases under 1.1-1. Without dbname = "x.db"it works on both RSQLite 1.1-1 and 1.0.0. Commented Jan 7, 2017 at 20:42

2 Answers 2

5

UPDATE: newest versions of RSQLite and sqldf do not have the incompatibility issues addressed by this question i.e.: sqldf_0.4-10 RSQLite_1.1-2 work together well - Nathan

All: Thanks to G. Grothendieck for pointers to incompatibility issues introduced by RSQLite 1.1-1. As stated in the comment to the answer, downgrade RSQLite to 1.0.0:

devtools::install_url("https://cran.r-project.org/src/contrib/Archive/RSQLite/RSQLite_1.0.0.tar.gz")
Sign up to request clarification or add additional context in comments.

3 Comments

I believe the comment I provided to the question was correct but the above changed the advice and was wrong so I have edited the answer. You do NOT want to re-install sqldf and particularly you do NOT want to install an old version of sqldf. The problem was introduced by RSQLite 1.1-1 and that is the only package you need to roll back. If you haven't already installed sqldf then just install it in the ordinary way using install.packages("sqldf") making sure you do it BEFORE you do the above. (Also I have removed the library(devtools) line as being redundant.)
Thanks, you are correct. Installing sqldf after RSQLite will overwrite the 1.0.0 version, which was leading me astray. Installing the up-to-date sqldf followed by the above install of RSQLite 1.0.0 passes the example test. Thanks again.
I opened a related thread here unix.stackexchange.com/q/372927/16920 to keep strictly RSQLite 1.0.0 in your system because I ofen run into this problem because of updates coming to the system, also updating RSQLite, so breaking the setup once again.
3

This is indeed a compatibility problem between the current version of RSQLite and the sqldf package. RSQLite is now stricter about the arguments it accepts for dbReadTable(), dbWriteTable() and dbRemoveTable(), warnings will be issued (but only once per session) until sqldf is adapted.

I'll release a compatibility update soon, there is an issue on GitHub that will contain progress updates.

2 Comments

Do you know when it is coming out? I think it is not yet released because I am having the same problem.
Unfortunately not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.