How to assign CustomerID for ERMS with a Perl Rule

How to assign CustomerID for ERMS with a Perl Rule.

 

Goal :

Get CustomerID based on the “From Address” from an SQL Database and set the CustomerID for an Email with Perl Rule.

 

Prerequisites:

Voxtron Communication Center 2016 installed with Perl runtime as known in VCC2016 System Reference Guide.

 

 

 

Solution:

  • Goto VCCWebCenter.
  • Select ERMS Instance
  • Select your Mailbox and navigate to Routing Rules.

Screenshot

 

  • Add new Routing Rule and select “Type in a free expression email rule”

Screenshot

 

Copy the example to the wizard and save.

Screenshot

 

Example Perl Rule:

###################################################

use DBI;

 

#Declare Database Connection Settings

my $host     = ‘SQLServer\Instance’;

my $database = ‘Voxtron10’;

my $user     = ‘SQLUser’;

my $auth     = ‘SQLPassword!’;

 

 

#Connect to Database

my $dsn = “dbi:ODBC:Driver={SQL Server};Server=$host;Database=$database”;

my $dbh = DBI->connect($dsn, $user, $auth)

or die(“\n\nCONNECT ERROR:\n\n$DBI::errstr”);

 

 

#Prepare SQL Statement

my $sth = $dbh->prepare(”

SELECT

CustomID

FROM CustomerDatabase

WHERE Email = ?

“)

or die(“\n\nPREPARE ERROR:\n\n$DBI::errstr”);

 

#Execute the query

$sth->execute($From)

or die(“\n\nQUERY ERROR:\n\n$DBI::errstr”);;

 

# Read the matching records and print them out

while (@data = $sth->fetchrow_array()) {

$CustomerId = $data[0];

$Matched = “1”;

}

 

#Close sth Command

$sth->finish();

 

#Disconnect from Database

$dbh->disconnect;

###################################################

Leave a Reply