Reading sqlite views from R

[Oduor]

Hi, is it possible to read sqlite views from R ?

I have tried

dbConnect(con, 'select * from myview')

But am getting errors.

Any help?

[nirgrahamuk]

what is your con? (how have you made it ?)
and what is your first error ?
At first glance I think you are mixing up connecting to a database, with querying the connection.
look at an example from the documentation for DBI.

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)
dbGetQuery(con, "SELECT * FROM mtcars")
dbGetQuery(con, "SELECT * FROM mtcars", n = 6)

# Pass values using the param argument:
# (This query runs eight times, once for each different
# parameter. The resulting rows are combined into a single
# data frame.)
dbGetQuery(
  con,
  "SELECT COUNT(*) FROM mtcars WHERE cyl = ?",
  params = list(1:8)
)

dbDisconnect(con)

[Oduor]

Oo i think i confused in writing yhe query

con <- dbConnect(RSQLite::SQLite(), "mydb.sqlite")

dbSendQuery(con, 'select * from myview')

[nirgrahamuk]

try

DBI::dbListTables(con)

to see if myview is there.
also use dbGetQuery to do queries...
in your most recent post you use dbConnect twice.

[Oduor]

my view is not there, right, this takes me to my original question, is it possible ti get the views results from R ?

[nirgrahamuk]

Are other tables that you would expect there?

dbListTables: List remote tables

Description

Returns the unquoted names of remote tables accessible through this connection. This should include views and temporary objects, but not all database backends (in particular RMariaDB and RMySQL) support this.

[Oduor]

Thanks for your willingness to help me out. I am not so good in DBMS but am basing my arguments on this tutorial Sqlite Views

where the tutor here later suggests;

using normal SQL statements to read a view.

[nirgrahamuk]

in your database what table had you based your view on, and does that show in the dbListTables ?