Linking Plone3 / Zope with MS SQL Server 2005

Yes its blasphemy, making open source work with a product built by what some people call the evil corporation Microsoft. But hey what can I say I love MS SQL Server, the tools are so nice and easy to use (MS SQL Management Studio) and it links straight into .NET solutions which in term gives you the ability to link the hottest framework .NET and the hottest CMS Plone. Wow. 🙂

Anyway these are the main steps involved.

1) Install UnixODBC from http://www.unixodbc.org/
Install with the following:

   >sudo ./configure --prefix=/usr/local/unixODBC --sysconfdir=/etc --enable-gui=no
   >make
   >make install

2) Install FreeTDS (download from http://www.freetds.org)

   >sudo ./configure -with-tdsver=8.0 -enable-msdblib -sysconfdir=/etc -prefix=/usr/local/freetds-with-unixodbc --with-odbc-nodm=/usr/local/unixODBC

3) Get FreeTDS talking to MS SQL
First open the file /etc/freetds.conf and at the bottom add something like the following:

[MYSQLSERVER]
   host = 192.168.2.4
   port = 1433
   tds version = 8.0

save and close now test with the following command:

   >tsql -S MYSQLSERVER -U myusername
   password: enteryoursqlpasswordhere
   1>

You will be prompted a password and then should get a prompt of just ‘1>’ that indicates a successful connection. To test further use the following to run a select command from your database and see some results.

   1> USE MyDatabaseName
   2> GO
   1> SELECT * FROM MyTableName
   2> GO

You should get a dump of the select results.

4) Get UnixODBC talking to FreeTDS.
This step had me stuck for a while. First edit the file /etc/odbcinst.ini and add the following (but confirm the locations of libtdsodbc.so and libtdsS.so, libtds.so used to ship with FreeTDS but that is no longer the case so the included version with UnixODBC seems to work fine):

   [FreeTDS]
   Description     = TDS driver (Sybase/MS SQL)
   Driver          = /usr/local/freetds-with-unixodbc/lib/libtdsodbc.so
   Setup           = /usr/local/unixODBC/lib/libtdsS.so
   CPTimeout       =
   CPReuse         =
   FileUsage       = 1

Now edit /etc/odbc.ini

   [SQLDATABASE1]
   Driver          = FreeTDS
   Description     = ODBC connection via FreeTDS
   Trace           = No
   Servername      = MYSQLSERVER
   Database        = MyDatabaseName

Now the tricky bit, execute the following command:

   >isql

If you get a command not found error just run the following:

   >sudo sudo ln -s /usr/local/unixODBC/bin/isql /usr/bin/isql

now try the following:

   >isql SQLDATABASE1 mydatabaselogin mydatabasepassword -v

If you get some output saying Connected! and a ‘SQL>’ prompt you have succeeded. If you get an error about failing to connect run the following and look through the output trying to identify problems. One othe pointer, if you installed unixodbc through the Ubuntu installer, just delete the isql file from /usr/local/bin and /usr/bin and make a new symbolic link as above so the isql being executed is in fact the correct one not a stale copy from a prior installation.

   >strace -f 2>&1 isql SQLDATABASE1 mydatabaselogin mydatabasepassword -v

I had a lot of errors the first time, the odbcinst.ini file trying to be opened from /home/myprofile/.odbcinst.ini and many more because I spent a lot of time stuffing about trying to get this to work and had installed several versions with and without the default Ubuntu installer. You can either uninstall everything you tried and start some clean installs of the packages in this guide or just copy your files to the right place. I uninstalled everything and tried again to keep it all nice and clean and it worked the first time after doing what is in this guide.

btw, the strace rules now I know about it!

5) Install and configure PyODBC
Download PyODBC from http://code.google.com/p/pyodbc and install with the following:

   >sudo /usr/local/Plone/Python-2.4/bin/python setup.py install

Now run python (make sure you run the Plone version not the default system one).

   >/usr/local/Plone/Python-2.4/bin/python
   >>>import pyodbc
   >>>

Now this is where I am stuck, when I go something like conn = pyodbc.connect(‘Connection String Here’) it always fails. hmmm…. So I try with sqlrelay

6) Install Rudiments and SQL Relay, note use my patched versions if you have problems with the originals (I think it’s an Ubuntu thing)
Install Rudiments with just configure, make, make install
Now configure SQL Relay with the following (change and paths required).

>sudo ./configure --with-freetds-prefix=/usr/local/freetds-with-unixodbc \
                       --with-zope-prefix=/usr/local/Plone/zeocluster/parts/zope2 \
                       --with-python-prefix=/usr/local/Plone/python-2.4

If either of these steps fail at build with an error about wno-long-double then download my patched versions here and try.

No edit your sqlrelay config file, this is located at /usr/local/firstworks/etc/sqlrelay.conf .

This is what I have.

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
        <!-- Regular SQL Relay Instance -->
        <instance id="mysqlserver" port="9000" socket="/tmp/example.socket" dbase="freetds" connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="false">
                <users>
                        <user user="myusername" password="mypassword"/>
                </users>
                <connections>
                        <connection connectionid="db1" string="Sybase=/etc/freetds.conf;user=myusername;password=mypassword;server=mysqlserver;db=mydatabasename" metric="1" behindloadbalancer="no"/>
                </connections>
        </instance>
</instances>

Now start sqlrelay with the command

>>sudo sqlr-start -id mysqlserver

I will add examples of calling sqlrelay from Python once I get round to it.