Bitcoins and IBMi / AS400 ? Webservices from SQL DB2 for i

2
1183

This is a good drawing card to catch your eyes at this post where we use some DB2 for i HTTP Functions …

Sorry, this is not a porting of Blockchain and Bitcoins applications on our loved IBMi … but we can use new SQL HTTP Functions to call Webservices … get JSON data … store them in a table with a Blob field and parse JSON only with some SQL statements. I hope not let you down !

 

We’ll see only 3 UDFs and UDTFs … stay tuned on http://www.all-about-i.info to see more next days …

  • HTTPGETCLOB: Call a Rest Webservice in GET
  • JSON2BSON: Store JSON in a Blob field
  • JSON_VAL: Get values (parsing) from a JSON

Normally, when I need to consume Webservices from my RPG Applications I use HTTPAPI utility form big Scott Klement … thank you Scott !, but now it’s all more simplex with news HTTP Functions … and I want to share with you some of my first experiences.

This is only an example for consuming web service from RPG and SQL … untill now my clients normally ask me for use webservices for get Euro/Dollars change rates, or sending SMS or managing files csv, doc, pdf from Dropbox or GoogleDocs but, if I had’nt titled this post “Bitcoins and IBMi / AS400” somebody would read this post ? !

 

Let’s get started

HTTPGETCLOB e JSON2BSON

dcl-proc store_data ;
exec sql
insert into qtemp.jsondata (
Select 'BitCoin-EUR' ,
'/tmp/',
systools.json2bson(json_info) as json_info
From (Values(SYSTOOLS.HTTPGETCLOB(
replace(trim('https://api.bitcoinaverage.com/ticker/EUR/'),
' ','%20'),'')))
x(json_info) );
end-proc;

Here you can see how SYSTOOLS.HTTPGETCLOB “call” a BitCoinAverage’s  WebService  and SYSTOOLS.JSON2BSON store JSON in a Blob field of a temporary table.

 

Funzione JSON_VAL

dcl-proc Extract_data ;
exec sql
select
JSON_VAL(JSON_INFO,'24h_avg','f') AS Avg_Rate ,
JSON_VAL(JSON_INFO,'ask','f') AS Ask_Rate,
JSON_VAL(JSON_INFO,'bid','f') AS Bid_Rate,
JSON_VAL(JSON_INFO,'last','f') AS Last_Rate,
JSON_VAL(JSON_INFO,'timestamp','s') AS Timest_String
into
:Avg_Rate,
:Ask_Rate,
:Bid_Rate,
:Last_Rate,
:Timest_String
from qtemp.jsondata
where file_name='BitCoin-EUR';
END-PROC;

Here we can see how to parse JSON with JSON_VAL and retrieve data from a JSON doc.

 

HTTP e CCSID and Decimal Point

I’m in Italy and I have 280 CCSID … to use HTTP Functions I need to set CCSID 37 … CHGJOB CCSID(37)

But I have also a problem with decimal point … in this case I don’t solve my problem neither with DECEDIT(‘.’) in ctl-opt (H spec), neither with SET OPTION DECPMT=*PERIOD … JSON_VAL return always rounded number from Jsond fload (‘f’) data … Somebody can help me ? Maybe something in java default.proprietes ?

 

Conclusions

HTTP Functions open our applications in a simplest way to webservices world and we can be more hopeful for our future and future of our loved system.

 

Complete source of my little test

