Bij het uitvoeren van de volgende query:
select /*+ join_set(pnt, entrynumber, 10000) */ coalesce(pnt.PaymentMethod, pcn.PaymentMethod) PaymentMethod
, pnt.BankAccountNumber
, pnt.AccountBankAccountNumber
, aom.*
from ( select to_number(aom.division_code) Division
, to_guid(replace(aom.OUTSTANDINGITEMS_AP_ACCOUNT_ID_ATTR, '{', '', '}', '')) AccountId
, aom.Description
, aom.Number_Attr
, aom.YourRef
, aom.InvoiceDate
, aom.DueDate
from APOutstandingItems aom
where aom.DueDate <= trunc(sysdateutc) + 7
)
aom
left
outer
join Payments pnt
on pnt.division = aom.division
and pnt.entrynumber = aom.NUMBER_ATTR
join AccountsIncremental act
on act.Division = aom.Division
and act.id = aom.AccountId
left
outer
join ExactOnlineREST..PaymentConditions pcn
on pcn.Division = act.Division
and pcn.Code = act.PaymentConditionPurchase
duurt het 6 seconden voor 18 rijen als ik limit 10 in de subquery zet, en 11 seconden voor 194 rijen als ik limit 100 in de subquery zet.
Echter, deze query haalt pakweg 500 rijen op in de subquery, maar duurt dan eeuwig.
Als ik dan kijk in de SessionIOs@DataDictionary met:
select id, url, *
from sessionios@datadictionary
where id > 1093
order
by id desc
dan zie ik dat het ophalen niet lukt. Telkens meldingen zoals:
https://start.exactonline.nl/api/v1/2957010/cashflow/Payments?$select=*
itgenclr227
De gegevens kunnen niet worden gedownload omdat de verbinding is verbroken voordat de resultaten werden geretourneerd.
Time-out is ingesteld op 23.000 ms.
Er is een time-out opgetreden voor de bewerking.
Dit patroon zich eindeloos opnieuw. Na 10 pogingen met steeds langere tussenpozen zal Invantive Control for Excel er mee stoppen qua pogingen met een melding. De foutmelding na 6 minuten is:
itgenoda316: De verbinding met de website is onverwacht en met geweld verbroken om 27-3-2025 11:11:57 voor redenen zoals onvoldoende rechten, het vastlopen van de website, het opnieuw opstarten van het systeem, het hard afsluiten van een externe host of firewallactie.
De gegevens kunnen niet worden gedownload omdat de verbinding is verbroken voordat de resultaten werden geretourneerd. Time-out is ingesteld op 23.000 ms.
Er is een time-out opgetreden voor de bewerking.
Het ophalen van gegevens is mislukt voor alle 10 pogingen gedurende 388 seconden. Neem contact op met de API-ondersteuning van Exact Online.
De native call logging laat zien dat de API-call niet lukt binnen 23 seconden:
{
"MetadataType":"Invantive.Data.NativeCallEvent",
"Url":"https://start.exactonline.nl/api/v1/2957010/cashflow/Payments?$select=*",
"Success":false,
"CallDirection":0,
"Id":4,
"DateRegisteredUtc":"2025-03-27T11:06:17.0715538Z",
"SqlExecutionStep":{
"MetadataType":"Invantive.Data.SqlExecutionStep",
"Id":309,
"ParentId":308,
"Alias":"pnt",
"Depth":9,
"ManagedThreadId":67,
"Description":"Data container I/O on 'pnt' (PAYMENTS)",
"StartedUtc":"2025-03-27T11:05:29.1927565Z",
"LoggedSessionIOs":[
{
"MetadataType":"Invantive.Basics.LoggedSessionIO",
"Id":1236,
"Uid":"0502fcdf-5ad9-4927-ae7f-74632c8dc4b0",
"SqlExecutionStepId":309,
"ProviderObjectId":"8ff0816d-e5d7-4f04-b3ad-a577eefa0d19",
"RecursionLevel":0,
"Partition":"DEFAULT",
"Action":"WRITE",
"DataContainerAlias":"ExactOnlineRest",
"ClientId":"6768780a-8494-494b-a9b3-c17d4f5cad31",
"DataContainerId":"https://start.exactonline.nl/64371154",
"ExecutionDateUtc":"2025-03-27T11:05:29.2533678Z",
"Successful":true,
"Url":"https://start.exactonline.nl/api/oauth2/token",
"CallSafeName":"Token",
"LicenseContractCode":"something",
"LicenseKeyIdentifier":"something",
"ProviderName":"ExactOnlineRest",
"ManagedThreadId":67,
"ExecutionOptionsId":"b1cda7ae-d4fb-4563-8f21-058468266f16",
"FromMemoryCache":false,
"FromDiskCache":false,
"FromApplicationCache":false,
"ForwardedToCustomerService":false,
"Iiid":"something",
"Iuid":"something https://start.exactonline.nl/64371154 from something",
"SessionId":"17402782E6FB4E2473580582569BC3EB",
"MachineNameFull":"something",
"ExecutableName":"C:\\Program Files\\Microsoft Office\\Root\\Office16\\EXCEL.EXE",
"UserName":"something",
"ProcessId":12804,
"IsSharedEnvironment":false,
"IsInternalOnly":false,
"BytesSent":1092,
"BytesReceived":1798,
"RoundTrips":1,
"RowCount":0,
"DurationMs":307,
"DurationOnCpuMs":46,
"DurationOnSleepMs":0,
"DurationOnIoMs":261
},
{
"MetadataType":"Invantive.Basics.LoggedSessionIO",
"Id":1237,
"Uid":"a62f8d5b-c3af-493b-9eef-bbf8b9eb048d",
"SqlExecutionStepId":309,
"ProviderObjectId":"8ff0816d-e5d7-4f04-b3ad-a577eefa0d19",
"RecursionLevel":0,
"Partition":"2957010",
"Action":"READ",
"DataContainerAlias":"ExactOnlineRest",
"ClientId":"6768780a-8494-494b-a9b3-c17d4f5cad31",
"DataContainerId":"https://start.exactonline.nl/64371154",
"ExecutionDateUtc":"2025-03-27T11:05:29.6146661Z",
"Successful":false,
"Url":"https://start.exactonline.nl/api/v1/2957010/cashflow/Payments?$select=*",
"ErrorMessageCode":"itgenclr227",
"ErrorMessage":"De gegevens kunnen niet worden gedownload omdat de verbinding is verbroken voordat de resultaten werden geretourneerd. Time-out is ingesteld op 23.000 ms. (https://start.exactonline.nl/api/v1/2957010/cashflow/Payments?$select=*).\r\n\r\nEr is een time-out opgetreden voor de bewerking.",
"CallSafeName":"ExactOnlineREST.Cashflow.Payments",
"LicenseContractCode":"something",
"LicenseKeyIdentifier":"something",
"ProviderName":"ExactOnlineRest",
"ManagedThreadId":67,
"ExecutionOptionsId":"b1cda7ae-d4fb-4563-8f21-058468266f16",
"FromMemoryCache":false,
"FromDiskCache":false,
"FromApplicationCache":false,
"ForwardedToCustomerService":false,
"Iiid":"something",
"Iuid":"something on https://start.exactonline.nl/64371154 from something",
"SessionId":"17402782E6FB4E2473580582569BC3EB",
"MachineNameFull":"PAAS.LOCAL\\PA-LPT-12",
"ExecutableName":"C:\\Program Files\\Microsoft Office\\Root\\Office16\\EXCEL.EXE",
"UserName":"something",
"ProcessId":12804,
"IsSharedEnvironment":false,
"IsInternalOnly":false,
"BytesSent":1140,
"BytesReceived":0,
"RoundTrips":0,
"RowCount":0,
"DurationMs":23043,
"DurationOnCpuMs":11,
"DurationOnSleepMs":0,
"DurationOnIoMs":23032
}
],
"Sleeps":[
{
"MetadataType":"Invantive.Data.SqlExecutionSleep",
"Id":21,
"MessageCodeOrigin":"itgenhbr134",
"Partition":"2957010",
"ActualMs":1146,
"ExpectedDurationMs":1140,
"StartUtc":"2025-03-27T11:05:52.6748769Z",
"EndUtc":"2025-03-27T11:05:53.821517Z"
}
],
"Children":[
]
},
"RequestBodyAsString":"",
"ResponseBodyAsString":"",
"StartUtc":"2025-03-27T11:05:54.0522504Z",
"TableFullQualifiedName":"ExactOnlineREST.Cashflow.Payments",
"MachineNameFull":"something",
"UserLogonCodeWithDomain":"something",
"ManagedThreadId":67,
"ProcessId":12804,
"Uid":"a1186e9f-cdf1-4620-9263-80818d41fd07",
"MessageText":"GET",
"MessageCode":"itgenoda502",
"CallUid":"44e8c6a5-9451-469c-98b8-2e362c689611",
"ResponseStatus":"",
"PartitionCode":"2957010",
"DataContainerId":"https://start.exactonline.nl/64371154",
"UserLogOnCode":"something",
"RequestContents":"Automatic decompression: GZip, Deflate\r\nMaximum automatic redirections: 5\r\nMaximum response headers length: 64\r\nProtocol version: 1.1\r\nTimeout: 23.000 ms\r\nContinue Timeout: 350 ms\r\nRead/write Timeout: 300.000 ms\r\nSend chunked: False\r\nRequest header Accept: application/json\r\nRequest header Accept-Encoding: gzip, deflate\r\nRequest header Authorization: Bearer stampNL001.gAAAA...iuD0Mx\r\nRequest header Host: start.exactonline.nl\r\nRequest header User-Agent: PostmanRuntime/7.28.0\r\n",
"RequestHttpHeaders":"Accept=application/json,User-Agent=PostmanRuntime/7.28.0,Authorization=Bearer stampNL001.gAAAAHc9...lYWiuD0Mx,Host=start.exactonline.nl,Accept-Encoding=gzip, deflate",
"ExecutableName":"C:\\Program Files\\Microsoft Office\\Root\\Office16\\EXCEL.EXE",
"UserName":"something"
}
Het bericht aan de Exact Online API-server is:
{
"Url":"https://start.exactonline.nl/api/v1/2957010/cashflow/Payments?$select=*",
"Success":false,
"DateRegisteredUtc":"2025-03-27T11:06:17.0715538Z",
"RequestBodyAsString":"",
"ResponseBodyAsString":"",
"StartUtc":"2025-03-27T11:05:54.0522504Z",
"TableFullQualifiedName":"ExactOnlineREST.Cashflow.Payments",
"MessageText":"GET",
"RequestContents":"Automatic decompression: GZip, Deflate\r\nMaximum automatic redirections: 5\r\nMaximum response headers length: 64\r\nProtocol version: 1.1\r\nTimeout: 23.000 ms\r\nContinue Timeout: 350 ms\r\nRead/write Timeout: 300.000 ms\r\nSend chunked: False\r\nRequest header Accept: application/json\r\nRequest header Accept-Encoding: gzip, deflate\r\nRequest header Authorization: Bearer stampNL001.gAAAA...iuD0Mx\r\nRequest header Host: start.exactonline.nl\r\nRequest header User-Agent: PostmanRuntime/7.28.0\r\n",
"RequestHttpHeaders":"Accept=application/json,User-Agent=PostmanRuntime/7.28.0,Authorization=Bearer stampNL001.gAAAAHc9...lYWiuD0Mx,Host=start.exactonline.nl,Accept-Encoding=gzip, deflate"
}
Raar genoeg gaat dit dus niet alleen fout bij het ophalen van de betalingen, maar gespiegeld blijkbaar ook bij de ontvangsten, zie:
Hoe kan ik dan nog de data opgehaald krijgen?