Target Group Definition - Let LISTSERV Select Recipients From a Database or LDAP Directory

To create a new target group, select New Recipient Target Group... from the menu (or go via the right-click menu of the Target Groups node or a target group folder in the subscriber warehouse).

Note: If the new target group has not the desired type "Let LISTSERV Select Recipients From a Database or LDAP Directory", you can change the target group type on the source page.

The Target Group Definition wizard lets you define a target group of the "Let LISTSERV Select Recipients From a Database or LDAP Directory" type that can then be used in the recipients wizard to define the recipients of a job.

The wizard for a target group of the "Let LISTSERV Select Recipients From a Database or LDAP Directory" type has multiple pages:
General, Source, Parameters, Input Layout, Input Preview, Recipients Details, and Summary.

The top row of the wizard displays links to these pages. The page that is currently open is marked with a highlighted background color. Depending on the choices made on some of the wizard pages, other pages may become disabled or may be shown in different versions. If a wizard page is disabled, then it means that the page is not necessary with the current choices and can safely be ignored.

Source Page: SQL Statement or LDAP Query

Define the method employed by LISTSERV to retrieve the recipients represented by this target group.

Enter the server alias used by LISTSERV to retrieve the recipients.

Decide if LISTSERV shall access an SQL database and execute the supplied statement or if LISTSERV shall access an LDAP directory and execute the supplied query.

Enter the SQL statement or LDAP query that shall be executed by LISTSERV to retrieve the recipients in the corresponding edit box. The query may be a fixed statement, or it may contain parameters that are later filled out by the end user when this target group is used in a recipients definition.

For SQL statements, see here for details on how to parameterize the SQL statement and for details about the meaning of the opening and closing tag input fields.
At the bottom of the screen, define which character the database uses to quote string literals (often this is a single quote character) in SQL statements. Also, define how that quote character is to be escaped if it occurs inside of a string literal's value. Escaping the quote character is often two single quote characters appearing next to each other.
For example: the quote character is "'", and the escaped quote character is "''". With these definitions, "O'Brian" as a quoted string literal would become "'O''Brian'".

For LDAP queries, see here for details on how to parameterize the query and for details about the meaning of the opening and closing tag input fields and about special issues regarding LDAP directory access.

How to parameterize a SQL statement, LDAP search filter or LISTSERV condition

Note: This explanation is valid for SQL statements, LDAP search filters and for LISTSERV conditions. All allow parameterization in a very similar manner. For the sake of simplicity however, the explanation below only mentions SQL statements (to avoid repeated occurrences of phrases like "SQL statement, LDAP search filter or LISTSERV condition").

To apply this explanation to a LISTSERV condition or LDAP search filter, simply read "condition" wherever the text says "SQL statement" or "statement" and modify the examples given to match a corresponding LISTSERV condition instead. The rules for LDAP queries are slightly different, see below.

A SQL statement for a target group can either be a "fixed" statement or a parameterized statement.

A fixed statement contains SQL code that is fixed, meaning that it will be used in the same form every time it is used with the target group. For example:

select * from recipients

Or more complex:

select email, name, gender from recipients where age >= 30 and age <= 39

Such a fixed statement will always yield the same results regardless of the circumstances of how the target group is used, as long as the actual database content does not change. A fixed statement is useful, but only under certain circumstances. The first example given here would simply select all entries from a certain table. If that is the required behavior, then a fixed statement is the correct statement type to use. The second example, on the other hand, selects only those recipients that are in their thirties (age >= 30 and <= 39). This is very limiting. Targeting a different age group would necessitate creating a new target group with a different SQL statement. In fact, with fixed statements, every different age group used would require its own target group and SQL statement. Setting up these fixed statements would involve a lot of work for the LISTSERV Maestro database administrator, as well as take away flexibility for the end users.

Using a parameterized statement can save time and effort as well as giving end users more flexibility in selecting recipients.

A parameterized statement contains placeholders in the SQL code that are replaced by the end user who uses the target group for the actual recipients definition. These placeholders are not "real" SQL code, but they are a sort of "meta" code. By using placeholders, parts of the SQL statement can be defined that are not yet "known" at the moment the SQL statement is entered into the system. These placeholders are then replaced with actual values before the statement is executed.

Using placeholders makes it possible to create a parameterized SQL statement that selects all recipients of a certain age range. The end user who employs the target group in a recipients definition is left with the decision of what age range to use when creating the recipients definition.

This example shows how the actual age range values are replaced with placeholders "{{from}}" and "{{to}}".

select email, name, gender from recipients where age >= {{from}} and age <= {{to}}

A placeholder is any string of characters that appears between special opening and closing tags. By default, the opening tag is "{{" and the closing tag is "}}". Everything surrounded by these two tags will be treated as parameter placeholders, and not as part of the actual SQL statement. If the default tag strings need to be used somewhere else in the statement without them being recognized as placeholder tags, different tag strings can be defined. This is done by entering new tags in the two edit boxes below the SQL statement input field.