ctl-opt dftactgrp(*no) decedit('.');
//-------------------------------------------------------
// Test HTTPGETBLOB: Reading Bitcoin Exchange Rate
// via API Rest, JSON, SQL UDF
//-------------------------------------------------
dcl-s Avg_Rate packed(9:2);
dcl-s Ask_Rate packed(9:2);
dcl-s Bid_Rate packed(9:2);
dcl-s Last_Rate packed(9:2);
dcl-s Timest_String varchar(50);
dcl-pr qCmdExc ExtPgm('QCMDEXC');
Cmd char(1000) options(*varsize) const ;
CmdLenn packed(15:5) const ;
END-PR;
DCL-S Cmd char(1000);
// Change CCSID for HTTP Functions
Cmd='CHGJOB CCSID(37)';
qCmdExc(Cmd:%len(Cmd));
// Create a temporary table to store JSON Data
create_temp_table();
// Call Rest API and store JSON data to temp table
store_data();
// Read and parse data from JSON in a BLOB field
extract_data();
// Display some output values
DSPLY Avg_Rate;
// Reset Current JOB to DEFAULT Values
Cmd='CHGJOB CCSID(*USRPRF)';
qCmdExc(Cmd:%len(Cmd));
// Exit
*inlr = *on ;
//---------------------------
// store_data
// Call BitCoinAverage API to retrieve current BitCoin Rate
// and store JSON in my temp table (in a Blob Field)
//---------------------------
dcl-proc store_data ;
exec sql
insert into qtemp.jsondata (
Select 'BitCoin-EUR' ,
'/tmp/',
systools.json2bson(json_info) as json_info
From (Values(SYSTOOLS.HTTPGETCLOB(
replace(trim('https://api.bitcoinaverage.com/ticker/EUR/'),
' ','%20'),'')))
x(json_info) );
end-proc;
//---------------------------
// extract_data
// Extract data from Blob Table, parse JSON for specific values
//---------------------------
dcl-proc Extract_data ;
exec sql
select
JSON_VAL(JSON_INFO,'24h_avg','f') AS Avg_Rate ,
JSON_VAL(JSON_INFO,'ask','f') AS Ask_Rate,
JSON_VAL(JSON_INFO,'bid','f') AS Bid_Rate,
JSON_VAL(JSON_INFO,'last','f') AS Last_Rate,
JSON_VAL(JSON_INFO,'timestamp','s') AS Timest_String
into
:Avg_Rate,
:Ask_Rate,
:Bid_Rate,
:Last_Rate,
:Timest_String
from qtemp.jsondata
where file_name='BitCoin-EUR';
END-PROC;
//---------------------------------
// Create_Temp_Table
// Create a temp table to store JSON Data
// returned from Rest API "call" ....
// Useful to debug this service
//---------------------------------
DCL-PROC Create_Temp_Table;
exec sql
CREATE OR REPLACE TABLE QTEMP.JSONDATA
( FILE_NAME VARCHAR(64) CCSID 1144 NOT NULL,
IFS_COPY_PATH VARCHAR(256) CCSID 1144 NOT NULL,
JSON_INFO BLOB(64K) NOT NULL);
exec sql
delete from qtemp.jsondata
where file_name='BitCoin-EUR' ;
END-PROC;

Referenze

Accessing web services Using IBM DB2 for i HTTP UDFs and UDTFs

MC Press Online – TechTip: Store and Parse JSON Data Natively with DB2 for i

IBM Developerworks – IBM DB2 for i: JSON Store Technology Preview

2 COMMENTS

  1. Roberto,
    The ‘n’ formatter may be used instead of ‘f’ to return the value as a decimal. Then store it in a local, host variable. This converts it to a DECFLOAT(34) which is SQL-speak for packed Decimal. That should help, and if not, then wrap the result of JSON_VAL in SQL’s DEC(… , 7,6) or whatever you want to get it in the format you need.
    I’ve been using this a while and the documentation is mostly found on the IBM mainframe webiste, z/OS.
    Hope this helps.
    -Bob Cozzi

  2. Thank you Bob … I tried with ‘n’ formatter and dec() but nothing … every time I see rounded values … I think that I have t find solutions on Java settings for decimalpoint=decimal but I don’t know ho to do it

    I changed a part of code ..

    select
    JSON_VAL(JSON_INFO,’24h_avg’,’n’) AS Avg_Rate ,
    dec(JSON_VAL(JSON_INFO,’ask’,’f’), 9, 2) AS Ask_Rate,

    and I got Avg_Rate= 380.00 and Aks_Rage=378 in front of this Json returned :
    {
    “24h_avg”: 380.31,
    “ask”: 378.23,
    “bid”: 376.93,
    “last”: 377.76,
    “timestamp”: “Sun, 17 Apr 2016 20:21:20 -0000”,
    “total_vol”: 6644.5
    }