New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

`dbWriteTable` fails to write to non-default schema #91

Closed
EthanTaft opened this Issue Jul 17, 2017 · 46 comments

Comments

Projects
None yet
@EthanTaft

EthanTaft commented Jul 17, 2017

Issue Description and Expected Result

-dbWriteTable does not write to non-default schema. It instead writes to dbo.non-default
schema.tablename
-Would like functionality to specify what schema within dbWriteTable()

Database

SQL Server 2012

Reproducible Example

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)

Upon further inspection, SSMS shows a table that exists with the schema and name of: dbo.guest.MikeTestTable

@jimhester jimhester changed the title from `dbWriteTable` fails to find tables in non-default schemas, lacks functionality to `dbWriteTable` fails to find tables in non-default schemas Jul 20, 2017

@jimhester

This comment has been minimized.

Show comment
Hide comment
@jimhester

jimhester Jul 20, 2017

Member

You need to use DBI::SQL("schema.table") to specify a given schema and table. Note the schema and table names are not automatically quoted when using DBI::SQL(), so if it contains special characters you will have to add the quoting manually.

Member

jimhester commented Jul 20, 2017

You need to use DBI::SQL("schema.table") to specify a given schema and table. Note the schema and table names are not automatically quoted when using DBI::SQL(), so if it contains special characters you will have to add the quoting manually.

@EthanTaft

This comment has been minimized.

Show comment
Hide comment
@EthanTaft

EthanTaft Jul 20, 2017

Thanks for the reply. After trying DBI::SQL("guest.MikeTestTable") before running dbWriteTable() in accordance with the example from above, I still do not get the desired results.

What I am finding out now (I think my original post did not correctly identify the error): writing to a non-default schema(.guest) produces the table dbo.guest.MikeTestTable without having created that table to begin with.

What am I doing wrong?

EthanTaft commented Jul 20, 2017

Thanks for the reply. After trying DBI::SQL("guest.MikeTestTable") before running dbWriteTable() in accordance with the example from above, I still do not get the desired results.

What I am finding out now (I think my original post did not correctly identify the error): writing to a non-default schema(.guest) produces the table dbo.guest.MikeTestTable without having created that table to begin with.

What am I doing wrong?

@EthanTaft

This comment has been minimized.

Show comment
Hide comment
@EthanTaft

EthanTaft Jul 27, 2017

I also tried this:

DBI::dbWriteTable(conn = con,
                  name = SQL("guest.MikeTestTable"),
                  value = df,
                  append = TRUE)

This returns the error:

Error: <SQL> 'CREATE TABLE guest.MikeTestTable (
  "a" FLOAT,
  "b" FLOAT,
  "c" varchar(255)
)
'
  nanodbc/nanodbc.cpp:1587: 42S01: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'MikeTestTable' in the database. 

EthanTaft commented Jul 27, 2017

I also tried this:

DBI::dbWriteTable(conn = con,
                  name = SQL("guest.MikeTestTable"),
                  value = df,
                  append = TRUE)

This returns the error:

Error: <SQL> 'CREATE TABLE guest.MikeTestTable (
  "a" FLOAT,
  "b" FLOAT,
  "c" varchar(255)
)
'
  nanodbc/nanodbc.cpp:1587: 42S01: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'MikeTestTable' in the database. 

@EthanTaft EthanTaft changed the title from `dbWriteTable` fails to find tables in non-default schemas to `dbWriteTable` fails to write to non-default schema Jul 27, 2017

@jmi5

This comment has been minimized.

Show comment
Hide comment
@jmi5

jmi5 Aug 18, 2017

+1 - I am also trying to write to a nondefault schema and running into the same problem.

jmi5 commented Aug 18, 2017

+1 - I am also trying to write to a nondefault schema and running into the same problem.

@aschmu

This comment has been minimized.

Show comment
Hide comment
@aschmu

aschmu Aug 20, 2017

@EthanTaft Hi, maybe you should try dropping the table before recreating it.

aschmu commented Aug 20, 2017

@EthanTaft Hi, maybe you should try dropping the table before recreating it.

@vicm159

This comment has been minimized.

Show comment
Hide comment
@vicm159

vicm159 Aug 21, 2017

I have the same problem. If the table does not exist using SQL("Schema.TableName") works fine. However, I am trying to append to the same table which creates the error described by EthanTaft.

vicm159 commented Aug 21, 2017

I have the same problem. If the table does not exist using SQL("Schema.TableName") works fine. However, I am trying to append to the same table which creates the error described by EthanTaft.

@EthanTaft

This comment has been minimized.

Show comment
Hide comment
@EthanTaft

EthanTaft Aug 21, 2017

@aschmu Not too sure I follow. In my post it's not implied that it's "recreated" anywhere. I've made sure that I create it once in SSMS, then I try to write to it...I think you're trying to answer the "second" error.

EthanTaft commented Aug 21, 2017

@aschmu Not too sure I follow. In my post it's not implied that it's "recreated" anywhere. I've made sure that I create it once in SSMS, then I try to write to it...I think you're trying to answer the "second" error.

@jimhester

This comment has been minimized.

Show comment
Hide comment
@jimhester

jimhester Aug 21, 2017

Member

The SQLTable branch should make this work more smoothly. It will likely be merged with the master in the near future once we settle on the API. For now you can try it out using the following

devtools::install_github("rstats-db/odbc@SQLTable")
library(DBI)
library(odbc)
con <- dbConnect(odbc::odbc(), "PostgreSQL")
# table named `mtcars`, schema named `abc`
tbl <- dbId(con, "mtcars", "abc")
tbl
#> <SQL> "abc"."mtcars"
dbWriteTable(con, tbl, mtcars)
dbExistsTable(con, tbl)
#> [1] TRUE
nrow(dbReadTable(con, tbl))
#> [1] 32
dbWriteTable(con, tbl, mtcars, append = T)
nrow(dbReadTable(con, tbl, row.names = FALSE))
#> [1] 64
dbWriteTable(con, tbl, mtcars, overwrite = T)
nrow(dbReadTable(con, tbl))
#> [1] 32
dbRemoveTable(con, tbl)
dbExistsTable(con, tbl)
#> [1] FALSE
Member

