Using MS SQL databases in Zope running Linux

If, like me, you objected to having only one clearly documented way to connect Zope to Microsoft SQL Server (MSSQL) databases, don't want to run anything on MS Windows (other than the database of course!), or if you do not want to use a commercial approach for some reason then please read on....

This document will attempt to guide you through installing all the required software to allow you to connect your Zope site to a MSSQL database without running anything on widows other than the MSSQL server. As far as I can tell this approach will also work across firewalls, but you would need to read some of the additional material first.

Overview

Briefly the whole thing is based around getting SQLRelay and FreeTDS working together. SQLRelay does most of the legwork between Zope and the database, the final connection being provided by FreeTDS.

Both applications are capable of a whole lot more than I cover here, you can find more information sqlrelay.sourceforge.net, and www.freetds.org respectively.

I have also added steps to get unixODBC working with a MSSQL database. I am not entirely sure if it is necessary – please let me know if its necessary or not.

Having said that it would be nice if we had a free linux based Zope product that would just plug into unixODBC as it would save some setting up (maybe newZODBCDA could be adapted?)

Details

The following steps relate to a relatively clean install of SUSE Linux 9.1. I doubt it would be much different for other flavours of linux so long as you ensure you have the pre-requisites installed. I had already installed zope from the SUSE RPMS and plone and set them up prior to these steps.

Grab the software:

      Stop Zope from running

      Install unixODBC and unixODBC Devel rpms from Yast

      If you are likely to need PostgreSQL or MySQL install these (and the 
      devel packages too) now

      Download freeTDS tarball

      Download sqlrelay-0.35 tarball

      Download rudiments-0.27 tarball from http://www.firstworks.com

1. Install, Configure and Test FreeTDS:

         >cd to your downloaded location
         >tar zxvf freeTDS
         >cd freetds-0.62.4
         >./configure –enable-msdblib –sysconfdir=/etc –prefix=/usr/local/freetds–with-unixodbc
         >make
         >make install
         >vi /etc/profile.local
   
         Add the following:
   
            Export FREETDS=/etc/freetds.conf
            Export SYBASE=/usr/local/freetds
            Export TDSDUMP=/var/log/freetds.log
   
            >vi /etc/freetds.conf 
   
         Add a section for your server you wish to connect to, eg::
   
         [myserver]
               host = myserver.test
               port = 1433
               tds version = 7.0
   
         Test by connecting to server using:
   
            >tsql –S myserver –U username
            password:
            1> 
            Use ctrl-c to exit
   
         The ‘1>’ signifies a successful connection.
   

2. Install, Configure and Test unixODBC:

   Either use ODBConfig or edit /etc/unixODBC/odbcinst and add a new section for our ODBC driver as follows:

               [TDS]
               Description = Freetds.org
               Driver = /usr/local/freetds/lib/libtdsodbc.so
               Setup = /usr/locall/freetds/lib/libtds.so
               FileUsage = 1
   

   Add a dsn for your connection either in ~/.odbc.ini (user) or /etc/unixODBC/odbc.ini (system):

               [mydsn]
               Description = blah
               Driver = TDS
               Servername = myserver
               Database = <database>
               UID = <username>
               PWD = <password>
               Port = 1433
   

   Test by using:

               >isql –v mydsn <username> <password>
   

   You should see ‘Connected!’ Displayed.

   You can type in a sql command (eg SELECT * from table) and see the results displayed. Quit (or ctrl-c) to exit.

   www.theevilpixel.com has more in depth explanations if you need further reading (I used it as a guide in the steps above)

3. Install the SQLRelay pre-regisites

   You must make and install the rudiments class library before SQLRelay:

             >tar zxvf  rudiments-0.27.tar.gz
             >cd rudiments.0.27
             >./configure
             >make
             >make install
             >cd ..
   

4. Install, Configure and Test SQLRelay

   Now make and install SQLRelay as follows:

             >tar zxvf sqlrelay-0.35.tar.gz
             >cd sqlrelay-0.35
             >./configure
             >make
             >make install
   

   cd to where sqlrelay was installed (/usr/local/firstworks by default) Now copy the example config file and change it to reflect your setup. Eg:

             >cp etc/sqlrelay.conf.example sqlrelay.conf
             >vi sqlrelay.conf
   

   There is some help for this at sqlrelay.sourceforge.net

   The main thing you need to edit is the example instance. I just changed the id= MyID and dbase=”freetds”

   Change the ‘users’ section (I only had one entry here) and ‘connections’ section (again only one entry). ie:

             String=”Sybase=/etc;user=<MSSQLusername>;password=<MSSQLPassword;
                   Server=<servername_set_in_etc_sybase.conf>;db=<MyMSSQLDbName>;”
   

   At this point you may need to modify the default PATH – do this by adding the following to /etc/profile.local:

             export PATH=$PATH:/usr/local/firstworks/bin:/usr/local/freetds/bin
   

   Obviously if you already have a PATH statement in your /etc/profile.local then modify it to suit. I also added the path to the freetds binaries for completeness although its not absolutely necessary.

   Now you are ready to start SQLRelay – do so by running:

             sqlr-start –id MyID
   

   MyID should be whatever you defined on the instance line in /usr/local/firstworks/.etc/sqlrelay.conf above.

   You should see it start the listener, connections, scalar and cache manager (and a thank you to the sponsor).

   You are now ready to start Zope (or re-start it), got to the ZMI and add a new Z SQLRelay Database Connection. You simply need to enter the following connection string:

             localhost 9000 <username> <password>
   

   where the username and password relates to one of the entries in the users section of sqlrelay.conf., and port relates to the port number for that instance, again defined in sqlrelay.conf.

   test the new connection with a simple select statement and hey presto all should work!!!

   Good luck!

   Although this works, I have not fully tested it and not yet used it in a production environment so know nothing of its performance. It would be cool if NewZODBCDA could be made to work with unixODBC rather than just be a MS Windows based product, but I guess that’s for the future.

   Also note that SQLRelay components do not necessarily need to run on the same machine – I will let you read up on this as its way outside my scope here.