Skip to content

Instantly share code, notes, and snippets.

@RainerRoss

RainerRoss/COPPERXML.SQLRPGLE

Last active March 20, 2022 17:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save RainerRoss/227443c2799f7b1d2fce91f64809e7a5 to your computer and use it in GitHub Desktop.
Save RainerRoss/227443c2799f7b1d2fce91f64809e7a5 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
CopperPrice.xml
<?xml version="1.0" encoding="UTF-8"?>
<quandl-response>
<dataset>
<id type="integer">19701916</id>
<dataset-code>PR_CU</dataset-code>
<database-code>LME</database-code>
<name>Copper Prices</name>
<description>LME Official Prices in US$ per tonne.</description>
<refreshed-at type="dateTime">2018-08-06T07:04:18Z</refreshed-at>
<newest-available-date type="date">2018-08-03</newest-available-date>
<oldest-available-date type="date">2012-01-03</oldest-available-date>
<column-names type="array">
<column-name>Date</column-name>
<column-name>Cash Buyer</column-name>
<column-name>Cash Seller &amp; Settlement</column-name>
<column-name>3-months Buyer</column-name>
<column-name>3-months Seller</column-name>
<column-name>15-months Buyer</column-name>
<column-name>15-months Seller</column-name>
<column-name>Dec 1 Buyer</column-name>
<column-name>Dec 1 Seller</column-name>
<column-name>Dec 2 Buyer</column-name>
<column-name>Dec 2 Seller</column-name>
<column-name>Dec 3 Buyer</column-name>
<column-name>Dec 3 Seller</column-name>
</column-names>
<frequency>daily</frequency>
<type>Time Series</type>
<premium type="boolean">false</premium>
<limit nil="true"/>
<transform nil="true"/>
<column-index nil="true"/>
<start-date type="date">2018-08-01</start-date>
<end-date type="date">2018-08-03</end-date>
<data type="array">
<datum type="array">
<datum type="date">2018-08-03</datum>
<datum type="float">6166.0</datum>
<datum type="float">6167.0</datum>
<datum type="float">6197.0</datum>
<datum type="float">6198.0</datum>
<datum nil="true"/>
<datum nil="true"/>
<datum type="float">6295.0</datum>
<datum type="float">6305.0</datum>
<datum type="float">6350.0</datum>
<datum type="float">6360.0</datum>
<datum type="float">6355.0</datum>
<datum type="float">6365.0</datum>
</datum>
<datum type="array">
<datum type="date">2018-08-02</datum>
<datum type="float">6062.5</datum>
<datum type="float">6063.0</datum>
<datum type="float">6090.0</datum>
<datum type="float">6091.0</datum>
<datum nil="true"/>
<datum nil="true"/>
<datum type="float">6185.0</datum>
<datum type="float">6195.0</datum>
<datum type="float">6235.0</datum>
<datum type="float">6245.0</datum>
<datum type="float">6240.0</datum>
<datum type="float">6250.0</datum>
</datum>
<datum type="array">
<datum type="date">2018-08-01</datum>
<datum type="float">6136.5</datum>
<datum type="float">6137.0</datum>
<datum type="float">6160.0</datum>
<datum type="float">6162.0</datum>
<datum nil="true"/>
<datum nil="true"/>
<datum type="float">6245.0</datum>
<datum type="float">6255.0</datum>
<datum type="float">6290.0</datum>
<datum type="float">6300.0</datum>
<datum type="float">6295.0</datum>
<datum type="float">6305.0</datum>
</datum>
</data>
<collapse nil="true"/>
<order nil="true"/>
<database-id type="integer">13206</database-id>
</dataset>
</quandl-response>
Raw
COPPERXML.SQLRPGLE
ctl-opt main(main) dftactgrp(*no) option(*nounref);
//------------------------------------------------------------------//
// //
// Get Copper-Prices with HTTPGETCLOB //
// //
//----------------- //
// R.Ross 08.2018 * //
//------------------------------------------------------------------//
// Array XML-File - Das Rootelement quandl-response wird mit //
// Rsp (Response) abgekürzt: path=quandl-response //
//------------------------------------------------------------------//
dcl-ds rsp qualified inz;
dataset likeds(dataset);
end-ds;
dcl-ds dataset qualified;
data likeds(data) dim(1);
cnt_data uns(10);
end-ds;
dcl-ds data qualified;
datum likeds(datum) dim(100);
cnt_datum uns(10);
end-ds;
dcl-ds datum qualified;
datum char(20) dim(15);
cnt_datum uns(10);
end-ds;
//------------------------------------------------------------------//
// Array Result //
//------------------------------------------------------------------//
dcl-ds Result qualified dim(100) inz;
Date date;
Buyer packed(11:2);
Seller packed(11:2);
end-ds;
//------------------------------------------------------------------//
// SQL-Options //
//------------------------------------------------------------------//
exec sql set option datfmt=*iso, timfmt=*iso, commit=*none,
closqlcsr=*endactgrp;
//------------------------------------------------------------------//
// Variables //
//------------------------------------------------------------------//
dcl-s GblData varchar(100000);
//------------------------------------------------------------------//
// Main //
//------------------------------------------------------------------//
dcl-proc main;
dcl-s LocUrl varchar(256);
dcl-s LocClob sqltype(CLOB:100000);
dcl-s LocData like(GblData);
LocUrl = 'https://www.quandl.com/api/v3/datasets/LME/PR_CU.xml?'+
'start_date=2018-07-01&api_key=YourKey';
exec sql values systools.httpgetclob(:LocUrl,'') into :LocClob;
if sqlcode >= *zero and LocClob_len > *zero;
LocData = %subst(LocClob_data:1:LocClob_len); // CLOB -> Data
parseData(LocData);
endif;
end-proc;
//------------------------------------------------------------------//
// Parse XML-Data //
//------------------------------------------------------------------//
dcl-proc parseData;
dcl-pi *n;
PiData like(GblData) const;
end-pi;
dcl-s LocOpt varchar(256);
dcl-s LocInd uns(10);
LocOpt = 'doc=string +
path=quandl-response +
case=any allowextra=yes +
countprefix=cnt_ +
allowmissing=yes'; // XML-Options
xml-into rsp %xml(%trim(PiData):%trim(LocOpt));
dou LocInd >= rsp.dataset.data(1).cnt_datum;
LocInd += 1;
if LocInd <= rsp.dataset.data(1).cnt_datum;
Result(LocInd).Date =
%date(rsp.dataset.data(1).datum(LocInd).datum(1));
Result(LocInd).Buyer =
%dec(rsp.dataset.data(1).datum(LocInd).datum(2):11:2);
Result(LocInd).Seller =
%dec(rsp.dataset.data(1).datum(LocInd).datum(3):11:2);
endif;
enddo;
Result = Result; // Debug
end-proc;
//------------------------------------------------------------------//
Raw
GetCopperPrices.SQL
values systools.HTTPGETCLOB('https://www.quandl.com/api/v3/datasets/LME/PR_CU.xml?&start_date=2018-07-01&column_index=1&api_key=YourKey','');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment