0

I have a Shiny app that has a checkbox group input. The user can select multiple inputs. I also have an ODBC connection linked to a database. The process would be that when a user selects items from the check box group, that user input would be part of a string in the sql query to filter the data.

UI.R (partial to show example)

checkboxGroupInput('Type', 'Type', c(
                          "AX"="AX",
                          "AY"="AY",
                          "AZ"="AZ",
                          "BGB"="BGB",
                          "BT"="BT",
                          "BX"="BX",
                          "BXT"="BXT",
                          "C"="C",
                          "CNT"="CNT")),

The column in the table where the "Type" information is in is called COMPONENT, so my sql query using RODBC is

data <- odbcConnect("database", uid="username", pwd="password")
query <- (SELECT ID, NAME, TYPE FROM COMPONENT WHERE TYPE LIKE Input$Type)
df <- odbcQuery(data, query)

The query line would not work, but I have no idea how to take multiple inputs and place them properly in the query. Also, there is an added level of complexity that I am not sure how to handle. The data in the database is alpha numeric, so instead of AX, it might be listed as AX14 or AX 71. Also, because there are some one letter types, using a wildcard seems a little difficult.

GK89
  • 646
  • 5
  • 29
  • What kind of database are you connecting to? – Matthew Plourde Aug 07 '14 at 18:09
  • @MatthewPlourde It's an Oracle database. It's a 32 bit data source and I'm running on a 64-bit machine. DSN is configured correctly as I was a able to make simply queries in the console – GK89 Aug 07 '14 at 18:12
  • 1
    Take a look at the answer to [this question](http://stackoverflow.com/questions/1387612/how-can-i-introduce-multiple-conditions-in-like-operator). – Matthew Plourde Aug 07 '14 at 18:15
  • @MatthewPlourde The regexp looks promising. I'll post a solution once I work it out. thanks – GK89 Aug 07 '14 at 18:36

1 Answers1

1

To answer your initial question regarding "multiple inputs in the query", I use concatenation to achieve this.

Using paste0(), I write something as follows:

type = "AX14"
myQuery <- paste0("Select variable1, variable2 from my_table where type like ",type)
myQuery
[1] "Select variable1, variable2 from my_table where type like AX14"

You can add little things like single quotes or wildcard operators as follows:

myQuery <- paste0("Select variable1, variable2 from my_table where type like '%",type,"%'")
myQuery
[1] "Select variable1, variable2 from my_table where type like '%AX14%'"

Then proceed with actually running the query:

df <- odbcQuery(data, myQuery)

Brian Correro
  • 143
  • 1
  • 1
  • 8