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.
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.
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?)
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
- 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.
- 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)
- 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 ..
- 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:
At this point you may need to modify the default PATH – do this by adding the following to /etc/profile.local:
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!!!
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.