Database Error in Shiny

Dear All,

I connected a DB with this code:

con <- DBI::dbConnect(odbc::odbc(), Driver = "ODBC Driver 17 for SQL Server", 
    Database = "***", Server = "***", UID = "***", 
    PWD = "****", Port = 1433)

and I created a DF with dbGetQuery.

When I deploy this app to Shiny, I get this error:

Warning: Error in : nanodbc/nanodbc.cpp:983: 00000: [unixODBC][Driver Manager]Data source name not found, and no default driver specified

This might be relevant:

Hi,

I use this method but it is not the solution...

If by "deploy to shiny" you mean publishing to shinyapps.io, this specific driver is not supported, look here for supported database drivers

https://docs.rstudio.com/shinyapps.io/applications.html

I tried to connect with "SQL Server" as an ODBC driver and I connected to DB, but the error still persists.

con <- DBI::dbConnect(odbc::odbc(), Driver = "SQL Server", 
    Database = "***", Server = "***", UID = "***", 
    PWD = "****", Port = 5432)

Try SQLServer without the space ?

There is no driver whose name is SQLServer driver in odbcListDrivers().

Is that the case when you run it locally, or on shinyapps.io, or both?

It runs in local.

When I publish app to Shiny, I get the error that you can find in below:

I'm asking about the odbc drivers listed though

Installed ODBC drivers are:

  • ODBC Driver 17 for SQL Server
  • PostgreSQL Driver
  • SQLite Driver
  • SQL Server Driver

When I run this code:

dbConnect(odbc::odbc(),
          Driver = "PostgreSQL Driver",
          Database = "***", 
          UID = "****",
          PWD = "*****",
          Server = "178.1*****",
          Port = 5432)

I get this error:

nanodbc/nanodbc.cpp:983: 00000: 

You haven't answered whether those 4 are listed for you locally , or also on shinyapps.io
It might be worth writing a trivial shinyapp that gets the list of available drivers in it's environment and displays it in a textOutput

I made the following shiny app

library(shiny)
library(odbc)

ui <- fluidPage( verbatimTextOutput("txtdrv"))

server <- function(input, output) {
  output$txtdrv <- renderPrint({
        odbcListDrivers()
  })
  }
shinyApp(ui = ui, server = server)

running it locally on my machine gives:

name        attribute value
1 SQL Server         APILevel     2
2 SQL Server ConnectFunctions   YYY
3 SQL Server        CPTimeout    60
4 SQL Server    DriverODBCVer 03.50
5 SQL Server        FileUsage     0
6 SQL Server         SQLLevel     1
7 SQL Server       UsageCount     1

I published it here
https://nguk.shinyapps.io/odbcdrivertest/

name       attribute
1                 SQLite     Description
2                 SQLite          Driver
3                 SQLite           Setup
4                 SQLite      UsageCount
5                SQLite3     Description
6                SQLite3          Driver
7                SQLite3           Setup
8                SQLite3      UsageCount
9        PostgreSQL ANSI     Description
10       PostgreSQL ANSI          Driver
11       PostgreSQL ANSI           Setup
12       PostgreSQL ANSI           Debug
13       PostgreSQL ANSI         CommLog
14       PostgreSQL ANSI      UsageCount
15    PostgreSQL Unicode     Description
16    PostgreSQL Unicode          Driver
17    PostgreSQL Unicode           Setup
18    PostgreSQL Unicode           Debug
19    PostgreSQL Unicode         CommLog
20    PostgreSQL Unicode      UsageCount
21               FreeTDS     Description
22               FreeTDS          Driver
23               FreeTDS           Setup
24               FreeTDS       CPTimeout
25               FreeTDS         CPReuse
26               FreeTDS      UsageCount
27                 MySQL          Driver
28                 MySQL      UsageCount
29 MySQL ODBC 8.0 Driver          Driver
30 MySQL ODBC 8.0 Driver      UsageCount
31                Athena          Driver
32                Athena RStudio_Version
33                Athena   Simba_Version
34                Athena       Installer
35              BigQuery          Driver
36              BigQuery RStudio_Version
37              BigQuery   Simba_Version
38              BigQuery       Installer
39             Cassandra          Driver
40             Cassandra RStudio_Version
41             Cassandra   Simba_Version
42             Cassandra       Installer
43               MongoDB          Driver
44               MongoDB RStudio_Version
45               MongoDB   Simba_Version
46               MongoDB       Installer
47                 MySQL          Driver
48                 MySQL      UsageCount
49               Netezza          Driver
50               Netezza RStudio_Version
51               Netezza   Simba_Version
52               Netezza       Installer
53              Redshift          Driver
54              Redshift RStudio_Version
55              Redshift   Simba_Version
56              Redshift       Installer
57        AmazonRedshift          Driver
58                  Hive          Driver
59                Impala          Driver
60                Oracle          Driver
61            PostgreSQL          Driver
62            Salesforce          Driver
63             SQLServer          Driver
64              Teradata          Driver
value
1                                                         SQLite ODBC Driver
2                                                           libsqliteodbc.so
3                                                           libsqliteodbc.so
4                                                                          1
5                                                        SQLite3 ODBC Driver
6                                                          libsqlite3odbc.so
7                                                          libsqlite3odbc.so
8                                                                          1
9                                      PostgreSQL ODBC driver (ANSI version)
10                                                              psqlodbca.so
11                                                           libodbcpsqlS.so
12                                                                         0
13                                                                         1
14                                                                         1
15                                  PostgreSQL ODBC driver (Unicode version)
16                                                              psqlodbcw.so
17                                                           libodbcpsqlS.so
18                                                                         0
19                                                                         1
20                                                                         1
21                                                TDS driver (Sybase/MS SQL)
22                                                             libtdsodbc.so
23                                                                libtdsS.so
24                                                                      <NA>
  25                                                                      <NA>
  26                                                                         1
