Using non-default schema in database

tidyverse
, , ,
1

We can create our temporary tablesunder a specific "DELETE" schema in our database (by temporary I mean temporary use and the tables under this schema can be cleaned up by database maintenance. This might be different the temporary table concept in certain databases) . To access this non-default schema, I read Schema selection but many examples doesn't work.

I didn't create an github issue because I'm not sure which package might be responsible for the error (and we don't have access to github in company).

I'm connecting the database (IBM DB2) with odbc and DBI.

  • read works
    dplyr::tbl(con, dbplyr::in_schema("DELETE", "sometable"))

  • but I cannot create table

copy_to(con, iris, dbplyr::in_schema("DELETE", "iris"))
# Error: Can't unquote DELETE.iris

The third example in Schema selection article also doesn't work for me.

dbWriteTable(con, SQL("DELETE.iris"), iris)
# Error: Can't unquote DELETE.iris

Finally I found out a usage that works

dbWriteTable(con, name = DBI::Id(schema="DELETE", name="iris"), iris)
dbRemoveTable(con, name = DBI::Id(schema="DELETE", name="iris"), iris)

Is there something wrong with my setup? I think at least the Schema selection article should mention the DBI::Id method. I only incidentally found this through reading an issue discussion in github.

2

In your post you do not specify which DBMS are you having issues with - is it Postgres by chance?

I had issues with copying to a Postgres schema in the past that I was never able to truly solve. I ended up writing my data to a temporary table and flipping it over to its proper place by executing a piece of SQL code directly.

A suboptimal solution, but it got the job done (eventually).

Thanks for the pointer to DBI::Id function!

3

Thanks for the reminder, I added database information in original post (IBM DB2). I thought it's relative irrelevant since odbc/DBI is standard interface (but dbplyr probably do depend on backend support).

Does DBI::Id solve your problem?

4

I am not sure, as I solved the problem via a workaround when it was pressing, and now I don't have time to test it again - but I will keep the idea in mind for the future.

By the way good luck working with DB2, I still get shivers when remembering the short time I was forced to use the technology :slight_smile:

5

For the Postgresql case, I actually saw they support the schema syntax as a vector.

Database issues are so often to be vendor specific, so abstracting away the details like dbplyr seemed to be a very difficult job.

6

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.