0

I am new to Shiny and R both. I am trying to capture input value from Shiny UI part and based on the value, I am filtering the records from dataframe. I am using this code:

dat <- sqldf("select * from dat where CMPNT_NM = 'input$Compound'")

It's not returning any rows, just column name is coming. If I give any hardcoded value like mP, then it's running perfectly.

Any idea if I am doing something wrong? Thanks in advance.

Blo
  • 11,903
  • 5
  • 45
  • 99
Piyush
  • 1,571
  • 4
  • 14
  • 21
  • I would not have guessed that `CMPNT_NM = 'input$Compound'` would have delivered the right message to the SQL driver. Is that really a valid value in the database column's entries? It looks more like R code which I would not expect to be residing in the database. – IRTFM May 16 '14 at 01:07
  • Thanks Bonde!Yes, this is R code. I am receiving the compund name from user interface (ui.R). I don't want to pass any hard code value. I am receiving the value from the following code: output$choose_Compound <- renderUI({ selectInput("Compound", "Compound_List", as.list(Compound2)) }) And then I am filtering the record from dataframe with user input value. Any help will be greatly appreciated. Piyush – Piyush May 16 '14 at 02:28
  • output$choose_Compound <- renderUI({ selectInput("Compound", "Compound_List", as.list(Compound2)) }) I am trying to pass user input value in sqlquery. – Piyush May 16 '14 at 02:37

1 Answers1

0

To get interpretation of something passed as an R value you should use a function that will ... evaluate it. Try:

dat <- sqldf( paste0( "select * from dat where CMPNT_NM = ", input$Compound) )
IRTFM
  • 258,963
  • 21
  • 364
  • 487
  • Thanks a lot! It worked very closely. But not 100%. Actually its giving following message :----> "Error: RS-DBI driver: (error in statement: no such column: Insulin)". Actually its taking the value of "input$Compound" as column name. We want to take this value as data value of column "CMPNT_NM" – Piyush May 16 '14 at 03:20
  • I want to do like CMPNT_NM='mP' but I want to pass the value of "CMPNT_NM" from "input$Compound". – Piyush May 16 '14 at 03:29
  • Hi Bond, Thanks a lot for your help. This gives a nice help to proceed. I used the below line and it resolved the problem. I used "fn$sqldf" ======> dat <- fn$sqldf( paste0( "select * from dat where CMPNT_NM = '$test1'") ) . Thanks again for your time. – Piyush May 16 '14 at 03:52
  • dat <- fn$sqldf( paste0( "select * from dat where CMPNT_NM = '$test1'") ) --- Worked fine for this issue. – Piyush May 16 '14 at 03:54
  • `paste` is not needed: `fn$sqldf("select * from dat where COMPNT_NM = '$test' ")` is sufficient. – G. Grothendieck May 16 '14 at 18:42
  • 1
    I hope I'm correct in thinking that the `paste` is not needed but only because he is using `pkg:gsubfn`. The original question did not reference that package, and that is why I am leaving my answer as it was. I think it is still needed if `fn$...` is not used. – IRTFM May 16 '14 at 21:42
  • The question referenced sqldf and sqldf automatically pulls in gsubfn. Read the sqldf where examples are shown. – G. Grothendieck May 19 '14 at 09:06
  • Someone downvoted this answer. Was it incorrect? My question to G.Grothendieck was not directly answered. (I do understand that fn$sqldf(...) would have succeeded because gsubfn is loaded when sqldf is loaded.) – IRTFM May 20 '14 at 21:16