dbWriteTable fails for non-default schemas #197

KimmoMW · 2018-06-20T02:04:26Z

Following up on issue 91 and
#175

, we haven't been able to get this working.

We're just trying to write to a non-default schema using the tableID's as suggested.

Code is:

KimmoMW · 2018-06-20T02:07:30Z

con <- DBI::dbConnect(odbc::odbc(), 
+                       Driver = "SQL Server", 
+                       Server = "ADS-UAT", 
+                       Database = "Sandbox", 
+                       Trusted_Connection = "True")
> table_id <- Id(schema = "Belmont", 
+                name = "IRIS")
> res <- dbWriteTable(conn = con,
+                     name = table_id,
+                     value = iris,
+                     append = TRUE)
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 

Another error on different machine is:

Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in%  : 
  nanodbc/nanodbc.cpp:4266: 08S01: [Microsoft][ODBC SQL Server Driver]Communication link failure

The odbc writes fine to a default schema, but haven't been able to write to a non-default schema. Any help would be most appreciated. Thanks.

jimhester · 2018-06-20T13:40:40Z

You need to use Id(schema = "Belmont", table = "IRIS") not name = .

KimmoMW · 2018-06-20T20:59:01Z

Thanks for that Jim. Have tried this but same error is happening as below:

library("DBI")
con <- DBI::dbConnect(odbc::odbc(), 
                                              Driver = "SQL Server", 
                                              Server = "ADS-UAT", 
                                              Database = "Sandbox", 
                                              Trusted_Connection = "True")
table_id <- Id(schema = "Belmont", 
                                  table = "IRIS")
res <- dbWriteTable(conn = con,
                                           name = table_id,
                                           value = iris,
                                          append = TRUE)
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

colearendt · 2018-07-21T03:14:16Z

Interesting. Is your connection in a good state immediately prior to the dbWriteTable? I.e. does dbGetQuery(con, "SELECT 1;") succeed before dbWriteTable? Just curious if another query may be impacting.

What version of SQL Server are you connecting to? And are you connecting from a Windows machine to get Trusted_Connection? What type of ODBC driver are you using? I am unable to repro from linux using standard connection. However, did get an interesting error trying to append.

library("DBI")
cfg <- config::get(file = "~/wrk/conn.yml")
con <- do.call(DBI::dbConnect, cfg$mssql)
table_id <- Id(schema = "Belmont", 
               table = "IRIS")
res <- dbWriteTable(conn = con,
                    name = table_id,
                    value = iris,
                    append = TRUE, 
                    overwrite = FALSE)

head(dbReadTable(con, table_id))
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> 1          5.1         3.5          1.4         0.2  setosa
#> 2          4.9         3.0          1.4         0.2  setosa
#> 3          4.7         3.2          1.3         0.2  setosa
#> 4          4.6         3.1          1.5         0.2  setosa
#> 5          5.0         3.6          1.4         0.2  setosa
#> 6          5.4         3.9          1.7         0.4  setosa

res <- dbWriteTable(conn = con,
                    name = table_id,
                    value = iris,
                    append = TRUE, 
                    overwrite = FALSE)
#> Error: <SQL> 'CREATE TABLE "Belmont"."IRIS" (
#>   "Sepal.Length" FLOAT,
#>   "Sepal.Width" FLOAT,
#>   "Petal.Length" FLOAT,
#>   "Petal.Width" FLOAT,
#>   "Species" varchar(255)
#> )
#> '
#>   nanodbc/nanodbc.cpp:1587: 42S01: There is already an object named 'IRIS' in the database.,

Created on 2018-07-21 by the reprex package (v0.2.0).

hakki13 · 2018-08-14T19:04:53Z

Hi, I have similar problems. I can't get anything to work.

library(odbc)
library(DBI)
con <- dbConnect(odbc(), 
             Driver = "SQL Server", 
             Server = "LAPTOPSQL", 
             Database = "Test", 
            Trusted_Connection = "True")

#Default works, will write Table1 under dbo
(dbWriteTable(con, "Table1", iris, overwrite = T))
[1] TRUE

#First Try
table_id <- Id(schema = "other",  table = "Table1")
dbWriteTable(conn = con, name = table_id, value = iris, append = FALSE, overwrite = TRUE)

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 

