Problem connecting to database

shiny shinyapps.io
,
#1

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)

Database Error in Shiny
#2

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

#3

please, how can I set the parameters asides using a DSN?

#4

https://db.rstudio.com/getting-started/connect-to-database

#5

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

#6

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)