Shiny Application hangs when call some data from database.. puzzled

Question

I have below 3 files (Retrieve_AOI_Utilization.R, ui.R, server.R)

# Retrieve_AOI_Utilization.R
library(lubridate)
library(RODBC)
myconn<- odbcConnect("pfods", uid = "testingt", pwd = "****")

systemtype <- '0043-004'
startDate <- "08/12/2014" # DD/MM/YYYY format
endDate <- "11/12/2014" # DD/MM/YYYY format
TimeDiff <- as.Date(as.character(endDate), format="%d/%m/%Y")- as.Date(as.character(startDate),   format="%d/%m/%Y")
TimeDiff <-as.data.frame(TimeDiff)
nDays <- TimeDiff$TimeDiff[[1]]

conveyortime <- 25
querytest <- paste("SELECT distinct MONO, LASTUPDATE, SYSTEMTYPE, TESTTIME 
FROM PFODS.PPLPRODUCTAOI 
WHERE SYSTEMTYPE = '",systemtype,"'
AND LASTUPDATE >= todate('",startDate,"','DD/MM/YYYY')
AND LASTUPDATE <= todate('",endDate,"','DD/MM/YYYY') 
AND TESTTIME IS NOT NULL
ORDER BY LASTUPDATE ASC, MONO" , sep="") 
test <- sqlQuery(myconn, query_test)

testtime <- test$TESTTIME
HourMinSec <-strftime(testtime, format="%H:%M:%S")
TotalTimeInSec <- periodtoseconds(hms(HourMinSec)) # convert to total seconds
Utilization = (sum(TotalTimeInSec) + nrow(test)conveyor_time)/ (nDays24*3600) *100

# ui.R
shinyUI(fluidPage(
titlePanel("TestSystem Utilization for AOI Machines in SMT."),

sidebarLayout(
sidebarPanel(
helpText("Select a TestSystem and Date Range and press Submit button to retrieve its Utilization      value."),

selectInput("var", 
          label = "Select a TestSystem",
          choices = list("0043-001","0043-002","0043-003","0043-004","0043-A067-001","0043-A067-  003"),
          selected = "0043-001")
 ),

mainPanel(
  textOutput("text1")
 )
)
))

# server.R

Utilization <- source('Retrieve_AOI_Utilization.R')

shinyServer(
function(input, output) {

#Utilization <- 50
specify_decimal <- function(x, k) format(round(x, k), nsmall=k)
output$text1 <- renderText({ 
   paste("TestSystem", input$var, "has Utilization value of", specify_decimal(Utilization$value, 2),"%")
 })
}
)

If I retrieve Utilization value directly from Utilization <- 50, the application runs perfectly OK in Shiny Server.

I see that in the localhost, it is working, when the Utilization value is retrieved from "Utilization <- source('Retrieve_AOI_Utilization.R')" , see printscreen below: http://imgur.com/8h24p5h

But if I retrieve Utilization value from source('Retrieve_AOI_Utilization.R'), and deployed to the Shiny server, the application hangs with a grey screen, as seen below: http://imgur.com/BcqwMfb

Why is this so? Please help.

Hamilton Blake · Answer 1 · 2015-01-18T16:26:45.193

0

Firstly, do you have two different files: Retrieve_AOI_Utilization.R and RetrieveAOIUtilization.R? Are you using both on purpose, or are you supposed to be using one? Because you have only shown the code for the latter one.

Secondly, if you press F12 on your browser when it crashes on shiny server, and go to the "Console" tab, the line of your R code which breaks could be displayed there. You can debug from this point.

Edit

You have the following connection:

myconn<- odbcConnect("pfods", uid = "testingt", pwd = "****")

Are you certain that you can connect to the pfods ODBC connector from where you are hosting shiny-server? Be sure to have the identical ODBC, database, username and password.

edited Jan 18 '15 at 16:26

answered Jan 15 '15 at 13:29

Hamilton Blake

622
1
6
19

there is only one Retrieve_AOI_Utilization.R file. I have corrected my question. My typo error, apologize. – THIAM HUAT Tan Jan 16 '15 at 00:53
there is only one Retrieve_AOI_Utilization.R file. I have corrected my question. My typo error, apologize. I follow your steps of press F12 on the browser when it crashes, but the Console displays blank, as seen from http://imgur.com/k8S7LSx – THIAM HUAT Tan Jan 16 '15 at 04:07
yes, the issue is database connectivity over internet.. over in localhost it is ok...I try another with MySQL, and "grant all privileges on *.* to 'test'@'%' IDENTIFIED BY 'PASSWORD'".. it has the same issue too... – THIAM HUAT Tan Jan 19 '15 at 08:06
You need to set up the ODBC connector to use IP address of the host server if it is over the internet. See [here](http://stackoverflow.com/questions/15420999/rodbc-odbcdriverconnect-connection-error) how to use an IP in odbcConnect() . – Hamilton Blake Jan 19 '15 at 23:09

Shiny Application hangs when call some data from database.. puzzled

1 Answers1