05 - Web Services (EN)05a - IBM i Web Services miscellanea

Consuming REST web services with basic authentication

It happens more and more often to come across the request of having to manage integrations with applications, potentially installed on any type of platform. On the one hand this kind of activity scares us a lot because we are all used to locally managing on our preferred server any known need and delegating the problem to third parts or other development languages but alas (or maybe luckily!) nowadays business informations systems are not based only on a single server and increasingly they also use hybrid systems with part of the applications executed locally and others distributed in the cloud or in remote sites. No problem we can handle the most disparate needs. In this article we see how to call a web service using RPG and managing the result (positive or negative) of the call.


A good method for communicating with this heterogeneous ecosystem is to use web services. In fact, with a single protocol (http / https) we can do what we previously had to manage with connections between DB, text file, flag file or even worse with “boundary” libraries with physical files. A further great advantage of using web services is synchrony: when the client consumes a remote service it contacts a sw component that resides on the server and at the exact moment of the call it knows the answer. In fact, the operating system manages the entire conversation between client and server so it is able to report communication problems, authentication, … In this way we could potentially insert a call to a service even interactively communicating to the user the data requested to an application located on the other side of the globe or notifying him/her that the application contacted for the data is not responding.


Let us now see an example of how the RPG language is able to generate a call to a service using basic HTTP authentication and manage the result of the call which could be XML or JSON. First of all we need to have the URL (myUrl variable) to contact and the credentials for authentication (myLogin variable) in format user:password.

First we define the variables we will use in this example.

D myClob          S                   SQLType(CLOB:1000000) inz;    
D myData          S          30000A   varying       
D myHeader        S           1000A   varying       
D myLogin         S            200A   varying       
D myLoginBase64   S            200A   varying       
D myUrl           S            300A   varying      

Now we generate the string containing the credentials encoded in base64 using a function made available to us by IBM in the SYSTOOLS library.

myLogin = 'petris:s3cr3tPassW0rd ';


exec sql PREPARE SQL_statement FROM :SQL_query;

DECLARE SQL_cursor CURSOR FOR SQL_statement;

OPEN SQL_cursor;

FETCH NEXT FROM SQL_cursor INTO :myLoginBase64;

CLOSE SQL_cursor; 

If you receive the error SQL0332 related to the conversion of characters it is possible that your job is running with parameter CCSID (65535). To change it will be enough to modify the value of this parameter before it turns the query and modifying it on the contrary later.

Next step is to populate a string variable with xml text that we will use to pass our login credentials to the server

 myHeader = '<httpHeader><header name="Authorization" value="Basic ' + %trim(myLoginBase64) + '"/></httpHeader>'; 

We just have to make the call using another function contained in the SYSTOOLS library and check the server response:

myUrl = 'http://nnn.nnn.nnn.nnn:nnnn/reports/report?id=KJFD556XDS';

// ws call with authentication

//  ws call without authentication 

// manage the result 
myData = %subst(myClob_data:1:myClob_len);
exsr parseMyData;   

// the parseMyData routine can use the JSON_TABLE function in case the result is returned in JSON format, 
// otherwise you can use the built-in function xml-into to manage data in xml format 


In this example I called a report of my Business Intelligence software at the end of a calculation processing of my ERP to make sure that the report data is updated as soon as the calculation is finished but the same concept can be applied to any other need.

An example of response could be: {“Status”: 200, “Text”: “Report updated successfully”}

Whether the call closes successfully or not the message structure will remain unchanged (only the Status and Text values will change) and I will manage the result with the JSON_TABLE function. If the communication fails or the url is incorrect or the authentication is not completed, the SQL engine will take care of notifying me of the problem encountered (eg code SQL4302). Just retrieve the text of the message from the QSQLMSG message file and you’re done.

In this example, the system returns an HTTP 401 error but the database engine handles any response with any message. The detail of the HTTP error codes can be found on wikipedia (https://en.wikipedia.org/wiki/List_of_HTTP_status_codes)

Related Posts

It is the question I always ask my fellow programmers when they ask me something: "Have you read the manual?". Read more

SQL and DB2 for i – FAQ & Howtos – (Part 4 EN)

Here is the fourth part of this "FAQ & Howtos" for SQL and DB2 for i ... the previous 3 Read more

IBM i System Management: FAQ & Howto (Part 4) IT

This is the third part of the IBM i system management FAQ & Howto collection: you can see the other Read more

Writing messages to the job log with SQL

LPRINTF is a simple utility that allows you to write messages in the current job's log, directly with SQL. Let's Read more

About author

Head of Application development & Systems integration I have always worked on "i". Programmer, analyst, project manager, computer systems analyst, ... lots of hats but a single preferred platform. I collaborate with the FAQ400 community because I firmly believe in teamworking and I hope for a continuous and constant growth of the ecosystem of which I feel part.

Leave a Reply

%d bloggers like this: