golem
golem copied to clipboard
Published
20 hours ago •
ThinkR-open
ThinkR-open
Can't load DBI driver correctly within golem
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:
- Compile package
- 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)
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
})
}
Closing as non reproducible.
Feel free to comment if you still have this issue 🤘
