You are here: Home → Tech → Setup FreeTDS, ODBC and pyodbc to Connect to MS-SQL on FreeBSD
Navigation
 

Setup FreeTDS, ODBC and pyodbc to Connect to MS-SQL on FreeBSD

by Reed O'Brien — last modified Jan 18, 2010 03:57 PM
— filed under: , , , , , ,

Instructions for connecting FreeBSD 8.0 to MSSQL server (2005) for use with pyodbc and SQLAlchemy. it should also work fine on OSX 10.6.

Overview

I wanted to be able to query 17 years worth of data living in an MSSQL server. It has been there since MS-SQL 6.5. the plan was to use the lovely SqlSoup extension from SQLAlchemy. I don't use windows so I needed another way to connect. I found little useful documentation online to help me on my way, so here are my notes.

Requirements

  • FreeBSD 8.0 (Should work on 6.x and 7.x too)
    • building FreeBSD is beyond the scop of this document. Please see the FreeBSD Handbook for more help.
  • FreeTDS 0.64 (freetds-0.64_4,1 form ports as of this writing)
  • iODBC 3.52 (libiodbc-3.52.7 form ports as of this writing)
  • Distribute (or setuptools)
  • virtualenv (via easy_install virtualenv)
  • pyodbc 2.1 (pyodbc-2.1.6 from source as of this writing)
  • SQLAchemy 0.6 (the required patch hasn't landed on trunk yet.)

The FreeTDS port will work with either iODBC or unixODBC. I chose to go with iODBC as it ships on OSX 10.6 by default. This should (and does for me) work on OSX if you compile FreeTDS via ./configure && make && make install (CMMI) and then easy_install pyodbc.

Let's get started.

FreeTDS and iODBC

Installing FreeTDS and iODBC from ports.

$ cd /usr/ports/databases/freetds
$ sudo make config
# Select the following options only. It wouldn't
# work with openssl or gnutls for me. YMMV.
[X] IODBC Use iodbc (Mutually Exclusive)
[X] MSDBLIB MS SQL Server support (breaks databases/sybtcl)
$ sudo make install
...output elided
# If all goes well you should see a line like the following
# ===> Registering installation for freetds-0.64_4,1
# somewhere near the end of the output. (Right near SECURITY
# REPORT)

That was easy wasn't it?

Configuration

We need to setup two files.

  1. /usr/local/etc/freetds.conf:
# You can copy /usr/local/etc/freetds.conf.dist
# to /usr/local/etc/freetds.conf and edit it to
# add a section that looks like this
# A typical Microsoft SQL Server 2000 configuration
[YOURCONFNAME]
host = 10.10.10.x # Your address here
port = 1433
tds version = 8.0

Several documents I found state you should be able to use tsql to connect now:

$ tsql -S YOURCONFNAME -U <user> -P <password>
$ > use a_real_db
$ > select * from a_real_table;
$ > should be result but I get none.

But I get nothing when I connect. So let's ignore it for now and configure ODBC.

  1. /usr/local/etc/odbcinst.ini:
# the iODBC install left me no dist or example file (maybe in share?)
# so I just created one like the following
[FreedTDS]
Description = FreeTDS MSSQL
Driver = /usr/local/lib/libtdsodbc.so
Database = YOURDBNAME
Driver = /usr/local/lib/libtdsodbc.so
UID = YOUR_USER
PWD = YOUR_PASSWORD
Port = 1433
Server = 10.10.10.x # YOUR IP ADDRESS
client charset = utf-8
TDS Version = 8.0

OK that was easy, too. Now let's install pyodbc and SQLAlchemy.

virtualenv, pyodbc and virtualenv

You can install pyodbc from ports but I like to work in virtualenvs so I will download and compile it myself. easy_install doesn't work because for whatever reason it doesn't look in /usr/local for libs or includes.

  1. distribute
$ cd ~ && mkdir dev && cd dev
$ curl -O http://python-distribute.org/distribute_setup.py
$ python distribute_setup.py
# ...output elided

Done with distribute...

  1. virtualenv
# if you are using csh you will need to rehash to get the command on your path.
$ rehash
$ easy_install virtualenv
# ... output elided

Done with virtualenv

  1. Setup a virtualenv to work in
# if you are using csh you will need to rehash to get the command on your path.
$ rehash
$ virtualenv mssqlenv --no-site-packages
# ... output elided
$ . mssqlenv/bin/activate # bash only (maybe zsh)
# (t)csh will need to use full path i.e. ~/dev/mssqlenv/bin/python

virtualenv now set up.

  1. Setup pyodbc
$ curl -O http://pyodbc.googlecode.com/files/pyodbc-2.1.6.zip
$ unzip pyodbc-2.1.6.zip
cd pyodbc-2.1.6
# now we apply the patch from ports so it can find libs and includes
$ patch -p0 < /usr/ports/databases/py-odbc/files/patch-setup-libs
$ python setup.py install
# ~/dev/mssqlenv/bin/python setup.py install on (t)csh

pyodbc now installed

  1. Install SQLAlchemy

When 0.6 lands with the right patches we can easy_install it. For now it is a manual install...

$ cd ~/dev/
$ svn checkout http://svn.sqlalchemy.org/sqlalchemy/trunk sqlalchemy_trunk
$ curl -O http://www.sqlalchemy.org/trac/attachment/ticket/1629/fix_descending_column_names.patch?format=raw
# It looks like as of this writing that the patch has landed http://www.sqlalchemy.org/trac/changeset/6639
# so it may be unnecessary to apply.
$ cd sqlalchemy_trunk
$ patch -p0 < ../fix_descending_column_names.patch
python setup.py install
## ~/dev/mssqlenv/bin/python setup.py install on (t)csh

Now we should be ready to give it a whirl...

Try It Out

Let's fire up and interpreter and go

$ python
## ~/dev/mssqlenv/bin/python on (t)csh
>>> import pyodbc
>>> pyodbc.connect("Driver=/usr/local/lib/libtdsodbc.so;Server=10.10.10.x;UID=USER;PWD=SECRETPASSWORD;PORT=1433;DATABASE=YOUR_DB;TDS_VERSION=8.0;")
<pyodbc.Connection object at 0x284353b8>
>>> ## That is great it gave me a connection...
>>> ## let's try a query
>>> from sqlalchemy import create_engine
>>> from sqlalchemy.ext.sqlsoup import SqlSoup
>>> engine = create_engine('mssql:///', creator=conn_factory, encoding='windows-1252')
>>> soup = SqlSoup(engine)
>>> [(o.NameLast, o.NameFirst) for o in soup.Authors.filter(soup.Authors.NameLast.startswith('obri'))].pop(0)
('Obrien', 'Reed')

Conclusion

This wasn't really that hard, but it took many permutations to get it going for me. I hope it helps someone else out there.

Document Actions