LISTSERV Tech Tip |
By Alexander Hyder
Software Support Engineer, L-Soft
Dynamic Query Lists (DQLs) provide a flexible way to connect LISTSERV with existing LDAP directories and database systems. Rather than storing subscribers in a static .LIST file, DQLs retrieve subscriber information directly from a data source at runtime.
For organizations that already maintain user information in an LDAP directory or DBMS, DQLs can reduce manual administration, improve data consistency and help keep mailing lists synchronized with existing systems.
This tech tip explains how Dynamic Query Lists work, how to configure them and some common pitfalls to avoid.
For help in deciding between DQLs and DBMS-backed lists, please refer to:
https://www.lsoft.com/news/techtipLSV-issue3-2013.asp
Furthermore, it is assumed that you have already consulted the LISTSERV Advanced Topics Manual and created a query that returns the values you want from your data source.
For more information on the mechanics of connecting LISTSERV to an existing DBMS, please refer to our white paper:
https://www.lsoft.com/resources/pdf/wp-ConnectingLStoDBMS.pdf
For more information on connecting LISTSERV to an LDAP data source, please refer to:
https://www.lsoft.com/news/techtipLSV-issue2-2014.asp
Once you have tested your queries, you must store them in your site configuration file (SITE.CFG for Windows or go.user for Unix). It is important to use a text editor that does not automatically wrap long lines. Each query definition must remain on a single line.
Each query must also be given a "nickname" and defined as a site configuration variable:
|
DYN_QUERY_MYFIRSTQUERY=kwd1 *kwd2 *…++ |
The order of queries within the file is not strictly important. However, if you have several queries, you may wish to determine an organizational scheme for your query definitions.
If any query values contain white space, they should be in quotes. Quotes within a quoted string must be doubled.
These queries, along with your data source connection settings cannot be set using the web interface.
Because they are stored as site configuration variables, queries (as well as DBMS or LDAP connection data) will only be read when you restart the LISTSERV service. As with any LISTSERV restart, it is critical to ensure that your license and maintenance files are up-to-date before rebooting.
Rather than storing multiple similar queries, you can pass the queries a parameter when they are called in the list configuration file. Parameters are passed to the query in the order that they are specified in the list header and assigned the placeholders %1, %2, %3 and so on. For example, a site catering to fans of Big Ten university athletics may wish to have lists for users from each university.
Rather than creating separate queries for all 18 member universities, the site administrator could simply create a single SELECT_DOMAIN query that accepts a single parameter:
|
DYN_QUERY_SELECT_DOMAIN=TYPE=DBMS SERVER=B1GDATA SEARCH='SELECT USR_EMAIL, USR_NAME FROM SOMETABLE WHERE USR_EMAIL LIKE CONCAT(''%%@'',%1)' E-MAIL=USR_EMAIL |
At runtime, the value specified in the list header fills the place of %1 when the query hits the DBMS.
The administrator could then define:
This has the added benefit that the query can be re-used for future lists. For example, if another school were to join the conference, the administrator could create a new WEST-VIRGINIA list with Sub-lists=Query(SELECT_DOMAIN,WVU.EDU) without ever having to modify site.cfg or even reboot LISTSERV.
Note that parameters must be enclosed in true single quotes (ASCII hex 27) if they contain white space or commas.
Queries will first be executed when a list header is updated to include them to ensure that the user making the change is a valid postmaster and that the query does not result in an error. From then on, queries will be executed on-the-fly when an incoming message is processed. This is important to note if you expect your database entries to change on a regular basis. Any change in your database will automatically be reflected when LISTSERV processes a list posting.
LISTSERV administrators can test a query by entering the following command via email or the web interface:
|
DEBUG QUERY QUERY(QUERYNAME,PARAMETER1,PARAMETER2...) |
Only use the parameters if QUERYNAME has defined parameters.
LISTSERV will return the addresses that the query returns, as well as a count. No mail will be sent.
Yes. You can use multiple queries for one list. For example, a school could define four queries: FRESHMEN, SOPHOMORES, JUNIORS and SENIORS. The school's administrator could then make lists for each graduating class. But the administrator could also make, for example, an UPPERCLASSMEN list with:
|
SUB-LISTS= QUERY(JUNIORS),QUERY(SENIORS) |
Yes. A list can have some subscribers determined by SUB-LISTS=QUERY(...) and some subscribers defined statically (in the binary .LIST file, as is the case with a traditional LISTSERV list). However, it is important to note that with DQLs, communication with your DBMS is one-way. Subscribe and unsubscribe requests, auto-delete, and other list management features can modify the list of statically defined subscribers, but subscribers returned by a query will not be affected. You will need another means to update your data source.
If you wish to have LISTSERV manage your data source, you should consider DBMS-backed lists instead.
There is no explicit method to search lists by queries. However, since any queries that populate a list's subscribers are specified under the "Sub-Lists" keyword, you can use the web interface to generate a report based on that keyword. Click on "List Management" > "List Dashboard" or "List Management" > "List Reports" in the navigation menu. On the resulting screen, select the keywords that you want to view and ensure that "Sub-Lists" is included. If you are generating a web report, you can click on the "Sub-Lists" column header to sort the information by that column.
Some of the most common mistakes include:
Setting Queries Through the Web Interface
Values stored in site.cfg or go.user are loaded into memory early in LISTSERV's boot process. It is required that dynamic queries be loaded at this stage. However, the web interface does not have write access to these files. The web interface can read values in site.cfg or go.user, but any values that are entered or modified via the web interface are stored in a separate file: SITECFG.FILE. The values stored in SITECFG.FILE are loaded later in the boot process (and can override values in site.cfg or go.user), and dynamic query information read from this file will not work properly.
Allowing Text Editors to Wrap Long Lines
No matter how many characters are in your query, each query must be stored on one line in site.cfg. Some text editors may automatically insert line breaks for long lines, which will prevent your site.cfg changes from working correctly. Additionally, if you are passing queries back and forth with colleagues or L-Soft support, it's very possible that email clients may alter the formatting of queries or insert line breaks. You must use a text editor intended for system administrators or developers.
Using Placeholder Names Literally
L-Soft's official manuals go into detail about setting up dynamic queries. Some customers have had issues interpreting the examples in the manual. In particular, the documentation contains the keyword "DYN_QUERY_nickname", but "nickname" is just a placeholder for the actual name of the query. Do not store your queries like:
|
DYN_QUERY_nickname=SR1 TYPE= DBMS kwd kwd kwd |
|
DYN_QUERY_SR1=TYPE=DBMS kwd kwd kwd |
Not Having Any Statically Defined Administrators
Using Dynamic Queries to specify list administrators can be convenient and allows for changes in your organization to be reflected in real time. However, it's possible for queries to fail – perhaps due to a connection issue or changes in the data source itself. As a backup plan, a list should have at least one statically defined email address included in administrative keywords, such as Owner, Editor, Moderator and Errors-To.
Having List Owners Attempt to Add or Modify Queries
For security reasons, only site-level administrators (in other words, those specified in the POSTMASTER configuration variable) can add or change queries in a list header or change the parameters that are passed to the query. List owners remain free to modify keywords that don't use queries, modify non-query keyword values and delete queries.
Dynamic Query Lists provide a flexible way to integrate LISTSERV with LDAP directories and database systems. When properly configured, DQLs can reduce manual administration, improve data consistency and ensure that list membership reflects the latest information available in your organization's data sources.
For organizations that already maintain user records in a directory service or database, DQLs can be an effective tool for keeping LISTSERV lists synchronized with existing infrastructure while minimizing ongoing maintenance.
Was this article helpful to you? Would you recommend it to a colleague? Your input helps us create content that truly supports your work. Thank you!
 Want More Insights? Catch up with the latest LISTSERV developments, industry best practices, expert tips, tutorials and more. |
LISTSERV is a registered trademark licensed to L-Soft international, Inc.
See Guidelines for Proper Usage of the LISTSERV Trademark for more details.
All other trademarks, both marked and unmarked, are the property of their respective owners.
