Last Updated on 20 October 2019 by Roberto De Pedrini
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.
Introduction
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.
Example
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 ';
SQL_query = 'SELECT SYSTOOLS.BASE64ENCODE(CAST('''+ %trim(myLogin) + ''' AS VARCHAR(200) CCSID 1208)) FROM SYSIBM.SYSDUMMY1';
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 exec sql SELECT SYSTOOLS.HTTPGETCLOB(:myUrl, :myHeader) INTO :myClob FROM SYSIBM.SYSDUMMY1; // ws call without authentication exec sql SELECT SYSTOOLS.HTTPGETCLOB(:myUrl, '') INTO :myClob FROM SYSIBM.SYSDUMMY1; // 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
Conclusion
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)
Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more
DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more
Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more
Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more
Why not replace
SQL_query = ‘SELECT SYSTOOLS.BASE64ENCODE(CAST(”’+ %trim(myLogin) + ”’ AS VARCHAR(200) CCSID 1208)) FROM SYSIBM.SYSDUMMY1′;
…
with
exec sql set :myLoginBase64 = systools.base64encode( :myLogin) as varchar(200) ccsid 1208));