Unable to connect to database using RSQLite

Question

So I'm trying to access my DB file without success. Here is my script:

library(DBI)

library(sqldf)

drv <- dbDriver("SQLite")

con <- dbConnect(drv, dbname = "database.sqlite")

and here is the error:

drv <- dbDriver("SQLite")

con <- dbConnect(drv, dbname = "database.sqlite")

Error in rsqlite_connect(dbname, loadable.extensions, flags, vfs) : Could not connect to database: unable to open database file

I've checked, of course, and made sure that I've installed the packages correctly and that my working directory is set.

Answer 1

I have solved my problem, and its a bit embarrassing:

I've saved my file on my desktop. since my OS in installed in my native language (Hebrew) the file path had one Hebrew word in it, and while that doesn't pose a problem with reading tables into R, it does pose a problem to the SQL connection.

solving it was easy - I've saved the file in a new folder on my hard drive (c:\database), set as working dir, and everything worked fine.

Answer 2

I can duplicate this error in two ways:

• The file exists but you don't have permission to open it

This could be because of operating system permissions. Check your permissions.

• The file doesn't exist and you don't have permission to create it.

If SQLite is asked to open a database file that doesn't exist, it tries to create it. If this fails, you get that error message. This will fail if the path to the DB file (in this case the current working directory) does not let you create files. Check your permissions.

Note that if the file does exist but is corrupted, I get a different error:

> con <- dbConnect(drv, dbname = "database.sqlite")
Error in rsqlite_send_query(conn@ptr, statement) : 
  file is encrypted or is not a database
> 

so that's probably not your problem.