Use Perl to query DME via HTTP

I haven't really found much information out there about how to use perl to access Lawson data through the DME's, so I figured I would share with you what I know.

Sometimes, reporting calls for more powerful measures than excel can handle through the Office Add-ins, and well, I know that a scripting language like perl is more than capable through my years of prior experience doing data analysis for a semiconductor company.  Sometimes, 65536 data records is a limitation of Excel that eliminates it as a solution option when trying to solve a problem.  Bring on perl!

So my goal was to figure out how to use perl to query Lawson data through the DME that is already in place.  This is important in our environment where our Lawson system is hosted, so we don't have the database connections or server-side programming that in-house Lawson setups would have.  So this means that I need to use HTTP access from perl to navigate through the HTTP basic authentication to query using the DME.  This is the only option that we have that would use Lawson security, as our ODBC connection is an all-access connection.

So let me show you how to use perl to whip the Lawson data into shape.

Step 1)

Find a DME query.  Easy.  Just use the Lawson Office Add-ins to develop the query, run the query once, click on the little magnifying glass at the top of the Office Add-ins tool to get the full DME call.  Done!

Step 2)

Very simple perl code.  For this example, pulling Company, Employee, first and last name from the EMPLOYEE table in our PROD environment via the DME query.

#!/usr/bin/perl

use strict;

use LWP::UserAgent;

Next goes your DME query that you pulled from the Office add-ins.

# copy and paste the URL directly from the Office Add-ins Query Wizard

# IMPORTANT: if your server has a port, you NEED to add it in, otherwise the auth won't work

my $simpleURL = "http://my.lawson.server.com:30077/cgi-lawson/dme.exe?PROD=PROD&FILE=EMPLOYEE&INDEX=EMPSET1&KEY=1&FIELD=COMPANY;EMPLOYEE;FIRST%2DNAME;LAST%2DNAME&MAX=600&OUT=CSV&DELIM=%09&NOHEADER=TRUE";

Next, initialize the perl LWP::UserAgent module to do the HTTP request.  If you don't know your authentication realm, copy and paste the DME query into a blank web browser window, hit enter, and see what the prompt says.  Or just run this script, and an error will pop up that shows you, more details on that down below.

my $ua = LWP::UserAgent->new;

# set the basic HTTP auth credentials

# arguments are server:port, authentication realm, username => password

$ua->credentials('my.lawson.server.com:30077','Lawson Restricted Access','username'=>'password');

my $response = $ua->get($simpleURL);

Now we will verify the response, and print out an error that will contain the reason for failure.

if($response->is_success) {

  print $response->content;

}

else {

  # this will print out any errors

  # IMPORTANT: This also will tell you what your auth realm is if you don't know it

  die "Error: ", $response->header('WWW-Authenticate') || 'Error accessing', "\n", $response->status_line, "\n at $url\n Aborting";

}

Here is the full code listing for you copy and pasters.

#!/usr/bin/perl

use strict;

use LWP::UserAgent;


# copy and paste the URL directly from the Office Add-ins Query Wizard

# IMPORTANT: if your server has a port, you NEED to add it in, otherwise the auth won't work

my $simpleURL = "http://my.lawson.server.com:30077/cgi-lawson/dme.exe?PROD=PROD&FILE=EMPLOYEE&INDEX=EMPSET1&KEY=1&FIELD=COMPANY;EMPLOYEE;FIRST%2DNAME;LAST%2DNAME&MAX=600&OUT=CSV&DELIM=%09&NOHEADER=TRUE";



my $ua = LWP::UserAgent->new;

# set the basic HTTP auth credentials

# arguments are server:port, authentication realm, username => password

$ua->credentials('my.lawson.server.com:30077','Lawson Restricted Access','username'=>'password');

my $response = $ua->get($simpleURL);



if($response->is_success) {

  print $response->content;

}

else {

  # this will print out any errors

  # IMPORTANT: This also will tell you what your auth realm is if you don't know it

  die "Error: ", $response->header('WWW-Authenticate') || 'Error accessing', "\n", $response->status_line, "\n at $url\n Aborting";

}