Developing web apps cross platform can be a pain sometimes, as the drivers used in Windows are typically not available (by default anyway) in linux. For example, if you are using perl based software in linux, and you need to connect to a Windows database (for example, MSDE 2000 or SQL Server)
There are two essential pieces of open source software you will need to use. The first one is UnixODBC [http://www.unixodbc.org]. This software provides the software to create an ODBC connection. Secondly, you will need the driver to connect to your Windows data source. This driver is provided by FreeTDS [http://www.freetds.org].
After installing this software according to their respective websites, if you are using perl you need to also install the module “DBD::ODBC”.
perl -MCPAN -e 'install DBD::ODBC'
You can now set up datasources in your configuration files. In my case, it would be located in /usr/local/etc/odbc.ini, but you can find the location of your driver file using the command
odbcinst -j
In addition to setting up your connection here, you can create a “DSN-less” connection using a connection string. Depending on how your system is set up, you may need to supply the connection password in your connection string anyway.
I’m not going to go over the coding of the database connection, for better instructions on how to use the UnixODBC software see their website:
The software I am using as a particular example is Lyris Technologies ListManager. You can synchronize your mailing lists with your database. Depending on your database software your needs will change, so you will need to talk to your software provider for specific information. The information I am providing below is for iMIS software from Advanced Solutions International (ASI).
For the connection string in ListManager, you will use the following syntax:
DRIVER=FreeTDS;SERVER=10.0.0.X;UID=dbusername;PWD=dbpassword;DATABASE=dbname;TDS_Version=8.0;Port=1433;
dbusername = database username, must have dbreader access to your database
dbpassword = password to match username
dbname = actual database name
Make sure your SERVER IP is correct, and the port for MSDE 2000 is 1433 by default. (As a gotcha, make sure any firewall installed allows connections between your servers and ports).
This allows your linux server to connect to your windows server running the database.
Additionally, you’ll need to provide the query string. I’ve found that Listmanager’s tolerance is not very good when dealing with SQL queries, for example it does not accept JOINs. You need to use an alternative method of joining tables. Refer to the Listmanager documentation to see what variables you can pull into their databases.
Below is the SQL statement for 2 different mailing lists.
Selecting a particular Committee or Section:
SELECT Name.EMAIL AS EmailAddr_, Name.FULL_NAME AS FullName_, Name.ID AS Additional_, 'H' AS MailFormat_
FROM Name, Activity, Product
WHERE Activity.ID = Name.ID AND Activity.PRODUCT_CODE = Product.PRODUCT_CODE AND Product.PRODUCT_MINOR ='TEC'
In this case, ‘TEC’ is the code used in iMIS for the committee. You use the WHERE clause to specify joins. I do not think this is the best way, but it works in this case.
To select all Active Members:
SELECT Name.EMAIL as EmailAddr_, Name.FULL_NAME as FullName_, Name.ID as Additional_, 'H' AS MailFormat_
FROM Name
WHERE ((Name.MEMBER_TYPE)='REG' Or (Name.MEMBER_TYPE)='AFF' Or (Name.MEMBER_TYPE)='HON' Or (Name.MEMBER_TYPE)='SEN') AND ((Name.STATUS)='A')
As you can see, it is not exactly simple but it is easy enough to do once you have the right software. Once your software is talking to your databases, it makes things a lot easier!
