6

I've been using sqldf in my R-scripts until now when I got the following error:

library(sqldf)
data(mtcars)
out <- sqldf("SELECT * FROM mtcars")

> Error in rsqlite_send_query(conn@ptr, statement) : no such table: mtcars

This hasn't been a problem before now, anyone know what's the issue?

Roman Luštrik
  • 69,533
  • 24
  • 154
  • 197
user134489
  • 355
  • 1
  • 3
  • 10

1 Answers1

10

I had this problem with 0.4-10 from CRAN (Windows 10).

> out <- sqldf("SELECT * FROM mtcars")
Loading required package: tcltk
Error in rsqlite_send_query(conn@ptr, statement) : no such table: mtcars

Then I upgraded from GitHub, version 0.4-11

> devtools::install_github("ggrothendieck/sqldf")
Downloading GitHub repo ggrothendieck/sqldf@master
from URL https://api.github.com/repos/ggrothendieck/sqldf/zipball/master
Installing sqldf
Installing 1 package: DBI
Installing package into ‘C:/Users/romunov/Documents/R/win-library/3.3’
(as ‘lib’ is unspecified)
trying URL 'https://cran.rstudio.com/bin/windows/contrib/3.3/DBI_0.7.zip'
Content type 'application/zip' length 746913 bytes (729 KB)
downloaded 729 KB

package ‘DBI’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
    C:\Users\romunov\AppData\Local\Temp\Rtmp6vYjwO\downloaded_packages
"C:/PROGRA~1/R/R-33~1.3/bin/x64/R" --no-site-file --no-environ  \
  --no-save --no-restore --quiet CMD INSTALL  \
  "C:/Users/romunov/AppData/Local/Temp/Rtmp6vYjwO/devtoolsdb05f0015fc/ggrothendieck-sqldf-af1277f"  \
  --library="C:/Users/romunov/Documents/R/win-library/3.3"  \
  --install-tests 

* installing *source* package 'sqldf' ...
** R
** demo
** inst
** tests
** byte-compile and prepare package for lazy loading
** help
*** installing help indices
** building package indices
** testing if installed package can be loaded
*** arch - i386
*** arch - x64
* DONE (sqldf)
Reloading installed sqldf
> sqldf("SELECT * FROM mtcars LIMIT 6")
   mpg cyl disp  hp drat    wt  qsec vs am gear carb
1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
3 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
4 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
5 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
6 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

Looking at the last two commits from GitHub, no substantial change has been done to sqldf package. DBI did get upgraded to 0.7, though, which might have solved the problem. Gabor might chip in with the details.

Roman Luštrik
  • 69,533
  • 24
  • 154
  • 197
  • 4
    The problem is that RSQLite 2.0 was just released and does not work with older versions of sqldf. sqldf 0.4-11 which was just released should be used (or else use an older version of RSQLite). – G. Grothendieck Jun 28 '17 at 14:11
  • Ah, so not DBI. I had assumed DBI was causing this because that's the only thing I see updating when installing a new version of sqldf. – Roman Luštrik Jun 28 '17 at 14:19
  • @G.Grothendieck perhaps dependencies of the packages should be fixed somehow, so that users don't bump to this problem over and over again. June 2018 and I still encountered this problem. – Tomas Jun 19 '18 at 09:04