LISTSERV Maestro 8.1-4 Help

Target Group Definition: Based on Database Access By LISTSERV Maestro

This Target Group Definition wizard lets you define a target group of the Database access by LISTSERV Maestro type that can then be used in the Define Recipients wizard.

The wizard for a target group of the Database access by LISTSERV Maestro type has multiple pages:
General, Source, Parameters, Input Layout, Input Preview, Recipients Details, Duplicate Elimination, and Summary.

The top row of the wizard displays links to each of 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 this page is not necessary with the current choices and can safely be ignored.

---

Source Page: Connection Settings And Parameterized SQL or LDAP Filter

Selecting the Database Connection: Click the drop-down menu and select the connection to the database you want to access. Only connections that have previously been defined are available.

Adding a Database Connection: If the list of defined database connections does not contain a suitable entry for the database you want to access, click the "Add Connection" entry at the end of the drop-down menu. This opens a pop-up dialog allowing you to supply the necessary details for the connection. First select the database plugin from the drop-down menu. Only plugins that have previously been registered by the administrator are available. Once you have selected a plugin, the plugin-specific parameters are displayed and you need to fill them out with the values that allow LISTSERV Maestro to connect to the database that you want to access. If you are unsure as to what values to fill in, contact your administrator for assistance. When you are finished supplying the connection parameters and database credentials, click the [Ok] button to use the new connection. This connection is now listed and available for future use.

Supply the SQL statement or LDAP search that shall be executed to retrieve the recipients in the corresponding edit box. This statement is executed using the connection you specified above. The statement text can be fixed, or it can contain parameters that are later supplied by the end user when this target group is used in a recipients definition. For more information on how to parameterize the SQL statement or LDAP filter text and for details about the meaning of the opening and closing tag input fields, see below.

At the bottom of the screen, use the options to define when the SQL statement or LDAP search shall be executed:

• During recipient definition: The SQL statement or LDAP search is executed when this target group is used in the Define Recipients wizard to define the recipients of a job, and when the Summary screen of that recipients wizard is entered. This means that if the end user chooses this target group in the recipients wizard, then the list of the actual recipients is defined at the moment the user completes the recipients wizard. If the database content changes between that moment and the actual sending of the job, then those changes are not reflected in the list of recipients of the job.

• Just before sending: The SQL statement or LDAP search is executed when this target group is used in the recipients wizard to define the recipients of a job at the moment before the job is actually sent. This means that if the end user chooses this target group in the recipients wizard, then the list of the actual recipients is defined at the moment the job is actually sent, which could be some time after the recipients wizard is completed. If the database content changes between the completion of the recipients wizard and the actual sending of the job, those changes are reflected in the list of recipients of the job.

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.