5

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.

The FPGA Race
  • 104
  • 1
  • 7

2 Answers2

7

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

Ruben Helsloot
  • 12,582
  • 6
  • 26
  • 49
m.b
  • 311
  • 3
  • 8
  • 1
    I'm running with a similar problem. I followed the same intructions but I still get an error when pushing to heroku. I was wondering if you could share your AptFile along with a screen shot of your buildpacks in Heroku ? – ZaraThoustra Sep 10 '21 at 08:55
  • @ZaraThoustra thanks for opening a new question - I will answer there https://stackoverflow.com/q/69114557 – m.b Sep 12 '21 at 04:54
  • can you precompile ODBC 13 driver please ? – MoonLight Sep 20 '21 at 11:01
  • I recently updated the heroku stack to 22.04 and now the connection fails [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection (0) (SQLDriverConnect)') – Pedro Moisés Camacho Ureña Oct 03 '22 at 23:21
0

Previous answer worked for me until I had to switch to heroku stack 22. After that I started getting the following error:

pyodbc.OperationalError: ('08001', '[08001] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection (0) (SQLDriverConnect)')

To solve this issue I switched to another library python pymssql, that handles connections to Microsoft SQL server in azure. here is a simple example

conn = pymssql.connect(server=xxx,\
        user=yyy, \
        password=zzz, \
        database=xxx)
cursor = conn.cursor(as_dict=True)
cursor.execute(query)
rows = cursor.fetchall()
cursor.close()
conn.close()
Pedro Moisés Camacho Ureña
  • 391
  • 4
  • 7