#Other solution
dbWriteTable(con, SQL("other.Table1"), iris, overwrite = T)

Error: Can't unquote other.Table1

other attached packages:
[1] odbc_1.1.6 DBI_1.0.0

What can I do?

KimmoMW · 2018-08-15T03:24:50Z

I ended up just writing a server side process which moves the table from the default schema to desired schema. I call the stored p procedure using the rodbc package. The odbc passage moves the data quickly into the database. The rodbc triggers a fast server side process to move and delete the temporary default schema table.

colearendt · 2018-08-16T13:22:18Z

Yuck! That should definitely not be necessary - have you seen issue #91 ? A handful of other solutions there. I'll try to take a look and see if I can repro today.

What version of SQL Server are you connecting to? And are you connecting from a Windows machine to get Trusted_Connection? What type of ODBC driver are you using?

hakki13 · 2018-08-16T13:33:53Z

My SQL Version is 14.0.1000.169, I'm connecting from windows 10. I don't know what type odbc driver, I have. I'm using odbc package in R. Package version are in my comment above.

krlmlr · 2018-08-22T07:21:22Z

For troubleshooting this, can you please try the following:

odbc:::connection_sql_tables(
  con@ptr,
  catalog_name = "",
  schema_name = "<your schema name>",
  table_name = "<your table name>"
)

The odbc package uses catalog_name = "%" but I'm not sure this is correct; the syntax above worked for me with Microsoft SQL Server.

(This won't fix the problems with creating the table, but we might be able to better understand what's going on.)

hakki13 · 2018-08-22T14:19:17Z

Ok, Here is what I have. I created by hand "Table1" to Schema "other" in "Test" Catalog.

> odbc:::connection_sql_tables(
+   con@ptr,
+   catalog_name = "Test",
+   schema_name = "other",
+   table_name = "Table1"
+ )
  table_catalog table_name table_type table_schema table_remarks
1          Test     Table1      TABLE        other

So looks like it will find it, but If I leave catalog_name empty as in your example:

> odbc:::connection_sql_tables(
+   con@ptr,
+   catalog_name = "",
+   schema_name = "other",
+   table_name = "Table1"
+ )
[1] table_catalog table_name    table_type    table_schema  table_remarks
<0 rows> (or 0-length row.names)

And "%" gives similar cursor state error, what I have in creating table.

