Running Code Chunks in R (knitr package)

Question

I'm working on a markdown file I was sent that wants some of the script to be written in code chunks using the knitr package, which I've never used before.

The problem I'm running into is that the scripts don't appear to be working when placed in the chunks, but work just fine when I run them outside of that context.

Example:

sqlite <- dbDriver("SQLite")
con <- dbConnect(sqlite,"db/sqlite/table.db")

That works.

```{r db_connect}
sqlite <- dbDriver("SQLite")
con <- dbConnect(sqlite,"db/sqlite/table.db")
```

This does not work. Here is the traceback on the error:

Error in rsqlite_connect(dbname, loadable.extensions, flags, vfs) : Could 
not connect to database: unable to open database file
8. stop(structure(list(message = "Could not connect to database:\nunable to open database file", call = rsqlite_connect(dbname, loadable.extensions, flags, vfs), cppstack = structure(list(file = "", line = -1L, stack = "C++ stack not available on this system"), .Names = c("file", ...
7. rsqlite_connect(dbname, loadable.extensions, flags, vfs)
6. initialize(value, ...)
5. initialize(value, ...)
4. new("SQLiteConnection", ptr = rsqlite_connect(dbname,loadable.extensions, flags, vfs), dbname = dbname, flags = flags, vfs = vfs, loadable.extensions = loadable.extensions, ref = new.env(parent = emptyenv()))
3. .local(drv, ...)
2. dbConnect(sqlite, "db/sqlite/table.db")
1. dbConnect(sqlite, "db/sqlite/table.db")

Does anyone have any recommendations on how to troubleshoot this?

you likely had `library()` calls in your R script environment and you need those same `library()` calls in the Rmd (near the top, generally, and never cached). — hrbrmstr, Dec 15 '17 at 20:38
I've tried to reproduce your error, and get it both inside and outside knitr. I'm not sure this is a problem linked with loading the library, this produces the following error : could not find function "dbDriver" — Cedric, Dec 15 '17 at 20:45
Trying with defaults arguments works in knitr `require("RSQLite") sqlite <- dbDriver("SQLite") con <- dbConnect(RSQLite::SQLite(), ":memory:") dbListTables(con)` I've posted the full code in an Rnw file does that help you trying it out ? — Cedric, Dec 15 '17 at 20:54
@hrbrmstr What do you mean? I have those library calls at the top of my script. https://imgur.com/a/SlrEl That's what it looks like and when I run that chunk, it works fine. — , Dec 15 '17 at 21:12
I've now tried to creating a database, it works fine in knitr. I can access it and retreive the results in the chunk. Note I've been using knitr and Rpostgres and sqldf without problems for years. Sorry I'm not dismissing your problem, but I can't reproduce it so it's hard to help — Cedric, Dec 15 '17 at 21:40
if @RIPHarambe had posted a reproducible example or even a representative sample we'd all be able to help more vs guessing. — hrbrmstr, Dec 15 '17 at 23:05

Cedric · Answer 1 · 2017-12-16T08:25:16.720

0

second test

% 
\documentclass[a4paper]{article}
\title{test }
\begin{document}
<<db_create>>=
require("RSQLite")
sqlite <- dbDriver("SQLite")
db <- dbConnect(SQLite(), dbname="Test.sqlite")
dbSendQuery(conn = db,
    "CREATE TABLE School
               (SchID INTEGER,
        Location TEXT,
        Authority TEXT,
        SchSize TEXT)")
dbListTables(con)
dbSendQuery(conn = db,
    "INSERT INTO School
VALUES (1, 'urban', 'state', 'medium')")
dbSendQuery(conn = db,
    "INSERT INTO School
VALUES (2, 'urban', 'independent', 'large')")
dbSendQuery(conn = db,
    "INSERT INTO School
VALUES (3, 'rural', 'state', 'small')")
@

<<db_connect>>=

con <- dbConnect(RSQLite::SQLite(), "Test.sqlite")
dbListTables(con)
@
\end{document}

edited Dec 16 '17 at 08:25

answered Dec 15 '17 at 20:57

Cedric

2,412
17
31

You'll have to forgive me, but what do you want me to do with this? – Dec 15 '17 at 21:13
Of course, does it works for you ? I mean I've just tested this example and it works fine, in knitr ? When trying dbConnect(sqlite,"db/sqlite/table.db") it fails both in the knitr and outside. Or is it a problem because I've used sweave instead of markdown ? – Cedric Dec 15 '17 at 21:15
Well, I'm guessing that's because you don't have the db/sqlite/table.db file, right? – Dec 15 '17 at 21:19
Yes that's obviously the case. But then how can we test to help you ? By the way I'll remove my useless post :-) – Cedric Dec 15 '17 at 21:21
I've replaced this with the second example, in case it helps you. – Cedric Dec 16 '17 at 08:27

Running Code Chunks in R (knitr package)

1 Answers1

Linked