IBM i SQL HTTP Services

How to Retrieve Web Stuff using SQL

For years I've been using the free iSockets library I wrote a decade ago to do simplified SOCKETS programming within RPG IV. Thousands of programmers have downloaded and integrated iSockets into their own code. There are two other SOCKETS packages out there, Scott Klements free HTTPAPI is one of the most popular, and there is also Brad Stone's GETURI -- to be fair, Brad's GETURI is maintained, while the other two have been pretty stable for the last few years.

But today much of what iSockets and the other tools were being used for (Restful web services, HTTP page or file retrieval, posting orders) is now possible without writting a single line of SOCKETS code or using any of those tools. I give you IBM SQL HTTP Services.

IBM HTTP Services allow you to effectively do an HTTP or HTTPS request from within RPG or as part of an SQL Scripting tool, such as SQL Query or the RUN SQL SCRIPTS tool that is part of the current version of Client Access (known as ACS). One huge advantage of course is that you do not have to know SOCKETS programming and SSL/TLS support via the IBM GSK toolkit is unnecessary as the interfaces do everything for you.

The Services support the POST and GET methods along with methods most people don't use, such as PUT and HEAD. There are distinct versions of the services for each of the methods, or you can use the generalized version that includes an extra parameter for the Method. See my HTTP UDF/UDTF listing in Table 1.

Let's start off with an example. Let's download the YAHOO.com homepage. To do that, we would use the character form of the UDTF HTTPGETCLOB.

SELECT varchar(responseMsg,2048), varchar(responseHttpHeader,512)  
 from table( SYSTOOLS.HTTPPOSTCLOBVerbose(                         
'https://www.yahoo.com',                                           
'','') ) as YAHOO

In this example, I retrieve the first 2048 characters of the Yahoo.com homepage. Let's break it down.

SELECT VARCHAR(responseMsg,2048), varchar(responseHttpHeader,512)

The SQL HTTP Service or UDTF being used is HTTPPOSTCLOBVerbose(). The "CLOB" in the name means "Character Large Object". You've probably heard of BLOB (Binary Large Object). a CLOB is basically the same technology but restricted in the same was as a character varaible or VARCHAR (VARYING in RPG IV). Therefore it can be cast (convert to) a character string for use to more easily use. So in the first line I cast both the web page HTML response that is returned (the field named RESPONSEMSG contains the actual returned data or the Yahoo home page in this context) adnt he field named RESPONSEHTTPHEADER contains the, HTTP Headers that the Yahoo HTTP server sends you. Normally both of these things would have been received by a web brower and the home page displayed. But now you can read that information into your RPG program via EXEC SQL and the INTO clause. Or, if you're like me, you use something like SQL Query File or the ACS Run SQL Scripts to run the statement interactively.

FROM TABLE( SYSTOOLS.HTTPPOSTCLOBVerbose(

The HTTP POST CLOB VERBOSE or HTTPPOSTCLOBVERBOSE function returns the HTTP response and headers as two columns (noted above). The function exists in library SYSTOOLS as do all the HTTP functions. Since it is a UDTF "User Defined Table Function" we have to cast it as a Table, hence the Table(...) wrapper around it. The POST means to send the request using the HTTP POST method, there is also a GET method.

The CLOB means that you want the response back as a character LOB (instead of a BLOB). This is good for textual responses, such as XML, plain text, and non-compressed javascript object notation (JSON) formats.

Finally, the Verbose is used to uniquely name this function over the UDF versions. But it also means the headers are returned.

The synax of the HTTPPOSTCLOBVerbose UDTF is:

HTTPPOSTCLOBVerbose( 'url' , 'http-headers-as-XML', 'request-data-to-be-sent')

The URL parameter can be HTTP:// or HTTPS:// followed by the domain or IP address, such as https://www.yahoo.com

If you need a subpage, you would include it here, using normal browser syntax, such as: https://www.yahoo.com/inbox

The HTTP-Headers are specified as a relatively cool XML document. You use the outer tag of <httpHeader> and then include each header you wish to pass as a nested tag.

For example to include the Content-Type header, you would specify:

'<header name="content-type" value="text/html"></header>'

as the second parameter.

The 3rd and final parameter is the request-data you would send to the HTTP server. For example, if you had filled out a form with your First and Last name on a web page and pressed SUBMIT, it would build the request data as something like this:

FIRST=Bob&LAST=Cozzi

This is the request data and shall be passed as the 3rd parameter.

When you've specified request data, it is normally good practice to also include the Content-Length HTTP Header.

Normally you should calculate this value and not hard-code it in your programs.

RPG with Embedded SQL

To use this function in RPG, you can leaverage the SQL CLOB datatype by declaring it on the Definition Specs with the SQLTYPE keyword.

            // SQLTYPE(CLOB_FILE) subfields:
            //   xxxx_NL = Length of file name
            //   xxxx_DL = Length of data being read/written
            //   xxxx_FO = File options
            //   xxxx_NAME CHAR(255) = IFS File name

     D replyStmf       S                   SQLTYPE(CLOB_FILE)
     D replyHeader     S                   SQLTYPE(CLOB : 10000)
     D iReply          S              5I 0
     D iHdr            S              5I 0

The two CLOB's include on CLOB File and one CLOB variable:

1. REPLYSTMF - The CLOB that is mapped to an IFS file so that the response from the HTTP server is written directly to a file on the IFS.
2. REPLYHDR - The CLOB variable that receives the response headers from the HTTP server. This is going to contain XML and can be inspected for a valid response. You could use XML-INTO or some of the XML SQL Services to parse out the response headers.

To use the REPLYSTMF CLOB on the SQL statement's INTO clause we have to set it up a bit.

replyStmf_name = '/home/cozzi/yahooHome.html';
replyStmf_NL = %len(%trimR(replyStmf_name));
if (mbropt = '*REPLACE');
   replyStmf_FO = SQFOVR;   // Create or replace IFS file
else;
  replyStmf_FO = SQFAPP;    // Create or Add to IFS file.
endif;

Once we have the Name, Name Length, and File Option specified, we can use this variable on the INTO clause as follows:

exec sql
   SELECT responseMsg, responseHttpHeader 
   INTO  :replyStmf:iReply, :replyHdr:iHdr 
   FROM   table( SYSTOOLS.HTTPPOSTCLOBVerbose(                         
                 'https://www.yahoo.com', '','') ) as YAHOO;

When this statement runs successfully, it would store the contents of the Yahoo.com homepage into the /home/cozzi/yahoohome.html file on the IFS. The CCSID of the file depends on a couple of things. If you want to control the output file's CCSID, you should create the file first, specifying the file's CCSID, and then use the SQFAPP file option in the REPLY_FO variable.

That's all for now. Give it a try and let me know how it works out in the comments below. I'll be updating this as I have time, with more examples and information.

Table 1: HTTP SQL Functions