Using SQL to retrieve information from the internet

There are many different web sites that offer information that would be useful to consume in IBM i. I have seen several examples of doing this, and I thought I would show my example, which I think, shows how simple it can be.

In my example I want to retrieve the share prices for the three biggest technology companies in the city I live in:

• Dell, stock symbol: DELL
• IBM, symbol: IBM
• Tesla, symbol: TSLA

After research I found that the web site that IMHO best suited my requirements, and is free, is Finnhub stock API. If you are looking to do the same thing you will find there are other web sites that provide the same information.

I signed up with Finnhub, and created the unique token I would need to communicate successfully with them. In these examples I am not going to share my token, I am going to use: "xxxxxxxxxx" to denote the token. If you copy this example you will need to replace that with your own token.

I will be using Db2 for i to fetch the data from the web site and to extract the data I want from the received JSON array. I will be using:

• QSYS2.HTTP_GET:  I have given the library for the scalar function as I will be using the HTTP_GET in the QSYS2 library, not the older version in SYSTOOLS
• JSON_TABLE:  This function allows me to separate the parts of the JSON array into separate columns

My first example shows how I worked out the parameters, etc., using ACS's Run SQL Scripts, RSS:

01 SELECT * FROM JSON_TABLE( 02 QSYS2.HTTP_GET('https://finnhub.io/api/v1/quote?symbol=TSLA&token=xxxxxxxxxx', 03 '{"sslTolerate": "true"}' 04 ), 05 '$' COLUMNS( 06 CurrentPrice DECIMAL(10,2) PATH '$.c', 07 HighPrice DECIMAL(10,2) PATH '$.h', 08 LowPrice DECIMAL(10,2) PATH '$.l', 09 OpenPrice DECIMAL(10,2) PATH '$.o', 10 PreviousClose DECIMAL(10,2) PATH '$.pc' 11 ) 12 ) ;

Line 1: I want to show all in the results all of the columns I define in the JSON_TABLE table function.

Lines 2 - 4: These are the two parameters I need to pass to HTTP_GET. The first is the URL, which includes the share symbol and my token. The second, line 3, is the HTTP header that is used to help with the authentication, which is unique to the service used.

Line 5: The '$' is what IBM's documentation describes as the sql-json-path-expression. In my experience the "$" denotes the root, or highest level of the path.

Lines 6 – 10: The definitions of the columns I want to retrieve from the JSON. It looks pretty straight self-explanatory. The path, for example: '$.c', is the name of the element in the JSON array that contains the information I want to retrieve into the column.

The results were:

CURRENTPRICE HIGHPRICE LOWPRICE OPENPRICE PREVIOUSCLOSE ------------ --------- -------- --------- ------------- 404.66 412.42 400.54 404.11 411.15

As share prices fluctuate if you were to use this example you will see different results.

Having shown how simple the SQL statement is, I can now show an RPG program using it:

01 **free 02 ctl-opt main(Main) option(*srcstmt) ; 03 dcl-proc Main ; 04 dcl-c URL 'https://finnhub.io/api/v1/quote?symbol=' ; 05 dcl-c Token '&token=xxxxxxxxxx' ; 06 dcl-c Header '{"sslTolerate":"true"}' ; 07 dcl-s ApiURL varchar(200) ; 08 dcl-s Symbol char(5) ; 09 dcl-s Price packed(10 : 2) ; 10 for-each Symbol in %list('DELL' : 'IBM' : 'TSLA') ; 11 ApiURL = URL + %trimr(Symbol) + %trimr(Token) ; 12 exec sql SELECT CurrentPrice INTO :Price 13 FROM JSON_TABLE( 14 QSYS2.HTTP_GET(:ApiURL,:Header), 15 '$' COLUMNS(CurrentPrice DECIMAL(10,2) PATH '$.c') 16 ) ; 17 if (SQLCOD = 0) ; 18 dsply (Symbol + ' $' + %char(Price) ) ; 19 else ; 20 dsply (Symbol + ' SQL error ' + %char(SQLCOD) ) ; 21 endif ; 22 endfor ; 23 return ; 24 end-proc ;

Line 1: To make my program run faster I am using a Main procedure, so that this program does not include the instructions for the RPG cycle. And I am including my favorite RPG control option.

Line 3: Start of the Main procedure.

Lines 4 – 6: I decided to have the parts that would change if you were to copy this example as constants. This way any necessary changes can be made here, rather that having to find the places, deeper into the program, to make any changes. I think the names of the constants explain their contents.

Lines 7 – 9: These are the definitions for the variables I will be using in the program.

Line 10: The FOR-EACH operation code will "read" all elements from an array, one at time, into the variable Symbol. Here I am "reading" a temporary array I created using the %LIST Built in Function, BiF, that contains the share symbols of the companies I am interested in.

Line 11: I am populating the variable ApiURL with the constants that contain the URL, share symbol, and token.

Lines 12 – 16: The SQL statement that is used to get the current share price.

Line 12: I am only interested in one column, the current share price, and when it is retrieved, I want to place the value into the variable Price.

Line 14: The URL, that includes the share symbol and token, is in the RPG variable ApiURL, which is why it is prefixed with a colon ( : ), and the RPG constant Header contains the header information to be passed, which is also prefixed.

Line 15: I only want the current share price.

Lines 17 – 22: If the SQL code returned from the SQL statement is zero it completed successfully, and I am using the DSPLY op-code to show the share symbol and the price, line 18. If it encountered any error, I am displaying the share symbol and SQL code, line 20, to allow for someone to analyze the reason for the error.

After compiling the program, I called it and the following was displayed:

DSPLY DELL $404.08 DSPLY IBM $270.81 DSPLY TSLA $404.66

I could modify this program to insert into a DDL table, then add the modified program to the job scheduler. This way I can capture the share prices at the same time on the days I care about, without the need for human intervention.

 

You can learn more about the HTTP_GET from the IBM website here.

 

This article was written for IBM i 7.6, and should work for some earlier releases too.