13

Is there a way to programmatically discover the named arguments for ODBC connections?

For instance, using the "ODBC Driver 17 for SQL Server", the ODBC Data Source Administrator (in Windows) allows for authentication using a "Login ID" and "Password", yet when connecting programmatically, we need to use uid= and pwd=, respectively. These two configuration points are clearly listed in configuration documentation (e.g., https://db.rstudio.com/databases/microsoft-sql-server/), but there are several other configuration options that the driver supports that are less- or un-documented.

(For SQL Server, there are more docs, including https://learn.microsoft.com/en-us/sql/connect/odbc/windows/driver-aware-connection-pooling-in-the-odbc-driver-for-sql-server?view=sql-server-ver15, that lists several other arguments (though not their possible values). That works for this driver, though since this page has not been updated for Driver 17 we have to assume that all options are unchanged.)

There are programmatic ways to learn what drivers are available, and what pre-defined data sources are available:

odbc::odbcListDrivers()
#                             name        attribute value
# ...truncated...
# 33 ODBC Driver 17 for SQL Server       UsageCount     1
# 34 ODBC Driver 17 for SQL Server         APILevel     2
# 35 ODBC Driver 17 for SQL Server ConnectFunctions   YYY
# 36 ODBC Driver 17 for SQL Server        CPTimeout    60
# 37 ODBC Driver 17 for SQL Server    DriverODBCVer 03.80
# 38 ODBC Driver 17 for SQL Server        FileUsage     0
# 39 ODBC Driver 17 for SQL Server         SQLLevel     1

odbc::odbcListDataSources()
#                 name                   description
# 1             somedb ODBC Driver 17 for SQL Server
# 2 SQLite3 Datasource           SQLite3 ODBC Driver

but nothing to discover connection arguments. Perhaps something akin to this non-existent function:

discover_odbc_arguments("ODBC Driver 17 for SQL Server")
# [1] "Address"                "AnsiNPW"                "App"                   
# [4] "Database"               "Encrypt"                "PWD"                   
# [7] "Trusted_Connection"     "TrustServerCertificate" "UID"

(I used SQL Server as an example here, but I'm interested in more general methods. And since I'm looking for programmatic mechanisms, I'd prefer to avoid suggestions to read the docs for each driver.)

(I am not opposed to non-R methods.)

Some non-R attempts that did not work. Using a mssql odbc driver library as an example with known parameter patterns.

$ strings /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1  \
  | egrep -i 'Trusted.Conn|AnsiDPW|TrustServer'
### <no output>
$ grep -rli 'Trusted.Conn|AnsiDPW|TrustServer' /opt/microsoft/msodbcsql17/
### <no output>
r2evans
  • 141,215
  • 6
  • 77
  • 149
  • 1
    Just to be clear here - you are looking for a way to programmatically query the ODBC driver and retrieve the API capabilities? Correct? Are you connecting through to a REST API via an ODBC driver? – Technophobe01 May 27 '20 at 17:28
  • I don't know of an API for ODBC *drivers* (is there such a thing?), but most likely the ODBC protocol itself. One problem is that drivers are all written by different organizations, so I think looking at the driver DLLs themselves is weak to begin with. I'm asking if there is a programmatic way (within the ODBC spectrum?) to discover the existence and names of the parameters, even if nothing more than that. – r2evans May 27 '20 at 17:30
  • ODBC Drivers support connection strings that document the parameters required. https://www.connectionstrings.com/microsoft-access-odbc-driver/ you may also want to publish your own connection string or query it. I think that is what you are looking for. If you are using an ODBC driver to connect to a REST API there are solution that will query and generate the correct queries against the REST API. https://www.progress.com/tutorials/odbc/query-any-rest-api-using-odbc-in-5-mins---getting-started-(part-1) [I am not affiliated just mentioned for reference. Hope the above helps. – Technophobe01 May 27 '20 at 18:36
  • I'm not connecting to a REST API nor looking to do so, I'm using ODBC drivers to make connections to the DBMS. If we take "REST" out, what I'm asking is if there is a way to query the driver or scrape or determine its properties *programmatically*. Not via documentation. While that connection-string website does have value, it is incomplete (when compared to the original documentation at microsoft). Again, though, I'm asking about programmatic discovery. Thanks! – r2evans May 27 '20 at 18:52
  • @JoshuaMire, thanks for the comment (deleted answer) about `RODBC` versus `odbc`. I don't think it matters: its [docs](https://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf) even say *"What fields are supported is driver-specific (and it can be hard to find documentation)"*, suggesting the author is not aware of programmatic discovery (or at least has not deeply investigated them). I fear this question is mostly just confirmation that ODBC as a protocol does not support the concept. Thanks again. – r2evans May 31 '20 at 18:39
  • @r2evans can I rephrase your question to make sure I understand it? You are trying to access the @param codes of an installed ODBC driver pre-connection from the `R` `odbc` interface? (When I say @param I'm referring to: https://github.com/r-dbi/odbc/blob/master/R/Driver.R) – sgdata Jun 02 '20 at 01:50
  • I'm trying to determine ODBC parameters via *any* (programmatic) interface. R is not strictly required (though great if it is). The "obvious" parameters are not really a factor, such as server/host, user/uid, password/pwd. I'm trying to discover things like "Encrypt", "AnsiDPW", "TrustServerCertificate", three parameters that the MS ODBC driver happen to document. (ODBC seems to be such a wild-wild-west protocol, well beyond normal "ANSI vs proprietary", it seems more implement only what you really care about and name things in a haphazard manner ...). – r2evans Jun 02 '20 at 01:57

1 Answers1

4

You need to somehow call SQLBrowseConnect function to get all available parameters of connection string for specific ODBC driver or DSN. Unfortunately, as I can see this call isn’t implemented in R ODBC packages. See documentation and example code at the link for more information.

Alexander Ushakov
  • 5,139
  • 3
  • 27
  • 50
  • That's a good find ... more importantly I think to `odbc` is that it is not in `nanodbc`, the underlying library on which `odbc` is built. Bummer ... but good find. – r2evans Jun 02 '20 at 23:06
  • Any chance you want to write a command-line helper program, since neither `odbc` nor `nanodbc` support it? :-) – r2evans Jun 02 '20 at 23:18
  • 1
    Thanks Alexander! I'm not surprised that no existing-tool (that can optionally be used from R) has been found/presented, but I *am* surprised that there is this capability built into the ODBC standard. (I'm surprised b/c there are several things broken about ODBC.) – r2evans Jun 03 '20 at 14:29
  • 2
    From this, I submitted a [feature-request](https://github.com/nanodbc/nanodbc/issues/235) to `nanodbc` to support this protocol function, and it seems to have been received well. I have no idea of implementation timeline, but getting interesting among its developers is promising. – r2evans Jun 03 '20 at 14:35
  • 2
    Great to see that my answer is helpful. Let's hope `nanodbc` implement this useful feature. – Alexander Ushakov Jun 03 '20 at 14:51