Simple SQL RPGLE Example to connect to website and consume webservice
Let's write a SQL script to connect and get a reply from this website.
I know the owner of that website and vouch for him. Barely 😉
So - connect to nicklitten.com/sample.json and it will return this reply as json:
"firstName": "Nick",
"surName": "Litten",
"webSite": "https://www.nicklitten.com",
"active": true
}
Test that yourself by simply typing that address into your browser.
Assuming my little webservice is up and running we can now test that the HTTPGETCLOB SQL Function is alive and well on your IBM i System.
What is HTTPGETCLOB?
HTTPGETCLOB is a function available in IBM i that allows you to perform an HTTP GET request and retrieve the response as a CLOB (Character Large Object). This function is particularly useful when you expect to receive a large amount of text data from a web service, such as JSON or XML responses.
We can use HTTPGETCLOB like this:
SQL to consume this webservice
SURNAME,
WEBSITE
from json_table(
SYSTOOLS.HTTPGETCLOB(
'https://nicklitten.com/sample.json', NULL),
'$'
columns(
FIRSTNAME char(50) path '$.firstName',
SURNAME char(50) path '$.surName',
WEBSITE char(50) path '$.webSite'
) error on error
) as X
If you run this script from your IBM i System you will get the reply from nicklitten.com like this:
Testing 2 way - from ACS SQL Tool
Test this in the IBM i ACS Run SQL Scripts tool and you will see the reply like this:
Using STRSQL from the Command Line
Or we can test it old school from the IBM i SQL command Line Interface:
Thats the hard bit done. We know the SQL works, and the webservice is up and running so how about we write a short little RPG program and embed that SQL in it.
We can consume the webservice and simply spit the status back into the jolog for this test.
Embed this SQL in an RPG Program:
SQLRPGLE Program to do the same thing:
/title Simply consume a webservice response from nicklitten dot com
// ----------------------------------------------------------
//
// Service - SIMPWEBSQL
//
// ----------------------------------------------------------
// COMPILE NOTES:
// Change the source location to match yours:
//
// CRTSQLRPGI OBJ(WEBSERVICE/SIMPWEBSQL)
// SRCSTMF('/home/nicklitten/source-webservices/SIMPWEBSQL.sqlrpgle')
// COMMIT(*NONE) OBJTYPE(*PGM) DBGVIEW(*SOURCE) CVTCCSID(*JOB)
//
// ----------------------------------------------------------
// Modification History:
// 2022-06-11 V1.0 Created by Nick Litten
// ----------------------------------------------------------
ctl-opt
main(SIMPWEBSQL)
pgminfo(*PCML:*MODULE:*DCLCASE)
option(*srcstmt:*nodebugio:*noshowcpy)
/if Defined(*CRTSQLRPGI)
dftactgrp(*no) actgrp('NICKLITTEN')
/endIf
copyright('SIMPWEBSQL: Version 1.0 June 2022');
dcl-ds psds PSDS qualified;
program char(10) pos(1);
end-ds;
//-- SIMPWEBSQL - Consume an external internet webservice from
//-- my IBMi System. Get the JSON result and let SQL decompose
//-- that from JSON into fields that I can use.
dcl-proc SIMPWEBSQL;
dcl-pi SIMPWEBSQL end-pi;
// Output values
dcl-s g_firstname varchar(50);
dcl-s g_surname varchar(50);
dcl-s g_website varchar(50);
// result string humans who like to know what happened
dcl-s g_result char(50);
monitor;
// consume external webservice
exec sql
select firstname, surname, website
into :g_firstname, :g_surname, :g_website
from JSON_TABLE( SYSTOOLS.HTTPGETCLOB (
'https://nicklitten.com/sample.json', null), '$'
COLUMNS(
firstname VARCHAR(50) path '$.firstName',
surname VARCHAR(50) path '$.surName',
website VARCHAR(50) path '$.webSite'
) error on error
) as x;
Select;
when sqlcode = 0;
g_result = 'Completed normally';
when sqlcode = 100;
g_result = 'No data found';
when sqlcode > 0;
g_result = 'Completed with warning';
when sqlcode < 0;
g_result = 'Did not complete normally';
endsl;
// Display values returned
DSPLY sqlcode;
DSPLY g_firstname;
DSPLY g_surname;
DSPLY g_website;
return;
on-error ;
dump(a);
dsply ('*** Webservice(' + %trim(psds.program) + ') has failed!');
endmon ;
end-proc;
Compile it using:
WEBSQL.sqlrpgle') COMMIT(*NONE) OBJTYPE(*PGM) DBGVIEW(*SOURCE) CVTCCSID(*JOB)
and thats it!
Testing the program
Now it's time to call the program to see what it does:
I told you that would be simple!