6

The Issue

When trying to write to a table with a non-default schema, dbWriteTable in the package DBI, writes to default.non-default.tablename rather than writing to non-default.tablename. I know that non-default.tablename exists because it's showing up in my SSMS database.

Reproducible Example/What I've Tried

Create this table in SQL Server with a non-default schema 'guest'. I am placing it in a database called 'SAM':

CREATE TABLE guest.MikeTestTable(
[a] [float] NULL,
[b] [float] NULL,
[c] [varchar](255) NULL)

#Create a df to insert into guest.MikeTestTable
df <- data.frame(a = c(10, 20, 30),
                 b = c(20, 40, 60),
                 c = c("oneT", "twoT", "threeT"))

#Create a connection:
con <- DBI::dbConnect(odbc::odbc(),
             .connection_string = "Driver={SQL Server};
                                   server=localhost;
                                   database=SAM;
                                   trustedConnection=true;")

#Try to write contents of df to the table using `dbWriteTable`
DBI::dbWriteTable(conn = con,
                  name = "guest.MikeTestTable",
                  value = df,
                  append = TRUE)

#Create a query to read the data from `"guest.MikeTestTable"`:
q <- "SELECT [a]
  ,[b]
  ,[c]
  FROM guest.MikeTestTable"

##Read the table into R to show that nothing actually got written to the 
##table but that it recognizes `guest.MikeTestTable` does exist:
DBI::dbGetQuery(con, q)

[1] a b c
<0 rows> (or 0-length row.names)

I thought this was a weird result so I opened up my SSMS and lo and behold, the table dbo.guest.MikeTestTable was created. Any help would be much appreciated.

Community
  • 1
  • 1
user111417
  • 143
  • 1
  • 9
  • Try the following: `dbWriteTable(con, c("schema_name","table_name"), df, append = TRUE)` – Parfait Jul 27 '17 at 16:47
  • Thank you for the advice. Trying `dbWriteTable(con, c("guest", "MikeTestTable"), df, append = TRUE)` returns the error: `Error: length(name) == 1 is not TRUE` – user111417 Jul 27 '17 at 17:14
  • Well, it seems that only works with the [Postgres driver](https://stackoverflow.com/a/12001451/1422451). Try using RODBC package. I see someone [pinged](https://github.com/rstats-db/DBI/issues/181) the DBI authors. Is that you? – Parfait Jul 27 '17 at 17:30
  • 1
    We do not want to use the RODBC package because it doesn't play nicely with Travis builds on linux and mac. I made some comments in the ping of the DBI authors. – user111417 Jul 27 '17 at 18:21

2 Answers2

6

The CRAN release last week (related to the issue @user111417 linked to) resolves this using the new DBI::Id() function, where the schema and table names are separate and explicit. Here's an example.

library(magrittr)
table_id <- DBI::Id(
  schema  = "schema_1",
  table   = "car"
)

ds <- mtcars %>% 
  tibble::rownames_to_column("car")

# Create the Table
channel <- DBI::dbConnect(
  drv   = odbc::odbc(),
  dsn   = "cdw_cache"
)
result <- DBI::dbWriteTable(
  conn        = channel,
  name        = table_id,
  value       = ds,
  overwrite   = T,
  append      = F
)

DBI::dbGetQuery(channel, "SELECT COUNT(*) FROM schema_1.car")
# Produces `1 32`

DBI::dbExistsTable(channel, table_id)
# Produces: [1] TRUE

DBI::dbDisconnect(channel)

(Thanks to Daniel Wood for help in https://github.com/r-dbi/odbc/issues/191.)

wibeasley
  • 5,000
  • 3
  • 34
  • 62
1

Please see here for the answer

user111417
  • 143
  • 1
  • 9