Good day all. I am having same issue described here after deploying shiny app to server, I keep getting same error 'Data source name not found, and no default driver specified'
find below sql odbc connection. sql_script is my input variable. I first created a data source name using an 'odbc data source connection 64 bit' on my computer system before running the code on R console.
However, it worked when I first deployed it but stopped working after a while.
Please how do I go about this?
#SQL_DB_CONNECTION
con=odbcConnect("dsn",uid="xxxxx", pwd="xxxxxxxxxx")
on.exit(odbcClose(con))
sql_db=sqlQuery(con,sql_script)
The error message is telling you that the DSN doesn't exist on the server (only in your local system) so there is no information about the sql server's address or what driver to use, try setting those parameters explicitly instead of using a DSN
please, how can I set the parameters asides using a DSN?
And since you are deploying to shinyapps.io, check out the documentation for deploying an app with a database connection,
https://docs.rstudio.com/shinyapps.io/applications.html#accessing-databases-with-odbc
Hi guys found a useful code snip regarding connecting to sql server on shiny. https://itsalocke.com/blog/connecting-to-sql-server-on-shinyapps.io/
is_local = Sys.getenv('SHINY_PORT') == ""
server = "lockedata.westeurope.cloudapp.azure.com"
database = "datasci"
uid = "example"
pwd = "HBBFSE"
dbConnector <- function(server, database, uid, pwd,
local=TRUE, port=1433, tds_version=7.4){
if(local){
DBI::dbConnect(odbc::odbc(),
driver = "ODBC Driver 13 for SQL Server",
server = server,
database = database,
uid = uid,
pwd = pwd
)
}else{
DBI::dbConnect(odbc::odbc(),
Driver = "FreeTDS",
Database = database,
Uid = uid,
Pwd = pwd,
Server = server,
Port = port,
TDS_Version=tds_version
)
}
}
dbConn <- dbConnector(server, database, uid, pwd, is_local)