General Tcl Commands
The following Tcl commands can be used in either message or recipient-level Tcl scripts.
sql
The sql Tcl command allows an administrator to execute an SQL statement and obtain the results. For security reasons, the administrator must login first using the login command. The sqlcommand can be used to execute an SQL statement that returns a single result set or no results. That result set can have multiple columns and rows. SQL statements that return multiple result sets are not currently supported. The syntax for the SQL command is:
%%sql (tag) (function) (function arguments)%%
The first argument to the sql command is a tag that is used to identify the query when executing a statement and retrieving results. It allows multiple queries to be distinguished within a message.
It is important to note that queries that are done within a message-level Tcl script are in a different scope than those in a recipient-level script. Therefore, the tags that are used to identify them are also in different scopes. A tag used in a message-level script will be distinct from the same tag used in a recipient-level script. A tag can be any string.
A message-level script is executed only once for the message and a recipient-level script is executed for each recipient. The results of SQL queries executed in each of these scopes are stored separately and cannot be accessed from a different scope. In other words, recipient-level scripts cannot access the results of queries performed in a message-level script. The reverse is also true. Since the results of message-level queries might be required at the recipient level, they can be stored in variables and accessed for recipients without any command call overhead.
The second argument to the "sql" command is the name of the function that will be executed. These functions and their arguments are:
sql (tag) execute (sql statement)
This function will execute the specified SQL statement.
sql (tag) numrows
This function will return the number of rows that were retrieved.
sql (tag) numcolumns
This function will return the number of columns that were retrieved.
sql (tag) columnname (column index)
This function will return the name of the column at the specified index. The index must be between 1 and N where N is the number of columns retrieved.
sql (tag) data (column index) (row index)
This function will return the data that corresponds to the specified column and row indices. The row index must be between 1 and M where M is the number of rows retrieved. If the row index argument is omitted, it will default to 1. If both the column index and row index are omitted, they will both default to 1.
Here is an example of how to return all of the names and email addresses of everybody on the current list in the form:
Name = Full Name, Email = email@address
%%!
init ;
errormode debug ;
login youradmin@email.addr yourpassword ;
%%
%%!
sql listmembers execute "SELECT FullName_, EmailAddr_ FROM members_ WHERE List_='[list.name]'" ;
set numrows [sql listmembers numrows] ;
set results "" ;
for { set row 1 } { $row <= $numrows } { incr row } {
set Name [sql listmembers data 1 $row] ;
set EmailAddr [sql listmembers data 2 $row] ;
append results "Name = $Name, Email = $EmailAddr\n" ;
} ;
return $results ;
sendmessagelm
This command is a simple method of sending a message using ListManager's standard message send system. This function takes four arguments:
sendmessagelm (from) (to) (subject) (body)
Due to the length of the subject and body, it may be more convenient to define these as variables to pass to this command. For example:
%% after ;
set From "me@myaddress.dom" ;
set To "me@myaddress.dom" ;
set Subject "message send has completed!" ;
set Body <
The message send for:
[merge inmail_.HdrSubject_]
is complete.> ;
sendmessagelm $From $To $Subject $Body
%%
process_merge_tags
The process_merge_tags TclMerge command is useful anytime you want to merge content from another source and have the merge tags evaluated. This command is very useful for HTML mailings. This command will most likely be used in mailings, but can apply to other documents, such as hello and goodbye documents as well. The process_merge_tags TclMerge command will evaluate any %%-delimited merge tags in the argument passed to it and return the results. Doing so is useful in cases where you want to perform a mail merge on text that is retrieved from the database or via http.
Note that these tags are evaluated as straight Tcl and therefore any ListManager Tcl language extensions will not work in these tags. For example, the "init" and "before" modifiers will not be evaluated properly and line unwrapping is not done. Additionally, any special merge tags that are not handled as normal Tcl procedures such as %%MemberID_%% will result in errors. You must use the %%merge MemberID_%% convention for all merged data fields.
Example:
%%process_merge_tags [httpget http://myserver/newsletter.html]%%
Message Level Commands
The following commands are valid only in message-level or send-completion scripts.
login
In order to use certain Tcl commands, the administrator must first log in. This is to keep someone from sending in a message containing malicious SQL or other hazardous code pretending to be an administrator by using the administrator's email address, a practice known as "spoofing".
The "login" command is called with the administrator's email address and password. The syntax for this command is:
%% init ; login (email address) (password) %%
Since this command requires a database query to determine if this address and password are those of an administrator, it should only be used in message-level scripts -- Tcl scripts that begin with "%%init ;" or "%%before ;". Otherwise, the query will be done for each recipient, and it will slow down the send.
redirect
For testing purposes, it is advantageous to be able to preview what each recipient will see when their message is mail-merged. The "redirect" command will allow the administrator to redirect all messages to a specified email address.
The syntax for this command is:
%% init ; redirect (email address) %%
errormode
Since Tcl can be a tricky language to program, debugging message sends will almost certainly be required or desirable. In order to make sure that the Tcl in the message executes properly, the sender should turn on error handling so that if there is an error, the message send stops and the sender is notified. The following error modes are supported: notify, abort, replace, debug, and normal.
The most helpful use of this command is the "notify" mode:
%% init ; errormode notify user@domain.ext %%
When this mode is set, any Tcl errors will cause the message send to stop and an email that details the error description and the tag in which it occurred will be sent to the specified email address.
The "abort" error mode simply causes the message send to stop but no notification is sent. The error text can be found in the transaction log for the message. The syntax for this mode is:
%% init ; errormode abort %%
The "replace" error mode will cause the tag in which the error occurred to be replaced with the specified text. The syntax for this mode is:
%% init ; errormode replace "replacement text" %%
The "debug" error mode will replace the tag with the error string. This string indicates
what the error was and the text of the script.
The "normal" error mode replaces the tag with an empty string. This is equivalent to calling "errormode replace" with an empty string. This is the default behavior.
mailtest
This command, which takes no arguments and returns an empty string, tells ListManager to put this message into "mailtest" mode. This means that the send will proceed as normal and go through the standard SMTP transaction but will reset before the message data is sent to the server. Any messages sent in this mode will not reach the recipients. This should only be used for testing mail-merge and send speeds. Since messages sent this mode are otherwise indistinguishable from a standard send, handling of transient or permanent failures for bad or intermittent email addresses will still apply.
Recipient Level Commands
recipindex
This command takes no arguments and returns a number that indicates the relative index of the current recipient during the current send. It is based on the range 1 to N where N is the total number of recipients to which the message is being sent. The ordering is based on the retrieval order from the database but isn't guaranteed to be in this order.
numrecips
This command takes no arguments and returns the number of total recipients that are being sent to. This number represents N in the range of results returned by the "recipindex" command.
skip
There are times when the sender of a message may want to not send to certain recipients that would be part of a normal send. For example, if the sender was testing a message with the "redirect" command but they were sending to a list with 100,000 messages, they would receive 100,000 copies of the message. Or, the sender might want to do mail-merge for the entire send and verify that there were no errors before doing the actual send.
Using the skip command, which takes no arguments and returns an empty string, the sender could send to every 1000th recipient and skip the rest using the following Tcl mail-merge tag:
%% if { [recipindex] % 1000 != 0 } { skip } %%
This means that if the index of this recipient as retrieved by the recipindex command is not evenly divisible by 1000 then it will be skipped.
The sender could skip every recipient by simply adding the tag "%%skip%%" to the end of the message.
When the "skip" command is executed, further processing of the current message is stopped so if you are testing the message to make sure that all the scripts are executed properly, call this command at the end of the message. Otherwise, call it at the beginning.
abort
This command terminates the sending of the current message. If the error mode was currently set "notify," then the notification will be sent. Otherwise, the error mode will be set to abort and an error will be generated causing the message send to stop. abort can be called with a text string argument which will be passed on as the error text. For example:
%%abort "Terminating this message send."%%
memberstable, emailaddrcolumn, and memberidcolumn
The name of the members table and the column names of the email address and member ID are all configurable by the server administrator. These commands return these names. When writing custom Tcl commands for library code, use these commands in place of members_, EmailAddr_, and MemberID_
memberidchar
Each member in the ListManager database has a unique ID and for each ID, there is a special code character which is a single letter. The ID and code character are used in many places for the validation of confirmations and unsubscribes. The code character makes it harder to accidentally unsubscribe someone else by typing in the wrong ID. The memberidchar command returns the member ID and the code character as a string that can be used in your scripts. For example:
To unsubscribe, click here:
http://www.yourdomain.dom/u?id=%%memberidchar%%&c=F
emailunsub
The member unsubscribe address is fundamental to the operation of a mailing list and our failsafe unsubscribe feature makes it convenient and simple for a member to unsubscribe. The emailunsub command will return an unsubscribe address specific to the current member. This command is equivalent to the legacy subst tag version: $subst('email.unsub') which is also supported via the Tcl command "subst email.unsub".
Legacy Subst Commands
ListManager has always had some mail-merge capability using special subst tags of the form $subst('argument'). All of these commands are supported in the new mail-merge functionality. Prior to 6.0h, the form of the tag was subst (argument), which conflicted with the Tcl subst command. For 6.0h and higher, the form of the new tags is:
lmsubst (argument)
where (argument) is the same argument that was passed to the original $subst version.
IMPORTANT NOTE: When upgrading to ListManager 6.0h and higher, those who have created scripts using the ListManager subst command must update them to use lmsubst.