Hi Adam, it is specific to PostgreSQL (not via ODBC), but I don't think I can answer the second part fully - it's all drivers, but some of them are thin-wrappers around the real DB library. I've been wondering if there'll be a more direct way to leverage Manifold SQL that is not via ODBC - and if so it's very easy to rewrite the R code to use that instead.
Here's something of a guide under the R hood:
The current backend for PostgreSQL in R is via this package:
dbConnect in src_postgres, is running this R-level code, with a .Call() to the C-library underneath:
"conId <- .Call("RS_PostgreSQL_newConnection"
In the C source, "RS_PostgreSQL_newConnection" is here in line 201:
I'm working on src_manifold here, which is using the RODBC package underneath.
I have an example there with Access (just because nearly anyone can reproduce that and it proves it works) and then another one with Manifold, my real target. It was gratifyingly simple (for an experienced R programmer).
My src_manifold is doing the real work with RODBC:
Ultimately, this is all R code that leverages the underlying DB connection - usually by C code built into the R package - it just simplifies it (at the user-level!). The ODBC stuff for R is not "DBI-compliant" (which just means it doesn't have a front-end for the DBI package in R). The DBI package aims to be the controller for all other DB packages in R. So dplyr talks to DBI, and DB packages (like RODBC and RPostgreSQL and RSQLite) extend the virtual methods in DBI. There's a fair bit of package-chaos and fragmentation because things have been evolving very quickly, but the dplyr-DBI scheme is looking very stable now.