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

By -


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

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

**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.


Comments

Post a Comment

Popular posts from this blog

IBMi (AS400) fans only ' SQLCODE values: Dear readers, unleash your suggestions!

By -
Image
This RPG code uses a DOU loop to fetch data from a cursor named C1 until the SQLCODE is not equal to 0, indicating the end of the recordset. Inside the loop, it clears a data structure (RecordDsC1), fetches data into it, and then checks if the fetch was successful. If successful, it assigns values and writes to an output file. After the loop, it checks if the SQLCODE is not 100, indicating unexpected termination, and displays a warning message if needed. // Loop until SQLCODE is not equal to 0 dow sqlCode = 0 ;   // Clear the data structure RecordDsC1   clear RecordDsC1;   // Fetch data into RecordDsC1 using cursor C1   exec sql fetch C1 into :RecordDsC1;   // If fetch was successful (SQLCODE = 0)   if sqlcode = 0 ;     // Assign values and write to output     W1QUEU = rDsW1QUEU;     write W1SF1;   endif; enddo; // Check if SQLCODE is not 100 (indicating unexpected termination) if sqlCode <> 100;   // Build a warning message   wMsg='Warning: +    The selection has terminated
Read more

IBMi (AS400) fans only ‘ Memories (IBM Coding Forms)

By -
Image
 IBM Coding Forms Oh mom! No, not my sweet, sweet natural mom, but IBM, the mother of us RPG programmers. The adventure with IBM began way back in 1992 when my friend said to me: "They need smart guys at a software house in Cesena!" And thanks to my dear friend, I ended up there, amidst the hills, good wine, Piadina Romagnola, and fresh pasta! It was December, and the company was an IBM partner. They asked me to take a course followed by an exam at IBM in Bologna. If I passed the test, they would hire me. Boom! Just 20 days later, there I was, at work! I started tinkering with the legendary IBM S/36 - today considered an informatics relic, but I swear, back then, it was a powerful machine! The first program I wrote, believe it or not, was on paper provided by the ancient scribes of IBM. Yes, I handwrote a program on paper. It felt more like a grandma's recipe than the result of my hard-earned studies! The "boss" said: "It looks decent, but load it onto the
Read more

Efficient WRKSPLF with WSF: How to Search string into spooled files, Sort, and Generate PDFs on IBMi

By -
Image
Searching Spooled Files Made Easy: A Unique Solution Managing spooled files efficiently is crucial for users dealing with numerous rows of print data. IBM's WRKSPLF command provides a standard way to interact with spooled files, but it lacks certain features that can significantly enhance user experience. In this article, we'll delve into a powerful functionality that IBM doesn't offer — the ability to search among spooled file lists. Our goal is to highlight the advantages of this capability and introduce a practical solution. The Limitations of Standard WRKSPLF While the WRKSPLF command is a handy tool for working with spooled files, it falls short in certain aspects. One prominent limitation is the inability to search efficiently through the list of spooled file rows. Users often find it challenging to quickly locate specific files based on criteria like creation date, status, or content. Introducing a Unique Solution: WSF (WorkSpooledFiles) To address the limitations of
Read more