how to install RODBC on heroku?

I have been trying to get RODBC to work with heroku for a few days and feel I have tried everything I can think of, but all ends up being a dead end. I am trying to run an r script that connects to the production postgres database, runs an algorithm, updates the database, and then exits. It works perfectly on my local macOS machine, but on heroku I am unable to install RODBC.

What I have tried:

1. I have installed the R buildpack. When I try to install RODBC through r like this: install.packages("RODBC"), I get the error saying that it can't find the sql.h and sqlext.h header files. I tried copying those to my app folder, but heroku does not see them. I believe I just need to add them to my path, but I am not quite sure how. I tried adding a PATH key to my env vars with value PATH=$PATH:~/sql.h:~/sqlext.h, but I am still getting the same error when I try to install RODBC through R -- R is not finding those header files, despite being on my PATH (I double checked with eho $PATH in the heroku bash shell). So this is a dead end.

2. Next, I saw online that I need to install unixodbc and unixodbc-dev. I went ahead and added the apt buildpack, and then added those package names to my apt file. They installed properly, and the error above went away because apparently those headers were properly installed and added to my PATH. However, I now have a new error saying that it cannot find the driver file libodbc.so.2. I can in fact find this file in my app directory under /app/.apt/, but for some reason R is not able to find it.

3. abandoning the idea of installing RODBC through R, I then moved on to trying to install it directly through apt. I added r-cran-rodbc to my aptfile per another suggestion on SO. Then once installed, I tried to run library(RODBC,lib.loc=/path-to-r-stie-libraries). It found the rodbc installation, but said that it was installed under a lower version of R (<4.0), and I needed to reinstall it. Unfortunately, that is the latest version of RODBC available on apt. dead end.

4. next, instead of upgrading r-cran-rodbc, I tried to downgrade R, ignoring what I saw saying that heroku stack 20 is only compatible with R 4.0 (I thought it was more of a suggestion). I created my own custom buildpack for R forked from the official one, that was configured to use r 3.6.3 instead of r 4.2.1. However, during the installation process, it got a forbidden error when trying to download the tarball for the buildpack. I get the build error: remote: Downloading https://heroku-buildpack-r.s3.amazonaws.com/latest/heroku-buildpack-r-20-3.6.3-deploy.tar.gz remote: curl: (22) The requested URL returned error: 403 Forbidden . I assume this has to do with the restriction of using r>4.0 with heroku 20, but I'm not sure.

5. finally, I tried to install R 3.6.3 directly from apt. This did not work, as it was unable to find R_HOME. even when I set R_HOME and added the r binaries to my home directory, I got an error saying WARNING: ignoring environment value of R_HOME .apt/usr/bin/R: line 240: /usr/lib/R/etc/ldpaths: No such file or directory ERROR: R_HOME ('/usr/lib/R') not found. The fix to this seems very non-trivial. yet another dead end.

Does anyone have any suggestions for how to get rodbc to work with heroku? The only other options I can think of are using PyODBC (which is a bit daunting, since I see similar posts complaining about the difficulty of that process as well), or trying to avoid using an ODBC connection on heroku entirely, and simply copying the table into a CSV and reading it into R from there (which has major drawbacks itself).

Any help would be greatly appreciated!

EDIT 1: Just before giving up I tried clearing my heroku cache, and then trying #2 again, since that once seemed the most promising. and it worked! I guess something in the cache was masking that libodbc.so.2 file? Not sure.

In any case, the prevailing wisdom about how to get odbc to work on heroku (both for R and python) when you run into that error about sql.h and sqlext.h not exiting is correct -- you need to install unixodbc and unixodbc-dev`, with the asterisk that you should CLEAR HEROKU CACHE FIRST. Especially if you have been noodling around with heroku a lot already like me.

EDIT 2: unfortunately, this still did not solve the problem of being able to actually use RODBC. When I try to run it with the driver file present in my app directory, it gives me a misleading error: In odbcDriverConnect("driver=./psqlodbcw.so;database=nw_server_production;trusted_connection=true;uid=nw_server") : [RODBC] ERROR: state 01000, code 0, message [unixODBC][Driver Manager]Can't open lib './psqlodbcw.so' : file not found. Misleading because the file is in fact there. I tried using a fully qualified path but no impact. I posted a follow-up question here