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