27                                             /usr/local/lib/libmyodbc8w.so
28                                                                         1
29                                             /usr/local/lib/libmyodbc8w.so
30                                                                         1
31                 /opt/rstudio-drivers/athena/bin/lib/libathenaodbc_sb64.so
32                                                                     1.6.1
33                                                                1.0.2.1003
34                                                       RStudio Pro Drivers
35       /opt/rstudio-drivers/bigquery/bin/lib/libgooglebigqueryodbc_sb64.so
36                                                                     1.6.1
37                                                               2.1.21.1026
38                                                       RStudio Pro Drivers
39           /opt/rstudio-drivers/cassandra/bin/lib/libcassandraodbc_sb64.so
40                                                                     1.6.1
41                                                                2.5.5.1010
42                                                       RStudio Pro Drivers
43               /opt/rstudio-drivers/mongodb/bin/lib/libmongodbodbc_sb64.so
44                                                                     1.6.1
45                                                                2.3.0.1000
46                                                       RStudio Pro Drivers
47                                             /usr/local/lib/libmyodbc8w.so
48                                                                         1
49               /opt/rstudio-drivers/netezza/bin/lib/libnetezzaodbc_sb64.so
50                                                                     1.6.1
51                                                                1.0.5.1002
52                                                       RStudio Pro Drivers
53       /opt/rstudio-drivers/redshift/bin/lib/libamazonredshiftodbc_sb64.so
54                                                                     1.6.1
55                                                                1.4.3.1000
56                                                       RStudio Pro Drivers
57 /opt/rstudio-drivers/amazonredshift/bin/lib/libamazonredshiftodbc_sb64.so
58                     /opt/rstudio-drivers/hive/bin/lib/libhiveodbc_sb64.so
59                 /opt/rstudio-drivers/impala/bin/lib/libimpalaodbc_sb64.so
60                 /opt/rstudio-drivers/oracle/bin/lib/liboracleodbc_sb64.so
61         /opt/rstudio-drivers/postgresql/bin/lib/libpostgresqlodbc_sb64.so
62         /opt/rstudio-drivers/salesforce/bin/lib/libsalesforceodbc_sb64.so
63           /opt/rstudio-drivers/sqlserver/bin/lib/libsqlserverodbc_sb64.so
64                   /opt/rstudio-drivers/teradata/bin/lib/tdataodbc_sb64.so

I ran your code and I got this output:

                            name        attribute value
1                     SQL Server         APILevel     2
2                     SQL Server ConnectFunctions   YYY
3                     SQL Server        CPTimeout    60
4                     SQL Server    DriverODBCVer 03.50
5                     SQL Server        FileUsage     0
6                     SQL Server         SQLLevel     1
7                     SQL Server       UsageCount     1
8  SQL Server Native Client 11.0       UsageCount     1
9  SQL Server Native Client 11.0         APILevel     2
10 SQL Server Native Client 11.0 ConnectFunctions   YYY
11 SQL Server Native Client 11.0        CPTimeout    60
12 SQL Server Native Client 11.0    DriverODBCVer 03.80
13 SQL Server Native Client 11.0        FileUsage     0
14 SQL Server Native Client 11.0         SQLLevel     1
15 ODBC Driver 13 for SQL Server       UsageCount     1
16 ODBC Driver 13 for SQL Server         APILevel     2
17 ODBC Driver 13 for SQL Server ConnectFunctions   YYY
18 ODBC Driver 13 for SQL Server        CPTimeout    60
19 ODBC Driver 13 for SQL Server    DriverODBCVer 03.80
20 ODBC Driver 13 for SQL Server        FileUsage     0
21 ODBC Driver 13 for SQL Server         SQLLevel     1

I can make queries with SQL Server but it does not work when I publish my app to Shinyapps.io.

yes, because what you have on your local computer is not what they have on their computer that you want to use. So try to pick one that they have ...

How I will download Athena or other drivers that shiny use? I think if I can download these drivers, I can publish my app on Shiny.

https://db.rstudio.com/databases/athena/

I downloaded Athena,MongoDB and MySQL driver but I can't see in odbc drivers list..

After downloading, did you install?

Yes, of course but I can't see in list :confused: