Issue when using RJAVA inside reactive expression in a Shiny App

[emilianomm]

Hi! I've been recently trying out rix and encountered the following issue

Context

I'm working in a shiny app docker image and decided to give a try to rix. Dependency management worked really well with minimum setup time, even for my project that has a considerable amount of dependencies.

I'm using RJDBC to query against a remote dbms to render a few tables on the app. All worked fine, except calls to RJDBC::dbGetQuery when called inside a reactive expression.

Examples

1. Would do fine

writeFile <- observe({
  # Consider conx properly defined in an external file invoked by source()
  query <- f("path/to/sql.sql", input$user_input)
  tbl <- RJDBC::dbGetQuery(conx, query)
  write.csv(tbl)
}) |> bindEvent(input$button_1)

2. Would fail

myTable <- reactive({
  # Consider conx properly defined in an external file invoked by source()
  query <- f("path/to/sql.sql", input$user_input)
  tbl <- RJDBC::dbGetQuery(conx, query)
  tbl
}) |> bindEvent(input$button_2)

Further Info

This is the dockerfile I used. Also tried with a very similar one using nixos/nix base docker image

FROM ubuntu:jammy

COPY generate_env.R .

RUN apt update -y

RUN apt install curl -y

RUN curl --proto '=https' --tlsv1.2 -sSf -L https://install.determinate.systems/nix | sh -s -- install linux \
  --extra-conf "sandbox = false" \
  --init none \
  --no-confirm

# Adds Nix to the path, as described by the Determinate Systems installer's documentation
ENV PATH="${PATH}:/nix/var/nix/profiles/default/bin"

RUN nix-channel --update

RUN nix-shell \
    --expr "$(curl -sl https://raw.githubusercontent.com/ropensci/rix/master/inst/extdata/default.nix)" \
    --run "Rscript generate_env.R"

RUN nix-build

EXPOSE 3838

COPY . /app

RUN chmod 777 -R /app

CMD ["nix-shell", "--run", "R CMD javareconf && Rscript /app/run-app.R"]

[emilianomm]

PD: I have also created this minimum, but complete Shiny app with Shiny Assistant that have a skeleton for both cases. If there are someone brave enough to put a real JDBC connection and reproduce the error.

library(shiny)
library(bslib)

# Mock database connection function to simulate RJDBC behavior
mock_db_connection <- function() {
  # Simulates creating a connection
  Sys.sleep(0.1)  # Simulate connection time
  list(
    status = "connected",
    timestamp = Sys.time()
  )
}

# Mock query function to simulate dbGetQuery
mock_query <- function(conn, query) {
  # Simulates querying data
  Sys.sleep(0.2)  # Simulate query time
  data.frame(
    id = 1:5,
    value = sample(100:999, 5),
    timestamp = Sys.time()
  )
}

# Create mock connection
conx <- mock_db_connection()

ui <- page_fluid(
  card(
    card_header("RJDBC Query Test Cases"),
    card_body(
      p("This app demonstrates the difference between using database queries in observe() vs reactive()"),
      actionButton("btn_observe", "Run Observe Example (Works)"),
      actionButton("btn_reactive", "Run Reactive Example (Fails)"),
      verbatimTextOutput("observe_result"),
      verbatimTextOutput("reactive_result"),
      verbatimTextOutput("error_messages")
    )
  )
)

server <- function(input, output, session) {
  # Case 1: Works fine with observe()
  observe({
    req(input$btn_observe)
    
    # Simulate query execution
    query <- "SELECT * FROM mock_table"  # Mock query
    tryCatch({
      tbl <- mock_query(conx, query)
      output$observe_result <- renderPrint({
        cat("Observe Example (Success):\n")
        print(tbl)
      })
    }, error = function(e) {
      output$error_messages <- renderPrint({
        cat("Error in observe:", conditionMessage(e), "\n")
      })
    })
  })
  
  # Case 2: Problematic with reactive()
  problematic_data <- reactive({
    req(input$btn_reactive)
    
    # Simulate query execution
    query <- "SELECT * FROM mock_table"  # Mock query
    # Simulate the issue by forcing an error in reactive context
    if (TRUE) {
      stop("Simulated RJDBC error: Connection cannot be established in reactive context")
    }
    mock_query(conx, query)
  })
  
  output$reactive_result <- renderPrint({
    tryCatch({
      cat("Reactive Example (Should Fail):\n")
      print(problematic_data())
    }, error = function(e) {
      cat("Error in reactive:", conditionMessage(e), "\n")
    })
  })
}

shinyApp(ui, server)

[b-rodrigues]

Thanks for the issue, this likely will take some time to investigate, but from a purely functioning perspective, does using observe() achieve what you need ?

[b-rodrigues]

could you also post the generate_env.R please ?

[b-rodrigues]

Also what's the error message ?

[emilianomm]

Hi Bruno. You have a really cool work here. Sadly I wasn't able to easily adapt observe() to my use case (The key difference being that the reactive has a return value and observe simply execute some expr). Here is the requested information, and really hope this helps on improving the package.

Error message

Error in .jcheck: Java Exception <no description because toString() failed>.jcall(class.loader, 
"[Ljava/lang/String;", "getClassPath")new("jobjRef", jobj = <pointer: 0x8fe7992>, jclass = "java/lang/Throwable")

generate_env.R

library(rix)

rix(
  r_ver = "4.3.3",
  r_pkgs = c(
    'rJava',
    'googlesheets4',
    'shiny',
    'data.table',
    'DT',
    'shinydashboard',
    'shinyjs',
    'waiter',
    'shinyWidgets',
    'bigrquery',
    'DBI',
    'dplyr',
    'tidyverse',
    'dummy',
    'jsonlite',
    'RJDBC',
    'shinybusy',
    'stringr',
    'zoo',
    'glue',
    'highcharter',
    'raster',
    'openxlsx',
    'R2HTML',
    'htmltools',
    'httr',
    'mime',
    'readr',
    'RMySQL',
    'RPostgres',
    'shinyalert'
  ),
  system_pkgs = 'zulu21',
  ide = "other",
  project_path = ".",
  overwrite = TRUE
)

[b-rodrigues]

thanks, I’ll look into it during this weekend likely

I know this is not the answer you are looking for, but could you use RODBC instead? https://cran.r-project.org/web/packages/RODBC/index.html

or DBI?