De onderstaande code op Exact Online Salaris geeft een foutmelding:
An error occured - 15e43c8a-589b-4ab1-8351-6893e482cc04
itgenpse009: Een select … into-instructie in PSQL moet precies een rij ophalen, maar er zijn meerdere rijen opgehaald.
Pas s.v.p. de logica van uw select-statement in PSQL aan om altijd precies een rij terug te geven in plaats van 29 rijen.
declare
l_divisions constant varchar2 := cloud_http.get_request_query_parameter_value('divisions');
--
-- Table function specific parameters.
--
l_payroll_year constant varchar2 := cloud_http.get_request_query_parameter_value('year');
--
l_json clob;
begin
use all@eol;
--
-- Return output as JSON.
--
cloud_http.set_response_content_type('application/json');
--
-- Append JSON form of table contents to HTTP output.
--
select *
into l_json
from ( select ptn.Division
, ptn.EmployeeHID
, ptn.PayrollComponentCode
, ptn.payrollYear
, ptn.Period
, ptn.CalculatedAmount
from systemdivisions@eol sdn
join PayrollTransactionsByPayrollYear(sdn.code, l_payroll_year) ptn
on ptn.Status != 7
where ',' || replace(l_divisions, ' ', '') || ',' like '%,' || sdn.code || ',%'
for json auto
)
;
cloud_http.append_to_response_body_text(l_json);
end;
Deze foutmelding treedt ook op als ik de inner-view samenvoeg met de buitenste query.
Als de tabelfunctie PayrollTransactionsByPayrollYear maar enkele honderden rijen teruggeeft, dan treedt de foutmelding niet op.
Het blijkt dat er sprake is van een punt dat ergens op grensvlak zweeft tussen “documentation bug”, “bug” en “feature”. Er kunnen meerdere rijen komen uit de for json auto, namelijk één per 1.000 rijen en dan nog afhankelijk van aantal randvoorwaarden. Dit is niet beschreven en niet instelbaar.
Nieuw: for json output per <GETAL> rows
Een aanpassing wordt verwerkt in de Invantive SQL-grammatica bij de for json clause. Voortaan kan er naast de root node, wel/niet opnemen null waarden en wel/niet opnemen JSON array-buitenlaag ook opgegeven worden hoeveel rijen per regel terug moeten komen via:
, output per <GETAL> rows
Indien niet opgegeven, dan wordt de eerder gebruikte standaardwaarde van 1.000 rijen gebruikt.
De eerste regel zal - tenzij onderdrukt - het array-karakter [ bevatten, de laatste regel eindigt met ].
Een voorbeeld van een Invantive SQL-query die de getallen tot/met 100 per drie gegroepeerd teruggeeft is:
select rge.value
from range@datadictionary(100) rge
for json auto
, output per 3 rows
Waarom losse rijen met JSON?
Het gebruik van de for json-clause in Invantive SQL heeft een grote vlucht genomen. Inmiddels worden soms honderden gigabytes aan data per dag verwerkt hiermee. Echter, deze verwerking is sterk batchmatig. Dit leidt tot een grote belasting van de apparatuur. Door meer mogelijkheden te bieden voor het opknippen van de JSON-berichten wordt het eenvoudiger om tussenresultaten alvast op te sturen naar de ontvanger. Hierdoor daalt het maximale geheugenbeslag en kunnen ook resultaten van meer dan 2 GB verwerkt worden.
In de toekomst is het streven om ook teksten te kunnen toevoegen aan de HTTP stream van Invantive Cloud als het totale volume meer dan 2 GB is.
Toepassing: alles in één rij
Om alle data in één rij te plaatsen gebruik je een bijzonder hoge waarde voor het getal.
De nieuwe functionaliteit is nog niet in productie genomen. Zodra de tag “issue-waiting-release” op dit topic verwijderd wordt, zal het in productie genomen zijn. Dit kan enkele dagen tot weken duren.
De verbeterde for json implementatie is in productie genomen op Invantive Cloud.
Gebruik bij voorkeur een for...loop... end loop; om alle data te verwerken of gebruik een hoge waarde voor output per ... rows zoals beschreven in de SQL grammatica op:
Hogere waarde werk niet helaas, zie onderstaande foutmelding:
itgenpse009: Een select … into-instructie in PSQL moet precies één rij ophalen, maar er zijn meerdere rijen opgehaald. Pas s.v.p. de logica van uw select-statement in PSQL aan om altijd precies één rij terug te geven in plaats van 979 rijen.
Onderstaand is het script wat ik gebruik, ook als ik het precies aantal rijen opgeef dan krijg ik de melding.
declare
l_divisions constant varchar2 := cloud_http.get_request_query_parameter_value('divisions');
l_payroll_year constant varchar2 := cloud_http.get_request_query_parameter_value('year');
--
l_json clob;
begin
use all@eol;
--
-- Return output as JSON.
--
cloud_http.set_response_content_type('application/json');
--
-- Append JSON form of table contents to HTTP output.
--
select *
into l_json
from ( select ptn.Division
, ptn.EmployeeHID
, ptn.PayrollComponentCode
, ptn.payrollYear
, ptn.Period
, ptn.CalculatedAmount
from systemdivisions@eol sdn
join PayrollTransactionsByPayrollYear(sdn.code, l_payroll_year) ptn
on ptn.Status != 7
where ',' || replace(l_divisions, ' ', '') || ',' like '%,' || sdn.code || ',%'
for json auto
, output per 1000 rows
)
;
cloud_http.append_to_response_body_text(l_json);
end;
Eerste mogelijkheid is om een hogere waarde dan 1000 te gebruiken, bijvoorbeeld een miljoen. Dit kost wel veel werkgeheugen. Een andere poging zou zijn om het om te schrijven naar een loop:
declare
l_divisions constant varchar2 := cloud_http.get_request_query_parameter_value('divisions');
l_payroll_year constant varchar2 := cloud_http.get_request_query_parameter_value('year');
--
l_json clob;
begin
use all@eol;
--
-- Return output as JSON.
--
cloud_http.set_response_content_type('application/json');
--
-- Append JSON form of table contents to HTTP output.
--
for r
in
( select *
from ( select ptn.Division
, ptn.EmployeeHID
, ptn.PayrollComponentCode
, ptn.payrollYear
, ptn.Period
, ptn.CalculatedAmount
from systemdivisions@eol sdn
join PayrollTransactionsByPayrollYear(sdn.code, l_payroll_year) ptn
on ptn.Status != 7
where ',' || replace(l_divisions, ' ', '') || ',' like '%,' || sdn.code || ',%'
for json auto
, output per 1000 rows
)
)
loop
cloud_http.append_to_response_body_text(r.json);
end loop;
end;
Op dit moment geeft deze code nog een foutmelding door PSQL scoping. De oorzaak wordt nog onderzocht en weggenomen.
De for-loop zorgt er voor dat de data uit meerdere brokken mag bestaan en uiteindelijk achter elkaar op de HTTP-uitvoer gezet wordt.
Op dit moment is het nog niet mogelijk om meer dan 2 GB aan HTTP-verkeer terug te geven. De for-loop halveert het maximum in het geheugengebruik van dit proces.
Wens is om ook het toevoegen aan de HTTP-uitvoer te veranderen zodat de tussenresultaten al teruggegeven worden. Door deze streaming stijgen de prestaties omdat het teruggeven en ophalen gelijktijdig gebeurt en kan meer dan de huidige limiet van 2 GB aan HTTP-uitvoer teruggegeven worden. De OData-engine Invantive Bridge Online gebruikt ook een dergelijke techniek.
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.