Can't load DBI driver correctly within golem

[dkalisch]

Describe the bug
I try to build a shiny app with a database connection to a MSSQL Server via DBI and odic libraries. I can call the functions by hand correctly and they deliver the expected result. The package also builds without error. When I run the application with run_app() however, I get the error message: Warning: Error in <Anonymous>: unable to find an inherited method for function ‘dbGetInfo’ for signature ‘"Microsoft SQL Server"’ for this line of code: closed_loans <- package_files.db %>% in app_server.R

To Reproduce
Steps to reproduce the behavior:

1. Compile package
2. run run_app()

Expected behavior
A table of the requested data is shown in the app

session info

─ Session info ────────────────────────────────────────────────────────────────────────────────────
 setting  value
 version  R version 4.2.3 (2023-03-15)
 os       macOS Ventura 13.4.1
 system   aarch64, darwin20
 ui       RStudio
 language (EN)
 collate  en_US.UTF-8
 ctype    en_US.UTF-8
 tz       America/Chicago
 date     2023-07-12
 rstudio  2023.06.0+421 Mountain Hydrangea (desktop)
 pandoc   3.1 @ /opt/homebrew/bin/pandoc

─ Packages ────────────────────────────────────────────────────────────────────────────────────────
 package     * version    date (UTC) lib source
 attachment    0.4.0      2023-05-31 [1] CRAN (R 4.2.0)
 attempt       0.3.1      2020-05-03 [1] CRAN (R 4.2.0)
 bit           4.0.5      2022-11-15 [1] CRAN (R 4.2.0)
 bit64         4.0.5      2020-08-30 [1] CRAN (R 4.2.0)
 blob          1.2.4      2023-03-17 [1] CRAN (R 4.2.0)
 bslib         0.5.0.9000 2023-07-12 [1] Github (rstudio/bslib@890f847)
 cachem        1.0.8      2023-05-01 [1] CRAN (R 4.2.0)
 callr         3.7.3      2022-11-02 [1] CRAN (R 4.2.0)
 cli           3.6.1      2023-03-23 [1] CRAN (R 4.2.0)
 config        0.3.1      2020-12-17 [1] CRAN (R 4.2.0)
 crayon        1.5.2      2022-09-29 [1] CRAN (R 4.2.0)
 DBI         * 1.1.3      2022-06-18 [1] CRAN (R 4.2.0)
 dbplyr      * 2.3.2      2023-03-21 [1] CRAN (R 4.2.0)
 desc          1.4.2      2022-09-08 [1] CRAN (R 4.2.0)
 devtools      2.4.5      2022-10-11 [1] CRAN (R 4.2.0)
 digest        0.6.33     2023-07-07 [1] CRAN (R 4.2.0)
 dplyr       * 1.1.2      2023-04-20 [1] CRAN (R 4.2.0)
 ellipsis      0.3.2      2021-04-29 [1] CRAN (R 4.2.0)
 fansi         1.0.4      2023-01-22 [1] CRAN (R 4.2.0)
 fastmap       1.1.1      2023-02-24 [1] CRAN (R 4.2.0)
 fs            1.6.2      2023-04-25 [1] CRAN (R 4.2.0)
 generics      0.1.3      2022-07-05 [1] CRAN (R 4.2.0)
 glue          1.6.2      2022-02-24 [1] CRAN (R 4.2.0)
 golem         0.4.1      2023-06-05 [1] CRAN (R 4.2.0)
 hms           1.1.3      2023-03-21 [1] CRAN (R 4.2.0)
 htmltools     0.5.5      2023-03-23 [1] CRAN (R 4.2.0)
 htmlwidgets   1.6.2      2023-03-17 [1] CRAN (R 4.2.0)
 httpuv        1.6.11     2023-05-11 [1] CRAN (R 4.2.0)
 jquerylib     0.1.4      2021-04-26 [1] CRAN (R 4.2.0)
 jsonlite      1.8.7      2023-06-29 [1] CRAN (R 4.2.0)
 knitr         1.42       2023-01-25 [1] CRAN (R 4.2.0)
 kpiApp      * 0.0.0.9000 2023-07-12 [1] local
 later         1.3.1      2023-05-02 [1] CRAN (R 4.2.0)
 lifecycle     1.0.3      2022-10-07 [1] CRAN (R 4.2.0)
 magrittr      2.0.3      2022-03-30 [1] CRAN (R 4.2.0)
 memoise       2.0.1      2021-11-26 [1] CRAN (R 4.2.0)
 mime          0.12       2021-09-28 [1] CRAN (R 4.2.0)
 miniUI        0.1.1.1    2018-05-18 [1] CRAN (R 4.2.3)
 odbc        * 1.3.5      2023-06-29 [1] CRAN (R 4.2.0)
 pillar        1.9.0      2023-03-22 [1] CRAN (R 4.2.0)
 pkgbuild      1.4.0      2022-11-27 [1] CRAN (R 4.2.0)
 pkgconfig     2.0.3      2019-09-22 [1] CRAN (R 4.2.0)
 pkgload       1.3.2      2022-11-16 [1] CRAN (R 4.2.0)
 prettyunits   1.1.1      2020-01-24 [1] CRAN (R 4.2.0)
 processx      3.8.1      2023-04-18 [1] CRAN (R 4.2.0)
 profvis       0.3.7      2020-11-02 [1] CRAN (R 4.2.0)
 promises      1.2.0.1    2021-02-11 [1] CRAN (R 4.2.0)
 ps            1.7.5      2023-04-18 [1] CRAN (R 4.2.0)
 purrr         1.0.1      2023-01-10 [1] CRAN (R 4.2.0)
 R6            2.5.1      2021-08-19 [1] CRAN (R 4.2.0)
 Rcpp          1.0.11     2023-07-06 [1] CRAN (R 4.2.0)
 remotes       2.4.2      2021-11-30 [1] CRAN (R 4.2.0)
 rlang         1.1.1      2023-04-28 [1] CRAN (R 4.2.0)
 roxygen2      7.2.3      2022-12-08 [1] CRAN (R 4.2.0)
 rprojroot     2.0.3      2022-04-02 [1] CRAN (R 4.2.0)
 rsconnect     0.8.29     2023-01-09 [1] CRAN (R 4.2.0)
 rstudioapi    0.14       2022-08-22 [1] CRAN (R 4.2.0)
 sass          0.4.6      2023-05-03 [1] CRAN (R 4.2.0)
 sessioninfo   1.2.2      2021-12-06 [1] CRAN (R 4.2.0)
 shiny       * 1.7.4.1    2023-07-06 [1] CRAN (R 4.2.0)
 stringi       1.7.12     2023-01-11 [1] CRAN (R 4.2.0)
 stringr       1.5.0      2022-12-02 [1] CRAN (R 4.2.0)
 tibble        3.2.1      2023-03-20 [1] CRAN (R 4.2.0)
 tidyselect    1.2.0      2022-10-10 [1] CRAN (R 4.2.0)
 urlchecker    1.0.1      2021-11-30 [1] CRAN (R 4.2.0)
 usethis       2.1.6      2022-05-25 [1] CRAN (R 4.2.0)
 utf8          1.2.3      2023-01-31 [1] CRAN (R 4.2.0)
 vctrs         0.6.3      2023-06-14 [1] CRAN (R 4.2.0)
 withr         2.5.0      2022-03-03 [1] CRAN (R 4.2.0)
 xfun          0.39       2023-04-20 [1] CRAN (R 4.2.0)
 xml2          1.3.4      2023-04-27 [1] CRAN (R 4.2.0)
 xtable        1.8-4      2019-04-21 [1] CRAN (R 4.2.0)
 yaml          2.3.7      2023-01-23 [1] CRAN (R 4.2.0)

 [1] /Library/Frameworks/R.framework/Versions/4.2-arm64/Resources/library

