1. On the ListManager server, download, gunzip and untar UnixODBC from http://www.unixodbc.org/. Read the included documentation carefully, and be sure you are using the correct libraries.
Tip: Use odbcinst -j to see where UnixODBC is installed.
UnixODBC contains the odbcinst.ini and odbc.ini files, which will point to the specific driver(s) you are using.
2. On the ListManager server, download and install the correct ODBC driver for your database. Read the included documentation carefully. In our testing, we used drivers from:
http://dev.mysql.com/downloads/connector/odbc/3.51.html
The driver installer should prompt you for database connection information and populate the odbc.ini file with the correct values.
3. The installer for the driver may ask whether you want to use a connection string to connect, or DSN (Data Source Name). Both contain connection information, but in different formats and in different places.
This is the value you will enter in Utilities: Administration: Server: Database: New Database Connection: Connection String. Note: In our tests, drivers from Easysoft always use DSN.
Sample Connection Strings
MS SQL
Driver={SQL Server};Server=lmtest;Database=lmdb;Uid=sa;Pwd=pass
Oracle
DSN=DSN_Name;Server=lmtest;Uid=lmuser;Pwd=pass
MySQL For Unix:
Driver={MySQL ODBC 3.51 driver};server=lmtest;database=lmdb;uid=mysqluser;pwd=pass;
MySQL for Windows:
Driver={MySQL ODBC 3.51 driver};server=lmtest;database=lmdb;uid=mysqluser;pwd=pass;
Sample Connection Using DSN
If your driver uses DSN, the connection information in the odbc.ini file will be used to authenticate and connect to the database. No connection string is required; instead, indicate the Data Source Name. Example:
DSN=mysql
Sample odbc.ini File
[ODBC Data Sources]
[mysql]
Driver = /usr/lib/libmyodbc3.so
Description = MySQL on lmtest
SERVER = lmtest
USER = lmuser
Password = pass
OPTION = 3
Sample Select Query
This query is connecting to a table called info. The state field has been added to the ListManager Members_ table.
select info.email as EmailAddr_,
info.status as MemberType_,
info.state as state
from info
Important Notes
Troubleshooting Checklist
1. Make sure you have installed UnixODBC.
2. Make sure you have installed the correct driver.
3. Make sure you are using the correct libraries.
4. Make sure you are using the correct information to connect to the database.
5. If using DSN, be sure the odbc.ini file has the correct connection information.
More
1. ODBC
1. ODBC on Unix
3. Sample ODBC Connection Strings and Queries