> odbc:::connection_sql_tables(
+   con@ptr,
+   catalog_name = "%",
+   schema_name = "other",
+   table_name = "Table1"
+ )
Error in odbc:::connection_sql_tables(con@ptr, catalog_name = "%", schema_name = "other",  : 
  nanodbc/nanodbc.cpp:2525: 24000: [Microsoft][ODBC SQL Server Driver]Invalid cursor state

krlmlr · 2018-08-26T22:38:01Z

Thanks. What about

odbc:::connection_sql_tables(
  con@ptr,
  catalog_name = NULL,
  schema_name = "<your schema name>",
  table_name = "<your table name>"
)

?

hakki13 · 2018-08-30T08:29:42Z

Sorry for late response, here is what I get.

> odbc:::connection_sql_tables(
+   con@ptr,
+   catalog_name = NULL,
+   schema_name = "other",
+   table_name = "Table_1"
+ )
  table_catalog table_name table_type table_schema table_remarks
1          Test    Table_1      TABLE        other

SO seems to work with NULL

Patrick01 · 2018-09-10T07:39:27Z

I maybe had the same problem.

I had the change the table name to inclucde a "_" for the database table. This seemed to be a condition by the database.

raffscallion · 2018-09-11T23:42:06Z

Piping in to say I have the exact same situation as @hakki13. I am connecting to SQL Server 13.0.4223.10 from Windows 10 with odbc 1.1.6. I receive the same invalid cursor state error when trying to write to the non-default schema. Below is the result of the connection_sql_tables(). Note the potential extra wrinkle of the dot in the table_catalog.

odbc:::connection_sql_tables(con@ptr, 
catalog_name = NULL, 
schema_name = "analysis", 
table_name = "FieldBlankStats")

  table_catalog      table_name table_type table_schema table_remarks
1   Improve_2.1 FieldBlankStats      TABLE     analysis

raffscallion · 2018-09-20T23:39:23Z

Hoping to provide a bit more to help with debugging.

# Cannot append to table in non-default schema - why?
library(DBI)
con <- dbConnect(odbc::odbc(), Driver = "SQL Server", server = "CL-SQL-APPDEV",
                      database = "CIA_1_0", port = 1433L)

table <- Id(catalog = "CIA_1_0", schema = "import", table = "test")

df <- data.frame(a = c(1, 3),
                 b = c(4, 5))
post <- dbWriteTable(con, name = table, value = df, append = TRUE)
#> Error: Can't unquote "CIA_1_0"."import"."test"

# It appears that something is not working with this round trip. Should this succeed?
q <- dbQuoteIdentifier(con, table)
u <- dbUnquoteIdentifier(con, q)
#> Error: Can't unquote "CIA_1_0"."import"."test"

DBI::dbDisconnect(con)

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

Session info

devtools::session_info()
#> Session info -------------------------------------------------------------
#>  setting  value                       
#>  version  R version 3.5.1 (2018-07-02)
#>  system   x86_64, mingw32             
#>  ui       RTerm                       
#>  language (EN)                        
#>  collate  English_United States.1252  
#>  tz       America/Los_Angeles         
#>  date     2018-09-20
#> Packages -----------------------------------------------------------------
#>  package   * version    date       source                      
#>  backports   1.1.2      2017-12-13 CRAN (R 3.5.0)              
#>  base      * 3.5.1      2018-07-02 local                       
#>  bit         1.1-14     2018-05-29 CRAN (R 3.5.0)              
#>  bit64       0.9-7      2017-05-08 CRAN (R 3.5.0)              
#>  blob        1.1.1      2018-03-25 CRAN (R 3.5.1)              
#>  compiler    3.5.1      2018-07-02 local                       
#>  datasets  * 3.5.1      2018-07-02 local                       
#>  DBI       * 1.0.0.9000 2018-09-05 Github (r-dbi/DBI@dbc32a0)  
#>  devtools    1.13.6     2018-06-27 CRAN (R 3.5.1)              
#>  digest      0.6.15     2018-01-28 CRAN (R 3.5.1)              
#>  evaluate    0.10.1     2017-06-24 CRAN (R 3.5.1)              
#>  graphics  * 3.5.1      2018-07-02 local                       
#>  grDevices * 3.5.1      2018-07-02 local                       
#>  hms         0.4.2      2018-03-10 CRAN (R 3.5.1)              
#>  htmltools   0.3.6      2017-04-28 CRAN (R 3.5.1)              
#>  knitr       1.20       2018-02-20 CRAN (R 3.5.1)              
#>  magrittr    1.5        2014-11-22 CRAN (R 3.5.1)              
#>  memoise     1.1.0      2017-04-21 CRAN (R 3.5.1)              
#>  methods   * 3.5.1      2018-07-02 local                       
#>  odbc        1.1.6      2018-06-09 CRAN (R 3.5.1)              
#>  pkgconfig   2.0.1      2017-03-21 CRAN (R 3.5.1)              
#>  Rcpp        0.12.18    2018-07-23 CRAN (R 3.5.1)              
#>  rlang       0.2.2.9000 2018-08-20 Github (r-lib/rlang@2cf70c8)
#>  rmarkdown   1.10       2018-06-11 CRAN (R 3.5.1)              
#>  rprojroot   1.3-2      2018-01-03 CRAN (R 3.5.1)              
#>  stats     * 3.5.1      2018-07-02 local                       
#>  stringi     1.1.7      2018-03-12 CRAN (R 3.5.0)              
#>  stringr     1.3.1      2018-05-10 CRAN (R 3.5.1)              
#>  tools       3.5.1      2018-07-02 local                       
#>  utils     * 3.5.1      2018-07-02 local                       
#>  withr       2.1.2      2018-03-15 CRAN (R 3.5.1)              
#>  yaml        2.1.19     2018-05-01 CRAN (R 3.5.1)

raffscallion · 2018-09-25T16:32:33Z

Here's some more info. I'm hoping this will elicit a response from the experts.

library(DBI)

# This works
id1 <- Id(schema = "my_schema", table = "my_table")
unquoted <- dbUnquoteIdentifier(ANSI(), id1)

# This works
id2 <- Id(catalog = "my_db", schema = "my_schema", table = "my_table")
unquoted2 <- dbUnquoteIdentifier(ANSI(), id2)

### Round-tripping

# This works
q1 <- dbQuoteIdentifier(ANSI(), id1)
rt1 <- dbUnquoteIdentifier(ANSI(), q1)

# This fails
q2 <- dbQuoteIdentifier(ANSI(), id2)
rt2 <- dbUnquoteIdentifier(ANSI(), q2)
#> Error: Can't unquote "my_db"."my_schema"."my_table"

Created on 2018-09-25 by the reprex package (v0.2.0).

The offending code appears to be these lines in quote.R:

if (is(x, "SQL")) {
rx <- '^(?:(?:|"((?:[^"]|"")+)"[.])(?:|"((?:[^"]|"")*)")|([^". ]+))$'
bad <- grep(rx, x, invert = TRUE)
if (length(bad) > 0) {
stop("Can't unquote ", x[bad[[1]]], call. = FALSE)
}

If your Id contains a catalog, then the SQL object created by dbQuoteIdentifier cannot pass the regex test above. I'm not competent enough with regex to know how to fix it, but I'm hoping it's straightforward.

dfalbel · 2018-09-25T20:41:34Z

I am experiencing the same issue described here. I am using:

Microsoft SQL Server Version: 12.00.5207

And here is my session info:

> sessionInfo()
R version 3.5.0 (2018-04-23)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows Server >= 2012 x64 (build 9200)

Matrix products: default

locale:
[1] LC_COLLATE=Portuguese_Brazil.1252  LC_CTYPE=Portuguese_Brazil.1252    LC_MONETARY=Portuguese_Brazil.1252
[4] LC_NUMERIC=C                       LC_TIME=Portuguese_Brazil.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] bindrcpp_0.2.2 furrr_0.1.0    future_1.9.0   magrittr_1.5   odbc_1.1.6    

loaded via a namespace (and not attached):
 [1] progress_1.2.0    zoo_1.8-2         tidyselect_0.2.4  urca_1.3-0        purrr_0.2.5       listenv_0.7.0    
 [7] lattice_0.20-35   colorspace_1.3-2  blob_1.1.1        rlang_0.2.0       pillar_1.2.1      glue_1.2.0       
[13] withr_2.1.2       DBI_1.0.0         bit64_0.9-7       forecast_8.3      TTR_0.23-3        bindr_0.1.1      
[19] plyr_1.8.4        quantmod_0.4-13   timeDate_3043.102 munsell_0.4.3     gtable_0.2.0      devtools_1.13.6  
[25] codetools_0.2-15  memoise_1.1.0     tseries_0.10-44   lmtest_0.9-36     parallel_3.5.0    curl_3.2         
[31] xts_0.10-2        Rcpp_0.12.18      scales_0.5.0      bit_1.1-14        fracdiff_1.4-2    ggplot2_2.2.1    
[37] hms_0.4.2         digest_0.6.15     dplyr_0.7.6       grid_3.5.0        quadprog_1.5-5    tools_3.5.0      
[43] lazyeval_0.2.1    tibble_1.4.2      crayon_1.3.4      tidyr_0.8.1       pkgconfig_2.0.1   data.table_1.11.4
[49] prettyunits_1.0.2 assertthat_0.2.0  httr_1.3.1        rstudioapi_0.7    R6_2.2.2          globals_0.12.1   
[55] uroot_2.0-9       nlme_3.1-137      nnet_7.3-12       git2r_0.23.0      compiler_3.5.0

Running:

sql_previsao <- DBI::Id(schema = "StageR", table = "Previsao")
DBI::dbWriteTable(con, sql_previsao, value = mtcars)

Error in odbc:::connection_sql_tables(con@ptr, catalog_name = "%", schema_name = "", :
nanodbc/nanodbc.cpp:2525: 24000: [Microsoft][ODBC SQL Server Driver]Invalid cursor state

Do you suggest any quick-fix?

mkuehn10 · 2018-10-01T16:10:18Z

I have been having this issue as well. I can get it to work by using an underscore in my table name. I have no idea why it works, but it does.

# This works
dbWriteTable(con,
             Id(schema = "my_schema", table = "test_"),
             my_data,
             overwrite = TRUE)

# This does not
dbWriteTable(con,
             Id(schema = "my_schema", table = "test"),
             my_data,
             overwrite = TRUE)
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

fab64443 · 2018-12-14T11:34:36Z

Hi
I'm using odbc 1.1.6 and DBI 1.0.0 and the bug still exists.
But if I use odbc SQLTable branch (1.1.1.9000) it's working.
Is the fix planned to be merged in the CRAN version soon ?

fab64443 · 2018-12-14T12:39:01Z

I tried the same code, appending new rows in the no-default schema of SQL Server Database.

With the packages in the last version (CRAN) :

#>  install.packages('odbc',dependencies=F)

library(DBI)
library(odbc)

packageVersion('odbc')
packageVersion('DBI')

dbhandle <- dbConnect(odbc::odbc(), dsn = 'MISDAPRD', uid='MIS_DA_SSIS', pwd=unserialize(uncipherMISDA))
dbGetQuery(dbhandle,'select count(*) N from cci.testtable')

rawdata <- dbGetQuery(dbhandle,'select top(3) * from cci.testtable')
rawdata

dbWriteTable(dbhandle,value=rawdata,name=DBI::Id(schema='cci',table='testtable'),append=TRUE)

dbWriteTable(dbhandle,value=rawdata,name=DBI::Id(catalog='MISDA',schema='cci',table='testtable'),append=TRUE)

odbc::dbId(dbhandle,'testtable','cci','MISDA')

dbDisconnect(dbhandle)

Without catalog, something goes wrong in the dbWriteTable code.
With catalog, the function is not able to unquote the full DB name (catalog.schema.table) .

[1] '1.1.6'
[1] '1.0.0'

N
10


datefrom	dateto	perf
2016-09-01	2017-09-30	-0.5533046
2016-09-01	2017-09-30	2.4489095
2016-09-01	2017-09-30	-0.5399339


Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in% : nanodbc/nanodbc.cpp:4266: 24000: [Microsoft][ODBC Driver 13 for                                
Traceback:

1. dbWriteTable(dbhandle, value = rawdata, name = DBI::Id(schema = "cci", 
 .    table = "testtable"), append = TRUE)
2. dbWriteTable(dbhandle, value = rawdata, name = DBI::Id(schema = "cci", 
 .    table = "testtable"), append = TRUE)
3. dbWriteTable(conn, dbQuoteIdentifier(conn, name), value, ...)
4. dbWriteTable(conn, dbQuoteIdentifier(conn, name), value, ...)
5. .local(conn, name, value, ...)
6. dbExistsTable(conn, name)
7. dbExistsTable(conn, name)
8. dbExistsTable(conn, dbUnquoteIdentifier(conn, name)[[1]], ...)
9. dbExistsTable(conn, dbUnquoteIdentifier(conn, name)[[1]], ...)
10. name@name[["table"]] %in% connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in% 
  .    names(name@name)) name@name[["catalog"]] else "%", schema_name = if ("schema" %in% 
  .    names(name@name)) name@name[["schema"]] else "%", table_name = if ("table" %in% 
  .    names(name@name)) name@name[["table"]] else "%")
11. connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in% 
  .    names(name@name)) name@name[["catalog"]] else "%", schema_name = if ("schema" %in% 
  .    names(name@name)) name@name[["schema"]] else "%", table_name = if ("table" %in% 
  .    names(name@name)) name@name[["table"]] else "%")
  
  
  
