Pagination with Authentication and XML response

Benny — Tue, 21 Dec 2021 14:01:50 GMT

Dear community,

I am struggling with the Rest Connector at the moment.

I want to connect to a SharePoint list that has more than 100 entries. Therefore, the response is split into multiple pages.

The connection itself is working, so if I specify "No Pagination" I get 100 entries as a result.

Because I was not sure how to set the Pagination of SharePoint in QV Connector, I read the maximum ID from the result set and use this in a loop to connect to the next set:

Example:

1. First request: "url=https://{our page}/_api/Web/Lists(guid'{...}')/items?%24skiptoken=Paged%3dTRUE%26p_ID%3d0"

2. Next request (max ID = 112): "url=https://{our page}/_api/Web/Lists(guid'{...}')/items?%24skiptoken=Paged%3dTRUE%26p_ID%3d112"

And if I check this manually in Chrome, I can see that the next result entry I get is ID = 113.

But if I put this in a loop in QV, the first connection is set and the results are loaded in a table. Then the maximum ID is read into a variable, which is still working (var = 112).

But after the next connection, which uses the url from 2. above (I checked this in Debug mode - the url is correct), I still get the same result set as in the first query and my maximum ID again is 112... I tried this also with PaginationMode=Custom (see below), but to no avail.

So, can someone tell me if I miss some logic here? Here is the full code (shortened and censored):

SET vNextPage = https://{page}/_api/Web/Lists(guid'{...}')/items?%24skiptoken=Paged%3dTRUE%26p_ID%3d;
LET vLastId = 0;
SET ErrorMode = 0;

myList:
LOAD 0 as %KEY_myListId AutoGenerate 0;

Do
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=$(vNextPage)$(vLastId);timeout=30;method=GET;httpProtocol=1.1;isKeepAlive=1;bodyEncoding=UTF-8;sendExpect100Continue=1;autoDetectResponseType=1;checkResponseTypeOnTestConnection=1;keyGenerationStrategy=0;authSchema=ntlm;skipServerCertificateValidation=0;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;addMissingQueryParametersToFinalRequest=0;PaginationType=Custom;allowResponseHeaders=0;allowHttpsOnly=0;XUserId={...};XPassword={...};";

RestConnectorMasterTable:
SQL SELECT
"__KEY_feed",
(SELECT
(SELECT
FROM "category"),
(SELECT
FROM "link"),
(SELECT
FROM "author"),
(SELECT
(SELECT
(SELECT
FROM "FileSystemObjectType"),
(SELECT
"@Content" AS "@Content_u0",
"__FK_Id"
FROM "Id" FK "__FK_Id" ContentFieldAlias "@Content_u0"),
(SELECT
"@Content" AS "@Content_u1",
"__FK_Type"
FROM "Type" FK "__FK_Type" ContentFieldAlias "@Content_u1"),
(SELECT
"@Content" AS "@Content_u2",
"__FK_Description"
FROM "Description" FK "__FK_Description" ContentFieldAlias "@Content_u2"),

{...}
FROM "properties" PK "__KEY_properties" FK "__FK_properties")
FROM "content" PK "__KEY_content" FK "__FK_content")
FROM "entry" PK "__KEY_entry" FK "__FK_entry")
FROM XML "feed" PK "__KEY_feed";

[Id]:
LOAD [@Content_u0],
[__FK_Id] AS [__KEY_properties]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Id]);

maxID:
LOAD max(@Content_u0) as fMaxID RESIDENT Id;
LET vLastId = Peek('fMaxID', 0, 'maxID');
DROP TABLE maxID;

[Type]:
LOAD [@Content_u1],
[__FK_Type] AS [__KEY_properties]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Type]);

[Description]:
LOAD [@Content_u2],
[__FK_Description] AS [__KEY_properties]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Description]);

{...}

DROP TABLE RestConnectorMasterTable;

tmpTable:
LOAD [@Content_u0] as [%KEY_myListId], [__KEY_properties] RESIDENT Id;
LEFT JOIN LOAD [@Content_u1] as [Type], [__KEY_properties] RESIDENT Type;
LEFT JOIN LOAD [@Content_u2] as [Description], [__KEY_properties] RESIDENT Description;

{...}

Concatenate(myList)
LOAD * RESIDENT tmpTable;
DROP TABLE tmpTable;

DROP TABLE Id;
DROP TABLE Type;
DROP TABLE Description;

{...}

EXIT DO WHEN ISNULL(vLastId);
LOOP WHILE NOT IS NULL(vLastId)
SET ErrorMode = 1;

If anyone can tell me how to do it without a loop, directly within the pagination setup of the Connector, that would be even more awesome 🙂

Thank you all very much and hope someone can help me here.

Cheers, Ben

Re: Pagination with Authentication and XML response

Benny — Fri, 14 Feb 2020 08:37:03 GMT

Hi again,

I solved it by using "WITH CONNECTION". I realised I don't even need to specify the Authentication every time, so I connect once with a regular "Custom CONNECT" and then use variables for the IDs in the WITH CONNECT statement.

Cheers, Ben

topic Pagination with Authentication and XML response in Connectivity & Data Prep

Pagination with Authentication and XML response

Re: Pagination with Authentication and XML response