jimhester commented Aug 21, 2017

The SQLTable branch should make this work more smoothly. It will likely be merged with the master in the near future once we settle on the API. For now you can try it out using the following

devtools::install_github("rstats-db/odbc@SQLTable")
library(DBI)
library(odbc)
con <- dbConnect(odbc::odbc(), "PostgreSQL")
# table named `mtcars`, schema named `abc`
tbl <- dbId(con, "mtcars", "abc")
tbl
#> <SQL> "abc"."mtcars"
dbWriteTable(con, tbl, mtcars)
dbExistsTable(con, tbl)
#> [1] TRUE
nrow(dbReadTable(con, tbl))
#> [1] 32
dbWriteTable(con, tbl, mtcars, append = T)
nrow(dbReadTable(con, tbl, row.names = FALSE))
#> [1] 64
dbWriteTable(con, tbl, mtcars, overwrite = T)
nrow(dbReadTable(con, tbl))
#> [1] 32
dbRemoveTable(con, tbl)
dbExistsTable(con, tbl)
#> [1] FALSE
@EthanTaft

This comment has been minimized.

Show comment
Hide comment
@EthanTaft

EthanTaft Aug 21, 2017

@jimhester Thanks for your work and response. I've downloaded the SQLTable branch and I am now getting an error when trying to build a connection string. Did something change in the branch for creating connections?

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

Error in bigint_mappings() : object '_odbc_bigint_mappings' not found

EthanTaft commented Aug 21, 2017

@jimhester Thanks for your work and response. I've downloaded the SQLTable branch and I am now getting an error when trying to build a connection string. Did something change in the branch for creating connections?

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

Error in bigint_mappings() : object '_odbc_bigint_mappings' not found
@jimhester

This comment has been minimized.

Show comment
Hide comment
@jimhester

jimhester Aug 21, 2017

Member

No, you may have to restart the R process before trying to load the new version of the package.

Member

jimhester commented Aug 21, 2017

No, you may have to restart the R process before trying to load the new version of the package.

@EthanTaft

This comment has been minimized.

Show comment
Hide comment
@EthanTaft

EthanTaft Aug 21, 2017

Tried restarting and that didn't work. I re-installed from master and I get the same error so I uninstalled and re-installed from CRAN and now connection works but using CRAN version still getting original write issues.

EthanTaft commented Aug 21, 2017

Tried restarting and that didn't work. I re-installed from master and I get the same error so I uninstalled and re-installed from CRAN and now connection works but using CRAN version still getting original write issues.

@vicm159

This comment has been minimized.

Show comment
Hide comment
@vicm159

vicm159 Aug 22, 2017

I used the dbId function and it works great for me. Thank you for the update. BTW i am using MS SQL Server on Ubuntu server and I connected to the database using the .connection_string parameter.

vicm159 commented Aug 22, 2017

I used the dbId function and it works great for me. Thank you for the update. BTW i am using MS SQL Server on Ubuntu server and I connected to the database using the .connection_string parameter.

@EthanTaft

This comment has been minimized.

Show comment
Hide comment
@EthanTaft

EthanTaft Aug 22, 2017

@vicm159 What is the dbld function? I am using MS SQL in windows.

EthanTaft commented Aug 22, 2017

@vicm159 What is the dbld function? I am using MS SQL in windows.

@jimhester

This comment has been minimized.

Show comment
Hide comment
@jimhester

jimhester Aug 22, 2017

Member

It is the function defined in the branch I mentioned, I renamed it from SQLTable() to match the discussion in r-dbi/DBI#24

Member

jimhester commented Aug 22, 2017

It is the function defined in the branch I mentioned, I renamed it from SQLTable() to match the discussion in r-dbi/DBI#24

@EthanTaft

This comment has been minimized.

Show comment
Hide comment
@EthanTaft

EthanTaft Aug 22, 2017

@jimhester I got it working now using dbId(). The issue has been solved with your updates. Thank you for taking your time to work on this; it's a huge help!

EthanTaft commented Aug 22, 2017

@jimhester I got it working now using dbId(). The issue has been solved with your updates. Thank you for taking your time to work on this; it's a huge help!

@vicm159

This comment has been minimized.

Show comment
Hide comment
@vicm159

vicm159 Aug 22, 2017

@jimhester I have run into a little bit of a problem. I have a couple of tables to append however every once in while i get this message

Error in result_insert_dataframe(rs@ptr, values) :
nanodbc/nanodbc.cpp:1587: 22018: [Microsoft][ODBC Driver 13 for SQL Server]I

I'm not sure why since all these tables have the same type of data and only certain tables give me this message. Also, it looks like the message is truncated. Any way to see to the whole message? any idea why i keep getting this problem with? Thanks again for your help I really appreciate it.

vicm159 commented Aug 22, 2017

@jimhester I have run into a little bit of a problem. I have a couple of tables to append however every once in while i get this message

Error in result_insert_dataframe(rs@ptr, values) :
nanodbc/nanodbc.cpp:1587: 22018: [Microsoft][ODBC Driver 13 for SQL Server]I

I'm not sure why since all these tables have the same type of data and only certain tables give me this message. Also, it looks like the message is truncated. Any way to see to the whole message? any idea why i keep getting this problem with? Thanks again for your help I really appreciate it.

@jimhester

This comment has been minimized.

