search

tidyverse / dbplyr

Database (DBI) backend for dplyr
https://dbplyr.tidyverse.org
Other
474 stars 173 forks source link

rows_upsert worked in 2.4.0, broke with 2.5.0 #1484

Closed endorphinbomber closed 6 months ago

endorphinbomber commented 6 months ago

Hi everybody, Thanks everybody for this package. I was using the rows_upsert function in my scripts with version 2.4.0 and it worked just fine.

2.4.0

 > rows_upsert(tbl(con_sql_server, in_schema("testschema", "prognosis")),
               data,
               by = c('time_predicted','latitude','longitude'),
               copy = T,in_place = T)
Created a temporary table named #dbplyr_001
>

This works perfectly, "data" is a simple dataframe in the format of the database in "testschema"."prognosis" Connection is to a Microsoft SQL database made via Version=1.5.10.1010 of the RStudio Pro Drivers

Same code after updating to 2.5.0 runs into an error

2.5.0

>   rows_upsert(tbl(con_sql_server, in_schema("testschema", "prognosis")),
               data,
               by = c('time_predicted','latitude','longitude'),
               copy = T,in_place = T)

Created a temporary table named #dbplyr_YiyN1wpYYFError in `db_copy_to()`:
! Can't copy data to table "#dbplyr_YiyN1wpYYF".
Caused by error in `dplyr::db_write_table()`:
! Can't write table table "#dbplyr_YiyN1wpYYF".
Caused by error in `name@name[["table"]]`:
! subscript out of bounds
Backtrace:
  1. dplyr::rows_upsert(...)
  2. dbplyr:::rows_upsert.tbl_lazy(...)
  3. dbplyr:::rows_auto_copy(x, y, copy = copy)
  5. dbplyr:::auto_copy.tbl_sql(x, y, copy = copy, types = x_types)
  7. dbplyr:::copy_to.src_sql(...)
     ...
 16. odbc::dbWriteTable(...)
 17. odbc (local) .local(conn, name, value, ...)
 19. odbc::dbExistsTable(conn, name)
 21. odbc::dbExistsTable(...)
 22. name@name[["table"]] %in% ...

data i try to push looks like this:

structure(list(time_requested = structure(list(sec = c(9.20430302619934, 
9.20430302619934, 9.20430302619934, 9.20430302619934, 9.20430302619934, 
9.20430302619934), min = c(7L, 7L, 7L, 7L, 7L, 7L), hour = c(12L, 
12L, 12L, 12L, 12L, 12L), mday = c(25L, 25L, 25L, 25L, 25L, 25L
), mon = c(2L, 2L, 2L, 2L, 2L, 2L), year = c(124L, 124L, 124L, 
124L, 124L, 124L), wday = c(1L, 1L, 1L, 1L, 1L, 1L), yday = c(84L, 
84L, 84L, 84L, 84L, 84L), isdst = c(0L, 0L, 0L, 0L, 0L, 0L), 
    zone = c("UTC", "UTC", "UTC", "UTC", "UTC", "UTC"), gmtoff = c(0L, 
    0L, 0L, 0L, 0L, 0L)), balanced = TRUE, class = c("POSIXlt", 
"POSIXt"), tzone = "UTC"), time_predicted = structure(c(1711379700, 
1711380600, 1711381500, 1711382400, 1711383300, 1711384200), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), latitude = c(0, 0, 0, 0, 0, 0), longitude = c(0, 
0, 0, 0, 0, 0), t_2m.C = c(9.9, 9.6, 9.4, 9.1, 8.7, 8.4), snow_depth.cm = c(0, 
0, 0, 0, 0, 0), direct_rad.W = c(237.9, 155.3, 116.5, 147.4, 
76.3, 45.4), global_rad.W = c(355.7, 305.5, 262.6, 226.6, 171.9, 
121.1)), row.names = c(NA, 6L), class = "data.frame")

I guess something in the update broke, since the code runs again after re-installing 2.4.0, but maybe I did something wrong. I can provide additional details if pointed at specific things that are needed.

mgirlich commented 6 months ago

Can you please make sure that you run the newest odbc? (by running install.packages("odbc") and restarting R afterwards). If this is still an issue afterwards please create a reprex using the reprex package and include your session information.

endorphinbomber commented 6 months ago

Can you please make sure that you run the newest odbc? (by running install.packages("odbc") and restarting R afterwards). If this is

This seems to be indeed linked to another package not being up2date. Thank you very much for the quick response.