Q: What are Dynamic Query Lists and why should I learn about them?

By Alexander Hyder
Software Support Engineer, L-Soft

Recently, several customers have requested help with Dynamic Query Lists. Instead of storing subscribers in a statically defined .LIST file on your hard disk, DQLs connect to an LDAP directory or DBMS and retrieve subscribers at runtime. Dynamic Queries are a great way to leverage a new or existing DBMS and can help add flexibility and interoperability to your email list system.

This tech tip describes the mechanics of how to define and implement Dynamic Queries. For help in deciding between DQLs and an alternative method, DBMS-backed lists, please refer to:

http://www.lsoft.com/news/techtipLSV-issue3-2013.asp

Furthermore, it is assumed that you have already consulted the LISTSERV Advanced Topics Manual and have already 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:

http://www.lsoft.com/resources/pdf/wp-ConnectingLStoDBMS.pdf

For more information on connecting to an LDAP data source, please refer to our previous tech tip:

http://www.lsoft.com/news/techtipLSV-issue2-2014.asp

Where and how do I store the queries?

Once you have tested your queries, you must store them in your site configuration file (SITE.CFG for Windows, go.user for Unix). It is very important that you use a text editor that will not automatically cause lines of text to wrap.

Each query must be given a "nickname". The query is then defined as a site configuration variable. Each query should be stored on exactly one new line in the site configuration file:

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.

I edited site.cfg, but my queries aren't being read. When will the queries take effect?

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.

I have a lot of similar queries. How can I consolidate them?

Rather than storing multiple, similar queries in site.cfg, you may 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 10 university athletics may wish to have lists for users from each university.

Rather than creating separate queries for all 14 member universities, the site administrator could simply create a single SELECT_DOMAIN query that accepts a single parameter:

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:

• An INDIANA list with Sub-lists=Query(SELECT_DOMAIN,INDIANA.EDU)
• A MARYLAND list with Sub-lists=Query(SELECT_DOMAIN,UMD.EDU)

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.

When will the queries be executed and how often will they run?

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.

How can I test my queries without actually sending mail?

LISTSERV administrators may test a query by entering the following command via email or the web interface:

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.

Can I use multiple queries for one list?

Yes. You may also use one query on multiple lists. 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:

Can a DQL also have statically defined subscribers?

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.

How can I view which lists call which queries?

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 "List Management" > "List Dashboard" or "List Management" > "List Reports" > "List Reports" in the navigation bar. On the resulting screen, select the keywords that you want to view and ensure that "Sub-Lists" is checked. If you are generating a web report, you may click the "Sub-Lists" column heading to sort by that column.

What are some common mistakes administrators make when configuring queries?

Some of the common mistakes include:

• Setting queries using the web interface rather than editing the site.cfg or go.user files
  
  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.
  
  
• Using a text editor that automatically wraps 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.
  
  
• Following the manuals too 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
  
  Instead, if you want your query named SR1, the correct entry would be:
  
  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 themselves
  
  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.

Subscribe to LISTSERV at Work.