DBI::dbWriteTable writes only column names

Hi all!

I'm very confused as to why I am getting an error with DBI::dbWriteTable. The column names are created but the values are not copied. Thank you!

library(dbplyr)
library(DBI)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:dbplyr':
#> 
#>     ident, sql
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

con = DBI::dbConnect (
  odbc::odbc(),
  'THEDATABASE',
  UID = Sys.getenv("MYID"),
  PWD = Sys.getenv("MYPASSSWORD")
)

DBI::dbExistsTable(con,
                   Id(schema = Sys.getenv("MYID"),
                            table = "TMP_TBL"))
#> [1] FALSE
DBI::dbWriteTable(con,
                  name = Id(schema = Sys.getenv("MYID"),
                            table = "TMP_TBL"),
                  value = mtcars)
#> Error in eval(expr, envir, enclos): nanodbc/nanodbc.cpp:[XXXX]: 00000: [RStudio][OracleOCI] (3000) Oracle Caller Interface: ORA-00942: table or view does not exist
#> 

DBI::dbExistsTable(con,
                   name  = Id(schema = Sys.getenv("MYID"),
                              table = "TMP_TBL"))
#> [1] TRUE

tmp_tbl <- dplyr::tbl(con,
                       in_schema(Sys.getenv("MYID"), "TMP_TBL"))
tmp_tbl %>% colnames()
#>  [1] "row_names" "mpg"       "cyl"       "disp"      "hp"        "drat"     
#>  [7] "wt"        "qsec"      "vs"        "am"        "gear"      "carb"

tmp_tbl %>% collect()
#> # A tibble: 0 × 12
#> # ℹ 12 variables: row_names <chr>, mpg <dbl>, cyl <dbl>, disp <dbl>, hp <dbl>,
#> #   drat <dbl>, wt <dbl>, qsec <dbl>, vs <dbl>, am <dbl>, gear <dbl>,
#> #   carb <dbl>

mtcars %>% head(n = 2)
#>               mpg cyl disp  hp drat    wt  qsec vs am gear carb
#> Mazda RX4      21   6  160 110  3.9 2.620 16.46  0  1    4    4
#> Mazda RX4 Wag  21   6  160 110  3.9 2.875 17.02  0  1    4    4

sessioninfo::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value
#>  version  R version 4.3.0 (2023-04-21)
#>  os       Red Hat Enterprise Linux 8.9 (Ootpa)
#>  system   x86_64, linux-gnu
#>  ui       unknown
#>  language (EN)
#>  collate  en_US.UTF-8
#>  ctype    en_US.UTF-8
#>  tz       America/Los_Angeles
#>  date     2024-05-16
#>  pandoc   3.1.1 @ /usr/lib/rstudio-server/bin/quarto/bin/tools/ (via rmarkdown)
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  package     * version date (UTC) lib source
#>  bit           4.0.5   2022-11-15 [1] RSPM (R 4.3.0)
#>  bit64         4.0.5   2020-08-30 [1] RSPM (R 4.3.0)
#>  blob          1.2.4   2023-03-17 [1] RSPM (R 4.3.0)
#>  cli           3.6.1   2023-03-23 [2] RSPM (R 4.3.0)
#>  DBI         * 1.2.2   2024-02-16 [1] RSPM (R 4.3.0)
#>  dbplyr      * 2.5.0   2024-03-19 [1] RSPM (R 4.3.0)
#>  digest        0.6.33  2023-07-07 [1] RSPM (R 4.3.0)
#>  dplyr       * 1.1.4   2023-11-17 [1] RSPM (R 4.3.0)
#>  evaluate      0.22    2023-09-29 [1] RSPM (R 4.3.0)
#>  fansi         1.0.4   2023-01-22 [2] RSPM (R 4.3.0)
#>  fastmap       1.1.1   2023-02-24 [2] RSPM (R 4.3.0)
#>  fs            1.6.3   2023-07-20 [1] RSPM (R 4.3.0)
#>  generics      0.1.3   2022-07-05 [1] RSPM (R 4.3.0)
#>  glue          1.6.2   2022-02-24 [1] RSPM (R 4.3.0)
#>  hms           1.1.3   2023-03-21 [1] RSPM (R 4.3.0)
#>  htmltools     0.5.6.1 2023-10-06 [1] RSPM (R 4.3.0)
#>  knitr         1.44    2023-09-11 [1] RSPM (R 4.3.0)
#>  lifecycle     1.0.3   2022-10-07 [2] RSPM (R 4.3.0)
#>  magrittr      2.0.3   2022-03-30 [2] RSPM (R 4.3.0)
#>  odbc          1.3.5   2023-06-29 [1] RSPM (R 4.3.0)
#>  pillar        1.9.0   2023-03-22 [2] RSPM (R 4.3.0)
#>  pkgconfig     2.0.3   2019-09-22 [2] RSPM (R 4.3.0)
#>  purrr         1.0.2   2023-08-10 [1] RSPM (R 4.3.0)
#>  R6            2.5.1   2021-08-19 [2] RSPM (R 4.3.0)
#>  Rcpp          1.0.11  2023-07-06 [2] RSPM (R 4.3.0)
#>  reprex        2.0.2   2022-08-17 [1] RSPM (R 4.3.0)
#>  rlang         1.1.1   2023-04-28 [2] RSPM (R 4.3.0)
#>  rmarkdown     2.25    2023-09-18 [1] RSPM (R 4.3.0)
#>  rstudioapi    0.15.0  2023-07-07 [1] RSPM (R 4.3.0)
#>  sessioninfo   1.2.2   2021-12-06 [1] RSPM (R 4.3.0)
#>  tibble        3.2.1   2023-03-20 [2] RSPM (R 4.3.0)
#>  tidyselect    1.2.1   2024-03-11 [1] RSPM (R 4.3.0)
#>  utf8          1.2.3   2023-01-31 [2] RSPM (R 4.3.0)
#>  vctrs         0.6.5   2023-12-01 [1] RSPM (R 4.3.0)
#>  withr         2.5.1   2023-09-26 [2] RSPM (R 4.3.0)
#>  xfun          0.40    2023-08-09 [1] RSPM (R 4.3.0)
#>  yaml          2.3.7   2023-01-23 [2] RSPM (R 4.3.0)
#> 
#>  [1] /home/myid/data/R/x86_64-pc-linux-gnu-library/4.3
#>  [2] /opt/R/4.3.0/lib64/R/library
#> 
#> ──────────────────────────────────────────────────────────────────────────────

Created on 2024-05-16 with reprex v2.0.2

Edit:
I am willing to generally move to {dbplyr}, but I run into errors there as well.

This works to access the above-created table (though again, the table is only column names/ types, no rows), though I find the glue a bit clunky:

tmp_tbl <- dplyr::tbl(con,
                      I(glue::glue(Sys.getenv("MYID"),
                                   ".",
                                 "TMP_TBL")))

but I wasn't able to get copy_to to work:

library(dbplyr)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:dbplyr':
#> 
#>     ident, sql
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

con = DBI::dbConnect (
  odbc::odbc(),
  'THEDATABASE',
  UID = Sys.getenv("MYID"),
  PWD = Sys.getenv("MYPASSWORD")
)

tmp_tbl <- dplyr::copy_to(dest = con,
                          df = mtcars,
                          name = I(glue::glue(Sys.getenv("MYID"),
                                   ".",
                                 "TMP_TBL")))
#> Error in `db_copy_to()`:
#> ! Can't copy data to table 8675309.TMP_TBL.
#> Caused by error in `dplyr::db_write_table()`:
#> ! Can't write table table 8675309.TMP_TBL.
#> Caused by error in `name@name[["table"]]`:
#> ! subscript out of bounds
#> Backtrace:
#>      ▆
#>   1. ├─dplyr::copy_to(...)
#>   2. ├─dplyr:::copy_to.DBIConnection(...)
#>   3. │ ├─dplyr::copy_to(...)
#>   4. │ └─dbplyr:::copy_to.src_sql(...)
#>   5. │   ├─dbplyr::db_copy_to(...)
#>   6. │   └─dbplyr:::db_copy_to.DBIConnection(...)
#>   7. │     ├─dbplyr:::with_transaction(...)
#>   8. │     │ └─base::withCallingHandlers(...)
#>   9. │     ├─dplyr::db_write_table(...)
#>  10. │     └─dbplyr:::db_write_table.DBIConnection(...)
#>  11. │       ├─base::withCallingHandlers(...)
#>  12. │       ├─DBI::dbWriteTable(...)
#>  13. │       └─odbc::dbWriteTable(...)
#>  14. │         └─odbc (local) .local(conn, name, value, ...)
#>  15. │           ├─DBI::dbExistsTable(conn, name)
#>  16. │           └─odbc::dbExistsTable(conn, name)
#>  17. │             ├─DBI::dbExistsTable(...)
#>  18. │             └─odbc::dbExistsTable(...)
#>  19. │               └─name@name[["table"]] %in% ...
#>  20. └─dbplyr (local) `<fn>`(`<sbscOOBE>`)
#>  21.   └─cli::cli_abort(msg, parent = cnd)
#>  22.     └─rlang::abort(...)

Created on 2024-05-16 with reprex v2.0.2