Web requests with Embedded SQL

[worksofliam]

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.

[dancarlosgabriel]

very neat and complete, any rpg guy can quickly pick it up. I wish they will see this. thanks Liam.

[inserasinghe]

This was very help full I am try to send csv file over HTTPPOSTCLOB and define request as CLOB variable and load from IFS file into that. However I will get HTTP 415 error. do you have any idea or experience doing something similar to that. If you have any such a expressions could you please share some knowledge. I will be really appreciated.

[mk1tools]

Good suggestion. I tried the httpgetclob function with a clob host variable.

dcl-s headers varchar(200) inz(' ');
dcl-s Output sqltype(clob:500000);
url = 'https://pkgstore.datahub.io/core/country-list/latest/data/json/data.json';
exec sql
  set :Output = cast(systools.httpgetclob(:url, :headers) as clob(500000));

I receive error SQL4302 with message "Abnormal end of file". My job is set to ccsid = 1144. Do you have any idea or experience about this? Thanks a lot far any suggestions. Marco Riva

[FlorianGradot]

Sometimes you will need to import certificates. https://www.ibm.com/support/pages/how-import-certificate-java-keystore-using-java-keytool