how to find ODBC driver-specific arguments

Question

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>

Doorstone

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? – Eldridgeeldritch

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. – Doorstone

ODBC Drivers support connection strings that document the parameters required. 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. progress.com/tutorials/odbc/… [I am not affiliated just mentioned for reference. Hope the above helps. – Eldridgeeldritch

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! – Doorstone

@JoshuaMire, thanks for the comment (deleted answer) about RODBC versus odbc. I don't think it matters: its docs 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. – Doorstone

@Doorstone 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: github.com/r-dbi/odbc/blob/master/R/Driver.R) – Apograph

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 ...). – Doorstone

Answer 1

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.

Springclean

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. – Doorstone

Any chance you want to write a command-line helper program, since neither odbc nor nanodbc support it? :-) – Doorstone

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.) – Doorstone

From this, I submitted a feature-request 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. – Doorstone

Great to see that my answer is helpful. Let's hope nanodbc implement this useful feature. – Springclean