Updating Currencies from a Web Service

In this example, we demonstrate how to use ACF to fetch data from a web service and update a local currency table with this information. To accomplish this, we pull currency names from a local table and construct requests to the web service. For this example, we use an external plugin called Troi URL, which will later be integrated into the ACF plugin itself.

Table Definition

Here is the definition of the currency table:

Example

package Currencies "Functions to Update Currency Table";

/*
 - Create a request URL to Norges Bank WEB-service for currency information.
*/
function request_url(string kurskode)
    string url = format("https://data.norges-bank.no/api/data/EXR/B.%s.NOK.SP?startPeriod=%s&EndPeriod=%s&format=csv-:-comma-false-y&lastNObservations=1",
        kurskode, string(date(now()) - 7, "%Y-%m-%d"), string(date(now()), "%Y-%m-%d"));
    return url;
end

/*
 - Fetch all the tracked currency codes from db_Currency, get the exchange rate (spot), and update the table.
*/
function UpdateCurrencyTable_NorgesBank()
    string avail = "AUD, BDT, BGN, BRL, BYN, BYR, CAD, CHF, CNY, CZK, DKK, EUR, GBP, HKD, HRK, HUF, IDR, ILS, INR, ISK, JPY, KRW, LTL, MMK, MXN, MYR, NZD, PHP, PKR, PLN, RON, RUB, SEK, SGD, THB, TRY, TWD, USD, ZAR";

    string kurser = ExecuteSQL("SELECT Valutakode, Valutanavn FROM db_Currency"; "||"; "|*|");
    array string a_kurs = explode("|*|", kurser), a_line, resLines, resFields;
    int ant = sizeof(a_kurs);
    int i;
    string url, result, dato, val, kurskode, sql;

    for (i = 1, ant)
        a_line = explode("||", a_kurs[i]);
        kurskode = a_line[1];

        if (kurskode != "NOK") then
            $url = request_url(a_line[1]);
            result = @TURL_Get("-Encoding=UTF8"; $url)@;

            print "-" * 80 + "\n" + result + "\n";

            resLines = explode("\n", result); // We always get two lines: header + data line
            resFields = explode(",", substitute(resLines[2], "\"", ""));
            dato = resFields[1];
            val = resFields[2];

            sql = format("UPDATE db_Currency SET Valutadato = DATE '%s', ValutaKurs = %s WHERE Valutakode = '%s'", dato, val, kurskode);
            result = ExecuteSQL(sql);
        end if
    end for

    return "OK";
end

In this example, we first create a request URL to a web service provided by Norges Bank to obtain currency information. We then fetch all the tracked currency codes from the local db_Currency table, retrieve the exchange rate (spot) from the web service, and update the table with the obtained data.

This example demonstrates how ACF can be used to interact with web services and update local databases with external data.