db_query_fields and RMySQL

[Rueben]

Hi,

I am implementing the db_query_fields method in dbplyr package in rstudio in the function below to delete a selected row from MySQL database in a shiny web table:

row <- if (input$row %>% db_query_fields(pool,sql,input$responses))

I however, get the error message:

Warning: Error in UseMethod: no applicable method for 'db_query_fields' applied to an object of class "NULL"
81: db_query_fields
80: function_list[[k]]
78: freduce
77: _fseq
76: eval
75: eval
73: %>%

I have included the reproducible example below:

library(shiny)
  library(session)
  library(pool)
  library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
  library(dbplyr)
#> 
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#> 
#>     ident, sql
  library(RMySQL)
#> Loading required package: DBI
  
  #### When delete button is clicked####
  observeEvent(input$deleterow, {
  
    pool = dbPool(RMySQL::MySQL(),user='root',password='',dbname='Rueben',host='localhost')
    conn <- poolCheckout(pool)
    #input$responses - refers to the shiny table 
    
    row <- if (input$row %>% db_query_fields(pool,sql,input$responses)) {
      
      input$row
     
    } else {
      showModal(modalDialog(
        title = "Invalid Row name",
        "The selected row must be a row of the DB table",
        easyClose = TRUE, footer = NULL
      ))
      return()
    }
    
    df <- as_data_frame(pool %>% tbl(input$responses) %>% select(row))
    allUniqueVals <- unique(df[[row]])
    results <- lapply(as_list(input$vals), `%in%`, allUniqueVals)
    
    vals <- if (all(results)) {
      if (is(df[["description"]], "string")) input$vals
      else lapply(input$vals, sql_escape_string, con = pool)
    } else {
      showModal(modalDialog(
        title = "Invalid Row values",
        "The selected values do not exist in the selected table row",
        easyClose = TRUE, footer = NULL
      ))
      return()
    }
    
    sql <- paste0("DELETE FROM ?tasks WHERE ", row, " IN (",
                  paste0(vals, collapse = ", "), ");")
    
    query <- sqlInterpolate(pool, sql, table = input$responses)
    
    dbExecute(pool, query)
    
    
    #session$sendCustomMessage("messageBox", paste("Delete process completed" )) 
 })

^{Created on 2019-06-20 by the reprex package (v0.3.0)}

Session info

sessionInfo()
#> R version 3.6.0 (2019-04-26)
#> Platform: x86_64-w64-mingw32/x64 (64-bit)
#> Running under: Windows 10 x64 (build 15063)
#> 
#> Matrix products: default
#> 
#> locale:
#> [1] LC_COLLATE=Swedish_Sweden.1252  LC_CTYPE=Swedish_Sweden.1252   
#> [3] LC_MONETARY=Swedish_Sweden.1252 LC_NUMERIC=C                   
#> [5] LC_TIME=Swedish_Sweden.1252    
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> other attached packages:
#> [1] RMySQL_0.10.17 DBI_1.0.0      dbplyr_1.4.2   dplyr_0.8.1   
#> [5] pool_0.1.4.2   session_1.0.3  shiny_1.3.2   
#> 
#> loaded via a namespace (and not attached):
#>  [1] Rcpp_1.0.1       knitr_1.23       magrittr_1.5     tidyselect_0.2.5
#>  [5] xtable_1.8-4     R6_2.4.0         rlang_0.3.4      stringr_1.4.0   
#>  [9] highr_0.8        tools_3.6.0      xfun_0.7         htmltools_0.3.6 
#> [13] yaml_2.2.0       digest_0.6.19    assertthat_0.2.1 tibble_2.1.3    
#> [17] crayon_1.3.4     purrr_0.3.2      later_0.8.0      promises_1.0.1  
#> [21] glue_1.3.1       evaluate_0.14    mime_0.7         rmarkdown_1.13  
#> [25] stringi_1.4.3    pillar_1.4.1     compiler_3.6.0   httpuv_1.5.1    
#> [29] pkgconfig_2.0.2

Please, has anybody got the solution to this problem? I have attached a picture from my code in rstudio.
Thanks.

[cole]

I haven't had a chance to dig into this, but it is looking like db_query_fields is not happy with the values you are assigning to it.

The pipe there may be part of your problem. It will make input$row the first argument to db_query_fields. Is there a reason you are trying to pipe input$row %>% db_query_fields? You probably want to try something more like:

db_query_fields(pool, ...)

where ... represents other arguments to db_query_fields.

If you are trying to prevent things from happening before input$row is defined, try req(input$row). The req function works magic for Shiny control flow!!

https://shiny.rstudio.com/reference/shiny/latest/req.html

[Rueben]

Hi Cole,

Thanks for your response.

I have started from scratch. I first of all want to make sure that, the code is able to identify the selected row. So, I implemented a similar tutorial as shown in the link below:

https://yihui.shinyapps.io/DT-selection/

I realised that, the selected row number in the table "input$responses", returns null, instead of returning the row number. The "input$responses" table is a parameter in the "db_query_field" function. I used the same approach in selecting the row number as contained in the tutorial.The only difference is that the data in the tutorial is coming from the inbuilt iris dataset into a DT table, and my data is coming from MySQL database and contained in a modal window. I have attached a screenshot of my interface showing that the selected row is returning null, instead of the selected row number as demonstrated in the tutorial. I have also included a reproducible example of my code.

.

 library(shiny)
  library(session)
  library(pool)
  library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
  library(dbplyr)
#> 
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#> 
#>     ident, sql
  library(RMySQL)
#> Loading required package: DBI
  library(shinyalert)
#> 
#> Attaching package: 'shinyalert'
#> The following object is masked from 'package:shiny':
#> 
#>     runExample
 
  #### When delete button is clicked####
  observeEvent(input$deleterow, {
    
   
    #name data table
    dataTableOutput("responses")
    #load data table
    output$responses <- DT::renderDataTable(
      dbReadTable(conn,  "tasks") , rownames = FALSE, editable= TRUE, selection = "single") 
    
    output$selrows <- renderPrint(input$responses_rows_selected)
    
  
  })

^{Created on 2019-06-26 by the reprex package (v0.3.0)}

Please, can you help me know why my selected row is returning null, instead of the selected row number as depicted in the tutorial?

[cole]

So it is a bit hard for me to understand your code, since it looks like your shiny application code is missing? Any chance you can include the Shiny application code as well? I would recommend trying to do this in the body of the Shiny application first, as well, if that is an option - modals can be a bit tricky!

I will say that Shiny unfortunately does not pair well with the reprex package today, although I think that is something the community is working on improving. Also, your example may be simplified if you try to reproduce first with a local data frame, and then move to depending on a database. That way you can focus your efforts on (1) understanding shiny's intricacies and then (2) understanding how the database stuff interacts with the shiny intricacies.

There is the added benefit that a locally defined data frame can be reproduced by someone who does not have access to your database / database structure.