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

[martinschmelzer]

Up until now, I had no problem running this statement:

df <- tbl(src = pool, DBI::Id(schema = "dwh", table = "v_myview"))

I expected to find a lazy table in df. Now I get an error:

tbl(src = pool, DBI::Id(schema = "dwh", table = "v_ASA_population_overview"))

Running dbGetQuery(pool, SELECT * FROM dwh.v_myview") on the other hand works fine.

R version 4.1.3 (2022-03-10)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 22621)

Matrix products: default

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

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

other attached packages:
 [1] htmlwidgets_1.6.2        shinyWidgets_0.8.1       plotly_4.10.4            leaflet_2.2.1           
 [5] reactable_0.4.4          bslib_0.5.1              lubridate_1.9.2          forcats_1.0.0           
 [9] stringr_1.5.1            dplyr_1.1.2              purrr_1.0.1              readr_2.1.4             
[13] tidyr_1.3.0              tibble_3.2.1             ggplot2_3.4.4            tidyverse_2.0.0         
[17] pool_1.0.2               shinyjs_2.1.0            sparkline_2.0            shinycssloaders_1.0.0   
[21] shinydashboard_0.7.2     shinydashboardPlus_2.0.3 shiny_1.8.0             

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.10       digest_0.6.31     utf8_1.2.3        mime_0.12         R6_2.5.1          odbc_1.3.4       
 [7] httr_1.4.7        pillar_1.9.0      rlang_1.1.0       lazyeval_0.2.2    rstudioapi_0.15.0 data.table_1.14.8
[13] jquerylib_0.1.4   blob_1.2.4        bit_4.0.5         munsell_0.5.0     compiler_4.1.3    httpuv_1.6.9     
[19] pkgconfig_2.0.3   htmltools_0.5.5   tidyselect_1.2.0  viridisLite_0.4.2 fansi_1.0.4       crayon_1.5.2     
[25] dbplyr_2.3.4      tzdb_0.3.0        withr_3.0.0       later_1.3.0       grid_4.1.3        jsonlite_1.8.4   
[31] xtable_1.8-4      gtable_0.3.4      lifecycle_1.0.4   DBI_1.2.1         magrittr_2.0.3    scales_1.2.1     
[37] cli_3.6.1         stringi_1.7.12    cachem_1.0.7      farver_2.1.1      fs_1.5.2          promises_1.2.0.1 
[43] ellipsis_0.3.2    generics_0.1.3    vctrs_0.6.1       tools_4.1.3       bit64_4.0.5       glue_1.6.2       
[49] hms_1.1.3         crosstalk_1.2.1   fastmap_1.1.1     timechange_0.2.0  colorspace_2.1-0  memoise_2.0.1    
[55] sass_0.4.5       

[hadley]

Can you please provide a minimal reprex (reproducible example)? The goal of a reprex is to make it as easy as possible for me to recreate your problem so that I can fix it: please help me help you! If you've never heard of a reprex before, start by reading about the reprex package, including the advice further down the page. Please make sure your reprex is created with the reprex package as it gives nicely formatted output and avoids a number of common pitfalls.

[martinschmelzer]

Sorry, should have known better...

library(RSQLite)
library(pool)
library(dplyr)
#> 
#> Attache Paket: 'dplyr'
#> Die folgenden Objekte sind maskiert von 'package:stats':
#> 
#>     filter, lag
#> Die folgenden Objekte sind maskiert von 'package:base':
#> 
#>     intersect, setdiff, setequal, union

pool <- dbPool(RSQLite::SQLite())
dbWriteTable(pool, "mtcars", mtcars)
tbl(pool, "mtcars")
#> Error in get(x, envir = ns, inherits = FALSE) : 
#>   Objekt 'db_col_types' nicht gefunden
#> Error in UseMethod("sql_query_fields"): nicht anwendbare Methode für 'sql_query_fields' auf Objekt der Klasse "c('Pool', 'R6')" angewendet

^{Created on 2024-02-13 with reprex v2.1.0}

[hadley]

I think you mistyped mydb instead of pool in that example.

[martinschmelzer]

@hadley Jep, corrected it. In my actual code I the driver odbc::odbc(), but the problem or error is the same. Not sure if its an issue resulting from tbl or pool.

[hadley]

Can you please try updating dbplyr to see if that makes the problem go away?

[martinschmelzer]

Due to my companies policy I have no build tools available and the binary available of e.g. rlang (dependency of dbplyr) is 1.1.0 :/

[hadley]

Can you use a custom repository? It looks like Posit package manager has binaries for R 4.1:

options(repos = c(CRAN = "https://packagemanager.posit.co/cran/latest"))

[martinschmelzer]

I will see what I can do. I just tried at my desktop at home and the problem is gone with following session params. I guess this can be closed and - worst case - I have to fight my IT...

R version 4.2.2 (2022-10-31 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19045)

Matrix products: default

locale:
[1] LC_COLLATE=German_Germany.utf8  LC_CTYPE=German_Germany.utf8    LC_MONETARY=German_Germany.utf8 LC_NUMERIC=C                   
[5] LC_TIME=German_Germany.utf8    

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

other attached packages:
[1] dplyr_1.1.4   pool_1.0.2    RSQLite_2.3.5

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.12       dbplyr_2.4.0      pillar_1.9.0      compiler_4.2.2    later_1.3.2       tools_4.2.2       digest_0.6.34    
 [8] bit_4.0.5         evaluate_0.23     memoise_2.0.1     lifecycle_1.0.4   tibble_3.2.1      pkgconfig_2.0.3   rlang_1.1.3      
[15] DBI_1.2.1         cli_3.6.2         rstudioapi_0.15.0 yaml_2.3.8        xfun_0.41         ergecup_0.0.1     fastmap_1.1.1    
[22] xml2_1.3.6        knitr_1.45        generics_0.1.3    vctrs_0.6.5       hms_1.1.3         bit64_4.0.5       tidyselect_1.2.0 
[29] glue_1.7.0        R6_2.5.1          fansi_1.0.6       rmarkdown_2.25    tidyr_1.3.0       purrr_1.0.2       blob_1.2.4       
[36] magrittr_2.0.3    htmltools_0.5.7   gt_0.10.1         utf8_1.2.4        cachem_1.0.8     

[hadley]

Looks like I need to bump the required version of dbplyr. I'll do a new release shortly, which might help resolve your problems. (Otherwise you should see if your IT will let you use posit public package manager as it will allow you to keep using old R with latest packages)

[hadley]

It looks like sql_query_fields() was added in dbplyr 2.0.0 which was released 4 years ago, so I don't think it's worthwhile adding any other check.