Get Euro Exchange Rates from ECB/BCE WebService

0
1505

Some of my clients every day or every week load Exchange Rates into ERP’s exchange tables … so I Googled for something and I stumbled upon this IBM guide: Accessing web services using IBM DB2 for i HTTP UDFs and UDTFs … I took the cue from it for this simple utility.

You can download this source code from here TS_BCE or taking a look to the following code …

As you can see, rates came from ECB/BCE XML WebService http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml and I use SQL DB2 for i SYSTOOLS.HTTPGETBLOB function and XML parsing functions to retrieve desired exchange rate.

You can pass to the procedure currency and date and getting out last exchange rate available.

Enjoy with source code:

ctl-opt dftactgrp(*no) ;

//———————————————————————–*
// TS_BCE: Get Exchange Rate from ECB/BCE Web Service
//———————————————————————–*

dcl-pr qCmdExc ExtPgm(‘QCMDEXC’);
Cmd char(1000) options(*varsize) const ;
CmdLenn packed(15:5) const ;
END-PR;

dcl-s Currency varchar(30);
dcl-s ExcDate date(*DMY);
DCL-S ExcRate Packed(11:4);

ExcDate=%date();
Currency=’USD’;

ExcRate=Get_ExcRate(Currency:ExcDate);

dsply Excdate;
dsply %editc(ExcRate:’K’);

*inlr=*on;

//———————————————————————–*
// ExchageRate Get last exchange rate from BCE WbeService
//———————————————————————–*
dcl-proc Get_ExcRate export;
dcl-pi Get_ExcRate Packed(11:4);
i_currency varchar(30) value;
i_date date(*MDY) value;
end-pi;

DCL-S Returnval Packed(11:4);
DCL-S Cmd char(1000);

// Change CCSID for HTTP Functions
Cmd=’CHGJOB CCSID(37)’;
qCmdExc(Cmd:%len(Cmd));

// Gest last ExchangeRate from BCE WebService
exec sql
select rate into :returnval from (
SELECT * FROM XMLTABLE ( XMLNAMESPACES
( DEFAULT ‘http://www.ecb.int/vocabulary/2002-08-01/eurofxref’ ,
‘http://www.gesmes.org/xml/2002-08-01’ AS “gesmes” ) ,
‘gesmes:Envelope/Cube/Cube/Cube’ PASSING XMLPARSE
( DOCUMENT SYSTOOLS.HTTPGETBLOB
(‘http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml’, ”))
COLUMNS currency CHAR(3) PATH ‘@currency’,
rate REAL PATH ‘@rate’,
time DATE PATH ‘../@time’ ) AS ExchangeRates) x
where CURRENCY = :i_currency
and TIME <= :i_date
order by TIME DESC;

// Reset Current JOB to DEFAULT Values
Cmd=’CHGJOB CCSID(*USRPRF)’;
qCmdExc(Cmd:%len(Cmd));

return returnval;
end-proc;