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

This page lets you specify how Aurea List Manager should connect to an external data source.

Name

The name of this connection.

Connection String

The ODBC connection string that Aurea List Manager 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 fails.

The SQL query is executed and tested with read-only access so that if it attempts to make any changes to the source database, it fails.

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, Aurea List Manager automatically breaks apart imported email addresses around the @ symbol to produce the values for UserNameLC_ and Domain_ fields. If the EmailAddr_ field is not included, Aurea List Manager assembles the email address from the UserNameLC_ and Domain_ fields. If EmailAddr_ is missing and the query does not contain both UserNameLC_ and Domain_, synchronization fails.

Password

If the Password_ field is included, Aurea List Manager examines 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, Aurea List Manager checks whether the password is the word random. If so, Aurea List Manager generates a random numeric password and encrypt it. Otherwise, Aurea List Manager encrypts the given password. If the password is already encrypted, Aurea List Manager stores the password without modification. The Password_ field is only used for new members; the password of existing members is never updated.

List

The List_ field may not be used for synchronization. Synchronization of the list fails 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

• You must have permission to view the table in the query. If you do not have permission, you see the following error message when you test the database connection:
   
  Test failed because of the following error:
  If SQL query does not contain EmailAddr_ field, then it must contain both UserNameLC_ and Domain_ fields.
• The query must be a select query. Update, insert, and Delete queries generally does not work.
• Please test your query thoroughly before putting it into production.
• Do not update the password_ field. The Aurea List Manager password_ field is encrypted.