odbcConnect in R - how to extract dsn file information?

Question

HI I am using R and want to save the table views in ch but it does not work

I have the dsn file saved on a directory called dsnfiles:

ch <- odbcConnect("S:/dsnfiles/databasex.dsn")
sqlTables(ch)

Please can you help me? This code does not work.

Thanks

A

Answer 1

DSN files are generally stored on the system level, so usually not arbitrary files like that. I believe that the odbc package supports arbitrary files on macos and linux (not windows), but I haven't tested this with RODBC. Having said that, it's not hard to parse that file and get what you need.

For my configuration, I have a SQL Server instance running on my local host, port 21433.

I've created a DSN file named "~/StackOverflow/14549856/somedatabase.dsn" with these contents:

[ODBC]  
DRIVER=ODBC Driver 17 for SQL Server
TrustServerCertificate=Yes
DATABASE=mydbname
WSID=d2sb2
SERVER=127.0.0.1,21433
UID=myusername
PWD=mypassword

(replacing uid/pwd as appropriate).

I'm assuming that since you're just referencing a single file with no other qualifications, that the entry you need ([ODBC] in my example) is the only entry in the file, so I'll run with that. If you have any malformed entries, empty lines, or comments in that file, you may need to do a little more with the connection string formatting here.

So let's convert this file into a "connection string" (here's a good reference for these strings: https://www.connectionstrings.com/).

connstr <- paste(trimws(readLines("~/StackOverflow/14549856/somedatabase.dsn")[-1]), collapse = ";")
connstr
# [1] "DRIVER=ODBC Driver 17 for SQL Server;TrustServerCertificate=Yes;DATABASE=mydbname;WSID=d2sb2;SERVER=127.0.0.1,21433;UID=myusername;PWD=mypassword"

(The [-1] gets rid of the [ODBC] header. We use all other lines.)

We can use this directly in an opening call:

rcon <- RODBC::odbcDriverConnect(connection = connstr)
RODBC::sqlQuery(rcon, "select 2 as two")
#   two
# 1   2
RODBC::odbcClose(rcon)

It's possible (even likely) that your DSN file has more than this example. If there are multiple entries (e.g., [ODBC] and [SomeOtherODBC]), then you'll need a bit more work. Below is an example that might work (might not, depending on comments, etc).

New DSN file contents:

[ODBC]  
DRIVER=ODBC Driver 17 for SQL Server
TrustServerCertificate=Yes
DATABASE=mydbname
WSID=d2sb2
SERVER=127.0.0.1,21433
UID=myusername
PWD=mypassword

[ODBC2]  
DRIVER=ODBC Driver 17 for SQL Server
TrustServerCertificate=Yes
DATABASE=mydbname
WSID=d2sb2
SERVER=127.0.0.1,31433
UID=myusername2
PWD=mypassword2

dsnfile <- readLines("~/StackOverflow/14549856/somedatabase.dsn")
dsnfile <- split(dsnfile, cumsum(grepl("^[[:space:]]*\\[", dsnfile)))
dsnnames <- sapply(dsnfile, function(dsn) trimws(gsub("[][[:space:]]", "", dsn[1])))
dsnfile <- lapply(setNames(dsnfile, dsnnames), function(dsn) paste(trimws(dsn[-1]), collapse = ";"))
str(dsnfile)
# List of 2
#  $ ODBC : chr "DRIVER=ODBC Driver 17 for SQL Server;TrustServerCertificate=Yes;DATABASE=mydbname;WSID=d2sb2;SERVER=127.0.0.1,2"| __truncated__
#  $ ODBC2: chr "DRIVER=ODBC Driver 17 for SQL Server;TrustServerCertificate=Yes;DATABASE=mydbname;WSID=d2sb2;SERVER=127.0.0.1,3"| __truncated__
rcon <- RODBC::odbcDriverConnect(connection = dsnfile[["ODBC"]])
RODBC::sqlQuery(rcon, "select 3 as three")
#   three
# 1     3
RODBC::odbcClose(rcon)