IBM i (AS400) fans only ' Realtime exchange rate for any pair of physical and digital currency reading JSON data from a webservice with RPG, SQL and HTTPGETCLOB function
Realtime exchange rate for any pair of digital or physical currency using HTTP GET and JSON_TABLE reading with SQL HTTPGETCLOB.
This CLP is intended to test the FREX00.SQLRPGLE utility.
FREX00CL.CLP - convert exchange rates between two currencies
FREX00CL.CLP - convert exchange rates between two currencies
PGM PARM((&CURRENCY_1) +
(&CURRENCYN2) +
(&CURRENCY_3) +
(&CURRENCYN4) +
(&EXCHANGER5) +
(&EXCHANGED6) +
(&TIME_ZONE7) +
(&BID_PRICE8) +
(&ASK_PRICE9) +
(&ERROR_CODA))
DCL VAR(&CURRENCY_1) TYPE(*CHAR) LEN(03)
DCL VAR(&CURRENCYN2) TYPE(*CHAR) LEN(50)
DCL VAR(&CURRENCY_3) TYPE(*CHAR) LEN(03)
DCL VAR(&CURRENCYN4) TYPE(*CHAR) LEN(50)
DCL VAR(&EXCHANGER5) TYPE(*CHAR) LEN(15)
DCL VAR(&EXCHANGED6) TYPE(*CHAR) LEN(20)
DCL VAR(&TIME_ZONE7) TYPE(*CHAR) LEN(03)
DCL VAR(&BID_PRICE8) TYPE(*CHAR) LEN(25)
DCL VAR(&ASK_PRICE9) TYPE(*CHAR) LEN(25)
DCL VAR(&ERROR_CODA) TYPE(*CHAR) LEN(01)
CALL PGM(FREX00) +
PARM((&CURRENCY_1) +
(&CURRENCYN2) +
(&CURRENCY_3) +
(&CURRENCYN4) +
(&EXCHANGER5) +
(&EXCHANGED6) +
(&TIME_ZONE7) +
(&BID_PRICE8) +
(&ASK_PRICE9) +
(&ERROR_CODA) )
SNDUSRMSG MSG('1. From_Currency Code:' *CAT %TRIM(&CURRENCY_1))
SNDUSRMSG MSG('2. From_Currency Name:' *CAT %TRIM(&CURRENCYN2))
SNDUSRMSG MSG('3. To_Currency Code :' *CAT %TRIM(&CURRENCY_3))
SNDUSRMSG MSG('4. To_Currency Name :' *CAT %TRIM(&CURRENCYN4))
SNDUSRMSG MSG('5. Exchange Rate :' *CAT %TRIM(&EXCHANGER5))
SNDUSRMSG MSG('6. Last Refreshed :' *CAT %TRIM(&EXCHANGED6))
SNDUSRMSG MSG('7. Time Zone :' *CAT %TRIM(&TIME_ZONE7))
SNDUSRMSG MSG('8. Bid Price :' *CAT %TRIM(&BID_PRICE8))
SNDUSRMSG MSG('9. Ask Price :' *CAT %TRIM(&ASK_PRICE9))
SNDUSRMSG MSG(' :' *CAT %TRIM(&ERROR_CODA))
ENDPGM
FREX00 SQLRPGLE convert exchange rates between two currencies
FREX00 SQLRPGLE convert exchange rates between two currencies
**free
// **********************************************************************************************
// *
// Realtime Currency Exchange Rate Reader *
// *
// How to get json data from a webservice with RPG, SQL and HTTPGETCLOB function *
// *
// **********************************************************************************************
// *
// I have been using AlphaVantage's API service for financial market data and I am extremely *
// impressed with the quality of their data and the ease of use of their platform. *
// Their API is well-documented and easy to integrate into my projects, and I appreciate the *
// fact that they offer a free tier of service for small-scale projects. *
// The data provided by AlphaVantage is accurate and up-to-date, and I have found it to be *
// an invaluable resource for my test. *
// Their customer support team is also very responsive and helpful in answering any questions *
// I have had. *
// Overall, I highly recommend AlphaVantage to anyone who needs reliable and high-quality *
// financial market data. *
// Thank you, AlphaVantage, for providing such a great service! *
// *
// I have successfully used AlphaVantage's API "Realtime Currency Exchange Rate" in this *
// RPGfree with SQL embedded sample program *
// *
// Type to test this program: *
// CALL PGM(FREX00CL) PARM(('GBP') (' ') ('EUR') (' ') (' ') (' ') (' ') (' ') (' ') (' ')) *
// **********************************************************************************************
// *
// Another interesting AlphaVantage API is the 'Global Quote'. I plan to publish an example *
// that uses this API shortly. *
// https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol=IBM&apikey=demo *
// *
// **********************************************************************************************
ctl-opt option(*nodebugio:*srcstmt:*nounref) dftactgrp(*no)
ALWNULL(*USRCTL) ;
dcl-c cURL1 'https://www.alphavantage.co/';
dcl-c CURL2 'query?function=CURRENCY_EXCHANGE_RATE';
dcl-c cURL3 '&from_currency=';
// EUR
dcl-c cURL4 '&to_currency=';
// JPY
dcl-c cURL5 '&apikey=EGFIVJ80H10MTIP6';
dcl-s URL char(123);
dcl-ds Pgm psds qualified ;
User char(10) pos(358); // 358 367 Current user
end-ds ;
dcl-pi FREX00;
CURRENCY_1 char(03);
CURRENCYN2 char(50);
CURRENCY_3 char(03);
CURRENCYN4 char(50);
EXCHANGER5 char(15);
EXCHANGED6 char(20);
TIME_ZONE7 char(03);
BID_PRICE8 char(25);
ASK_PRICE9 char(25);
ERROR_CODA char(01);
end-pi;
exec sql set option Commit = *None;
exec sql set option SRTSEQ = *LANGIDSHR;
// **************************************************
// start working
// **************************************************
ERROR_CODA = 'E'; // Error
URL = cURL1 + cURL2 + cURL3 + %trim(CURRENCY_1) + cURL4 +
%trim(CURRENCY_3) + cURL5;
exec sql select * into :CURRENCY_1, :CURRENCYN2, :CURRENCY_3, :CURRENCYN4,
:EXCHANGER5, :EXCHANGED6, :TIME_ZONE7, :BID_PRICE8,
:ASK_PRICE9
from JSON_TABLE(SYSTOOLS.HTTPGETCLOB(:URL, null),
'lax $."Realtime Currency Exchange Rate"' COLUMNS (
FromCurrencyCode varchar(03) path '$."1. From_Currency Code"',
FromCurrencyName varchar(50) path '$."2. From_Currency Name"',
ToCurrencyCode varchar(03) path '$."3. To_Currency Code"',
ToCurrencyName varchar(50) path '$."4. To_Currency Name"',
ExchangeRate varchar(15) path '$."5. Exchange Rate"',
LastRefreshed varchar(20) path '$."6. Last Refreshed"',
TimeZonex varchar(03) path '$."7. Time Zone"',
BidPrice varchar(25) path '$."8. Bid Price"',
AskPrice varchar(25) path '$."9. Ask Price"'
) empty on error
);
if sqlcode = 0;
ERROR_CODA = ' ';
endif;
*inlr = *on;
Type the following to test the program:
CALL PGM(FREX00CL) PARM(('GBP') (' ') ('EUR') (' ') (' ') (' ') (' ') (' ') (' ') (' '))
I appreciate all the comments made on this blog.
Type the following to test the program:
CALL PGM(FREX00CL) PARM(('GBP') (' ') ('EUR') (' ') (' ') (' ') (' ') (' ') (' ') (' '))
I appreciate all the comments made on this blog.
Excelent, thanks for sharing
ReplyDeleteUse the newer qsys2.http_post
ReplyDelete