Error: Can't unquote "MISDA"."cci"."testtable"
Traceback:

1. dbWriteTable(dbhandle, value = rawdata, name = DBI::Id(catalog = "MISDA", 
 .    schema = "cci", table = "testtable"), append = TRUE)
2. dbWriteTable(dbhandle, value = rawdata, name = DBI::Id(catalog = "MISDA", 
 .    schema = "cci", table = "testtable"), append = TRUE)
3. dbWriteTable(conn, dbQuoteIdentifier(conn, name), value, ...)
4. dbWriteTable(conn, dbQuoteIdentifier(conn, name), value, ...)
5. .local(conn, name, value, ...)
6. dbExistsTable(conn, name)
7. dbExistsTable(conn, name)
8. dbExistsTable(conn, dbUnquoteIdentifier(conn, name)[[1]], ...)
9. dbUnquoteIdentifier(conn, name)
10. dbUnquoteIdentifier(conn, name)
11. stop("Can't unquote ", x[bad[[1]]], call. = FALSE)


Error: 'dbId' is not an exported object from 'namespace:odbc'
Traceback:

1. odbc::dbId
2. getExportedValue(pkg, name)
3. stop(gettextf("'%s' is not an exported object from 'namespace:%s'", 
 .    name, getNamespaceName(ns)), call. = FALSE, domain = NA)

I installed the SQLTable branch from odbc and ran the same code.
When using DBI::Id, the append option is not used, and when using odbc:dbId it is working !

#> devtools::install_github("rstats-db/odbc@SQLTable")

library(DBI)
library(odbc)

packageVersion('odbc')
packageVersion('DBI')

dbhandle <- dbConnect(odbc::odbc(), dsn = 'MISDAPRD', uid='MIS_DA_SSIS', pwd=unserialize(uncipherMISDA))
dbGetQuery(dbhandle,'select count(*) N from cci.testtable')

rawdata <- dbGetQuery(dbhandle,'select top(3) * from cci.testtable')
rawdata

dbWriteTable(dbhandle,value=rawdata,name=DBI::Id(catalog='MISDA',schema='cci',table='testtable'),append=TRUE)

dbWriteTable(dbhandle,value=rawdata,name=odbc::dbId(dbhandle,'testtable','cci','MIS_DA'),append=TRUE)

dbGetQuery(dbhandle,'select count(*) N from cci.testtable')

dbDisconnect(dbhandle)


[1] '1.1.1.9000'
[1] '1.0.0'

N
10


datefrom	dateto	perf
2016-09-01	2017-09-30	-0.5533046
2016-09-01	2017-09-30	2.4489095
2016-09-01	2017-09-30	-0.5399339


Error: <SQL> 'CREATE TABLE "MISDA"."cci"."testtable" (
  "datefrom" DATE,
  "dateto" DATE,
  "perf" FLOAT
)
'
  nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC Driver 13 for SQL Server]                                            
Traceback:

1. dbWriteTable(dbhandle, value = rawdata, name = DBI::Id(catalog = "MISDA", 
 .    schema = "cci", table = "testtable"), append = TRUE)
2. dbWriteTable(dbhandle, value = rawdata, name = DBI::Id(catalog = "MISDA", 
 .    schema = "cci", table = "testtable"), append = TRUE)
3. dbWriteTable(conn, dbQuoteIdentifier(conn, name), value, ...)
4. dbWriteTable(conn, dbQuoteIdentifier(conn, name), value, ...)
5. .local(conn, name, value, ...)
6. dbExecute(conn, sql)
7. dbExecute(conn, sql)
8. dbSendStatement(conn, statement, ...)
9. dbSendStatement(conn, statement, ...)
10. OdbcResult(connection = conn, statement = statement)
11. new_result(connection@ptr, statement)

*** No output for the working call of dbWriteTable with odbc::dbID *** 

N
13

nima1i · 2019-01-08T16:47:57Z

Hi, Is there any chance that we see this issue resolved in odbc??
Are there any alternatives??
I tried to use the package @fab64443 mentioned with this code:

devtools::install_github("rstats-db/odbc@SQLTable",dependencies=F)
library(odbc)
con <- dbConnect(odbc(), Driver="SQL Server", Server="ServerName", Database="Result", UID="---------------------------", PWD="--------------------------" )
nycf=nycflights13::flights[1:2000,]
dbWriteTable(con,value=nycf,name=odbc::dbId(con,"flights","DIT","ServerName"),append=TRUE)

but I got this error:
Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"Microsoft SQL Server", "dbId", "tbl_df"’

jimhester · 2019-11-14T16:31:24Z

I reproduced the issue writing to schemas and appending, it ultimately came down to driver interpretations of the % wildcard vs NULL. Switching dbExistsTable() to use NULL in all cases seems to address the issue.

Hopefully it will not cause other issues in other databases.

eduardocorrearaujo · 2022-02-03T20:13:23Z

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