Additional context

app_server.R:

#' The application server-side
#'
#' @param input,output,session Internal parameters for {shiny}.
#'     DO NOT REMOVE.
#' @import shiny
#' @import dplyr
#' @import odbc
#' @import DBI
#' @importFrom rlang sym
#' @noRd

# Open connection to Calyx on MSSQL
con_calyx <- DBI::dbConnect(odbc::odbc(),
                            driver = "ODBC Driver 17 for SQL Server",
                            server = "123.456.789.123\\TEST",
                            Database = "PDS",
                            UID      = Sys.getenv("userid"), # in .Renviron
                            PWD      = Sys.getenv("pwd"), # in .Renviron
                            Trusted_Connection = "No")


# Load core data
package_files.db <- dplyr::tbl(con_calyx, "Package_Files")


app_server <- function(input, output, session) {
  # Your application server logic
  
  closed_loans <- reactive({
    closed_loans <- package_files.db %>%
      dplyr::filter(as.Date(f6023) >= '2023-01-01' & f6022 == 23) %>%
      #group_by(month(f6023), f18) %>%
      dplyr::group_by(f18) %>%
      dplyr::summarise(count = n()) %>%
      dplyr::collect() %>%
      dplyr::mutate(pct = round(100*count/sum(count), 2))
    
    closed_loans
  })
  
  output$closed_loans_table <- renderTable({
    closed_loans()
  })
}

