La séptima vida

...o el gato así lo espera/teme

Accessing a relational database with Modbus, in Perl

UPDATE: This article has been updated to work with the latest API as of October 3, 2015.

Imagine that you want a machine to pull its parameters from a database. It is tempting; no wonder that this kind of functionality is easy to find in SCADA systems. But let's do it in Perl, using Device::Modbus.

Setting up the database table

The idea is to query a database using the PLC of the machine. Because the PLC speaks Modbus, the request is going to be constrained to asking for a certain quantity of 16-bit registers from a given address. We are still far away from an SQL query.

We'll use a mapping between Modbus requests and SQL queries. Let's consider that we want to download a set of related parameters, and that we know how many parameters there are in this set.

As an example, let's play with the parameters for a welder. These might include a slope time, the current to apply during the weld, the duration of the weld, and the duration of a cooling period. Let's say that these four parameters belong to the set number 14 in the parameters database, and that they must be returned in a given order (slope, current, weld duration, cooling duration).

If we call 'page' each set of parameters, a suitable table for MySQL would look like this:

CREATE TABLE process_parameters (
    page          TINYINT UNSIGNED NOT NULL,
    col           TINYINT UNSIGNED NOT NULL,
    name          VARCHAR(30) NOT NULL,
    description   VARCHAR(140),
    data          MEDIUMINT NOT NULL
);

The field page represents... the page number! and col (for column) will keep the order of the parameters.

The data field is where we will stock the actual parameter value. Since we are using Modbus for the communication, parameters need to be at most 16 bits. I used a MEDIUMINT, which takes three bytes, because it might be interesting to stock both unsigned integers (between 0 and 65,535) and signed integers (between -32,768 and 32,767). Using the three bytes of a signed MEDIUMINT, we can store both types without running out of space or having further considerations.

In order to obtain all the parameters from page 14 of our table in the order expected by our PLC, we could use the following query:

SELECT data FROM process_parameters WHERE page=14 ORDER BY col

This is the query that shall be executed every time that a request for the parameters in page 14 is received. What is more, if we use place holders instead of hard-coding the page number, we would have a query that could fetch any parameter page that we could ever ask.

But wait... MySQL does not understand Modbus.

Using Device::Modbus to map Modbus requests to SQL queries

This is where Device::Modbus comes into play. As I described in a previous post, Device::Modbus::Server will help us respond to Modbus requests in a way that is completely open. It will route requests to defined handlers much in the way that web application frameworks route HTTP requests. Request handlers are defined within Device::Modbus::Unit sub-classes.

Let's define our unit. Clearly, we need a database connection, and we need it to be available to request handlers. Let's use Moo and benefit from its BUILD and DEMOLISH methods for establishing and closing the connection, and let's stock the database handler in an attribute of our unit.

The init_unit method is called while the unit is loaded into the server. This is where we must define our route handlers. In this case, let's add only one handler that will respond to any address and work with any number of requested parameters.

Finally, our route handler needs to execute the SELECT query for the requested page, and it must return the list of parameters. Note that we are getting one parameter per row in an array of arrays, and we must return them as a flat array.

package Test::Unit;

use DBI;
use Moo;
extends 'Device::Modbus::Unit';

has dbh => (is => 'rw');

# Connect to the database during object creation
sub BUILD {
    my $self = shift;
    my $dbh = DBI->connect(
'DBI:mysql:database=test_modbus', 'julio', 'secret', { RaiseError => 1, AutoCommit => 1 }); $self->dbh($dbh); } # Close database connection as object is destroyed sub DEMOLISH { my $self = shift; $self->dbh->disconnect; } # Called when object is added to server sub init_unit { my $unit = shift; $unit->get('holding_registers', '*', '*', 'retrieve_parameters'); } # Request handler sub retrieve_parameters { my ($unit, $server, $req, $addr, $qty) = @_; my $sth = $unit->dbh->prepare( 'SELECT data FROM process_parameters WHERE page=? ORDER BY col'); $sth->execute($addr); my $page = $sth->fetchall_arrayref; # Flatten list of parameters my @params; foreach (@$page) { push @params, @$_; } return @params; } 1;

The server

The server is actually quite simple, it is just a few lines. We create a unit object, we add it to the server, and we hit start. The program is:

#! /usr/bin/env perl

use Device::Modbus::TCP::Server;
use Test::Unit;
use strict;
use warnings;

my $server = Device::Modbus::TCP::Server->new(
log_level => 4,
min_servers => 1,
max_servers => 3,
min_spare_servers => 1,
max_spare_servers => 2,
max_requests => 1000,
);

my $unit = Test::Unit->new(id => 1);
$server->add_server_unit($unit); $server->start;

Testing

To test our little demonstration, we must first load some data into our table of parameters. Then we start our server, and we send it some requests. Let's write a client program:

#! /usr/bin/env perl

use Device::Modbus::TCP::Client;
use Modern::Perl;

my $client = Device::Modbus::TCP::Client->new();

my $req = $client->read_holding_registers(
unit => 1,
address => 14,
quantity => 4
);

$client->send_request($req) || die "Send error: $!";
my $response = $client->receive_response;
say "Values: " . join '-', @{$response->values}; $client->disconnect;

And finally, we get our sweet results. With logging set at its maximum, this is what the server shows:

julio@julio-lap$ sudo perl -Ilib server_tcp.pl 
Starting server
2015/10/03-10:50:08 Device::Modbus::TCP::Server (type Net::Server::PreFork) starting! pid(30840)
Binding to TCP port 502 on host * with IPv4
Group Not Defined. Defaulting to EGID '0 0'
User Not Defined. Defaulting to EUID '0'
Setting up serialization via flock
Beginning prefork (3 processes)
Starting "3" children
Parent ready for children.
Child Preforked (30843)
Child Preforked (30842)
Child Preforked (30844)
2015/10/03-10:50:15 CONNECT TCP Peer: "[127.0.0.1]:44194" Local: "[127.0.0.1]:502"
Received message from 127.0.0.1
Request: $VAR1 = bless( {
'unit' => 1,
'length' => 6,
'id' => 1,
'message' => bless( {
'function' => 'Read Holding Registers',
'quantity' => 4,
'address' => 14,
'code' => 3
}, 'Device::Modbus::Request' )
}, 'Device::Modbus::TCP::ADU' );
Routing 'read' zone: <holding_registers> address: <14> quantity: <4>
Match was successful
Response: $VAR1 = bless( {
'unit' => 1,
'id' => 1,
'message' => bless( {
'function' => 'Read Holding Registers',
'values' => [
6,
45,
20,
15
],
'code' => 3
}, 'Device::Modbus::Response' )
}, 'Device::Modbus::TCP::ADU' );
Binary response: 0-1-0-0-0-11-1-3-8-0-6-0-45-0-20-0-15

And in a different terminal, the client says:

julio@julio-lap$ perl client_tcp.pl 
Values: 6-45-20-15

Conclusion

While still untested in the “real world”, Device::Modbus starts to show its potential! This little demonstration shows how to query a database using a Device::Modbus::Server. If everything goes well, exactly the same unit can be mounted on a RTU server to receive requests via the serial port instead.

From here, the next step is to send requests from a PLC using Modbus RTU, and it would be nice to send requests via Modbus TCP using an HMI. The PLC I have available for testing cannot issue requests via the Ethernet port; it is only a slave for Modbus TCP.