These rules apply when using parameter placeholders:

Any occurrence of a pair of the currently defined opening and closing tags will be interpreted as a placeholder. The opening and closing tags themselves are also considered part of the placeholder. The text between the tags is considered the parameter name.
Any string of characters can be used between the tags, becoming the parameter name.
The same parameter name can be used for several placeholders (with certain restrictions, see below). In this case, they are considered multiple occurrences of the same parameter, all of which will be replaced with the same value once the placeholders are replaced with the end user's selection.
A placeholder can be used in any position in the SQL statement. The most common locations for placeholders, however, are in comparisons in the where-clause of the statement.
A placeholder that is not enclosed with string literal quotes (as in "age >= {{from}}") is considered an integer parameter because the entire placeholder is replaced with the value (so that it becomes, for example "age >= 30"). The value supplied for the parameter must be an integer, otherwise the parameter input is not accepted.
A placeholder that is enclosed with string literal quotes (as in "city = '{{name}}'") is considered a non-integer parameter. Here, the entire placeholder, but not the enclosing quotes, is replaced. The example becomes "city = 'New York'"), and the value may be any string.

Any occurrences of the quote character itself in the value will automatically be escaped. For example, assume "lastname = '{{name}}'" and a value of "O'Brian". After replacement, this would automatically become "lastname = 'O''Brian'" (or whatever escape of the quote character is the correct one for the database in question). Do not define values with quote characters already escaped, since that would lead to a double-escaped character.