app_ui.R:

#' The application User-Interface
#'
#' @param request Internal parameter for `{shiny}`.
#'     DO NOT REMOVE.
#' @import shiny
#' @import dplyr
#' @importFrom rlang sym
#' @noRd
app_ui <- function(request) {
  tagList(
    # Leave this function for adding external resources
    golem_add_external_resources(),
    # Your application UI logic
    fluidPage(
      h1("kpiApp"),
      tableOutput("closed_loans_table")
    )
  )
}

#' Add external Resources to the Application
#'
#' This function is internally used to add external
#' resources inside the Shiny application.
#'
#' @import shiny
#' @importFrom golem add_resource_path activate_js favicon bundle_resources
#' @noRd
golem_add_external_resources <- function() {
  add_resource_path(
    "www",
    app_sys("app/www")
  )

  tags$head(
    favicon(ext = 'png'),
    bundle_resources(
      path = app_sys("app/www"),
      app_title = "kpiApp"
    )
    # Add here other external resources
    # for example, you can add shinyalert::useShinyalert()
  )
}

NAMESPACE:

# Generated by roxygen2: do not edit by hand

export("%>%")
export(run_app)
import(DBI)
import(dplyr)
import(odbc)
import(shiny)
importFrom(golem,activate_js)
importFrom(golem,add_resource_path)
importFrom(golem,bundle_resources)
importFrom(golem,favicon)
importFrom(golem,with_golem_options)
importFrom(magrittr,"%>%")
importFrom(rlang,sym)
importFrom(shiny,HTML)
importFrom(shiny,column)
importFrom(shiny,shinyApp)
importFrom(shiny,tagAppendAttributes)
importFrom(shiny,tagList)
importFrom(shiny,tags)

[ArthurData]

Hello @dkalisch,

I tried to reproduce the problem with another database. In this case, a SQLite database.

Can you try placing the connection and reading of the table in the contents of the app_server function?

Inside an observeEvent, just for once, I :

• make the connection to the database,
• read the data.

I use a reactiveValues to store my read and use it in my application.

app_server <- function(input, output, session) {
  # Your application server logic

  rv <- reactiveValues()

  observeEvent(TRUE, once = TRUE, {
    browser()
    db <- DBI::dbConnect(RSQLite::SQLite(), "localdatabase")

    rv$players <- dplyr::tbl(db, "players")

    rv$players_filtered <- rv$players %>%
      dplyr::filter(number > 10)
  })


  output$players_table <- renderTable({
    rv$players_filtered
  })

}

[ColinFay]

Closing as non reproducible.

Feel free to comment if you still have this issue 🤘