dbWriteTable fails to write to non-default schema

[EthanTaft]

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]

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]

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]

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. 

[jmi5]

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

[aschmu]

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

[vicm159]

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]

@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]

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]

@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]

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

[EthanTaft]

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]

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]

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

[jimhester]

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]

@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]

@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]

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.

[DeppLearning]

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]

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]

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]

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...

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]

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.

Looking forward to enhanced schema behavior in DBI!

[diegogarcilazo]

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]

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]

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

[LearningR1]

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]

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]

@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]

it only works when with creating a table

[leungi]

@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]

@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]

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]

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]

@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]

Schema.table was what I tried first because that’s how it’s done in RODBC. It doesn’t work in odbc with a non-default schema. Using table_id was flailing. Everything works with default schema, unfortunately that’s not how my tables are organized.

…

Sent from my iPhone

On Sep 1, 2018, at 3:50 PM, Cole Arendt ***@***.***> wrote: @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") — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

[colearendt]

@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]

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

[drjahu]

I still have the problem:
DBI version 1.0.0
MS SQL Server

> DBI::dbWriteTable(con, DBI::Id(schema = "tgxphenomics", name = "iris"), iris)
Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in%  : 
  nanodbc/nanodbc.cpp:2525: 24000: [Microsoft][ODBC SQL Server Driver]Invalid cursor state

How can I help to debug this?

$`dbname`
[1] "TDB01"

$dbms.name
[1] "Microsoft SQL Server"

$db.version
[1] "14.00.3076"

$username
[1] "dbo"

$host
[1] ""

$port
[1] ""

$sourcename
[1] "TDB64"

$servername
[1] "WINSQL"

$drivername
[1] "SQLSRV32.DLL"

$odbc.version
[1] "03.80.0000"

$driver.version
[1] "10.00.17763"

$odbcdriver.version
[1] "03.52"

$supports.transactions
[1] TRUE

attr(,"class")
[1] "Microsoft SQL Server" "driver_info"          "list"    ```

[GaneshHegde25]

I am also facing problem with dbWriteTable with oracle database. I couldn't append existing table in the same schema as well as other schema. Below workaround also didn't work.
dbWriteTable(connection, c(db_schema,table_name), value = df, append = TRUE)
Any update on the fix?

[german4589]

Hi @GaneshHegde25 I'm having exactly the same problem with an Oracle Database. Did you solve the problem or found a workarround for this?. I've been trying for a week now to append obsertations and haven't had any advancements on this. Thank you in advance!

[neros]

I tried a few of the solutions above to no avail, below a workaround for others, which worked on a PostGreSQL database, RPostgres library. It sends an SQL command to my preferred schema search_path within a transaction block. I'm sure Oracle would have similar native SQL to accomplish the same:

dbExecute(conn,"start transaction;")
dbExecute(conn,"SET search_path TO myschema;")
dbWriteTable(conn=conn,name="_temp_table",data)
dbExecute(conn,"commit;")

[GaneshHegde25]

Hi @GaneshHegde25 I'm having exactly the same problem with an Oracle Database. Did you solve the problem or found a workarround for this?. I've been trying for a week now to append obsertations and haven't had any advancements on this. Thank you in advance!

Workaround what I found is, creating temporary table and from there appending the actual table with SQL insert command.
After drop the temporary table.

[ashleylester]

I'm also having this problem on SQL Server.
Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in% : nanodbc/nanodbc.cpp:2525: 24000: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
This error disappears when I drop the table and create another one with an underscore in the table name.

[kchamarty]

I am getting the same issue even when I try to create a new table.
Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in% :
nanodbc/nanodbc.cpp:2525: 24000: [Microsoft][ODBC SQL Server Driver]Invalid cursor state

[bdforbes]

I just updated odbc to 1.2.0 and this issue appears to be fixed.

library(dplyr)
library(dbplyr)
library(DBI)
library(MASS)

con <- dbConnect(odbc::odbc(), "LocalSQLExpress")

df <- as_tibble(Boston)

dbWriteTable(con, Id(schema = "IDL", table = "HousePrices"), df, overwrite = TRUE)

[aryoda]

I can also also confirm that the new CRAN version 1.2.1 (released Dec. 05, 2019) is fixing the issue finally on Microsoft SQL Server 2016 (and it seems to be unrelated to the used ODBC driver - I have tested it with the drivers SQL server and SQL server native client 11.0).

Thanks Jim :-)

[pinkumbrella14]

@jimhester I just found this thread and I think this addresses the issue I am seeing. However, after attempting to ude Id(), I am still not able to get the dbWriteTable() function to work correctly. It seems like this issue has not yet been resolved for Oracle databases...

I am trying the following:
'''r
asss_re_connect <- dbConnect(odbc::odbc(), "ASSS", uid = "xx",
pwd = 'xx', believeNRows=FALSE)

dbWriteTable(conn = asss_re_connect,
name = Id(schema = "ASSS", table = "ANN_NOL_FORECAST_TAB"),
value = data_table_rbindlist_,
row.names = FALSE,
append = TRUE)

And am seeing the following error:
"Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in%  : 
  nanodbc/nanodbc.cpp:4266: HYC00: [Oracle][ODBC]Optional feature not implemented."

@GaneshHegde25 would you be able to post an example of your code that you used for your workaround? I am not sure that I'll be able to create a temp table, since I do not have create table priviledges on this database.... A working example would be really helpful!

[leungi]

@pinkumbrella14: is ASSS your dsn or database name?

Assuming it refers to database name, what I typically do is:

con <- DBI::dbConnect(odbc::odbc(), dsn = <my_database_IP>, database = "ASSS", port = <my_port>)

And when connecting to table, the schema piece may be omitted:

DBI::dbWriteTable(conn = con, name = "ANN_NOL_FORECAST_TAB", value = data_table_rbindlist_, ...)

[kasztan1]

Hi guys. I would really appreciate if you could help me with the following issue:

I've already tried what you suggest but there is one thing that stops me from success. On my Oracle data warehouse I also have tablespaces. My connection to the database is OK but dbWriteTable see only datatables in the default tablespace (it's not a schema). We have two tablespaces - USERS1 (default) and USERS2. I want to append data to the table on the non-default tablespace USERS2. Do you think it requires some changes in connection step or am I right and it's just dbWriteTable case? My connection doesn't point any tablespace, just the database (and it works).
A penny for your thoughts :)

[eduardocorrearaujo]

Im my case, use: dbWriteTable(con, c(schema, table_name), data) worked.

[aryoda]

Im my case, use: dbWriteTable(con, c(schema, table_name), data) worked.

On which DB system and using which odbc package version?