Parameters are the placeholders within the SQL statement that are replaced with values chosen during the recipient definition of the job. By using parameters, the data administrator can use the same basic SQL statement for many jobs, turning over the details to the end user, who can use them without having to know anything about the actual SQL that lies behind the target group, or even know SQL in general. This saves time and effort for all team members. It also allows group members to share the same target groups, while permitting each target group to create different recipient definitions for individual jobs.
A SQL statement for a target group can be a "fixed" statement, or a parameterized statement. A fixed statement contains SQL code that 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, city from recipients where age >= 30 and age <= 39
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 in 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
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 would require
its own target group and SQL statement. Setting up these fixed statements
would involve a lot of work for the data administrator, as well as take
away flexibility for the end users.
Using a parameterized statement can save time and effort as well as give 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 recipient definition. These placeholders are not "real" SQL code, but are a sort of "meta" code. By using placeholders, parts of the SQL statement that are not yet "known" can be defined at the moment the SQL statement is entered into the system. These placeholders are then replaced with actual values before the statement is executed.
Continuing with the example above, 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 recipient 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, city 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. Different tags can be defined if the default tag strings are used somewhere else in the statement and therefore cannot be recognized as placeholder tags. Enter tags in the Opening Tag and Closing Tag fields below the SQL statement field on the Source screen.
Parameter placeholders must follow these rules to function correctly:
· 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 simply 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 must be an integer; otherwise, the execution of the statement will result in an error or in unexpected results.
· A placeholder that is enclosed with string literal quotes (as in "city = '{{name}}'") is considered a non-integer parameter (a string or floating-point). 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, a placeholder is defined as "lastname = '{{name}}'" and a last name of O'Brian is entered as the parameter value (value of "O'Brian"). After replacement, the resulting data would automatically become "lastname = 'O''Brian'" (or whatever escape of the quote character is the correct one for the database in use). Do not define values with quote characters already escaped, since that would lead to a double-escaped character.
· Placeholders within an 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" it is possible to write "value in (x,y,z)", enumerating the possible values in a comma separated list within parenthesis.
To parameterize this, use a placeholder 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 will be 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 they must be integer values. If the quoted form is chosen, then all choices will be quoted (each one separately) and any quotes appearing in a choice value will automatically be escaped.
Here are some examples:
o Assume that the target group SQL statement includes the condition "value in ({{arg}})" and that the user selected the choices "1", "5", "23" and "412". The actual SQL query submitted by LISTSERV Maestro will include the condition: "value in (1,5,23,412)".
o Assume that the statement includes the condition "lastname in ('{{arg}}')" and that the user selected the choices "Miller", "O'Brian" and "Wagner". The actual SQL query submitted will include: "lastname in ('Miller','O''Brian','Wagner')".
· Two placeholders that have the same parameter name must also have the same integer/non-integer type. You cannot define an integer parameter and use the same parameter name 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. You cannot define a parameter inside of an
in-clause and then use the same parameter name for a parameter outside
of an in-clause, or vice versa.
There are four basic parameter types – checkbox, edit field, selection list, and date/time. Each parameter type is only available for certain types of select statements.
Table 4 Available Parameter Types for Select Statements
|
Available Parameter Types |
Integer
values outside any |
Integers
values inside an |
Non-integer values (quoted) outside any in-clause |
Non-integer
values (quoted) inside an |
|
Checkbox |
X |
|
X |
|
|
Edit field single value |
X |
|
X |
|
|
Edit field multiple values |
|
X |
|
X |
|
Selection list single value |
X |
|
X |
|
|
Selection list multiple values |
|
X |
|
X |
|
Date/time |
|
|
X |
|
This parameter type appears as a checkbox on the end user's screen. The checkbox has two associated values – checked or unchecked. Enter these values accordingly – they need to be different values.
Depending on the input from the user (if the box is checked or not), one or the other value will be used directly to replace the parameter placeholder. If the parameter placeholders were not quoted, then only integers can be entered as the two values. If the parameter placeholders were quoted, then any string can be entered for the values, and all occurrences of the quote character in these will be escaped automatically before the replacement. Remember do not escape quotes when entering the values.
Figure 136 Parameter Type Checkbox – Data Administrator's View
Figure 137 Parameter Type Checkbox – End User's View
This parameter type appears as a free input field on the end user's screen. The input is validated according to the sub-selection:
· Restrict value(s) to integer numbers – Only integer numbers are accepted as input.
· Restrict value(s) to floating point numbers – Only numbers (integer or floating point) are accepted.
· Allow free text input – Any kind of input (including numbers or text) is accepted.
The input from the end user will be used directly to replace the parameter placeholder. If the parameter placeholder was quoted, then all occurrences of the quote character in the user input will be automatically escaped before the replacement.
There are two versions of the Edit Field parameter type – single value and multiple values. In the single value version, the input field will be a one line input field, and the value entered by the end user will be the value used to replace the placeholder. This type is only available if the matching placeholder is not inside of an in-clause context. In the multiple values version, the input field will be rendered with multiple lines, and the end user may enter several lines of text. Each line will be interpreted as a separate value, and the placeholder will be replaced with a comma separated list of all values (of all lines). Empty lines will be ignored. This type is only available if the matching placeholder is within an in-clause context.
Figure 138 Parameter Type Edit Field, Single Value - Data Administrator's View
Figure 139 Parameter Type Edit Field, Single Value – End User's View
This parameter type appears as a selection list with multiple entries on the end user's screen. If the parameter is within an in-clause, the list is rendered as a multi-line list field, and the end user may select multiple entries by holding down the SHIFT or CTRL key. Otherwise, the list is rendered as a drop-down list and the end user may select only a single entry.
For parameters where LISTSERV Maestro selects from a database, the entries in the selection list can be specified in two ways. They can be specified manually on this wizard screen or a SQL statement can specify them. The SQL statement will then be used to retrieve the values that will appear in the list from the database. Select an option to choose between the two methods. For parameters where LISTSERV selects from a database, all entries must be specified manually on this wizard screen.
Each value consists of two parts, the visible text in the selection list (that is the text that the end user actually sees in the list), and the invisible value associated with that entry. This invisible value, associated with the entry selected by the end user, will be used directly to replace the parameter placeholder. If the parameter placeholder was not quoted, then only integers can be used as the internal value of each entry. If the parameter placeholder was quoted, then any string can be entered for the internal values, and all occurrences of the quote character in these will be escaped automatically before the replacement. Do not escape quotes when entering the values.
To add a new entry, click on the New link to the right of the list. Enter the visible text into the left edit fields, and the internal value into the right field. Click on [Save Entry]. The new entry will be added to the list.
To modify an existing entry, simply select the entry in the list, edit its visible text and/or internal value in the two edit fields above, and then click [Save Entry] to save the changes. The entry will be updated accordingly.
To change the ordering of the entries, select the entry to move and click Up or Down to move it in the list. To delete an entry, select it, and then click Delete.
Figure 140 Parameter Type Selection List, Manual Specification – Data Administrator's View
Figure 141 Parameter Type Selection List – End User's View
Enter a SQL statement. This statement will be executed with the same connection parameters that were specified on the Source screen of the wizard. The result set retrieved will be used to populate the selection list. The values from the first column of the result set will be used as the visible text of the selection list entries. If there is a second column in the result set, then its values will be used as the internal values for the entries. If there is no second column, then the values from the first column will be used both for the visible text and the internal values. Any further columns in the result set are ignored.
Make sure that the internal values match the quote context of the parameter. If the parameter placeholder is not quoted, then the internal values must be integer values (that is the values from the second result set column, or the ones from the first column, if there is only one column). If the values are not integers, then the parameter placeholder must be quoted (any necessary escaping of quotes in the values will happen automatically).
This option is very useful if it is not possible or desirable to enter all the selection values by hand. There may be many different selections, and some of the values may not yet be known. For example, think of a target group that has the city where a recipient lives as one of the parameters in order to do mailings limited to the residents of a certain city:
select * from recipients where city='{{name}}'
If this parameter is assigned the type "Selection List" all possible cities could be entered manually. But, this approach requires a lot of work, and all the possible cities may not yet be known. Another drawback to using this approach is that the list would have to be updated manually each time a recipient from a new city is entered into the database. To avoid all this time and effort, use an SQL statement like:
select distinct city from recipients order by city
This statement accesses the same table as the target group itself (see the first SQL statement above) using the same database connection settings. It generates exactly one column that contains all cities that are currently in the city column in the table, in alphabetical order. The end user can then simply select one of these cities.
Figure 142 Parameter Type Selection List, Database Specification – Data Administrator's View
Important: The list may only have a maximum of 100 entries, in order to not overburden the user interface and to protect against abuse. If more entries than this is required, it would probably be better to use the "Edit Field" type for this parameter, and let the end user input the value manually, instead of selecting it from a list with too many entries.
This parameter type appears as an input box on the end user's screen. Choose selections for Date Input Format and/or Time Input Format. Choose at least one or choose both. The selection determines whether the user will be asked to input a date, a time, or both. It also determines how the input fields will be arranged. Input fields will be rendered as three input fields for the date (day, month, year order, according to specification), and/or as two or three input fields for the time (hours and minutes with or without seconds, according to specification).
All end user time input must be in the 24h format from 00:00:00 to 23:59:59. An AM/PM input format is not available. In addition to defining how the date/time input will look for the end user, it is also necessary to define how the input from the user is converted into a string that matches the date/time format used in the database. Do this by entering a format string into the specified edit field.
In that format string, use any desired characters. For example, separation characters like ":" or ",". Also, use any of the format placeholders listed to the right of the field. Each format placeholder will later be replaced with the corresponding date/time value, in the corresponding format.
The possible format placeholders are:
· year4 – Will be replaced with a four digit representation of the year value entered by the end user (for example "2002"). Available only if a date input format was selected.
· year2 – Will be replaced with a two digit representation of the year value entered by the end user (for example "02"). Available only if a date input format was selected.
· month2 – Will be replaced with a two digit representation of the month value entered by the end user (for example "09" or "12"). Available only if a date input format was selected.
· month1 – Will be replaced with a one or two digit representation of the month value entered by the end user, with months January to September as one digit and months October to December as two digits (for example "9" or "12"). Available only if a date input format was selected.
· day2 – Will be replaced with a two digit representation of the day value entered by the end user (for example "01" or "31"). Available only if a date input format was selected.
· day1 – Will be replaced with a one or two digit representation of the day value entered by the end user, with days 1 to 9 as one digit and days 10 to 31 as two digits (for example "1" or "31"). Available only if a date input format was selected.
· hour2 – Will be replaced with a two digit representation of the hour value entered by the end user (for example "08" or "23"). Available only if a time input format was selected.
· hour1 – Will be replaced with a one or two digit representation of the hour value entered by the end user, with hours 0 to 9 as one digit and hours 10 to 23 as two digits (for example "1" or "23"). Available only if a time input format was selected.
· min2 – Will be replaced with a two digit representation of the minute value entered by the end user (for example "04" or "59"). Available only if a time input format was selected.
· min1 – Will be replaced with a one or two digit representation of the minute value entered by the end user, with minutes 0 to 9 as one digit and minutes 10 to 59 as two digits (for example "4" or "59"). Available only if a time input format was selected.
· sec2 – Will be replaced with a two digit representation of the seconds value entered by the end user (for example "06" or "59"). Available only if the time input format with seconds was selected.
· sec1 – Will be replaced with a one or two digit representation of the seconds value entered by the end user, with seconds 0 to 9 as one digit and seconds 10 to 59 as two digits (for example "6" or "59"). Available only if the time input format with seconds was selected.
While the format string is being typed into the input field, the sample date/time "Sep. 1, 2002 08:04:06 AM" will be converted into that format and the result displayed below the input field. For example, if "month2/day2/year4 - [hour2:min2:sec2]"is entered as the format, then the sample will display:
“09/01/2002 - [08:04:06]”
The input from the end user will be applied to the format string entered in the same way as with the sample date, and the resulting string will be used to replace the parameter placeholder. All occurrences of the quote character in the date/time string will be escaped before the replacement.
Figure 143 Parameter Type Date and/or Time – Data Administrator's View
Figure 144 Parameter Type Date and/or Time – End User's View
Important: This database format always requires a 24h time format from 00:00:00 to 23:59:59. This input type cannot be used to generate a database time format that includes AM/PM information with hours from 1 to 12. It is also not possible to generate a database date format where the month or day of the week is given in long text, like "Monday, December 2nd, 2003". If such a time format is necessary, use the Edit Field type instead, and let the end user input the date and time manually in the format required by the database (use the parameter's description field to tell end users which format they need to use to be compatible with the database).