Category Archives: Technology

Adding variables to ad-hoc SQL queries with CL

Some time ago I mentioned using REXX to build and execute ad-hoc SQL queries. For the sake of completeness, here is the same solution implemented in CL using the RUNSQL command.

The program does exactly the same as the previously describe REXX script — extracts all records flagged with today’s date so that they can be reviewed at a later date. And here it is:

dcl &date6 *char 6
dcl &date8 *char 8
dcl &statement *char 255

/* First retrieve the current system date (QDATE)                             */
/* and convert it to an 8 character value                                     */

/* Next, build the SQL statement                                              */
           VALUE('insert into EXPATPAUL1/INPUTDATA' *bcat +
                 'select * from EXPATPAUL1/PRODDATA where dhdate =' *bcat +
runsql sql(&statement) commit(*none)


Flattr this!

Service Programs and Call Stack APIs

I’m a big fan of service programs. From a maintainability point of view, encapsulated procedures are great. And exported procedures, which mean you only need to develop any piece of functionality once, are even better.

However, I now find myself in the position of having to start creating a set of these from scratch (nothing to copy/paste) and, since they can be a bit fiddly, now seems like a good time to document the steps.

In the example that follows, I create a service program with a single procedure, RtvProgram, which returns the name of the program that called the service program. It sounds a bit recursive, I know, but bear with me. Whenever coding a display file (or a report, for that matter) I like to put the program name somewhere on the screen (top left, unless someone tells me otherwise). This means that when someone has a problem, we can very quickly identify what program they are talking about.

Obviously, hard-coding the program name in the display file would be easy. But code gets copied and pasted and, sooner or later, all hard coded values end up being wrong. I have also seen cases of one display file being used by two or more programs, so it is much better to put the program name in a field and retrieve it dynamically. That way, you are always getting the right program.

So on to the service program.

First, you need a copy member to contain the procedure prototype. You could, of course, code the prototype in each program that uses the service program, but that way madness lies.

In my case, I have created the copy member in QRPGLESRC and called it LSS001RP. It looks like this:

 * Retrieve Program Name
d RtvProgram      pr            10a   

And then you need to write the service program:

 * ---------------------------------------------------------------------- *
 * Program     : LSS001R                                                  *
 * Description : Program information service programs                     *
 * ---------------------------------------------------------------------- *
h nomain

 * ---------------------------------------------------------------------- *
 * Exportable Prototypes                                                  *
 * ---------------------------------------------------------------------- *
 /copy LSCLIB/qrpglesrc,lss001rp

 * ---------------------------------------------------------------------- *
 * API Prototypes                                                         *
 * ---------------------------------------------------------------------- *
d RtvCallStack    pr                  extpgm('QWVRCSTK')
d                             2000a
d                               10i 0
d                                8a
d                               56a
d                                8a
d                               15a

 * ---------------------------------------------------------------------- *
 * RtvProgram: Retrieve the program name                                  *
 * ---------------------------------------------------------------------- *
p RtvProgram      b                   export
d RtvProgram      pi            10a

d Var             ds          2000    qualified
d  BytAvl                       10i 0
d  BytRtn                       10i 0
d  Entries                      10i 0
d  Offset                       10i 0
d  Count                        10i 0

d JobID           ds                  qualified
d  QName                        26a   inz('*')
d  IntID                        16a
d  Res3                          2a   inz(*loval)
d  ThreadInd                    10i 0 inz(1)
d  Thread                        8a   inz(*loval)

d Entry           ds                  qualified
d  Length                       10i 0
d  Program                      10a   overlay(Entry: 25)
d  Library                      10a   overlay(Entry: 35)

d VarLength       s             10i 0 inz(%size(Var))
d RcvFormat       s              8a   inz('CSTK0100')
d JobIdFmt        s              8a   inz('JIDF0100')
d ApiError        s             15a
d i               s             10i 0

     RtvCallStack(Var: VarLength: RcvFormat: JobID: JobIdFmt : ApiError);
     for i = 1 to 2;
         Entry = %subst(Var: Var.Offset + 1);
         Var.Offset += Entry.Length;

     return Entry.Program;

p RtvProgram      e
 * ---------------------------------------------------------------------- * 

I’m not going to go into too much detail here. The service program LSS001R contains one procedure, RtvProgram which uses the QWVRCSTK API to retrieve the current call stack then it reads back two entries: The first entry is the service program and the second entry is the calling program. And this is the program name that it returns.

You now need to create the RPG Module. Note the terminology here — you are not creating a Bound RPG Program (it’s the difference between options 15 and 14 in PDM).

I also need the binding source. In this case, the member is called LSS001S and I have put it in the QSRVSRC source file. It looks like this:


Note that the capitalisation is actually important here.

And now I’m ready to create the service program:


Since I’m doing this from scratch, I need to create a binder directory:

CRTBNDDIR BNDDIR(LSCLIB/LSBNDDIR) TEXT('General purpose binding directory')

And add the service program to it:


And we’re ready to go. All I have to do now is make a couple of amendments to the main program to take advantage of the service program:

The control spec needs this line:

h bnddir('LSBNDDIR')                                                       

Obviously, I need to copy the prototype definition somewhere in the definition specification:

 /copy LSCLIB/qrpglesrc,lss001rp                                       

And when the program starts, I need to identify the name of the program:


     // Identify the current program
     program = RtvProgram();

And that’s it.

Flattr this!

Using EXTFILE to override files within RPG

So here’s the situation: Five warehouses populating five sets of files (same filenames, different libraries) and I have an RPGLE program that needs to read through each of these to accumulate dispatch information.

The traditional way of doing this would involve writing a CL program to OVRDBF to each file before calling the RPGLE program. This works, but it’s a bit messy and this can cause future maintenance problems. It’s much better, therefore, to specify the file (and library) to open within the RPGLE program itself. You can do this with the EXTFILE keyword.

Here’s an example:

 * ---------------------------------------------------------------------- *
 * Program     : LSX001R                                                  *
 * Description : Dynamically select which file to open                    *
 * ---------------------------------------------------------------------- *
h main(Main)
fMOVEMENTS if   e           k DISK    usropn extfile(filename)

d Main            pr                  extpgm('LSX001R')
d  library                      10a

dfilename         s             21a

 * ------------------------------------------------------------------------
 * Main Procedure
 * ------------------------------------------------------------------------
p Main            b
d Main            pi
d  library                      10a

     filename = %trim(library) + '/' + 'MOVEMENTS';
     open MOVEMENTS;

     // Do whatever processing on the file needs to be done...

     close MOVEMENTS;

p Main            e

Hopefully this is reasonably straightforward. In the file spec for the MOVEMENTS file, I have used EXTFILE with a variable filename to specify the actual file opened. I have also used the USROPN keyword as I need to populate the filename variable before I attempt to open the file.

Populating this field is pretty simple. I pass the library name to the program as a parameter and the first thing the Main procedure does is concatenate the library and file with a ‘/’ separator.

Now I can open the correct file, do whatever processing is necessary, and then close the file afterwards.

A similar approach, using the EXTMBR keyword can also be used to open a specific file member. You can, of course, combine EXTFILE and EXTMBR in order to dynamically determine both the file and member, if you really want to.

It should be noted that this will only work if you are using traditional database IO. Any embedded SQL will remain unaffected by anything you do in the F-Spec. If you want to do something similar with SQL, you will need to look into the CREATE ALIAS statement.

Flattr this!

Digital dufferdom

I have to admit that, when I heard that Nokia was planning to re-enter the smartphone market I was more than a little interested. And the Nokia 8 does sound very appealing — stock Android, frequent updates and all from a company that, for all its managerial missteps, has always been very good at engineering.

And then I saw this quote from Pekka Rantala who has the job of reviving the brand:

The phones resonate well with older generations – we’re not excluding them.

I have the horrible feeling that they know me too well.

Flattr this!

Automated philosophy

I am indebted to Crys for pointing me in the direction of InspiroBot:

[A]n artificial intelligence dedicated to generating unlimited amounts of unique inspirational quotes for endless enrichment of pointless human existence.

You know the sort of thing, those wannabe inspirational posters that people keep obliviously posting all over the internet.

What InspiroBot does is allow you to generate these at random by simply clicking on a button. The joy of it is that the program is a lot better at grammar than it is at content.

Here’s an example:

Are past lives the orgasm of a vacuum?

Flattr this!

Shotwell ate my photos

I’m not sure what happened, but when I launched the Shotwell photo management application a couple of days ago, it started deleting my entire library of photos and by the time I realised what was happening, they were gone. Fortunately I have a backup, but that only goes as far as the middle of 2016, for he rest PhotoRec proved to be a life-saver (note to self — next time make sure to uncheck all the file types you don’t want to recover).

What did strike me when recovering my JPGs was the number of files that have been sitting on my hard drive that I had never actively downloaded. Clearly these are all images on various websites that I have visited. While I understand that a browser needs to download the contents of a page in order to display it, I was slightly taken aback at just how much of my browsing history was captured in these deleted files.

For now, though, most of my photos are restored and I am continuing with the slow and painful process of going through the recovered photos, removing duplicates and copying them back into my photos folder.

Once this is done, I shall be uninstalling Shotwell and looking for a safer photo management solution. To be fair, it is possible that I did something foolish when launching Shotwell — although what, I have no idea — but even if that is the case, I am not going to trust an application that starts deleting stuff without warning.

And I really should start thinking about a better backup strategy.

Flattr this!

Merging multiple PDF files with pdfunite

One thing that using Linux has taught me is to always look for the simplest solution, because it probably exists. As it turned out in this case.

In this case, I was emailed a five page PDF document that I had to print, sign, scan and send back. Printing and signing, of course, was easy enough and I can scan the five pages to get five, separate PDF files. Merging these files back into a single document is where pdfunite comes in, and it really is as simple as:

pdfunite page1.pdf page2.pdf page3.pdf page4.pdf page5.pdf outfile.pdf

You can specify as many source files as you like and the last file is the destination file.

And if I’d known about pdfseperate, I could have probably avoided printing the entire document in the first place.

Flattr this!

Crowdsourcing road safety

Ping if you care: volunteers map cycling danger spots

The Brussels-Capital Region has launched a pilot project that will allow cyclists to contribute to a map showing the danger spots on the region’s roads. Secretary of state for road safety Bianca Debaets sent out 540 volunteers this week equipped with “pingers” linked to an app that highlights dangers.

Each volunteer uses an app, connected by Bluetooth to a piece of kit attached to their handlebars like a bicycle bell. If the cyclist feels unsafe on the road at any point, they tap once on the pinger and the app records the location.

Once the information is uploaded to the database, along with any feedback the cyclist wants to give, it can be added to a map of the most dangerous places for cyclists on the region’s roads. It would then be up to the authorities to do something to remedy the situation if possible – in the case of a dangerous junction, for example, though not in the case of a vehicle parked on a cycle path.

Ping If You Care is one of those ideas that is both brilliant and really obvious now that someone else has thought of it.

Flattr this!

Solving the wrong problem

Dave Winer thinks that podcast RSS feeds should be ghettoised.

Here’s the problem. If you put a link to the RSS feed alongside the links to iTunes and Stitcher and whatever else, you’re going to get a bunch of emails from users about how your site is broken. I know, because I’ve gotten those emails.

And here’s his answer:

Create a simple page that says “This is a link to our RSS feed. It’s used by developers and hobbyists to build their own listeners and it helps support innovation on the internet.”

This is a terrible solution, for a number of reasons.

Firstly, the suggested statement is flat-out not true. Speaking for myself, I don’t use iTunes or Stitcher. I use gPodder. If I find an interesting podcast I need an RSS feed to follow it — if you don’t give me a feed I’m not going to follow your content. It really is as simple as that.

This leads to the second problem, which is that Winer is assuming that proprietary feeds are the norm and should therefore be given preferential treatment to open standards. I’m not going to dispute the first part of this assumption but to present RSS as some curiosity that is only of interest to hobbyists is to consign it to history. If you want RSS to remain a viable standard, the RSS feed needs to be given at least the same precedence as the proprietary feeds.

As to the problem that Winer is trying to solve. How many people, really, are incapable of clicking on the correct link? A quick search across the corporate podcasts that I listen to reveals that neither the BBC nor The Guardian feel the need to make some special “your’re stupid” statement about RSS. In fact, The Guardian even manages to force a few extra clicks out of you regardless of what feed you choose.

Of course, the best approach is that taken by the Duffercast1. A single subscribe link takes you to all the feeds with no special statements about any of them, because some audiocasts have listeners who are capable of using the internet.


  1. Disclaimer: Yes, I am a duffer

Flattr this!

Smarter Coffee and Systemd User Units

One of the particularly handy features that systemd supports is the ability to set up unit files on a per-user basis. You simply put the unit files in your home directory and tell systemd to start using them.

I have a couple of these and, because I can never remember the details, I’m putting them here to save me trawling through the interwebs next time I change something.

The user level unit files all go in ~/.config/systemd/user

If this folder doesn’t already exists, you will need to create it.

You can then manage the unit by adding a --user switch to the normal systemctl commands.

Clear as mud, I know, so here’s an example.

Back in October I was given a Smarter Coffee coffee machine — this is a filter coffee machine that can connect to your home network in order to send alerts to your phone. On receiving this, my first thought was to wonder if I could direct these alerts to my laptop.

Some searching revealed that not only is this possible but someone else has already done it. So I forked nanab’s repository and started playing around with the code, managing to direct the notifications to the Gnome notifications area. All of this is available on GitHub, but the systemd bit is described below.

First, I should mention that I have a small binary file (smartercoffee) in /usr/local/bin that points to the actual code. This looks like this:

#! /bin/bash
python2 /path/to/smartercoffee/ --notify GNOME

So the service file (smartercoffee.service), which needs to be dropped in ~/.config/systemd/user, looks like this:

Description=Monitor the coffee machine



You can enable the service with:
systemctl --user enable smartercoffee

And start it with:
systemctl --user start smartercoffee

So now, whenever I boot up my laptop, the first thing it does is tells me the status of the coffee machine.

Flattr this!