CODEWITHSUNDEEP
rsqliteshinytextinputshinydashboard
Alexi Coard
Alexi Coard

Reputation: 7752

R Shiny put content of a TextInput in sql queries

I'm facing a problem with R shiny and sqlite. My app is supposed to authenticate the user and load his/her preferences.

Here is my code :

server.R

library(shiny)
library(shinyBS)
library(dplyr)
library(lubridate)

library(DBI)
library(RSQLite)

############################
#    Database functions    #
###########################

# Connect the user to the dtabase app
connect <- function(userName,pwd){
    #Put a generic path here
  db <- dbConnect(SQLite(), dbname = "my_path/database.db")
  #Query to get the correct passwd
  qry = paste('SELECT password from USERS where name = "',userName,'"')
  res= dbGetQuery(db,qry )

  ifelse(res==pwd,"connected","unable to connect to the database")
  dbDisconnect(db)

}

function(input, output,session) {

  observeEvent(input$connectButton, {
   userName= renderPrint(input$username)
   print(userName)
   userPwd = paste(input$password)
       connect(user = userName,pwd = userPwd)

  })

ui.R

shinyUI(fluidPage(
titlePanel("Authentification"),
textInput('username', label="User name"),
textInput('password', label= "password"),
actionButton("connectButton", label='Connect'),
actionButton("subscribeButton",label='Subscribe')   
)
)

app.R

library(shiny)
library(shinyBS)
####### UI
ui <- source("ui.R")
####### Server
varserver <- source("server.R")
####### APP
shinyApp(ui = ui, server = varserver)

My problem is when I want to put the content of the TextInput for the queries. I've tried several methods

With the current version, renderPrint(input$username) returns me something what seems to be a function but it doesn't seem to be useful.

I also tried an other way using only

userName=paste(input$userName)

This returns me the content of the textField but when i integrate it to the query it puts 

[1] "SELECT password from USERS where name = \" test \""

and then I got the error 

Warning: Error in matrix: length of 'dimnames' [2] not equal to array extent

My objective is to have a query like this 

"Select password FROM USERS where name = "username"

with username representing the content of the TextInput.

EDIT

I know use this version for the query, and it put a syntaxly correct query

qry = paste0('SELECT password from USERS where name = \'',userName,'\'')
res= dbGetQuery(db,qry )

but I face this problem now :

Warning: Error in matrix: length of 'dimnames' [2] not equal to array extent

when I run the method

connect(db,qry)

I think the problem comes from the way i get the content of the TextInput : I use

function(input, output,session) {

  observeEvent(input$connectButton, {
   userName= paste0(input$username)
   userPwd = paste0(input$password)
       connect(user = userName,pwd = userPwd)

  })

What do you think about this ?

Upvotes: 2

Views: 901

Answers (1)

Alexi Coard
Alexi Coard

Reputation: 7752

I found a solution which work

connect <- function(userName,pwd){
    #Put a generic path here
  db <- dbConnect(SQLite(), dbname = "my_path/database.db")
  #Query to get the correct passwd
  qry = paste0("SELECT password from USERS where name = \'",userName,"\'")
  res= dbGetQuery(db,qry )
  res = paste0(res)
  ifelse(res==pwd,print("connected"),print("unable to connect to the database"))
  dbDisconnect(db)
}

I just cast arguments between simple quotes

Upvotes: 1

Related Questions