# ---------------------ui Code -----------------------------
library(shiny)
shinyUI(pageWithSidebar(
headerPanel("Time Analytics"),
sidebarPanel(
dateRangeInput(inputId = "dateRange",
label = "Date range",
start = "2007-09-17",
max = Sys.Date()
)
),#sidebar Panel Ends
# 09-Main Panel ----
mainPanel(
tabsetPanel(id ="theTabs",
tabPanel("Summary", tableOutput("tabi"),textOutput("tabii"))
)
)#Main Panel Ends
))
#------------------Server ----------------------------------
library(shiny);library(sqldf)
library(plyr);library(RODBC)
library(ggplot2)
#Creating the connection
shinyServer(function(input, output, session){ # pass in a session argument
# prep data once and then pass around the program
passData <- reactive({
ch = odbcConnect("Test")
#qry <- "SELECT * FROM Nifty50"
#qry <- cat("SELECT * FROM Nifty50 WHERE Date >= ",as.date(input$dateRange[1])," AND Date <= ",input$dateRange[2])
qry <- paste("SELECT * FROM Nifty50 WHERE Date >= ",input$dateRange[1]," AND Date <= ",input$dateRange[2])
#paste("SELECT * FROM Nifty50 WHERE Date >= ",input$dateRange[1]," AND Date <= ",input$dateRange[2])
subset_Table <- sqlQuery(ch,qry)
odbcClose(ch)
subset_Table <- as.data.frame(subset_Table)
return(subset_Table)
})
output$tabi <- renderDataTable({
d<- as.data.frame(passData())
d
})
output$tabii <- renderText({
paste("Minimium Data :",input$dateRange[1], "Max Date:",input$dateRange[2])
})
# ----------------------------------------------------End
})
Here i am trying to extract data from sql server from Shiny application on Date selection bases which is to and from Date. i have tried with similar code in r studio its working their but when it come to execute in Shiny environment using ODBC package.Thank you
What error message to do you receive when you try and run this in Shiny?
(As an aside: depending on how much data you are extracting from the database, you might run in to problems with passData() being a reactive as whenever one of the input date changes a query will be sent to the database to re-extract the data. It might be better to use eventReactive() instead and trigger the extraction with a button in the UI.)
I am Getting this error i think the Query is not passed appropriately. where i am passing input$dateRange[1] which is starting Date and input$dateRange[2] is the ending Date which is selected from Date range selector from Shiny interface , here i am unable to subset the data as per inputRange. But when i query SELECT * FROM Nifty50 i am able to fetch complete Table in Shiny environment , plz guide me on this. i dont know may be i am wrong at paste command.
From the looks of things it is the query itself that's causing problems. A couple of things to check:
Does your database expect SQL statements to be terminated with a ';' (some databases do if I remember correctly, whereas others aren't as fussy)?
Does your database support automatic conversion of data types? You're giving the database two strings and telling it to use those on a date field. You may need to explicitly convert the dates to a SQL date type in the where clause (e.g. using CAST(), CONVERT(), or your database-specific type conversion)
What is the actual value of the query string that is passed to the database? In your image it looks like you are sometimes passing it NA dates. NA is not a SQL term (most databases use NULL for missing) so I'm not sure how it will handle the NA value (it's likely it will cause an error).
You might also need to explicitly add the ' values around the dates when you pass them in, at the moment it looks like you're not, and I expect that the database is struggling to parse the query because of that. Try:
qry <- paste("SELECT * FROM Nifty50 WHERE Date >= '", input$dateRange[1], "' AND Date <= '", input$dateRange[2], "'", sep = "")
Alternatively rather than paste() you could use glue() from the glue packge:
qry <- glue::glue("select * from nifty50 where date >= '{input$dateRange[1]}' and date <= '{input$dateRange[2]}'")
(It looks like the GitHub version has a specific function glue_sql() that should serve your purpose well, but it's not in the CRAN version yet).
I think the approach would be the same, you'd just have to make sure that the data field had the appropriate type (in SQL I think that's datetime) and then pass the inputs in the same way, making sure they are formatted so that they are correctly interpreted as datetime by the database.
e.g.
query <- glue::glue("select * from some_table where some_datetime_field >= '{some_r_datetime_variable}'")
To make sure that the R variable gets correctly interpreted as a SQL datetime, you could add explicit type-conversion in the query:
query <- glue::glue("select * from some_table where some_datetime_field >= cast('{some_r_datetime_variable}' as datetime)")
Ah. Quoting the date string was what I was missing when I looked at this before. Another way to approach this is to avoid quoting strings altogether using a parameterized query. To do this, you could use
library(RODBCext)
query <- "SELECT * FROM Nifty50 WHERE Date BETWEEN ? AND ?"
sqlExecute(channel,
query = query,
data = list(format(input$dateRange[1], format = "%Y-%m-%d"),
format(input$dateRange[2], format = "%Y-%m-%d")),
fetch = TRUE,
stringsAsFactors = FALSE)
It might be a little more typing, but between the avoidance of SQL Injection and the elimination of having to quote strings and dates, I've found this pattern to be extremely useful.
That's a nice option, too. I think I prefer the approach in glue::glue_sql() as the parameters are named, which makes the query a bit easier to read (I can already see myself getting lost with the ? values in the query!).
E.g. see the example from the GitHub readme:
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
colnames(iris) <- gsub("[.]", "_", tolower(colnames(iris)))
DBI::dbWriteTable(con, "iris", iris)
var <- "sepal_width"
tbl <- "iris"
num <- 2
val <- "setosa"
glue_sql("
SELECT {`var`}
FROM {`tbl`}
WHERE {`tbl`}.sepal_length > {num}
AND {`tbl`}.species = {val}
", .con = con)
#> <SQL> SELECT `sepal_width`
#> FROM `iris`
#> WHERE `iris`.sepal_length > 2
#> AND `iris`.species = 'setosa'
# `glue_sql()` can be used in conjuction with parameterized queries using
# `DBI::dbBind()` to provide protection for SQL Injection attacks
sql <- glue_sql("
SELECT {`var`}
FROM {`tbl`}
WHERE {`tbl`}.sepal_length > ?
", .con = con)
query <- DBI::dbSendQuery(con, sql)
DBI::dbBind(query, list(num))
DBI::dbFetch(query, n = 4)
#> sepal_width
#> 1 3.5
#> 2 3.0
#> 3 3.2
#> 4 3.1
DBI::dbClearResult(query)
I would agree that the DBI style parameterized query is easier to read. I haven't been able to get too familiar with it yet, unfortunately. Mostly because it wasn't available until earlier this year (May, I believe), and I've needed the parameterized queries longer than that. More crucially, however, like Nabi, I use SQL Server, and I'm not satisfied with the RSqlServer package. RSqlServer also depends on rJava, which has been a nightmare for me to try and manage in the past, so I've pretty much given up on it.
I've looked at dbplyr. It still requires a DBI back end (see my previous post). I'm also not sure that it is parameterized. If it is susceptible to SQL injection attacks, I won't use it (and can't, by cyber security policy). But I didn't continue researching that susceptibility after determining that I didn't have a satisfactory back end to connect.
I have question whether DBI or RSQlite Package works with SQL Server in my case it is working with RODBC package.And any other examples with DBI and SQL Server connectivity Data fetching would helpful for Learning. Thank you guys.
@martin.R Thank you for Sharing , how can we fetch the data from SQL Server because i have seen example with MY SQL and others but i have not seen any example related to SQL Server Connection. Thank you
For me the advantage has been in being able to nest multiple joins and formulate quite complicated queries, but without writing the SQL strings because dbplyr writes it for you (check it via show_query()).
This may not be suitable for a production system (not sure), but for me it has transformed my work.
Okay, I'm sold. I'll still probably do a fair amount of work in SQL directly (defining views for really complex queries), but the real thorn in my side has been the SELECT * FROM table WHERE x IN (1, 2, 3) construct. RODBCext doesn't do IN very well, so I either had to let it loop over each value (effective, but when each query is somewhere from 5 - 20 seconds, it can take a while), or write a stored procedure to build the query correctly (this is just miserable).
Hi, another advantage of using the odbc package, combined with the DBI package, is that if you're using the RStudio IDE, you'll be able to preview your MS SQL tables in the new Connections pane, which is available in RStudio v1.1 and above: http://db.rstudio.com/rstudio/connections. Regarding security, we have a Best Practices article in the site that discusses ways to prevent SQL Injections here: http://db.rstudio.com/best-practices/run-queries-safely .
