I’m trying to fetch data from MS SQL server in a shiny app. I’m using the odbc package version 1.2.2.
When I run the app locally I am able to fetch the data and everything is fine. But on the server I have the following error :
Listening on http://127.0.0.1:5823
Warning in connection_release(conn@ptr) :
There is a result object still in use.
The connection will be automatically released when it is closed
Warning: Error in : <SQL> 'select Parameter.name,ParamValue.value
from Parameter
inner join ParamValue on Parameter.id = ParamValue.parameterId
where Parameter.name = 'airDensity'order by Parameter.name'
nanodbc/nanodbc.cpp:1587: 42000: [FreeTDS][SQL Server]Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.
81: <Anonymous>
The following is a short version of my app.
library(shiny)
ui <- fluidPage(
withMathJax(),
tabsetPanel(
tabPanel("Help Page",
h2(HTML("<ul> <b>How to use the tool</b></ul>"))
),
tabPanel("LCA",
dashboardPage(skin = "green",
dashboardHeader(title = "LCA Database"),
dashboardSidebar(
tags$a(href = "https://www.xxxxxxxxx.com/en-int", tags$img(src='SG_logo_neg_rgb.png', height = 70, width = 230)),
tags$hr(),
selectInput("t1","Select Parameter", choices = c("Param")),
selectInput("t2","Select Parameter", choices = c("-"))
),
dashboardBody(
)
)
)
)
)
and the server :
library(shiny)
library(odbc)
library(dbplyr)
server <- function(input, output, session){
main <- reactive({
con <- dbConnect(odbc(),
Driver = "libtdsodbc.so",
Server = "xxxxx.xxxxxx.xxxxx",
Database = "LCA",
UID = "LCA-User",
PWD = "xxxxxxxxxxxxxx",
encoding = "windows-1252",
Port = 1433)
on.exit(odbc::dbDisconnect(con))
squery = dbSendQuery(con,"select name from Parameter order by name",stringsAsFactors = FALSE)
parameterNmaes = dbFetch(squery )
result= list(parameterNmaes = parameterNmaes)
return(result)
})
# updating the first dropdown
observeEvent(main(),{
updateSelectInput(session, "t1",
choices = main()$parameterNmaes)
})
# second dropdown based on user selection
subselect1 <- reactive({
con <- dbConnect(odbc(),
Driver = "libtdsodbc.so",
Server = "xxxx.xxxxx.xxxx",
Database = "LCA",
UID = "LCA-User",
PWD = "xxxxxxxxxxxxxx",
encoding = "windows-1252",
Port = 1433)
on.exit(odbc::dbDisconnect(con))
squery = dbSendQuery(con,paste0("select Parameter.name,ParamValue.value
from Parameter
inner join ParamValue on Parameter.id = ParamValue.parameterId
where Parameter.name = '",input$t1,"'order by Parameter.name"),stringsAsFactors = FALSE)
p = dbFetch(squery )
p1 = p %>%
mutate(deserial = lapply(p[,2], mType))%>%
mutate(numericValue = sapply(lapply(deserial, deserialize),`[`, "v"))
res = sort(as.character(unique(p1$numericValue)))
result= list(res = res, p1 = p1)
return(result)
})
observeEvent(subselect1(),{
updateSelectInput(session, "t2",
choices = subselect1()$res)
})
}
Advertisement
Answer
You could try odbc::odbcListDrivers() , I my case I have :
odbc::odbcListDrivers()
name attribute value
1 PostgreSQL Description ODBC for PostgreSQL
2 PostgreSQL Driver /usr/lib/psqlodbcw.so
3 PostgreSQL Setup /usr/lib/libodbcpsqlS.so
4 PostgreSQL Driver64 /usr/lib64/psqlodbcw.so
5 PostgreSQL Setup64 /usr/lib64/libodbcpsqlS.so
6 PostgreSQL FileUsage 1
7 MySQL Description ODBC for MySQL
8 MySQL Driver /usr/lib/libmyodbc5.so
9 MySQL Setup /usr/lib/libodbcmyS.so
10 MySQL Driver64 /usr/lib64/libmyodbc5.so
11 MySQL Setup64 /usr/lib64/libodbcmyS.so
12 MySQL FileUsage 1
13 FreeTDS Description FreeTDS for MSSQL
14 FreeTDS Driver /usr/lib64/libtdsodbc.so
15 FreeTDS Setup /usr/lib64/libtdsS.so
16 FreeTDS Driver64 /usr/lib64/libtdsodbc.so
17 FreeTDS Setup64 /usr/lib64/libtdsS.so
18 FreeTDS FileUsage 1
19 FreeTDS UsageCount 1
20 MSODBC Description Microsoft ODBC Driver 13 for SQL Server
21 MSODBC Driver /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.1
22 MSODBC UsageCount
So then setting driver = "MSODBC " should solve the issue !