3

I have done a fresh install of package sqldf on my system, but whenever i run any sql queries I get

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

regardless of the name or table

I have looked through all guides on setting the package up etc and read the pdf, for some unknown reason it just wont work on my machine where it works fine at work.

example code - "a corrected answer on stacked overflow copied so should just work"

library(sqldf)

apps.rsd <- structure(list(appid = c(173L, 717L, 996L, 209L, 602L, 255L), 
                           cid = c(4L, 15L, 21L, 5L, 13L, 6L), 
                           price = c(0, 0, 0, 1.99, 0, 0.76), 
                           count = c(411, 411, 210, 18, 921, 22), 
                           sum = c(1226, 1870, 871, 66, 3948, 86), 
                           mean = c(2.98296836982968, 4.54987834549878, 4.14761904761905, 3.66666666666667, 4.28664495114007, 3.90909090909091), 
                           sd = c(1.73897694746568, 0.958668345866094, 1.31370760232218, 1.33373734360862, 1.62114131819336), 
                           rcount = c(3, 3, 3, 5, 5, 7), 
                           rsum = c(7, 0, 0, 13, 0, 19), 
                           rsd = c(2.3094010767585, 2.3094010767585, 2.3094010767585, 2.19089023002066, 2.19089023002066, 2.1380899352994)), 
                      .Names = c("appid", "cid", "price", "count", "sum", "mean", "sd", "rcount", "rsum", "rsd"), 
                      class = c("data.table", "data.frame"), 
                      row.names = c(NA, -6L))  

sqldf("SELECT appid FROM 'apps.rsd' WHERE rcount > 50")**
alistaire
  • 42,459
  • 4
  • 77
  • 117
  • 2
    RSQLite 2.0 is not backwards compatible and breaks sqldf. Use an older version of RSQLite or use the development version of sqldf on github. `devtools::install_github("ggrothendieck/sqldf")` – G. Grothendieck Jun 24 '17 at 03:44
  • 1
    thank you so much, that was doing my head in – hughmanatee Jun 25 '17 at 04:48
  • Possible duplicate of [sqldf can't find the data with error "no such table"](https://stackoverflow.com/questions/44755802/sqldf-cant-find-the-data-with-error-no-such-table) – Scarabee Jun 27 '17 at 13:43
  • 1
    The new sqldf 0.4-11 which works with RSQLite 2.0 is now on CRAN. – G. Grothendieck Jun 28 '17 at 22:41

1 Answers1

1

Putting the name of table in quotes ' ' makes it work since the name of the table has a fullstop within it which creates problems with SQL

i.e

> sqldf("SELECT appid FROM 'apps.rsd' WHERE rcount > 50")
Loading required package: tcltk
[1] appid
<0 rows> (or 0-length row.names)
Warning message:
Quoted identifiers should have class SQL, use DBI::SQL() if the caller performs the quoting.


> sqldf("SELECT appid FROM apps.rsd WHERE rcount > 50")
Error in rsqlite_send_query(conn@ptr, statement) : 
  no such table: apps.rsd


> sqldf("SELECT appid FROM 'apps.rsd' WHERE rcount > 0")
  appid
1   173
2   717
3   996
4   209
5   602
6   255
Ajay Ohri
  • 3,382
  • 3
  • 30
  • 60