This page lets you specify how ListManager should connect to an external data source.
Name
The name of this connection.
Connection String
The ODBC connection string that ListManager should use to connect to your external data source. Contact your driver vendor for the connection string to be used here.
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;
Example Using DSN
DSN=Oracle
Applies To
Specify which list or site may use the connection, or whether the connection may be used by any list on the server.
Select Query
Enter the SQL "select" query that should be used to synchronize data between your external data source and ListManager. The SQL query should have field names that exactly match field names in the members_table; if any do not match, the synchronization of the list will fail.
The SQL query will be executed and tested with read-only access so that if it attempts to make any changes to the source database it will fail.
Email Address
The select query must specify an email address by either specifying the value to use for EmailAddr_, or for both UserNameLC_ and Domain_. If the EmailAddr_ field is included, ListManager will automatically break apart imported email addresses around the @ symbol to produce the values for UserNameLC_ and Domain_ fields. If the EmailAddr_ field is not included, ListManager will assemble the email address from the UserNameLC_ and Domain_ fields. If EmailAddr_ is missing and the query does not contain both UserNameLC_ and Domain_, synchronization will fail.
Password
If the Password_ field is included, ListManager will examine each password to determine whether or not it is encrypted (if password is 32 characters long and all characters are a-f or 0-9, List Manager assumes it is encrypted). If password is not encrypted, ListManager will check whether the password is the word random. If so, ListManager will generate a random numeric password and encrypt it. Otherwise, ListManager will encrypt the given password. If the password is already encrypted, ListManager will store the password without modification. The Password_ field will only be used for new members; the password of existing members will never be updated.
List
The List_ field may not be used for synchronization. Synchronization of the list will fail with an error message if the SQL query contains the List_ field.
Membership Status (MemberType_)
The MemberType_ field will not be verified (i.e. any value will be permitted even if not in our standard set). Whether or not the MemberType_ field is actually used depends on options selected for creating new members and updating existing members in the synchronization settings for the list.
Valid MemberType_ values are:
normal (default)
unsub
held
private
expired
held
needs-goodbye
needs-hello
needs-confirm
See Members: Add Members: Add Member: Settings: Member Status for more information about these different member statuses.
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 Query Notes