RPGLE - Web Requests with Embedded SQL
Once again I write another blog post about how great I find embedded SQL within RPG - this time we talk about web requests. This isn't really much of a blog post either.. it's really just a 'text version' of a session I am giving at the RPG and DB2 Summit.
These are the things we're going to look at:
SQLTYPE
HTTPGETCLOB
HTTPPOSTCLOB
Not a massive list, but a lot to talk about.
SQLTYPE
SQLTYPE is a new keyword in RPG for when you're declaring variables.. only really useful if you're using Embedded SQL in your programs. When the SQLTYPE keyword is used, it secretly creates a data structure under the covers (but only for certain SQL types)
These are some of the available types:
CLOB
CLOB_LOCATOR
CLOB_FILE
BLOB
BLOB_LOCATOR
BLOB_FILE
DBCLOB_LOCATOR
DBCLOB_FILE
The list could go on..
So this makes the syntax Dcl-S gSQLVar SQLTYPE([SQLTYPE]:[len]);.
When I say 'creates a data-structure' - I mean it really does. Take a look at this diagram:
HTTPGETCLOB
HTTPGETCLOB has two parameters. The first is the URL which it will send the request to and the second are the headers you want to give the request You can read more about the headers parameter here in the 'Sending customized HTTP header values' section.
In this 'get clob' example, I send a simple GET request to a URL (which you can replace with a host variable) and it will give the response back. It's pretty neat! Notice that when we declare our CLOB, we also give it a type.
Dcl-S WebResponse SQLTYPE(CLOB:256); Exec SQL SET :WebResponse = SYSTOOLS.HTTPGETCLOB ('https://api.bitcoinaverage.com/ticker/EUR/','');
If you debug this statement in STRDBG, after you've steped through it you can do eval WebResponse_Data to view the response and eval WebResponse_Len to view the length of the response.
You can do this same SQL statement within an SQL client (Run SQL Scripts in ACS or STRSQL..): SELECT SYSTOOLS.HTTPGETCLOB('https://api.bitcoinaverage.com/ticker/EUR/', '') FROM SYSIBM.SYSDUMMY1
HTTPPOSTCLOB
HTTPPOSTCLOB is kinda like HTTPPOSTCLOB, but with the 'post clob' you get an extra parameter which allows you to pass the body of the request (which makes it a POST request).
I like to keep my code quite tidy, so I have my response variable (gData) and I usually have a data structure to store the URL, header and body of the request in - then we initialize them with the relevant data for the request.
Dcl-S gData SQLTYPE(CLOB:2000); Dcl-Ds Request Qualified; URL Char(128); Head Char(1024); Body Char(1024); END-DS; Request.Body = '<?xml version="1.0" encoding="UTF-8" ?>' + '<soap:Envelope xmlns:soap=' + '"http://schemas.xmlsoap.org/soap/envelope/" ' + 'xmlns:xsi=' + '"http://www.w3.org/2001/XMLSchema-instance" ' + 'xmlns:xsd=' + '"http://www.w3.org/2001/XMLSchema" ' + 'xmlns:wiz="http://wizard.ws">' + '<soap:Body>' + '<thing:addressInput>' + '<Locale>pl</Locale>' + '<ServiceAddressId>0</ServiceAddressId>' + '<AccountNumber>1234</AccountNumber>' + '<MemoOpenDate>2016-01-01</MemoOpenDate>' + '</thing:addressDetailInput>' + '</soap:Body>' + '</soap:Envelope>'; Request.Head = '<httpHeader>' + '<header name="Content-Type" ' + 'value="text/xml;charset=UTF-8" />' + '<header name="Content-Length" value="' + %Char(%Len(%TrimR(Request.Body))) + '" />' //Length of the body + '<header name="Accept-Encoding" value="gzip,deflate" />' + '</httpHeader>'; Request.URL = 'http://yourserver:1234/SomeWS/SomeWebServices'; EXEC SQL SET :gData = SYSTOOLS.HTTPPOSTCLOB( :Request.URL, :Request.Head, :Request.Body );
If you then debug this EXEC SQL statement and eval gData you'll see the response length and the actual response:
Well I hope this generally a useful post.. I will go into a lot more detail during the session - this is just the basics.