danielwo mentioned this issue Sep 5, 2018

DBI::dbExistsTable #191

Closed

colearendt mentioned this issue Sep 17, 2018

dbWriteTable fails to write to non-default schema #91

Closed

raffscallion mentioned this issue Oct 10, 2018

Support Id with catalog in dbUnquoteIdentifier r-dbi/DBI#266

Merged

krlmlr added the mssql Microsoft SQL Server label Jan 8, 2019

jrisi256 mentioned this issue Mar 6, 2019

Issue Writing to Non-Default Schema when Table Does Not Have "_" In Name #260

Closed

jimhester closed this as completed in 486655a Nov 14, 2019

anthonysena mentioned this issue Dec 1, 2021

insertTable() on existing, permanent table OHDSI/DatabaseConnector#163

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

dbWriteTable fails for non-default schemas #197

dbWriteTable fails for non-default schemas #197

KimmoMW commented Jun 20, 2018

KimmoMW commented Jun 20, 2018 •

edited by jimhester

Loading

jimhester commented Jun 20, 2018

KimmoMW commented Jun 20, 2018 •

edited by jimhester

Loading

colearendt commented Jul 21, 2018

hakki13 commented Aug 14, 2018

KimmoMW commented Aug 15, 2018 via email

colearendt commented Aug 16, 2018

hakki13 commented Aug 16, 2018 •

edited

Loading

krlmlr commented Aug 22, 2018 •

edited

Loading

hakki13 commented Aug 22, 2018 •

edited

Loading

krlmlr commented Aug 26, 2018

hakki13 commented Aug 30, 2018 •

edited

Loading

Patrick01 commented Sep 10, 2018

raffscallion commented Sep 11, 2018

raffscallion commented Sep 20, 2018

raffscallion commented Sep 25, 2018

dfalbel commented Sep 25, 2018 •

edited

Loading

mkuehn10 commented Oct 1, 2018 •

edited

Loading

fab64443 commented Dec 14, 2018 •

edited

Loading

fab64443 commented Dec 14, 2018

nima1i commented Jan 8, 2019 •

edited

Loading

jimhester commented Nov 14, 2019

eduardocorrearaujo commented Feb 3, 2022

dbWriteTable fails for non-default schemas #197

dbWriteTable fails for non-default schemas #197

Comments

KimmoMW commented Jun 20, 2018

KimmoMW commented Jun 20, 2018 • edited by jimhester Loading

jimhester commented Jun 20, 2018

KimmoMW commented Jun 20, 2018 • edited by jimhester Loading

colearendt commented Jul 21, 2018

hakki13 commented Aug 14, 2018

KimmoMW commented Aug 15, 2018 via email

colearendt commented Aug 16, 2018

hakki13 commented Aug 16, 2018 • edited Loading

krlmlr commented Aug 22, 2018 • edited Loading

hakki13 commented Aug 22, 2018 • edited Loading

krlmlr commented Aug 26, 2018

hakki13 commented Aug 30, 2018 • edited Loading

Patrick01 commented Sep 10, 2018

raffscallion commented Sep 11, 2018

raffscallion commented Sep 20, 2018

raffscallion commented Sep 25, 2018

dfalbel commented Sep 25, 2018 • edited Loading

mkuehn10 commented Oct 1, 2018 • edited Loading

fab64443 commented Dec 14, 2018 • edited Loading

fab64443 commented Dec 14, 2018

nima1i commented Jan 8, 2019 • edited Loading

jimhester commented Nov 14, 2019

eduardocorrearaujo commented Feb 3, 2022

KimmoMW commented Jun 20, 2018 •

edited by jimhester

Loading

KimmoMW commented Jun 20, 2018 •

edited by jimhester

Loading

hakki13 commented Aug 16, 2018 •

edited

Loading

krlmlr commented Aug 22, 2018 •

edited

Loading

hakki13 commented Aug 22, 2018 •

edited

Loading

hakki13 commented Aug 30, 2018 •

edited

Loading

dfalbel commented Sep 25, 2018 •

edited

Loading

mkuehn10 commented Oct 1, 2018 •

edited

Loading

fab64443 commented Dec 14, 2018 •

edited

Loading

nima1i commented Jan 8, 2019 •

edited

Loading