4

I have an R shiny application that works locally but when I upload it I get the error:

 first argument is not an open RODBC channel

Here is my code:

conn<- suppressWarnings( odbcDriverConnect('driver={SQL Server};server=SWDCH;    database=GR;    trusted_connection=yes;uid=RUser , pwd=ruser'))
  data <- sqlQuery(conn,paste("exec Performance  @MetricType = '", MetricType ,"',@Metric = '", Metric ,"',   @Market = '", Market  , "', @StartDate = '", StartDate, "', @EndDate = '", EndDate, "';",sep =""),errors=FALSE)

I think the issue is with the conn object but any idea what the issue is?

user3022875
  • 8,598
  • 26
  • 103
  • 167
  • any luck on fixing this? – collarblind Oct 24 '15 at 03:38
  • I had a Shiny app running for months using RODBC and Teradata. Today, it stopped working and I only get this error in app, but it works on my desktop. I tried the below solution from Brian Correro, but no luck. – hubbs5 Feb 21 '17 at 10:42

2 Answers2

1

I was having an identical issue that is now solved on my end. Let me know if this works for you.

The problem appears to be the "default" connection that is referenced through Shiny. I was running the following code that works locally but gave me the same "RODBC connection not open" error as you

Does not work in Shiny:
tdConnection2 <- odbcConnect("my_dsn", eid = "ABC123", pw = "Password)
results <- sqlQuery("Run This SQL")

I was able to fix everything by explicitly referencing which channel was being referenced in the tdQuery() function as follows

Does work in Shiny:
tdConnection2 <- odbcConnect("my_dsn", eid = "ABC123", pw = "Password)
results <- sqlQuery(channel = tdConnection2, query = "Run This SQL")

I diagnosed this by doing things like print(tdConnection2) which showed me an initial connection was successfully being made. My rPython was not working because of the default version of python that R was referencing so made me think about the default version/drivers that Shiny may be referencing as well. Hope this solves your issue as well.

Brian Correro
  • 143
  • 1
  • 1
  • 8
  • 1
    outstanding catch! Thanks so much! Necesarily, this must mean that there's somehow some other parameter being passed transpaently? Or what could be going on here? I feel this should be a reportable bug. Have you done so? – Amit Kohli Mar 10 '17 at 16:17
0

create sqlserver ODBC connection in PC, ODBC Data Souces. Use below code snippet to connect and store data into reactive variable.

 data <- reactive({
    ch <- odbcConnect(dsn = "DSNName", uid = "username", pwd = "password")
 sqlQuery(ch,paste('select  * from emp ')
  })

DSNName is the name of sql server ODBC created.

Rahul Mishra
  • 224
  • 2
  • 4