Error when using compute() to create a new table in a schema. Same code using connections created with dbConnect() works. #179

SimonCoulombe · 2024-02-06T20:11:49Z

Hi everyone,
I am unable to use compute() to run a dbplyr query and save the output into a new table if that table is located in a schema if that connection was created using pool::dbPool().

EDIT: here is the error, to make it more obvious:

# Error in UseMethod("sql_escape_ident") :
#   no applicable method for 'sql_escape_ident' applied to an object of class "c('Pool', 'R6')"

The code works if I create the connection using DBI::dbConnect()

Here's the closest I could create to a regexp. It uses Azure (you won't be able to connect) because I am not sure how to create schemas in databases.

#found in_schema(sql("schema"), "new_table ) in this post 
 #https://stackoverflow.com/questions/71410349/store-new-permanent-table-in-schema-using-compute


# libraries -----
library(dbplyr)
library(DBI)
library(pool)
library(dplyr)
library(odbc)
# connect using pool and con ----
pool <- pool::dbPool(drv = odbc::odbc(),
                     driver = "{ODBC17}",
                     uid = Sys.getenv("AZURE_UID"),          
                     pwd = Sys.getenv("AZURE_PWD"),        
                     server = Sys.getenv("AZURE_SERVER"),     
                     database = Sys.getenv("AZURE_DATABASE"),  
                     Authentication = "ActiveDirectoryPassword",
                     port = 1433,
                     minSize = 0, ## default is 1 which often leaves connections hanging (not cool)
                     idleTimeout = 600, ## 10 minutes until idle connection is closed, takes ~1 second to reopen
                     encoding = "UTF-8",
                     encrypt = "yes")

con <- DBI::dbConnect(
  odbc::odbc(),
  Driver   = "{ODBC Driver 17 for SQL Server}",
  Server   = Sys.getenv("AZURE_SERVER"),
  Database = Sys.getenv("AZURE_DATABASE"),
  UID      = Sys.getenv('AZURE_UID'),
  PWD      = Sys.getenv('AZURE_PWD'),
  Port     = 1433,
  Authentication = "ActiveDirectoryPassword",
  Encrypt  = "yes"
)



# upload iris to synapse  ----
#DBI::dbWriteTable(con, DBI::Id(schema = "my_schema",   table = "iris"),  value = iris, overwrite = TRUE) # slow but works

#  POOL --this doesnt work  -----

pool_iris <- tbl(pool,   dbplyr::in_schema(sql("my_schema"),"iris"))


pool_query <- pool_iris %>%
  head(10)

compute(pool_query, dbplyr::in_schema(sql("my_schema"),"iris_computed_from_pool"), temporary = FALSE)
# Error in UseMethod("sql_escape_ident") :
#   no applicable method for 'sql_escape_ident' applied to an object of class "c('Pool', 'R6')"


#  DBCONNECT - this works (same code) -----

con_iris <- tbl(con,   dbplyr::in_schema(sql("my_schema"),"iris"))


con_query <- con_iris %>%
  head(10)

compute(con_query, dbplyr::in_schema(sql("my_schema"),"iris_computed_from_con"), temporary = FALSE)


#  Source:   table<my_schema."iris_computed_from_con"> [10 x 5]
# # Database: Microsoft SQL Server 12.00.2531[usersqldatabaseXXXX@domainsqldatabaseXXXX.com@sqlserversqldatabaseXXXX/sqldatabaseXXXX]
#    Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#           <dbl>       <dbl>        <dbl>       <dbl> <chr>
#  1          4.6         3.1          1.5         0.2 setosa
#  2          5.5         3.5          1.3         0.2 setosa
#  3          4.9         3.6          1.4         0.1 setosa
#  4          6.4         3.1          5.5         1.8 virginica
#  5          4.4         3.2          1.3         0.2 setosa



