LISTSERV Tech Tip: How can I configure LISTSERV to retrieve list subscribers from a web services API?

Q: How can I configure LISTSERV to retrieve list subscribers from a web services API?

In a previous tech tip, we looked at how to connect LISTSERV to an existing database containing recipient email addresses. Both of the methods described in that tech tip rely on LISTSERV making a connection directly to the DBMS. Now, we'll look at how to configure LISTSERV to retrieve subscribers from a web services API using standard tool sets like SOAP and XML.

For the purpose of this tech tip, we'll assume that you have access to an existing web services API. We'll assume that you know the URL to access that API and that you have the necessary credentials (e.g., a security token) and any parameters (e.g., query method name) that you need to pass to the API in order to retrieve a recipient list of email addresses. We'll also assume that you're generally familiar with how to submit queries to your web services API and how to parse the results. Additionally, we'll assume that you're generally familiar with LISTSERV site configuration and have adequate experience with your programming or scripting language of choice.

In previous tech tips, we've looked at LISTSERV's Dynamic Query support for running LDAP and DBMS queries to fetch membership for LISTSERV lists. LISTSERV has a third method for running Dynamic Queries, which is a Dynamic Query Exit. A Dynamic Query Exit enables LISTSERV to call an external script to fetch a list of email addresses and to return the results of that script in a format that LISTSERV understands. In principle, this means that any data source to which access can be scripted can be used as a recipient data source for a LISTSERV Dynamic Query. In this example, we'll be writing a script to query a web services API for list membership.

Suppose we have a web services API with a method called 'GetStudents' that accepts a security token ('SecurityToken') and two parameters: 'Department' and 'GraduationYear'. The XML to submit to the API may look like this:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetStudents xmlns="http://www.example.edu/">
      <SecurityToken>string</SecurityToken>
      <Department>string</Department>
      <GraduationYear>string</GraduationYear>
    </GetStudents>
  </soap:Body>
</soap:Envelope>

We'll include the SecurityToken in the script that calls the API, and we'll expect LISTSERV to provide the Department and GraduationYear as arguments when it calls the script. (Those arguments will be supplied in the LISTSERV list configuration header so that multiple lists can use the same exit script but called with different arguments.) The return from the API will be an XML document supplying at a minimum the email address of the recipient, and optionally additional fields. For example, the web services output may look as follows:

<Subscribers>
  <Subscriber diffgr:id="Subscriber1" msdata:rowOrder="0">
    <Email>user1@example.edu</Email>
    <Name>User One </Name>
  </Subscriber>
  <Subscriber diffgr:id="Subscriber2" msdata:rowOrder="1">
    <Email>user2@example.edu</Email>
    <Name>User Two </Name>
  </Subscriber>
  <Subscriber diffgr:id="Subscriber2" msdata:rowOrder="2">
    <Email>user3@example.edu</Email>
    <Name>User Three </Name>
  </Subscriber>
</Subscribers>

Our script will need to provide this API output in a format that LISTSERV can parse, as detailed below.

In our example API, LISTSERV needs to pass two arguments to the exit script: Department and GraduationYear. Below, we'll configure the list configuration header to include those arguments. LISTSERV will pass them to the exit script in Plain-Text Counted Format. An argument is converted to plain-text counted format by prepending the length of the string, formatted as plain-text decimal characters, and an underscore. For instance, the string "Hello" becomes "5_Hello" and the empty string becomes "0_". Encoded strings are concatenated with no spaces or other delimiters in between. So if our Department is 'English' and our GraduationYear is '2017', the argument passed from LISTSERV to the exit script will be:

'English' is a 7-character string, and '2017' is a 4-character string. The other arguments are null.

The exit script needs to parse this argument, pass it to the API and read the result. It then needs to write the result to a file called exit.results, in Plain-Text Counted Format with a header line. So the exit script would need to convert the sample output XML above to a file called exit.results, in the following format:

4_MAIL4_NAME
17_user1@example.org8_User One
17_user2@example.org8_User Two
17_user3@example.org10_User Three

LISTSERV reads this exit.results file, parses the plain text counted format, and mails to the resulting email addresses.

On Windows, the exit script must go in \LISTSERV\MAIN, and must be a Windows Command (*.CMD) file. The CMD file may itself be a wrapper to call an external program. In our example, we'll make a script called \LISTSERV\MAIN\SAMPLE.CMD. That CMD file may call another script written in Visual Basic, .NET, C#, or any other supported scripting language.

On UNIX, the exit script goes in ~listserv/home, and may be in any supported scripting language. It should not have a file extension, and the filename is case-sensitive. Our sample script will be ~listserv/home/SAMPLE.

Once you've tested the exit script, you need to make LISTSERV aware of it by adding the exit to LISTSERV's site configuration. For our SAMPLE exit, the site configuration entries as entered through the LISTSERV web configuration interface might look as follows:

Because these are new site configuration variables, to add them through the web interface, you would navigate to Server Administration > Site Configuration > Site Configuration. Enter the variable name DYN_QUERY_SAMPLE, then Search. In the empty box that is returned, you would enter the value for the variable (TYPE=EXIT E-MAIL=MAIL NAME=NAME EXITPARM1=%1 EXITPARM2=%2). Click "Update" to save the variable, then do the same for the DYN_QUERY_EXIT variable.

In the example above, we're telling LISTSERV that there is dynamic query exit script called SAMPLE. On Windows, LISTSERV will look for \LISTSERV\MAIN\SAMPLE.CMD; on UNIX, it will be ~listserv/home/SAMPLE. DYN_QUERY_SAMPLE describes the configuration of the dynamic query. The TYPE is EXIT, telling LISTSERV that this is an exit script and not a DBMS or LDAP query. The E-MAIL and NAME parameters tell LISTSERV which columns in the exit.results file correspond to the email and name of the recipient. In this example, we have two arguments that we pass to the exit script, represented by EXITPARAM1 and EXITPARAM2. We could have hard-coded those into the site configuration variable (EXITPARAM1=English EXITPARAM2=2017), but in this case we're using %1 and %2, meaning that the values for these parameters will be supplied in the list header. This lets us have multiple lists using the same dynamic query exit, but passing different values for the parameters.

In the list header, we tell LISTSERV to use the SAMPLE query by way of the Sub-Lists keyword, and tell it what parameters to pass to the query. For our 2017 English grads list, that looks as follows:

Using the same Dynamic Query Exit, we can have other lists that pass different parameters:

When we mail to the list, LISTSERV runs the query (passing the parameters to it), the query calls the exit script (passing the parameters as an argument in plain-text-counted format), the exit script talks to the web services API and returns the results to exit.results, and LISTSERV reads the exit.results file and treats the resulting address list as a LISTSERV sub-list. This means that you can have multiple queries per list. So for a list for 2018 graduates in Math and Computer Science, you can do the following in a single list configuration:

In this case, LISTSERV runs the query once for each set of parameters, and combines the results. Because it uses the LISTSERV Sub-List mechanism, if there are any duplicate addresses (i.e., a student with a double major in Math and Computer Science), LISTSERV will remove duplicates prior to mailing.

Let's put it all together into a single working example. In this example, we've got a dynamic query exit on Windows that calls a CMD wrapper script to run a Perl script that queries a web services API.

rem Argument should be n_1234 format
set mydate=%date%
perl C:\LISTSERV\MAIN\API-FETCH.PL %1 >> C:\LISTSERV\LOG\SAMPLE-EXIT-%mydate%.LOG
copy exit.results exit.last

The %1 is the argument called with the script by LISTSERV, and contains the plain-text-counted formatted parameters.

#!/bin/perl
# Version 20150305
# Liam P Kelly <liam@lsoft.com>
# Script to query web services to use as list memberships
# Intended to be called as a DQL LISTSERV list exit

# The security password 
$securitykey='1234-5678-0987';
# SQL procedure to run
$storedprocedure='fetchSubscribers';

# Parse the department and year from the arguments called 
# Args will look something like 2_CS4_20150_0_0_

# Capture input argument from LISTSERV
$lsvinput = "$ARGV[0]";
chomp $lsvinput;
# Input looks like this: 2_CS4_20150_0_0_
# Split to two fields based on first underscore:
# (2)(CS4_20150_0_0_)
@deptsplit = split(/_/,"$lsvinput",2);
# $deptsplit[0] is the number of characters to read (and remove) at the beginning of $deptsplit[1]
$department = substr($deptsplit[1], 0, $deptsplit[0], "");
# Split the remaining string (4_20150_0_0_) into:
# (4)(20150_0_0_)
@yearsplit = split(/_/,"$deptsplit[1]",2);
# $yearsplit[0] is the number of characters to read (and remove) at the beginning of $yearsplit[1]
$year = substr($yearsplit[1], 0, $yearsplit[0], "");

# Uncomment for Debugging
# print "Deparment: $department\n";
# print "Year: $year\n";

# Uncomment for SOAP debugging
# use SOAP::Lite ( +trace => 'all', readable => 1, outputxml => 1, );

# Requires the DotNet SOAP client and LibXML
use XML::LibXML;
use SOAP::XML::Client::DotNet;

# Create an object with basic SOAP::Lite config stuff
  my $soap_client = SOAP::XML::Client::DotNet->new({
    # These URLs provided by the web services host
    # Apparently the trailing slash on the xmlns (but not the uri) is necessary
    uri                 => 'http://www.example.edu',
    proxy               => 'http://www.example.edu/api_client/listserv.asmx',
    xmlns               => 'http://www.example.edu/',
  });

# This is the XML we're passing to the web service
my $xml = "<Securitykey>$securitykey</Securitykey><Department>$department</Department><GraduationYear>$year</GraduationYear>";

# Actually do the call
if( $soap_client->fetch({
                         'method' => $storedprocedure,
                         'xml' => $xml,SOAP::XML::Client::DotNet
                     }) ) {
                      # Get result as a string
                      $xml_string = $soap_client->results();

  } else {
    # Got an error
    print "Problem using service:" . $soap_client->error();

  }

# Grab the SQL Result XML
my $parser = XML::LibXML->new();
my $tree = $parser->parse_string($xml_string);
my $root = $tree->getDocumentElement;
my @Rows = $root->getElementsByTagName('Subscriber');

# Each table row is one recipient
# Print the e-mail addresses and names in plain-text counted format
# 4_MAIL4_NAME
# 15_joe@example.com8_Joe User
# 16_jane@example.com9_Jane User

# Open the output file
open (OUTFILE,">exit.results");

# Print header row
print OUTFILE "4_MAIL4_NAME\n";
# Print records
foreach my $row (@Rows) {
    my $email  = $row->getElementsByTagName('Email');
    my $name = $row->getElementsByTagName('Name');
    my $emaillen = length($email);
    my $namelen = length($name);
    print OUTFILE "$emaillen\_$email$namelen\_$name\n";
}

close (OUTFILE);

exit;