sqldf can't find the data with error "no such table"

Question

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?

Charolettecharon

no such table: mtcars did you connect correct schema – Djebel

> mtcars[1,] mpg cyl disp hp drat wt qsec vs am gear carb Mazda RX4 21 6 160 110 3.9 2.62 16.46 0 1 4 4 The data frame works as expected – Charolettecharon

It's working fine for me on macOS. Might therefore be a platform dependant issue – Transform

See groups.google.com/forum/#!forum/sqldf (and in general look at the BugReports line of the Description file). – Ventail

Answer 1

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.

Madelenemadelin

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). – Ventail

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. – Contuse

@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. – Teak