Show comment
Hide comment
@jimhester

jimhester Aug 22, 2017

Member

I don't know and can't really tell without being able to see the whole error message. There is no code in R , this package or it's dependencies that truncates the message, so the truncation must be happening in the driver or driver manager. I would try and enabling logging to see if you can get an actionable error message that way. See #96 for another issue with truncated error messages from SQL Server.

Member

jimhester commented Aug 22, 2017

I don't know and can't really tell without being able to see the whole error message. There is no code in R , this package or it's dependencies that truncates the message, so the truncation must be happening in the driver or driver manager. I would try and enabling logging to see if you can get an actionable error message that way. See #96 for another issue with truncated error messages from SQL Server.

@weltherrschaf

This comment has been minimized.

Show comment
Hide comment
@weltherrschaf

weltherrschaf Sep 29, 2017

i get this message

Error in result_insert_dataframe(rs@ptr, values) :
nanodbc/nanodbc.cpp:1587: 22018: [Microsoft][ODBC Driver 13 for SQL Server]I

I had the same error and fixed it by changing the field catalog to an empty string in the object returned by dbId before passing it to dbWriteTable like so:

tbl@catalog <- ""

weltherrschaf commented Sep 29, 2017

i get this message

Error in result_insert_dataframe(rs@ptr, values) :
nanodbc/nanodbc.cpp:1587: 22018: [Microsoft][ODBC Driver 13 for SQL Server]I

I had the same error and fixed it by changing the field catalog to an empty string in the object returned by dbId before passing it to dbWriteTable like so:

tbl@catalog <- ""

@vicm159

This comment has been minimized.

Show comment
Hide comment
@vicm159

vicm159 Oct 6, 2017

Yes, I forgot to mention i found the answer to this. I was uploading multiple tables (mainly with numeric columns) and most numeric columns did not have commas (example 5,000) but every once in a while one of the tables i was appending had commas. I'm guessing that dbWriteTable tried to append a character column with a numeric column in SQL Server and it threw that error. example the first table had column price = 1000 and the second table that needed to be appended to the first table had column price = 90,000 . Note to self make sure properly configure and class columns.
This is from MS webpage:
https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/appendix-a-odbc-error-codes
22018 | Invalid character value for cast specification

vicm159 commented Oct 6, 2017

Yes, I forgot to mention i found the answer to this. I was uploading multiple tables (mainly with numeric columns) and most numeric columns did not have commas (example 5,000) but every once in a while one of the tables i was appending had commas. I'm guessing that dbWriteTable tried to append a character column with a numeric column in SQL Server and it threw that error. example the first table had column price = 1000 and the second table that needed to be appended to the first table had column price = 90,000 . Note to self make sure properly configure and class columns.
This is from MS webpage:
https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/appendix-a-odbc-error-codes
22018 | Invalid character value for cast specification

@jasonpcasey

This comment has been minimized.

Show comment
Hide comment
@jasonpcasey

jasonpcasey Dec 1, 2017

I'm having the same issues as above. Namely, an attempt to write to a table in a non-default schema on SQL Server when using DBI::SQL("schema.table") results in R attempting to send a CREATE TABLE statement for a table that already exists.

When I use the SQLTable branch to attempt to write to dev.MyTestTable per the example above:
`
tbl <- dbId(con, "MyTestTable", "dev")

system.time({
dbWriteTable(con,
tbl,
as.data.frame(dat),
append = TRUE)
})
`
I get the following:

Error in connection_sql_tables(conn@ptr, catalog_name = if (length(name@catalog) > :
nanodbc/nanodbc.cpp:4274: 24000: [Microsoft][SQL Server Native Client 11.0]Invalid cursor state
Timing stopped at: 0.01 0 0.05

Setting the tbl catalog to blank does nothing to solve the problem.

I love the speed for SELECT statements and have had no trouble reading data from my test table. Writing data back to anything other than the dbo schema fails consistently.

jasonpcasey commented Dec 1, 2017

I'm having the same issues as above. Namely, an attempt to write to a table in a non-default schema on SQL Server when using DBI::SQL("schema.table") results in R attempting to send a CREATE TABLE statement for a table that already exists.

When I use the SQLTable branch to attempt to write to dev.MyTestTable per the example above:
`
tbl <- dbId(con, "MyTestTable", "dev")

system.time({
dbWriteTable(con,
tbl,
as.data.frame(dat),
append = TRUE)
})
`
I get the following:

Error in connection_sql_tables(conn@ptr, catalog_name = if (length(name@catalog) > :
nanodbc/nanodbc.cpp:4274: 24000: [Microsoft][SQL Server Native Client 11.0]Invalid cursor state
Timing stopped at: 0.01 0 0.05

Setting the tbl catalog to blank does nothing to solve the problem.

I love the speed for SELECT statements and have had no trouble reading data from my test table. Writing data back to anything other than the dbo schema fails consistently.

@colearendt

This comment has been minimized.

Show comment
Hide comment
@colearendt

colearendt Dec 21, 2017

Cross-posting from duplicate issue. I think one of the issues encountered above is that dbExistsTable (which is called within dbWriteTable) does not seem to handle DBI::SQL("schema.table") properly. As a result, append or overwrite are not processed appropriately.

Example below to add color:

So extending what I said, I think what you have uncovered @etiennebr is an inconsistency in the way that SQL interpretation happens wrt schemas. Again, something that will hopefully be taken into account and tested for as DBI adds schema support.

Also, I reiterate my suggestion to do the following until that is the case.

  1. Use dbplyr for in_schema() references
  2. Alter the search path (dbExecute(con, "SET search_path = otherschema") in Postgres)
  3. Use database valid names... double quoting can get nasty
  4. Use verbatim SQL with dbGetQuery or dbSendQuery

Quick semantics - dbWriteTable uses dbExistsTable as a test internally when overwrite=TRUE to see whether it should drop the existing table. The problem you are running into comes from dbExistsTable and dbWriteTable understanding schemas differently.

Check out this sequence:

library(odbc)
library(DBI)

con <- dbConnect(odbc::odbc(), ...)

# I got this problematic sequence from the original example above
dbWriteTable(con, DBI::SQL("a"), data.frame(a = 1, b = 2))
dbWriteTable(con, DBI::SQL("public.a"), data.frame(a = 1, b = 2))
#> Error: <SQL> 'CREATE TABLE public.a (
#>   "a" DOUBLE PRECISION,
#>   "b" DOUBLE PRECISION
#> )
#> '
#>   nanodbc/nanodbc.cpp:1587: 42P07: [RStudio][PostgreSQL] (30) Error occurred while trying to execute a query: [SQLState 42P07] ERROR:  relation "a" already exists
#> 
dbWriteTable(con, DBI::SQL("public.a"), data.frame(a = 1, b = 2), overwrite = TRUE)
#> Error: <SQL> 'CREATE TABLE public.a (
#>   "a" DOUBLE PRECISION,
#>   "b" DOUBLE PRECISION
#> )
#> '
#>   nanodbc/nanodbc.cpp:1587: 42P07: [RStudio][PostgreSQL] (30) Error occurred while trying to execute a query: [SQLState 42P07] ERROR:  relation "a" already exists
#> 

# this is why that happens
dbExistsTable(con, DBI::SQL("public.a"))
#> [1] FALSE
dbWriteTable(con, "public.a", data.frame(a = 1, b = 2))
dbExistsTable(con, DBI::SQL("public.a"))
#> [1] TRUE

And our schema looks like...
image

Essentially, when you say overwrite=TRUE, dbExistsTable comes along and says "no worries - this table is not defined yet." So "DROP TABLE" never happens. Then the "Create Table" fails because the table already exists. They're referencing different tables!

colearendt commented Dec 21, 2017

Cross-posting from duplicate issue. I think one of the issues encountered above is that dbExistsTable (which is called within dbWriteTable) does not seem to handle DBI::SQL("schema.table") properly. As a result, append or overwrite are not processed appropriately.

Example below to add color:

So extending what I said, I think what you have uncovered @etiennebr is an inconsistency in the way that SQL interpretation happens wrt schemas. Again, something that will hopefully be taken into account and tested for as DBI adds schema support.

Also, I reiterate my suggestion to do the following until that is the case.

  1. Use dbplyr for in_schema() references
  2. Alter the search path (dbExecute(con, "SET search_path = otherschema") in Postgres)
  3. Use database valid names... double quoting can get nasty
  4. Use verbatim SQL with dbGetQuery or dbSendQuery

Quick semantics - dbWriteTable uses dbExistsTable as a test internally when overwrite=TRUE to see whether it should drop the existing table. The problem you are running into comes from dbExistsTable and dbWriteTable understanding schemas differently.

Check out this sequence:

library(odbc)
library(DBI)

con <- dbConnect(odbc::odbc(), ...)

# I got this problematic sequence from the original example above
dbWriteTable(con, DBI::SQL("a"), data.frame(a = 1, b = 2))
dbWriteTable(con, DBI::SQL("public.a"), data.frame(a = 1, b = 2))
#> Error: <SQL> 'CREATE TABLE public.a (
#>   "a" DOUBLE PRECISION,
#>   "b" DOUBLE PRECISION
#> )
#> '
#>   nanodbc/nanodbc.cpp:1587: 42P07: [RStudio][PostgreSQL] (30) Error occurred while trying to execute a query: [SQLState 42P07] ERROR:  relation "a" already exists
#> 
dbWriteTable(con, DBI::SQL("public.a"), data.frame(a = 1, b = 2), overwrite = TRUE)
#> Error: <SQL> 'CREATE TABLE public.a (
#>   "a" DOUBLE PRECISION,
#>   "b" DOUBLE PRECISION
#> )
#> '
#>   nanodbc/nanodbc.cpp:1587: 42P07: [RStudio][PostgreSQL] (30) Error occurred while trying to execute a query: [SQLState 42P07] ERROR:  relation "a" already exists
#> 

# this is why that happens
dbExistsTable(con, DBI::SQL("public.a"))
#> [1] FALSE
dbWriteTable(con, "public.a", data.frame(a = 1, b = 2))
dbExistsTable(con, DBI::SQL("public.a"))
#> [1] TRUE

And our schema looks like...
image

Essentially, when you say overwrite=TRUE, dbExistsTable comes along and says "no worries - this table is not defined yet." So "DROP TABLE" never happens. Then the "Create Table" fails because the table already exists. They're referencing different tables!

@colearendt

This comment has been minimized.

Show comment
Hide comment
@colearendt

colearendt Dec 27, 2017

Digging into this in the course of another project - it seems that the underlying issue is dbListTables, which does not respect schemas (or search_path, to my chagrin). dbListTables confuses dbExistsTable, which breaks dbWriteTable. Workarounds above were helpful.

image

Looking forward to enhanced schema behavior in DBI!

colearendt commented Dec 27, 2017

Digging into this in the course of another project - it seems that the underlying issue is dbListTables, which does not respect schemas (or search_path, to my chagrin). dbListTables confuses dbExistsTable, which breaks dbWriteTable. Workarounds above were helpful.

image

Looking forward to enhanced schema behavior in DBI!

@diegogarcilazo

This comment has been minimized.

Show comment
Hide comment
@diegogarcilazo

diegogarcilazo Jan 23, 2018

I have the same problem in Postgresql. But switching the driver from RPostgres::Postgres() to RPostgreSQL::PostgreSQL() in connection solve it.

con <- DBI::dbConnect(drv = RPostgreSQL::PostgreSQL(),
dbname = "dbname",
user = "user",
host = "localhost",
password = "password")

DBI::dbWriteTable(con,
c("schema", "table_name"),
value = df ,
row.names = F, overwrite = T, append = T)

diegogarcilazo commented Jan 23, 2018

I have the same problem in Postgresql. But switching the driver from RPostgres::Postgres() to RPostgreSQL::PostgreSQL() in connection solve it.

con <- DBI::dbConnect(drv = RPostgreSQL::PostgreSQL(),
dbname = "dbname",
user = "user",
host = "localhost",
password = "password")

DBI::dbWriteTable(con,
c("schema", "table_name"),
value = df ,
row.names = F, overwrite = T, append = T)

@mmastand

This comment has been minimized.

Show comment
Hide comment
@mmastand

mmastand Mar 9, 2018

It looks like DBI recently merged changes for schema support in their package. The new function is called Id. Will odbc be adding support of the standardized API or merging the SQLTable branch with the previous fix? Below is the odbc behavior of the DBI implementation.

cs <- "driver={SQL Server};
       server={localhost};
       database=testSAM;
       trusted_connection=true;"

con <- DBI::dbConnect(odbc::odbc(), .connection_string = cs)

df <- data.frame(id=1, word_of_day = "happy")
table_id <- DBI::Id(name = "hcai_unit_tests", 
                    schema = "dbo", 
                    catalog = "testSAM")


# Try with Id
res <- DBI::dbWriteTable(conn = con,
                         name = table_id,
                         value = df,
                         append = TRUE)

Errors with:
Error in (function (classes, fdef, mtable) :
unable to find an inherited method for function ‘dbWriteTable’ for
signature ‘"Microsoft SQL Server", "SQL", "missing"’

mmastand commented Mar 9, 2018

It looks like DBI recently merged changes for schema support in their package. The new function is called Id. Will odbc be adding support of the standardized API or merging the SQLTable branch with the previous fix? Below is the odbc behavior of the DBI implementation.

cs <- "driver={SQL Server};
       server={localhost};
       database=testSAM;
       trusted_connection=true;"

con <- DBI::dbConnect(odbc::odbc(), .connection_string = cs)

df <- data.frame(id=1, word_of_day = "happy")
table_id <- DBI::Id(name = "hcai_unit_tests", 
                    schema = "dbo", 
                    catalog = "testSAM")


# Try with Id
res <- DBI::dbWriteTable(conn = con,
                         name = table_id,
                         value = df,
                         append = TRUE)

Errors with:
Error in (function (classes, fdef, mtable) :
unable to find an inherited method for function ‘dbWriteTable’ for
signature ‘"Microsoft SQL Server", "SQL", "missing"’

@jimhester

This comment has been minimized.

Show comment
Hide comment
@jimhester

jimhester Mar 9, 2018

Member

Yes I will update odbc accordingly, likely in the next week or so.

Member

jimhester commented Mar 9, 2018

Yes I will update odbc accordingly, likely in the next week or so.

@mmastand

This comment has been minimized.

Show comment
Hide comment
@mmastand

mmastand Mar 9, 2018

Thank you so much! Looking forward to testing.

mmastand commented Mar 9, 2018

Thank you so much! Looking forward to testing.

@fuserlimon

This comment has been minimized.

Show comment
Hide comment
@fuserlimon

fuserlimon Mar 12, 2018

There is an RSQLS package with the push_data() function. That is able to write to non-default SQL schemas.

fuserlimon commented Mar 12, 2018

There is an RSQLS package with the push_data() function. That is able to write to non-default SQL schemas.

@jimhester

This comment has been minimized.

Show comment
Hide comment
@jimhester

jimhester Mar 20, 2018

Member

As of 536ee60 we now support schemas using DBI::Id()

library(DBI)
library(odbc)
con <- dbConnect(odbc::odbc(), "PostgreSQL")
# table named `mtcars`, schema named `abc`
tbl <- Id(schema = "abc", table = "mtcars")
tbl
#> <Table> schema = abc, table = mtcars
dbWriteTable(con, tbl, mtcars, overwrite = TRUE)
dbExistsTable(con, tbl)
#> [1] TRUE
nrow(dbReadTable(con, tbl))
#> [1] 32
dbWriteTable(con, tbl, mtcars, append = T)
nrow(dbReadTable(con, tbl, row.names = FALSE))
#> [1] 64
dbWriteTable(con, tbl, mtcars, overwrite = T)
nrow(dbReadTable(con, tbl))
#> [1] 32
dbRemoveTable(con, tbl)
dbExistsTable(con, tbl)
#> [1] FALSE

Created on 2018-03-20 by the reprex package (v0.2.0).

Note currently this needs a small patch to DBI to work correctly r-dbi/DBI#233 but that should be fixed in the near future.

Member

jimhester commented Mar 20, 2018

As of 536ee60 we now support schemas using DBI::Id()

library(DBI)
library(odbc)
con <- dbConnect(odbc::odbc(), "PostgreSQL")
# table named `mtcars`, schema named `abc`
tbl <- Id(schema = "abc", table = "mtcars")
tbl
#> <Table> schema = abc, table = mtcars
dbWriteTable(con, tbl, mtcars, overwrite = TRUE)
dbExistsTable(con, tbl)
#> [1] TRUE
nrow(dbReadTable(con, tbl))
#> [1] 32
dbWriteTable(con, tbl, mtcars, append = T)
nrow(dbReadTable(con, tbl, row.names = FALSE))
#> [1] 64
dbWriteTable(con, tbl, mtcars, overwrite = T)
nrow(dbReadTable(con, tbl))
#> [1] 32
dbRemoveTable(con, tbl)
dbExistsTable(con, tbl)
#> [1] FALSE

Created on 2018-03-20 by the reprex package (v0.2.0).

Note currently this needs a small patch to DBI to work correctly r-dbi/DBI#233 but that should be fixed in the near future.

@maxmoro

This comment has been minimized.

Show comment
Hide comment
@maxmoro

maxmoro Mar 29, 2018

I'm still having issue on writing a dataframe to a defined schema...
Here is my code

library(DBI)
library(odbc)
con <- DBI::dbConnect(odbc(),
                      driver = "SQL Server",
                      server = "BOXXXXx",
                      database = "hr",
                      uid = "hrxxxx",
                      pwd = 'xxxx',
                      port = 33xx)


DBI::dbGetQuery(con,'select site_label from hrdw.d_site')

tbl <- DBI::Id(schema = "hrds", table = "testmax2")
t=dbWriteTable(con, name = tbl, value = df, overwrite = T, append=F) 

The output shows that the dbGetQuery works

DBI::dbGetQuery(con,'select site_label from hrdw.d_site')
[1] "0-site0"                             "1 - site" "2 - site, Inc."   

but the dbWriteTable returns

 t=dbWriteTable(con, name = tbl, value = df, overwrite = T, append=F)

Error in (function (classes, fdef, mtable)  : 
  unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"Microsoft SQL Server", "SQL", "missing"’

If I change the writeTable with a non-schema table, it works

 t=DBI::dbWriteTable(con, name = 'testmax3', value = df, overwrite = T, append=F)
t
[1] TRUE

What am I missing?

Thank you
Max

maxmoro commented Mar 29, 2018

I'm still having issue on writing a dataframe to a defined schema...
Here is my code

library(DBI)
library(odbc)
con <- DBI::dbConnect(odbc(),
                      driver = "SQL Server",
                      server = "BOXXXXx",
                      database = "hr",
                      uid = "hrxxxx",
                      pwd = 'xxxx',
                      port = 33xx)


DBI::dbGetQuery(con,'select site_label from hrdw.d_site')

tbl <- DBI::Id(schema = "hrds", table = "testmax2")
t=dbWriteTable(con, name = tbl, value = df, overwrite = T, append=F) 

The output shows that the dbGetQuery works

DBI::dbGetQuery(con,'select site_label from hrdw.d_site')
[1] "0-site0"                             "1 - site" "2 - site, Inc."   

but the dbWriteTable returns

 t=dbWriteTable(con, name = tbl, value = df, overwrite = T, append=F)

Error in (function (classes, fdef, mtable)  : 
  unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"Microsoft SQL Server", "SQL", "missing"’

If I change the writeTable with a non-schema table, it works

 t=DBI::dbWriteTable(con, name = 'testmax3', value = df, overwrite = T, append=F)
t
[1] TRUE

What am I missing?

Thank you
Max

@jimhester

This comment has been minimized.

Show comment
Hide comment
@jimhester

jimhester Mar 29, 2018

Member

You need r-dbi/DBI#233 or the current development version of DBI

Member

jimhester commented Mar 29, 2018

You need r-dbi/DBI#233 or the current development version of DBI

@maxmoro

This comment has been minimized.

Show comment
Hide comment
@maxmoro

maxmoro Mar 29, 2018

Thank you. It is better.. but not there yet...
The Overwrite works, but the append doesn't

library(DBI)
library(odbc)
con <- DBI::dbConnect(odbc::odbc(),
                      driver = "SQL Server",
                      server = "xxx",
                      database = "hr",
                      uid = "xxx",
                      pwd = 'xxx',
                      port = 3890)


DBI::dbGetQuery(con,'select site_label from hrdw.d_site')
df=data.frame(a=c(1,2),b=c('x','y'))
tbl <- DBI::Id(schema = "hrds", table = "testmax2")

DBI::dbWriteTable(con, name = tbl , value = df, overwrite = T, append=F)
DBI::dbGetQuery(con,'select * from hrds.testmax2')

DBI::dbWriteTable(con, name = tbl , value = df, overwrite = F, append=T) 

output

> DBI::dbWriteTable(con, name = tbl , value = df, overwrite = T, append=F)
> DBI::dbGetQuery(con,'select * from hrds.testmax2')
    a    b
1   1    x
2   2    y

> DBI::dbWriteTable(con, name = tbl , value = df, overwrite = F, append=T)
Error: <SQL> 'CREATE TABLE "hrds"."testmax2" (
  "a" FLOAT,
  "b" varchar(255)
)
'
  nanodbc/nanodbc.cpp:1587: 42S01: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'testmax2' in the database. 
> 
> packageVersion('DBI')
[1] ‘0.8.0.9000’
> packageVersion('odbc')
[1] ‘1.1.5’
> 

I guess is trying to recreate the table, even if it exists.

maxmoro commented Mar 29, 2018

Thank you. It is better.. but not there yet...
The Overwrite works, but the append doesn't

library(DBI)
library(odbc)
con <- DBI::dbConnect(odbc::odbc(),
                      driver = "SQL Server",
                      server = "xxx",
                      database = "hr",
                      uid = "xxx",
                      pwd = 'xxx',
                      port = 3890)


DBI::dbGetQuery(con,'select site_label from hrdw.d_site')
df=data.frame(a=c(1,2),b=c('x','y'))
tbl <- DBI::Id(schema = "hrds", table = "testmax2")

DBI::dbWriteTable(con, name = tbl , value = df, overwrite = T, append=F)
DBI::dbGetQuery(con,'select * from hrds.testmax2')

DBI::dbWriteTable(con, name = tbl , value = df, overwrite = F, append=T) 

output

> DBI::dbWriteTable(con, name = tbl , value = df, overwrite = T, append=F)
> DBI::dbGetQuery(con,'select * from hrds.testmax2')
    a    b
1   1    x
2   2    y

> DBI::dbWriteTable(con, name = tbl , value = df, overwrite = F, append=T)
Error: <SQL> 'CREATE TABLE "hrds"."testmax2" (
  "a" FLOAT,
  "b" varchar(255)
)
'
  nanodbc/nanodbc.cpp:1587: 42S01: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'testmax2' in the database. 
> 
> packageVersion('DBI')
[1] ‘0.8.0.9000’
> packageVersion('odbc')
[1] ‘1.1.5’
> 

I guess is trying to recreate the table, even if it exists.

@LearningR1

This comment has been minimized.

Show comment
Hide comment
@LearningR1

LearningR1 Apr 4, 2018

It does not work. I do not think DBI::ID fix the problem.
tbl <-DBI::Id(schema = "IPQ_TEST", table = "R_REDCap")
DBI::dbWriteTable(conn = con,
name=tbl,
value = mydata, overwrite = T,
append=F
)
turns out Error in (function (classes, fdef, mtable) :
unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"Microsoft SQL Server", "SQL", "missing"’

LearningR1 commented Apr 4, 2018

It does not work. I do not think DBI::ID fix the problem.
tbl <-DBI::Id(schema = "IPQ_TEST", table = "R_REDCap")
DBI::dbWriteTable(conn = con,
name=tbl,
value = mydata, overwrite = T,
append=F
)
turns out Error in (function (classes, fdef, mtable) :
unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"Microsoft SQL Server", "SQL", "missing"’

@mmastand

This comment has been minimized.

Show comment
Hide comment
@mmastand

mmastand Apr 6, 2018

I was able to get it working as described.

table_id <- Id(schema = "Sepsis", 
               name = "Predictions")

res <- dbWriteTable(conn = my_con,
                    name = table_id,
                    value = predictions,
                    append = TRUE)

mmastand commented Apr 6, 2018

I was able to get it working as described.

table_id <- Id(schema = "Sepsis", 
               name = "Predictions")

res <- dbWriteTable(conn = my_con,
                    name = table_id,
                    value = predictions,
                    append = TRUE)
@jimhester

This comment has been minimized.

Show comment
Hide comment
@jimhester

jimhester Apr 6, 2018

Member

@LearningR1 that error indicates you need to update to the devel version of DBI

Member

jimhester commented Apr 6, 2018

@LearningR1 that error indicates you need to update to the devel version of DBI

@LearningR1

This comment has been minimized.

Show comment
Hide comment
@LearningR1

LearningR1 Apr 6, 2018

I looked at my DBI version is 0.8, and there is another one called DBI test which is 1.5-2.
Do you mean I need use DBItest? sorry I am new in R. or do you have a link that I can update my DBI package? Many thanks.

LearningR1 commented Apr 6, 2018

I looked at my DBI version is 0.8, and there is another one called DBI test which is 1.5-2.
Do you mean I need use DBItest? sorry I am new in R. or do you have a link that I can update my DBI package? Many thanks.

@jimhester

This comment has been minimized.

Show comment
Hide comment
@jimhester

jimhester Apr 6, 2018

Member

You can use devtools::install_github("r-dbi/DBI") to install the development version. However if you are new to R it might be better to wait until the relevant packages (DBI and odbc) have released updates on CRAN.

Member

jimhester commented Apr 6, 2018

You can use devtools::install_github("r-dbi/DBI") to install the development version. However if you are new to R it might be better to wait until the relevant packages (DBI and odbc) have released updates on CRAN.

@LearningR1

This comment has been minimized.

Show comment
Hide comment
@LearningR1

LearningR1 Apr 6, 2018

@jimhester, I use the command devtools::install_github("r-dbi/DBI" and library(DBI) , they are all successfull. I try to append data to the exsiting table.
but I got error:
Error: 'CREATE TABLE "IPQ_TEST"."R_REDCap" (
"record_id" INT,
"message" varchar(255),
"city" varchar(255),
"something_else" varchar(255),
"will_it_work" INT,
"my_first_instrument_complete" INT
)
'
nanodbc/nanodbc.cpp:1587: 42S01: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'R_REDCap' in the database.

LearningR1 commented Apr 6, 2018

@jimhester, I use the command devtools::install_github("r-dbi/DBI" and library(DBI) , they are all successfull. I try to append data to the exsiting table.
but I got error:
Error: 'CREATE TABLE "IPQ_TEST"."R_REDCap" (
"record_id" INT,
"message" varchar(255),
"city" varchar(255),
"something_else" varchar(255),
"will_it_work" INT,
"my_first_instrument_complete" INT
)
'
nanodbc/nanodbc.cpp:1587: 42S01: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'R_REDCap' in the database.

@LearningR1

This comment has been minimized.

Show comment
Hide comment
@LearningR1

LearningR1 Apr 6, 2018

it only works when with creating a table

LearningR1 commented Apr 6, 2018

it only works when with creating a table

@leungi

This comment has been minimized.

Show comment
Hide comment
@leungi

leungi Apr 19, 2018

@LearningR1 : I read and tried all the solutions from above, and same issue as you, and found a solution!

In SQL Server Studio, do this:

USE <your_databasename>;
ALTER USER <your_user_name> WITH DEFAULT_SCHEMA = <your_schema>;

Then do the usual db_insert_into(con = con, table = table, values = data, append = T).

Note to use actual table name (without schema prefix) in table argument. I tried and it works for dbWriteTable() as well as db_write_table()

I'm using

> [Microsoft][ODBC Driver 13 for SQL Server]
> packageVersion('DBI')
[1] ‘0.8.0.9000’
> packageVersion('odbc')
[1] ‘1.1.5’

leungi commented Apr 19, 2018

@LearningR1 : I read and tried all the solutions from above, and same issue as you, and found a solution!

In SQL Server Studio, do this:

USE <your_databasename>;
ALTER USER <your_user_name> WITH DEFAULT_SCHEMA = <your_schema>;

Then do the usual db_insert_into(con = con, table = table, values = data, append = T).

Note to use actual table name (without schema prefix) in table argument. I tried and it works for dbWriteTable() as well as db_write_table()

I'm using

> [Microsoft][ODBC Driver 13 for SQL Server]
> packageVersion('DBI')
[1] ‘0.8.0.9000’
> packageVersion('odbc')
[1] ‘1.1.5’
@mmastand

This comment has been minimized.

Show comment
Hide comment
@mmastand

mmastand Apr 23, 2018

@leungi Thanks for the work-around! That doesn't really fix the issue though. What if you don't have permission to alter a user or want to write to different schemas?

mmastand commented Apr 23, 2018

@leungi Thanks for the work-around! That doesn't really fix the issue though. What if you don't have permission to alter a user or want to write to different schemas?

@atroiano

This comment has been minimized.

Show comment
Hide comment
@atroiano

atroiano Aug 15, 2018

for sql server, the following works for me, assuming the schema exists

table_id = DBI::Id(schema = 'schema_name', table = 'table_name') with 
dbWriteTable(con, table_id, data)

atroiano commented Aug 15, 2018

for sql server, the following works for me, assuming the schema exists

table_id = DBI::Id(schema = 'schema_name', table = 'table_name') with 
dbWriteTable(con, table_id, data)
@andrew57jm

This comment has been minimized.

Show comment
Hide comment
@andrew57jm

andrew57jm Sep 1, 2018

for sql server I get a very disappointing mixed bag:

table_id = DBI::Id(schema = 'schema_name', table = 'table_name') 
dbWriteTable(con, table_id, data)
dbReadTable(con,table_id)

both work. But

dbExistsTable(con, table_id)
# as well as any custom query like
dbGetQuery(con, "select * from table_id")

all fail. I was looking to replace RODBC but this is not workable currently for non-null schemas.
(All using version ‘1.0.0.9000’ of DBI and ‘1.1.6’ of odbc)

andrew57jm commented Sep 1, 2018

for sql server I get a very disappointing mixed bag:

table_id = DBI::Id(schema = 'schema_name', table = 'table_name') 
dbWriteTable(con, table_id, data)
dbReadTable(con,table_id)

both work. But

dbExistsTable(con, table_id)
# as well as any custom query like
dbGetQuery(con, "select * from table_id")

all fail. I was looking to replace RODBC but this is not workable currently for non-null schemas.
(All using version ‘1.0.0.9000’ of DBI and ‘1.1.6’ of odbc)

@colearendt

This comment has been minimized.

Show comment
Hide comment
@colearendt

colearendt Sep 1, 2018

@andrew57jm Just to be clear on your second failure (dbGetQuery), you are passing verbatim SQL to the database (explicit SQL). Nothing is accessible from the R context, so you should not expect the query to succeed unless table_id exists within the database. For dbGetQuery, you would need to use something like glue or paste to build a query string, or write the SQL verbatim:

dbGetQuery(con, "select * from schema_name.table_name")

colearendt commented Sep 1, 2018

@andrew57jm Just to be clear on your second failure (dbGetQuery), you are passing verbatim SQL to the database (explicit SQL). Nothing is accessible from the R context, so you should not expect the query to succeed unless table_id exists within the database. For dbGetQuery, you would need to use something like glue or paste to build a query string, or write the SQL verbatim:

dbGetQuery(con, "select * from schema_name.table_name")
@andrew57jm

This comment has been minimized.

Show comment
Hide comment
@andrew57jm

andrew57jm Sep 2, 2018

andrew57jm commented Sep 2, 2018

@colearendt

This comment has been minimized.

Show comment
Hide comment
@colearendt

colearendt Sep 17, 2018

@andrew57jm It looks like you may have found some progress in #191 . In the future, a reprex or more information about the errors you are receiving can be helpful.

It also might be worth checking out #197 , where users are having some difficulty with similar behavior.

colearendt commented Sep 17, 2018

@andrew57jm It looks like you may have found some progress in #191 . In the future, a reprex or more information about the errors you are receiving can be helpful.

It also might be worth checking out #197 , where users are having some difficulty with similar behavior.

@DWecke

This comment has been minimized.

Show comment
Hide comment
@DWecke

DWecke Sep 26, 2018

Currently using DBI version 1.0.0 and odbc version 1.1.6

This currently gives an error;

DBI::dbWriteTable(con35raw, 
                  DBI::Id(schema = "dbo", name = "writetabletest"), 
                  test1)
Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in%  : 
  nanodbc/nanodbc.cpp:4266: 24000: [Microsoft][ODBC Driver 11 for

However if I change the sql name to the following it works:

DBI::dbWriteTable(con35raw, 
                  DBI::Id(schema = "dbo", name = "write_tabletest"), 
                  test1)

I'm not able to investigate as to why this is but I'd find it strange if this is the intended behaviour

DWecke commented Sep 26, 2018

Currently using DBI version 1.0.0 and odbc version 1.1.6

This currently gives an error;

DBI::dbWriteTable(con35raw, 
                  DBI::Id(schema = "dbo", name = "writetabletest"), 
                  test1)
Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in%  : 
  nanodbc/nanodbc.cpp:4266: 24000: [Microsoft][ODBC Driver 11 for

However if I change the sql name to the following it works:

DBI::dbWriteTable(con35raw, 
                  DBI::Id(schema = "dbo", name = "write_tabletest"), 
                  test1)

I'm not able to investigate as to why this is but I'd find it strange if this is the intended behaviour

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment