Webservices and Pick

Introduction

Articles on RESTful webservices, an article on Dojo, and another on jQuery easyUI combined to interest me in a concept of using webservices to connect to a 40 year old database environment.

My 40 year old database is Pick or Multi-value. Very popular 20 years ago, but not much written about it now although it still runs some very large systems around the world in healthcare, banking, and motor parts.

Background

Pick Databases

There are a number of flavours available, but I am using scarletdme which is an open source cut of openQM.

The advantage of this type of database is that it is impervious to most forms of attack including SQL injection. It also handles large amounts of data and could be considered a precursor to no-SQL databases and big data.

The disadvantage was that it is primarily text based and the communications are green screen or proprietary connections but this article explains how that has changed.

REST Basics

Representational state transfer (REST) is a style of software architecture and the thing about REST is that it is not tied to any particular technology or platform, requires no licence and requires no proprietary connector. A REST web service is a simply a service implemented using HTTP and the principles of REST.

Whilst the type of data supported is often JSON , XML or YAML, the easy option for the Pick world is to choose JSON (Javascript Object Notation).

JSON is text -[{"id":"EUR","text":"Euro"},{"id":"HUF","text":"Hungarian forint"},{"id":"GBP","text":"Uk pound"},{"id":"USD","text":"Us dollar"}] - is a JSON object. This is easily constructed by a routine in a Pick database from “LIST CURRENCY DESCRIPTION”. The triviality of the transformation demonstrates that this puts text based MultiValue right back in the game.

When we consider these RESTful services, it becomes clear that the service is something provided by the database server and the presentation becomes the domain of the web builder.

Using the Code

The first step is to make a connection point to the database from the web. This is achieved by creating a sites available connection within Apache2.

* create a directory to store our entry point
sudo mkdir /var/www/qmweb

* edit /var/www/qmweb/webapp
001 #!/bin/sh
002 cat – | qm –abase –quiet WEBAPP

That creates an entry point for account base where WEBAPP is our connect routine.

Head off and create a website at http://localhost/qmweb.

* edit /etc/apache2/sites–available/qmweb
001 <Location /qmweb>
002 SetHandler cgi–script
003 Options +ExecCGI
004 </Location>

Enable the site and set up the permissions correctly and restart Apache.

sudo a2ensite qmweb
sudo chown –R www–data:www–data /var/www/qmweb
sudo chmod –R 755 /var/www/qmweb
sudo /etc/init.d/apache2 reload

The major point of interest is that everything on our website downstream of /var/www/qmweb is passed to WEBAPP and we have complete control of everything that happens or is sent to our website.

To recap on how the web works, if the request is GET then the variables are sent in an environment variable QUERY_STRING. If the request is POST then the variables are sent as a buffered data string and must be retrieved with an IN or INPUT statement.

Environment variable CONTENT_LENGTH is a count of the number of characters in the data string.

The following code snippet shows how this information can be retrieved:

HTTP$ELEMENTS = ENV('QUERY_STRING')
IF CONTENT_LENGTH THEN
   * this is post
   ECHO OFF
   FOR J = 1 TO CONTENT_LENGTH
        IN A
        HTTP$DETAILS := CHAR(A)
   NEXT J
   ECHO ON
END
MX = DCOUNT(HTTP$DETAILS,'&')
EPS = 0
FOR J = 1 TO MX
   EPS += 1
   KA = FIELD(HTTP$DETAILS,'&',J)
   HTTP$DATA<1,EPS> = URLDECODE(FIELD(KA,'=',1))
   HTTP$DATA<2,EPS> = URLDECODE(FIELD(KA,'=',2))
NEXT J

MX = DCOUNT(HTTP$ELEMENTS,'&')
FOR J = 1 TO MX
   EPS += 1
   KA = FIELD(HTTP$ELEMENTS,'&',J)
   HTTP$DATA<1,EPS> = URLDECODE(FIELD(KA,'=',1))
   HTTP$DATA<2,EPS> = URLDECODE(FIELD(KA,'=',2))
NEXT J

MX = DCOUNT(HTTP$COOKIE,';')
FOR J = 1 TO MX
   KA = TRIM(FIELD(HTTP$COOKIE,';',J))
   COOKIE$DATA<1,J> = URLDECODE(FIELD(KA,'=',1))
   COOKIE$DATA<2,J> = FIELD(KA,'=',2)
NEXT J

The requested page is in environment variable PATH_INFO so we can extract the requested page.

We now have all of the information to respond to the request.

It must be remembered that every response we send must be prefaced with a Content-Type and a line feed like:

PRINT "Content–Type: text/html"
PRINT
PRINT R.DBREAD

where R.DBREAD is our webpage response.

How should we deal with webservice requests? In order to differentiate between the different types of web service we want to handle, the methods and properties need to be standardised. Since we are in complete control of our website, we can define those standards and they can be particular and peculiar to our website. Definitions of webservice for our purpose are described here.

The convention employed is that the service requested will consist of the database table plus the service definition. A service requesting a list from the country table will be described as country.lst.

In our WEBAPP routine, we define how to deal with our webservices and how to respond in case of error. The following code snippet explains this.

BEGIN CASE
   CASE REQUEST_METHOD = 'GET'   ; CALL JSON.FORMAT
   CASE REQUEST_METHOD = 'POST'  ; CALL PWEBSAVE
   CASE REQUEST_METHOD = 'PUT'   ; CALL PWEBSAVE
   CASE REQUEST_METHOD = 'DELETE'; CALL PWEBSAVE
   CASE REQUEST_METHOD = 'LIST'  ; CALL PFORMLIST
   CASE 1
        FATAL$ERROR = WEBERROR(6034:":Method:":REQUEST_METHOD:" not available")
END CASE
BEGIN CASE
   CASE REQUEST_METHOD = 'GET' OR REQUEST_METHOD = 'LIST'
        PRINT "Content–Type: text/html"
        PRINT
        PRINT COMI
   CASE FATAL$ERROR
        PRINT "Content–Type: text/html"
        PRINT
        PRINT '{"msg":':DQUOTE(EMGTEXT):'}'
   CASE 1
        PRINT "Content–Type: text/html"
        PRINT
        PRINT '{"success":true}'
END CASE

In this code, there are some variables with global scope so that appropriate messages can be returned. Because this is a webservice request, the requesting webpage is expecting a json response not a web page.

So, all of the components to build a generic website handler have now been defined. If the request is htm then load a webpage from the database. If the request is for a webservice, call the correct webservice and deliver the data. The format of the response is controlled by the server and the construct is such that we could write a web page generator for each table that we want to serve because all of the calls are of a generic nature.

I put together a website at pwchest.no-ip.biz to showcase this technology and provide code and links to go and build your own version. The website only uses a multi-value database, HTML5, CSS and some JavaScript - it is a PHP-free and SQL-free zone.

History

• 2013-08-13: First draft
• 2013-08-19: Additions
• 2013-08-19: Modified the json string as it wasn't valid json