Utilities: Administration: Server: Database: Database Connections: New/Edit Database Connection

 

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