R Studio Shiny Server - $ operator is invalid when using SQL scripts

1
votes

Anyone run in to his problem? I have a shiny app that uses SQL files to import data from MS SQL server using the RODBC package. I've narrowed down the problem to here in the server.R file:

ch <- odbcConnect(dsn = xxxxxx)
iQry <- readChar("LeaderDashInd.sql", file.info("LeaderDashInd.sql")$size, T)
oQry <- readChar("LeaderDashOrg.sql", file.info("LeaderDashOrg.sql")$size, T)
iDat <- sqlQuery(channel = ch, query = iQry, stringsAsFactors = F)
oDat <- sqlQuery(channel = ch, query = oQry, stringsAsFactors = F)
odbcClose(ch)

# PREPROCESSING --------------------------
cy <- max(iDat$CampYear)

The app stops at the last line above and gives... Error in iDat$CampYear : $ operator is invalid for atomic vectors. I know this chunk is the problem because when I have the same app run off imported csv files, it works.

A couple things to note:

  1. This code runs first in the server.R file outside the shinyServer function.
  2. The app runs fine when launched from my workstation through R Studio. It only stops working when run on our Shiny Server installation.
  3. Shiny packages are up to date and shiny server is a recent install.

Any thoughts?

1
rshinyshiny-serverrodbc
What is class(iDat)? What type of query are you running exactly? Doesn't sound like you are getting a data.frame back. Maybe you are getting an error. - MrFlick
If it works on your workstation but not on the server, it makes me think that "LeaderDashInd.sql" may not be in the working directory on the server. - Benjamin
iDat is a data frame. But yes, on the server version it might not be for some inexplicable reason. Based on some other troubleshooting I've been doing it seem like this is the crux of the problem... Not importing as a data frame. Yes SQL files are in the wd on the server. - jtdoud

1 Answers

0
votes

FYI... changing the SQL query to a stored procedure and then calling the stored procedure with RODBC::sqlQuery fixed the problem. The $ operator invalid error was coming up because the query was not running and returning an empty vector.

Still doesn't explain why the stand alone query using readChar doesn't work in Shiny Server but hey... its a fix.