We want to extract WeFact (WeFact API webservice - WeFact) data into a SQL Database. What would be the easiest way to do this?
they provide PHP on-the-shelve routines.
I see they use an API Key logic.
so if your are good at sql programming you can build something using httpdownload@DataDictionary with POST or GET
or submit improvement for native support ā¦ SQL/Power BI driver WeFact
2 likes
The WeFact API has rate limits:
- Minutely: 200
- Hourly: 3600
Also, the WeFact API returns HTTP status 200 in violation of the W3C specifications when an error occurs. It is necessary to inspect the payload.
A sample on settings is:
declare
g_api_key varchar2 := 'SECRETKEY';
--
l_retrieval_succesful boolean;
l_http_status_code int;
l_contents varchar2;
l_error_cnt pls_integer;
begin
select RETRIEVAL_SUCCESSFUL
, HTTP_STATUS_CODE
, CONTENTS_CHAR
into l_retrieval_succesful
, l_http_status_code
, l_contents
from HTTPDOWNLOAD@DataDictionary
( url => 'https://api.mijnwefact.nl/v2/'
, method => 'POST'
, contentType => 'application/x-www-form-urlencoded'
, textPayload => 'api_key=' || urlencode(g_api_key)
|| '&controller=' || urlencode('settings')
|| '&action=' || urlencode('list')
, minimumDurationMs => 1000 * 3600 / 3600 /* 3600 calls per hour. */
)
;
--
-- Any error returned?
--
select count(*)
into l_error_cnt
from jsontable
( 'errors[*]'
passing l_contents
columns error_text varchar2 path '::self'
)
;
if not l_retrieval_succesful
or l_http_status_code != 200
or l_error_cnt != 0
then
wefact_print_generic_payload(l_contents);
else
for r
in
( select *
from jsontable
( 'data[*]'
passing l_contents -- Such as '{"controller":..., "data": [{"identifier": "1"}, {"identifier": "2"}]}'
columns identifier varchar2 path 'identifier'
)
)
loop
dbms_output.put_line('ID: ' || r.identifier);
end loop;
end if;
end;
The PSQL-procedure for printing generic information is:
create or replace procedure wefact_print_generic_payload
( l_contents varchar2
)
is
l_controller varchar2;
l_action varchar2;
l_status varchar2;
l_date date;
l_totalresults int;
l_currentresults int;
l_offset int;
l_error_header_printed boolean;
begin
select t.controller
, t.action
, t.status
, t.date
, t.totalresults
, t.currentresults
, t.offset
into l_controller
, l_action
, l_status
, l_date
, l_totalresults
, l_currentresults
, l_offset
from jsontable
( ''
passing l_contents
columns controller varchar2 path 'controller'
, action varchar2 path 'action'
, status varchar2 path 'status'
, date datetime path 'date'
, totalresults int path 'totalresults'
, currentresults int path 'currentresults'
, offset int path 'offset'
) t
;
dbms_output.put_line('*** Generic Payload ***');
if l_controller is not null
then
dbms_output.put_line('Controller: ' || l_controller);
end if;
if l_action is not null
then
dbms_output.put_line('Action: ' || l_action);
end if;
if l_status is not null
then
dbms_output.put_line('Status: ' || l_status);
end if;
if l_date is not null
then
dbms_output.put_line('Date: ' || l_date);
end if;
if l_totalresults is not null
then
dbms_output.put_line('Total Results: ' || l_totalresults);
end if;
if l_currentresults is not null
then
dbms_output.put_line('Current Results: ' || l_currentresults);
end if;
if l_offset is not null
then
dbms_output.put_line('Offset: ' || l_offset);
end if;
--
-- Print errors (if any).
--
l_error_header_printed := false;
for r
in
( select error_text
from jsontable
( 'errors[*]'
passing l_contents
columns error_text varchar2 path '::self'
)
)
loop
if not l_error_header_printed
then
l_error_header_printed := true;
dbms_output.put_line('Errors:');
end if;
dbms_output.put_line('* ' || r.error_text);
end loop;
end;
A sample retrieving all debtors (with paging) is:
declare
g_api_key varchar2 := 'SECRET';
--
-- Choose page size between 1 and 10.000.
--
g_page_size pls_integer := 10000;
--
l_offset pls_integer;
l_totalresults pls_integer;
l_done boolean;
l_retrieval_succesful boolean;
l_http_status_code int;
l_contents varchar2;
l_error_cnt pls_integer;
begin
l_done := false;
l_totalresults := null;
l_offset := 0;
--
-- Create an empty table to load the data into.
--
-- Replace @inmemorystorage by the data container alias of for instance
-- a SQL Server database.
--
create or replace table we_fact_debtors@inmemorystorage
( identifier varchar2
, debtor_code varchar2
, company_name varchar2
, first_name varchar2
, last_name varchar2
);
--
while not l_done
loop
dbms_output.put_line('Retrieve ' || to_char(g_page_size) || ' rows starting at offset ' || to_char(l_offset) || '.');
select RETRIEVAL_SUCCESSFUL
, HTTP_STATUS_CODE
, CONTENTS_CHAR
into l_retrieval_succesful
, l_http_status_code
, l_contents
from HTTPDOWNLOAD@DataDictionary
( url => 'https://api.mijnwefact.nl/v2/'
, method => 'POST'
, contentType => 'application/x-www-form-urlencoded'
, textPayload => 'api_key=' || urlencode(g_api_key)
|| '&controller=' || urlencode('debtor')
|| '&action=' || urlencode('list')
|| '&limit=' || urlencode(g_page_size)
|| '&offset=' || urlencode(l_offset)
, minimumDurationMs => 1000 * 3600 / 3600 /* 3600 calls per hour. */
)
;
--
-- Any error returned?
--
select count(*)
into l_error_cnt
from jsontable
( 'errors[*]'
passing l_contents
columns error_text varchar2 path '::self'
)
;
if not l_retrieval_succesful
or l_http_status_code != 200
or l_error_cnt != 0
then
wefact_print_generic_payload(l_contents);
exit;
else
--
-- Sample 1: print the data row-by-row.
--
for r
in
( select *
from jsontable
( 'debtors[*]'
passing l_contents
columns identifier varchar2 path 'Identifier'
, debtor_code varchar2 path 'DebtorCode'
, company_name varchar2 path 'CompanyName'
, gender char path 'Sex'
, first_name varchar2 path 'Initials'
, last_name varchar2 path 'SurName'
, email_address varchar2 path 'EmailAddress'
, modified_date datetime path 'Modified'
)
)
loop
dbms_output.put_line(r.identifier || ' - ' || r.debtor_code || ': ' || r.company_name || ' (' || r.first_name || ' ' || r.last_name || ')');
end loop;
--
-- Sample 2: bulk load into table in-memory, SQL Server, Oracle, PostgreSQL or MySQL:
--
bulk insert into we_fact_debtors@inmemorystorage
( identifier
, debtor_code
, company_name
, first_name
, last_name
)
select identifier
, debtor_code
, company_name
, first_name
, last_name
from jsontable
( 'debtors[*]'
passing l_contents
columns identifier varchar2 path 'Identifier'
, debtor_code varchar2 path 'DebtorCode'
, company_name varchar2 path 'CompanyName'
, gender char path 'Sex'
, first_name varchar2 path 'Initials'
, last_name varchar2 path 'SurName'
, email_address varchar2 path 'EmailAddress'
, modified_date datetime path 'Modified'
)
;
end if;
--
-- Request next page when there is one.
-- Number of rows in result set doesn't change after initial retrieval.
--
if l_totalresults is null
then
select t.totalresults
into l_totalresults
from jsontable
( ''
passing l_contents
columns totalresults int path 'totalresults'
) t
;
end if;
--
l_offset := l_offset + g_page_size;
l_done := l_offset > l_totalresults;
end loop;
end;
Output is printed to the DBMS output in the first sample application.
The second sample application is to load the database into a database table:
WeFact API Performance
Retrieving approximately 7500 debtors is a nice number to assess the performance of the WeFact API across various page sizes, including loading into the in-memory database table.
The following table lists runtime depending on page size:
| Page Size | Runtime (sec) | Throughput (rows/sec) |
|---|---|---|
| 100 | 82 | 91 |
| 1.000 | 12.0 | 624 |
| 7.500 | 5.9 | 1.271 |
| 10.000 | 5.5 | 1.363 |
Please note that also the rate limiters are not on rows but on the number of API calls. The best approach with WeFact is to use the largest page size available.
Deze vraag is automatisch gesloten na 2 weken inactiviteit. Het laatste gegeven antwoord is gemarkeerd als oplossing.
Gelieve een nieuwe vraag te stellen via een apart topic als het probleem opnieuw optreedt. Gelieve in de nieuwe vraag een link naar dit topic op te nemen door de URL er van in de tekst te plakken.