sessioninfo::session_info()
# ─ Session info ───────────────────────────────────────────────────────────────
#  setting  value
#  version  R version 4.0.2 (2020-06-22)
#  os       OpenShift Enterprise
#  system   x86_64, linux-gnu
#  ui       RStudio
#  language (EN)
#  collate  en_CA.UTF-8
#  ctype    en_CA.UTF-8
#  tz       America/Toronto
#  date     2024-02-06
#  rstudio  2023.06.0+421.pro1 Mountain Hydrangea (server)
#  pandoc   3.1.1 @ /usr/lib/rstudio-server/bin/quarto/bin/tools/ (via rmarkdown)
#
# ─ Packages ───────────────────────────────────────────────────────────────────
#  ! package     * version date (UTC) lib source
#  P bit           4.0.5   2022-11-15 [?] RSPM (R 4.0.5)
#  P bit64         4.0.5   2020-08-30 [?] RSPM (R 4.0.5)
#  P blob          1.2.4   2023-03-17 [?] RSPM (R 4.0.5)
#  P cli           3.6.0   2023-01-09 [?] RSPM (R 4.0.5)
#  P DBI         * 1.1.3   2022-06-18 [?] CRAN (R 4.0.5)
#  P dbplyr      * 2.3.2   2023-03-21 [?] RSPM (R 4.0.5)
#  P digest        0.6.31  2022-12-11 [?] RSPM (R 4.0.5)
#  P dplyr       * 1.1.0   2023-01-29 [?] RSPM (R 4.0.5)
#  P evaluate      0.20    2023-01-17 [?] RSPM (R 4.0.5)
#  P fansi         1.0.4   2023-01-22 [?] CRAN (R 4.0.2)
#  P fastmap       1.1.1   2023-02-24 [?] RSPM (R 4.0.5)
#  P generics      0.1.3   2022-07-05 [?] RSPM (R 4.0.5)
#  P glue          1.6.2   2022-02-24 [?] RSPM (R 4.0.5)
#  P hms           1.1.3   2023-03-21 [?] RSPM (R 4.0.5)
#  P htmltools     0.5.7   2023-11-03 [?] RSPM (R 4.0.5)
#  P knitr         1.42    2023-01-25 [?] RSPM (R 4.0.5)
#  P later         1.3.0   2021-08-18 [?] RSPM (R 4.0.5)
#  P lifecycle     1.0.3   2022-10-07 [?] RSPM (R 4.0.5)
#    magrittr      2.0.3   2022-03-30 [1] RSPM (R 4.0.5)
#  P odbc        * 1.4.2   2024-01-22 [?] RSPM (R 4.0.5)
#  P pillar        1.8.1   2022-08-19 [?] RSPM (R 4.0.5)
#  P pkgconfig     2.0.3   2019-09-22 [?] RSPM (R 4.0.3)
#  P pool        * 1.0.1   2023-02-21 [?] RSPM (R 4.0.5)
#  P purrr         1.0.1   2023-01-10 [?] RSPM (R 4.0.5)
#    R6            2.5.1   2021-08-19 [1] RSPM (R 4.0.5)
#  P Rcpp          1.0.10  2023-01-22 [?] CRAN (R 4.0.2)
#    renv          1.0.3   2023-09-19 [1] RSPM (R 4.0.2)
#  P rlang         1.1.0   2023-03-14 [?] RSPM (R 4.0.5)
#  P rmarkdown     2.20    2023-01-19 [?] RSPM (R 4.0.5)
#  P rstudioapi    0.15.0  2023-07-07 [?] RSPM (R 4.0.5)
#  P sessioninfo   1.2.2   2021-12-06 [?] RSPM (R 4.0.5)
#  P tibble        3.2.1   2023-03-20 [?] RSPM (R 4.0.5)
#  P tidyselect    1.2.0   2022-10-10 [?] RSPM (R 4.0.5)
#  P utf8          1.2.3   2023-01-31 [?] RSPM (R 4.0.5)
#  P vctrs         0.6.0   2023-03-16 [?] RSPM (R 4.0.5)
#  P withr         2.5.0   2022-03-03 [?] RSPM (R 4.0.5)
#  P xfun          0.37    2023-01-31 [?] RSPM (R 4.0.5)
#  P yaml          2.3.7   2023-01-23 [?] RSPM (R 4.0.5)
#
#  [1] /XXXX/renv/library/R-4.0/x86_64-pc-linux-gnu
#  [2] /opt/R/4.0.2/lib/R/library
#
#  P ── Loaded and on-disk path mismatch.

The text was updated successfully, but these errors were encountered:

hadley · 2024-02-13T14:55:58Z

Would you mind filtering your reprex down to the bare minimum that illustrates the issue?

SimonCoulombe · 2024-02-13T16:18:18Z

Would you mind filtering your reprex down to the bare minimum that illustrates the issue?

Hi Hadley, thanks for chiming in.

I rewrote the regex to make it a bit shorter and allow it to run on my home computer (windows, R 4.3.1, connecting to postgres database ) instead of my work computer (linux, R 4.0.2, connecting to azure synapse).

Lo and behold, on my home computer there is no issue.

I'm afraid I can't identify if it's an azure or a linux issue because I can't change these parameters at work.
Probably can close this as "can not reproduce" if you dont have azure synapse handy.


library(dplyr)
library(dbplyr)
library(DBI)
library(pool)
library(RPostgres)

pool <- pool::dbPool(
  RPostgres::Postgres(),
  host =  "192.168.2.15",
  dbname = "postgres",
  user = "postgres",
  password = "mypassword
  port = 5432
)

