CODEWITHSUNDEEP
rsqlitedbirsqlite
Laura
Laura

Reputation: 139

R dbReadTable returns no such table error

I ran this bit of code yesterday successfully, but today, I am getting a 'no such table' error. I am trying to pull data in a table (named tblLatjamInputs) from an SQLite database into R using DBI::dbReadTable(), but it is acting as though the table does not exist.

Using SQLiteStudio

screenshot of SQLiteStudio showing the existence of the table

and separately the command line

screenshot of command line window showing that table exists in the database

enter image description here,

I can see that the table does indeed exist and that there are data in the table.

Here is the code, both written out and as a screenshot so you can see the error I'm getting. 

setwd("D:/hsm/databases")
db <- dbConnect(SQLite(), conn = "lookup_and_tracking.sqlite")
tblName <- "tblLatjamInputs"
df.full <- dbReadTable(db, tblName)

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

I got the same error when the tblName line is changed to this: tblName <- dbQuoteIdentifier(db, "tblLatjamInputs")

screenshot of code in R to retrieve table information from an sqlite database and the resulting error

dbListTables(db) returns character(0), and dbListFields(db, "lkpSpecies") (a different table in the db) returns the no such table error as well.

I checked that there are no spaces around the table name in the database. I also tried to pull data from other tables (to see if it was just an issue with this table), but I got the same error. I have tried disconnecting and reconnecting to the database multiple times, including disconnecting from the db, closing SQLiteStudio and the command line, and then reopening. I also tried closing everything, including R, reloading the project, and starting again from scratch. I also tried connecting to a different database altogether with the same results (R makes the connection, but can't seem to find any tables). I'm totally baffled because, as I mentioned, all this works fine in the command line, and I did this yesterday with the same database, table, and lines of code, and it worked fine.

Upvotes: 4

Views: 1743

Answers (1)

MrFlick
MrFlick

Reputation: 206197

Use

db <- dbConnect(SQLite(), "lookup_and_tracking.sqlite")

The problem is the file name parameter is not named conn=; It's dbname= and the default is "" which creates a new, empty data base.

Upvotes: 3

Related Questions