1

In monetdb I have set up a schema main and my tables are created into this schema.

For example, the department table is main.department.

With dplyr I try to query the table:

mdb <- src_monetdb(dbname="model", user="monetdb", password="monetdb")

tbl(mdb, "department")

But I get

Error in .local(conn, statement, ...) : 
  Unable to execute statement 'PREPARE SELECT * FROM "department"'.
Server says 'SELECT: no such table 'department'' [#42S02].

I tried to use "main.department" and other similar combinations with no luck.

What is the appropriate syntax?

Enzo
  • 2,543
  • 1
  • 25
  • 38
  • What is the output of `dbListTables(mdb$con)` (or `db_list_tables(mdb$con)`? – dickoa Aug 27 '15 at 18:19
  • `dbListTables()` is implemented in monetdb to lists all the tables except system tables, and this is what I see. – Enzo Aug 27 '15 at 20:09
  • what do you see ? I didn't see any output. In addition, what is also the result of `select current_schema;` ? – dickoa Aug 27 '15 at 21:44

2 Answers2

3

There is a somewhat hacky workaround for this: We can manually set the default schema for the connection. I have a database testing, in there is a schema foo with a table called bar.

mdb <- src_monetdb("testing")
dbSendQuery(mdb$con, "SET SCHEMA foo");
t <- tbl(mdb, "bar")
Hannes Mühleisen
  • 2,542
  • 11
  • 13
0

The dbplyr package (a backend of dplyr for database connections) has a in_schema() function for these cases:

conn <- dbConnect(
  MonetDB.R(),
  host = "localhost",
  dbname = "model",
  user = "monetdb",
  password = "monetdb",
  timeout = 86400L
)

department = tbl(conn, dbplyr::in_schema("main", "department"))
Fabio Vaz
  • 386
  • 2
  • 10