con <- dbConnect(
  RPostgres::Postgres(),
  host = "192.168.2.15",
  dbname = "postgres",
  port = 5432,
  user = "postgres",
  password = "mypassword
)

dbExecute(con, "create schema my_schema;")

# upload iris to db  ----
DBI::dbWriteTable(con, DBI::Id(schema = "my_schema",   table = "iris"),  value = iris, overwrite = TRUE) # slow but works

#  POOL works -------
pool_iris <- tbl(pool,   dbplyr::in_schema(sql("my_schema"),"iris"))
pool_query <- pool_iris %>%  head(10)
compute(pool_query, dbplyr::in_schema(sql("my_schema"),"iris_computed_from_pool"), temporary = FALSE)

#  DBCONNECT works -----
con_iris <- tbl(con,   dbplyr::in_schema(sql("my_schema"),"iris"))
con_query <- con_iris %>%  head(10)
compute(con_query, dbplyr::in_schema(sql("my_schema"),"iris_computed_from_con"), temporary = FALSE)

hadley · 2024-02-13T17:45:22Z

@simon you can remove any credentials from the reprex before positing it, but it's really useful if you can run it (even though I can't) so I can see exactly the error you're seeing.

hadley · 2024-02-13T22:30:25Z

Oh maybe the error was in your first repex. Was it this?

# Error in UseMethod("sql_escape_ident") :
#   no applicable method for 'sql_escape_ident' applied to an object of class "c('Pool', 'R6')"

In that case, can you please try updating dbplyr to 2.4.0 to see if that makes the problem go away?

* Bump required version of DBI. Fixes #178. * Message if dbplyr is old. Fixes #179 (as much as possible)

SimonCoulombe · 2024-02-14T01:08:55Z

Oh maybe the error was in your first repex. Was it this?
# Error in UseMethod("sql_escape_ident") :
#   no applicable method for 'sql_escape_ident' applied to an object of class "c('Pool', 'R6')"
In that case, can you please try updating dbplyr to 2.4.0 to see if that makes the problem go away?

Yes it was, should have made it clearer, sorry.

I updated dbplyr to 2.4.0 and got a new error:

compute(pool_query, dbplyr::in_schema(sql("my_schma"),"iris_computed_from_pool"), temporary = FALSE)

Error in `db_save_query.DBIConnection()`:
! Can't save query to table "my_schema"."iris_computed_from_pool".
Caused by error:
! nanodbc/nanodbc.cpp:1710: 00000
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]111212;Operation cannot be performed within a transaction. 
Run `rlang::last_trace()` to see where the error occurred.

same command run on a connection created using dbConnect() still works after updating the packages:


> compute(con_query, dbplyr::in_schema(sql("my_schema"),"iris_computed_from_con"), temporary = FALSE)
# Source:   table<iris_computed_from_con> [10 x 5]
# Database: Microsoft SQL Server 12.00.2531
   Sepal.Length Sepal.Width Petal.Length Petal.Width Species   
          <dbl>       <dbl>        <dbl>       <dbl> <chr>     
 1          5.4         3.4          1.7         0.2 setosa    
 2          6.5         3.2          5.1         2   virginica 
 3          7.2         3.2          6           1.8 virginica 
 4          5.6         3            4.5         1.5 versicolor
 5          6.4         2.8          5.6         2.1 virginica 
 6          6.9         3.1          5.1         2.3 virginica 
 7          5.6         2.7          4.2         1.3 versicolor
 8          6           2.9          4.5         1.5 versicolor
 9          5.5         2.6          4.4         1.2 versicolor
10          6.3         2.9          5.6         1.8 virginica

here's my updated session info:


> sessioninfo::session_info()
─ Session info ───────────────────────────────────────────────────────────────
 setting  value
 version  R version 4.0.2 (2020-06-22)
 os       OpenShift Enterprise
 system   x86_64, linux-gnu
 ui       RStudio
 language (EN)
 collate  en_CA.UTF-8
 ctype    en_CA.UTF-8
 tz       America/Toronto
 date     2024-02-13
 rstudio  2023.06.0+421.pro1 Mountain Hydrangea (server)
 pandoc   NA