(Note that the above sample with apostrophe (') as the string literal quote is meant for SQL statements. For LISTSERV conditions, the string literal quote character is the double-quotes character (") instead).
Placeholders in IN clauses require special attention. An IN clause is a SQL construct that allows an OR comparison with a range of values. Instead of writing "value = x or value = y or value = z" simply write "value in (x,y,z)", enumerating the possible values in a comma separated list within parenthesis.

To parameterize this, a placeholder can be used instead of this comma-separated list, as with "value in ({{arg}})" or in the quoted form "value in ('{{arg}}')".
Both forms are very similar. The placeholder is replaced with a comma separated list representing all choices the user makes. Do not include a whole list of placeholders, but only a single placeholder. This single placeholder is then replaced by the list of the choices the user makes.

If the non-quoted form is chosen, then the choices are also not quoted, so that they must be integer values. If the quoted form is chosen, then all choices are quoted (each one separately) and any quotes appearing in a choice value are automatically escaped.

Here are some examples:
Assume "value in ({{arg}})" and that the user selected the choices "1", "5", "23" and "412". This results in a replacement like this: "value in (1,5,23,412)".

Assume "lastname in ('{{arg}}')" and that the user selected the choices "Miller", "O'Brian" and "Wagner". This results in a replacement like this: "lastname in ('Miller','O''Brian','Wagner')".

(Note: IN clauses are not allowed in LISTSERV conditions or LDAP search filters, so the above explanation applies only to SQL statements.)
Two placeholders that have the same parameter name must also have the same integer/non-integer type. An integer parameter cannot be defined and the same parameter name used for a non-integer parameter, or vice versa.
Two placeholders that have the same parameter name must also have the same in-clause/non-in-clause type. A parameter cannot be defined inside of an in-clause and the same parameter name used for a parameter outside of an in-clause, or vice versa.
The target group administrator does not define the values that are replaced by the parameters when defining the target group. The values are defined by the end user who employs the target group in a recipient definition. The placeholders are simply a convenient way for the target group administrator to tell the system there is a variable parameter in the SQL statement, and the value will be supplied by the end user.

However, the target group administrator does have control of the way the end user supplies this value at the time the target group is defined. If a parameter (quoted or not quoted) appears outside of an in-clause, then it is interpreted as a single value parameter. If it appears inside of an in-clause, it is interpreted as a multiple value parameter. It is then up to the target group administrator to define how the user will input the value for each type of parameter: as a free text input, as a checkbox, as a choice list, or similar. See the help for the Parameters screen of the wizard for more details.

How to parameterize a LISTSERV LDAP query

Similar to LISTSERV conditions and SQL statements, LISTSERV LDAP queries for a target group can either be "fixed" or parameterized.

A fixed query contains code that is fixed, meaning that it will be used in the same form every time it is used with the target group. For example:

BASE ou=Example Faculty,o=Example University,c=country
FILTER (objectclass=*)

Or more complex:

BASE ou=Example Faculty,o=Example University,c=country
FILTER (&(gender=male)(objectclass=person))
ATTRS mail givenName

Such a fixed query will always yield the same results regardless of the circumstances of how the target group is used, as long as the actual LDAP directory content does not change. A fixed query is useful, but only under certain circumstances. The first example given here would simply select all entries underneath the base DN "ou=Example Faculty,o=Example University,c=country". If that is the required behavior, then a fixed query is the correct query type to use. The second example, on the other hand, selects only men (in the example, this is accomplished by restricting the entries to those that have "gender=male" and "objectclass=person"). This is very limiting. Targeting a different gender would necessitate creating a new target group with a different query.

Using a parameterized query can save time and effort as well as giving end users more flexibility in selecting recipients.

Much like SQL statements and LISTSERV conditions, a parameterized LDAP query contains placeholders in the query code that are replaced by the end user who uses the target group for the actual recipients definition.

Using placeholders makes it possible to create a parameterized query that retrieves all entries with certain attributes (by having a parameter placeholder in the FILTER part of the query) or that retrieves all entries underneath a certain base DN (where the actual DN is parameterized, for example to start the search at configurable entry points of the LDAP directory). The end user who employs the target group in a recipients definition is left with the decision of what attributes or LDAP directory entry point to use when creating the recipients definition.

This example shows how the actual base DN value is augmented with a placeholder "{{faculty}}".

BASE ou={{faculty}},o=Example University,c=country
FILTER (objectclass=*)

A placeholder is any string of characters that appears between special opening and closing tags. By default, the opening tag is "{{" and the closing tag is "}}". Everything surrounded by these two tags will be treated as parameter placeholders, and not as part of the actual query. If the default tag strings need to be used somewhere else in the statement without them being recognized as placeholder tags, different tag strings can be defined. This is done by entering new tags in the two edit boxes below the query input field.

These rules apply when using parameter placeholders:

Any occurrence of a pair of the currently defined opening and closing tags will be interpreted as a placeholder. The opening and closing tags themselves are also considered part of the placeholder. The text between the tags is considered the parameter name.
Any string of characters can be used between the tags, becoming the parameter name.
The same parameter name can be used for several placeholders. In this case, they are considered multiple occurrences of the same parameter, all of which will be replaced with the same value once the placeholders are replaced with the end user's selection.
A placeholder can be used in any position in the query (where it makes sense).
The target group administrator does not define the values that are replaced by the parameters when defining the target group. The values are defined by the end user who employs the target group in a recipient definition. The placeholders are simply a convenient way for the target group administrator to tell the system there is a variable parameter in the query, and the value will be supplied by the end user.

However, the target group administrator does have control of the way the end user supplies this value at the time the target group is defined. It is up to the target group administrator to define how the user will input the value for each type of parameter: As a free text input, as a checkbox, as a choice list, or similar. See the help for the Parameters screen of the wizard for more details.
The user-supplied values for placeholders that are configured as single value edit fields on the parameters screen are escaped in such a way that they don't cause problems in any part of the LDAP query. For all other placeholder configurations (i.e. choice lists, checkboxes or date input types), the internal values are supplied by the target group administrator and are not escaped, so that the administrator is able to define the internal value with the suitable escaping method, which depends on the location of the placeholder in the query.

Changing the Target Group's Type

The bottom of the screen contains a link to change the type of the target group that you currently are working on.

Click the link to open a dialog. In this dialog, you can choose the target group type that you want to change to.

Note: Your account may not have all types described below available, depending on how the system administrator has configured your account. You can choose:

Send to Recipients in the Subscriber Warehouse: This target group type is based on a subscriber list in the subscriber warehouse.

After clicking the [OK] button, the screen displays all of the list groups and their respective subscriber lists. Once a list is selected, the wizard will allow for conditions and parameters to be set in order to segment the list subscribers.
Help pages for wizard of this type
Send to Recipients in the Subscriber Warehouse: This target group type is based on a list group.

After clicking the [OK] button, the screen displays all available list groups. Once a list group is selected, the wizard allows for conditions and parameters to be set in order to segment the list subscribers in the group.
Help pages for wizard of this type
Select Recipients from a Database or LDAP Directory:

If you choose this target group type, in the target group wizard you will need to select a database or LDAP connection, a SQL statement (or LDAP search), merge column headers, and duplicate elimination, just as you would when using the underlying recipients type in the recipients wizard. Optionally, the SQL statement and LDAP search filters can be parameterized, meaning that you can define parameters for the SQL (or LDAP search) that are filled out by the user in the recipient wizard, when this target group is used for a recipient definition.
Help pages for wizard of this type
Send to a LISTSERV List:

After clicking the [OK] button, the screen displays all available LISTSERV lists on the server assigned to the account group. Select a list from the drop-down menu, and then select the type of LISTSERV message to send, standard or mail merge.
Help pages for wizard of this type
Upload Recipients:

The data administrator can set up a server file to be uploaded just before the job is delivered.
Help pages for wizard of this type
Let LISTSERV Select Recipients From a Database or LDAP Directory:

After clicking the [OK] button, you will need to fill out a database name, a SQL statement, and merge column headers, just as you would when using the underlying recipients type in the recipients wizard. Optionally, the SQL statement may be parameterized, meaning that you may define parameters for the SQL that are filled out by the user in the recipient wizard, when this target group is used for a recipient definition.
Help pages for wizard of this type

Note: The type of target group that you currently have selected in the target group wizard is omitted from the list of option shown in the dialog described above.