sql server - Python cant find ODBC Driver on Heroku after setting everything

Question

I have gone through every possible solution on the internet, but I'm unable to make pyobdc get the drivers on heroku.

The steps I have used to create the app are as follows:

heroku create
heroku config:set FLASK_CONFIG=heroku 

heroku buildpacks:add heroku/python
heroku buildpacks:add --index 1 heroku-community/apt

git push heroku master 

I tried even with odbc buildpack but still no luck:

heroku buildpacks:add https://github.com/iFix/heroku-buildpack-odbc.git

After going through microsoft website, I trimmed down my Aptfile to instuct heroku to install the following packages:

# install msodbcsql17
https://packages.microsoft.com/ubuntu/16.04/prod/pool/main/m/msodbcsql17/msodbcsql17_17.4.2.1-1_amd64.deb
# install mssql-tools
https://packages.microsoft.com/ubuntu/16.04/prod/pool/main/m/mssql-tools/mssql-tools_17.4.1.1-1_amd64.deb
# install unixodbc-dev
unixodbc-dev

This makes pyodbc installation go without error. But when I run pyodbc.drivers(), it returns nothing. The same command on Ubuntu 16.04 returns "ODBC Driver 17 for SQL Server"

The source code for the project is at : https://github.com/IamVNIE/odbcTestHeroku

The hosted app is at : https://pyodbctest.herokuapp.com/

Can someone please provide some pointers to make this work.

Answer 1

I have solved this issue by precompiling ODBC Driver 17 for SQL Server on a machine running Ubuntu 18.04 and copying the libmsodbcsql-17.5.so.2.1 and msodbcsqlr17.rll files into appropriate directories via a Heroku buildpack. This is ODBC Driver 17.5 for SQL Server and I likely won't be compiling other versions of this driver, but I imagine the concept remains the same.

The Heroku buildpack and its requirements can be found here https://github.com/matt-bertoncello/python-pyodbc-buildpack.git