─ Packages ───────────────────────────────────────────────────────────────────
 ! package     * version date (UTC) lib source
 P bit           4.0.5   2022-11-15 [?] RSPM (R 4.0.5)
 P bit64         4.0.5   2020-08-30 [?] RSPM (R 4.0.5)
 P blob          1.2.4   2023-03-17 [?] RSPM (R 4.0.5)
 P cli           3.6.2   2023-12-11 [?] RSPM (R 4.0.5)
 P DBI         * 1.2.1   2024-01-12 [?] RSPM (R 4.0.5)
 P dbplyr      * 2.4.0   2023-10-26 [?] RSPM (R 4.0.5)
 P dplyr       * 1.1.4   2023-11-17 [?] RSPM (R 4.0.5)
 P fansi         1.0.4   2023-01-22 [?] CRAN (R 4.0.2)
 P generics      0.1.3   2022-07-05 [?] RSPM (R 4.0.5)
 P glue          1.6.2   2022-02-24 [?] RSPM (R 4.0.5)
 P hms           1.1.3   2023-03-21 [?] RSPM (R 4.0.5)
 P later         1.3.0   2021-08-18 [?] RSPM (R 4.0.5)
 P lifecycle     1.0.3   2022-10-07 [?] RSPM (R 4.0.5)
   magrittr      2.0.3   2022-03-30 [1] RSPM (R 4.0.5)
 P odbc        * 1.4.2   2024-01-22 [?] RSPM (R 4.0.5)
 P pillar        1.9.0   2023-03-22 [?] RSPM (R 4.0.5)
 P pkgconfig     2.0.3   2019-09-22 [?] RSPM (R 4.0.3)
 P pool        * 1.0.2   2024-01-18 [?] RSPM (R 4.0.5)
 P purrr         1.0.1   2023-01-10 [?] RSPM (R 4.0.5)
   R6            2.5.1   2021-08-19 [1] RSPM (R 4.0.5)
 P Rcpp          1.0.10  2023-01-22 [?] CRAN (R 4.0.2)
   renv          1.0.3   2023-09-19 [1] RSPM (R 4.0.2)
 P rlang         1.1.3   2024-01-10 [?] RSPM (R 4.0.5)
 P rstudioapi    0.15.0  2023-07-07 [?] RSPM (R 4.0.5)
 P sessioninfo * 1.2.2   2021-12-06 [?] RSPM (R 4.0.5)
 P tibble        3.2.1   2023-03-20 [?] RSPM (R 4.0.5)
 P tidyselect    1.2.0   2022-10-10 [?] RSPM (R 4.0.5)
 P utf8          1.2.3   2023-01-31 [?] RSPM (R 4.0.5)
 P vctrs         0.6.5   2023-12-01 [?] RSPM (R 4.0.5)
 P withr         2.5.0   2022-03-03 [?] RSPM (R 4.0.5)

 [1] /devroot/sandbox/EG66451/git/raw-data/renv/library/R-4.0/x86_64-pc-linux-gnu
 [2] /opt/R/4.0.2/lib/R/library

 P ── Loaded and on-disk path mismatch.

* Bump required version of DBI. Fixes #178. * Message if dbplyr is old. Fixes #179 (as much as possible)

SimonCoulombe changed the title ~~can't use compute() to create a new table in a schema. connections created with dbConnect() can.~~ Error when using compute() to create a new table in a schema. Same code using connections created with dbConnect() works. Feb 6, 2024

hadley added a commit that referenced this issue Feb 13, 2024

Tweak dependency handling

ee2c676

* Bump required version of DBI. Fixes #178. * Message if dbplyr is old. Fixes #179 (as much as possible)

hadley mentioned this issue Feb 13, 2024

Tweak dependency handling #181

Merged

hadley closed this as completed in #181 Feb 14, 2024

hadley added a commit that referenced this issue Feb 14, 2024

Tweak dependency handling (#181)

4671ae5

* Bump required version of DBI. Fixes #178. * Message if dbplyr is old. Fixes #179 (as much as possible)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Error when using compute() to create a new table in a schema. Same code using connections created with dbConnect() works. #179

Error when using compute() to create a new table in a schema. Same code using connections created with dbConnect() works. #179

SimonCoulombe commented Feb 6, 2024 •

edited

Loading

hadley commented Feb 13, 2024

SimonCoulombe commented Feb 13, 2024 •

edited

Loading

hadley commented Feb 13, 2024

hadley commented Feb 13, 2024

SimonCoulombe commented Feb 14, 2024 •

edited

Loading

Error when using compute() to create a new table in a schema. Same code using connections created with dbConnect() works. #179

Error when using compute() to create a new table in a schema. Same code using connections created with dbConnect() works. #179

Comments

SimonCoulombe commented Feb 6, 2024 • edited Loading

hadley commented Feb 13, 2024

SimonCoulombe commented Feb 13, 2024 • edited Loading

hadley commented Feb 13, 2024

hadley commented Feb 13, 2024

SimonCoulombe commented Feb 14, 2024 • edited Loading

SimonCoulombe commented Feb 6, 2024 •

edited

Loading

SimonCoulombe commented Feb 13, 2024 •

edited

Loading

SimonCoulombe commented Feb 14, 2024 •

edited

Loading