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
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)