sqlrsql-serverr-dbi

How to use dbWriteTable on a connection with no default Database

I've seen many posts on SO and the DBI Github regarding trouble using DBI::dbWriteTable (i.e. [1], [2]). These mostly have to do with the use of non-default schemas or whatnot.

That's not my case.

I have a server running SQL Server 2014. This server contains multiple databases.

I'm developing a program which interacts with many of these databases at the same time. I therefore defined my connection using DBI::dbConnect() without a Database= argument.

I've so far only had to do SELECTs on the databases, and this connection works just fine with dbGetQuery(). I just need to name my tables including the database names: DatabaseFoo.dbo.TableBar, which is more than fine since it makes things transparent and intentional. It also stops me from being lazy and making some calls omitting the Database name on whichever DB I named in the connection.

I now need to add data to a table, and I can't get it to work. A call to

DBI::dbWriteTable(conn, "DatabaseFoo.dbo.TableBar", myData, append = TRUE)

works, but creates a table named DatabaseFoo.dbo.TableBar in the master Database, which isn't what I meant (I didn't even know there was a master Database).

The DBI::dbWriteTable man page states the name should be

A character string specifying the unquoted DBMS table name, or the result of a call to dbQuoteIdentifier().

So I tried dbQuoteIdentifier() (and a few other variations):

DBI::dbWriteTable(conn,
                  DBI::dbQuoteIdentifier(conn,
                                         "DatabaseFoo.dbo.TableBar"),
                  myData)
# no error, same problem as above

DBI::dbWriteTable(conn,
                  DBI::dbQuoteIdentifier(conn,
                                         DBI::SQL("DatabaseFoo.dbo.TableBar")),
                  myData)
# Error: Can't unquote DatabaseFoo.dbo.TableBar

DBI::dbWriteTable(conn,
                  DBI::SQL("DatabaseFoo.dbo.TableBar"),
                  myData)
# Error: Can't unquote DatabaseFoo.dbo.TableBar

DBI::dbWriteTable(conn,
                  DBI::dbQuoteIdentifier(conn,
                                         DBI::Id(catalog = "DatabaseFoo",
                                                 schema = "dbo",
                                                 table = "TableBar")),
                  myData)
# Error: Can't unquote "DatabaseFoo"."dbo"."TableBar"

DBI::dbWriteTable(conn,
                  DBI::Id(catalog = "DatabaseFoo",
                          schema = "dbo",
                          table = "TableBar"),
                  myData)
# Error: Can't unquote "DatabaseFoo"."dbo"."TableBar"

In the DBI::Id() attempts, I also tried using cluster instead of catalog. No effect, identical error.

However, if I change my dbConnect() call to add a Database="DatabaseFoo" argument, I can simply use dbWriteTable(conn, "TableBar", myData) and it works.

So the question becomes, am I doing something wrong? Is this related to the problems in the other questions?

Solution

  • This is a shortcoming in the DBI package. The dev version DBI >= 1.0.0.9002 no longer suffers from this problem, will hit CRAN as DBI 1.1.0 soonish.