`writeData` fails if table schema is not default

[Aylr]

@mmastand commented on Tue Jun 13 2017

I filed an issue here and here.

Reading works fine from any table. Writing only works if the table schema is the DB default. I've been working on it with Joe and Jeff. We've been using RODBC's sqlSave as a workaround.

[EthanTaft]

Filed an issue on odbc's github page here.

Let me know if the issue filed addresses the things we want it to.

[EthanTaft]

Also see the issue posted on DBI's repository here

And the issue posted on stack overflow here

[mmastand]

I'm going to move this to "issues" and we can keep an eye on it from there.

[EthanTaft]

Jim Hester did everyone a solid and solved our problem with a new branch update. As of this posting it hasn't been merged to master. Please see here for a link to the discussion.

Example:

#create this table in SSMS on localhost
CREATE TABLE guest.MikeTestTable(
[a] [float] NULL,
[b] [float] NULL,
[c] [varchar](255) NULL)

#install branch that supports writing to non-default schemas
devtools::install_github("rstats-db/odbc@SQLTable")
library(odbc)
library(DBI)

df <- data.frame(a = c(10, 20, 30),
                 b = c(20, 40, 60),
                 c = c("oneT", "twoT", "threeT"))

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

tbl <- dbId(con = con, name = "MikeTestTable", schema = "guest", catalog = "SAM")

#use append or overwrite commands appropriately
dbWriteTable(con, tbl, df, append = TRUE)

#does table exist
dbExistsTable(con, tbl)

#read table 
dbReadTable(con, tbl)

#remove table if wanted
dbRemoveTable(con, tbl)

[mmastand]

Do we need this? Also, @michaellevy , when we do database stuff in the refactor, keep this issue in mind. https://github.com/r-dbi/odbc/issues/140

[michaellevy]

We definitely need functionality like this. I've left selectData and writeData in for now. I don't know how db connections work well enough to rewrite them well.

[maxmoro]

Do you if there is any update on this topic? I see the rstats-db/odbc@SQLTable works with a different schema, but the current odbc version doesn't

[mmastand]

@maxmoro I'm working on this now. It looks like there is movement on the underlying problems with DBI but they aren't quite fixed. For now, I'm writing a workaround to go into healthcareai 2.0.

We've been using RODBC::sqlSave with good success.

[mmastand]

Bringing this back in just to check on this.

[mmastand]

This appears to work with the versions of DBI and odbc that are on CRAN. I'll update db_write to work with DBI.

[mmastand]

Database doesn't seem to work with DBI::Id. This prevents you from connecting to the Shared database but then write to the SAM database. I need to play around a little more to make sure and then create a reprex if this is the case.

[mmastand]

@michaellevy What do you think about db_write only being able to handle a table name and schema? It gets the database name from the connection string, which is consistent with RODBC.

Normal connection behavior is that you can connect to any database (or none) and read from all databases. But you can only write to the one that you are connected to.

[michaellevy]

@mmastand Is it rare that we'd want to write to multiple DBs? Maybe predictions and telemetry going to different DBs, or is that handled differently?

Is the worst case that you have to do something like below? If so, that doesn't seem too bad if it makes this function work.

con1 <- f("where predictions go")
write_db(con1, preds)
kill(con1)
con2 <- f("where metadata goes")
write_db(con2, metadata)

[mmastand]

Yeah, I think it's rare. Telemetry and results to different DBs is all I can think of as an exception. And yes, what you wrote is the worst case scenario.