RestConnector and JSON

[tmumaw]

Hi All,

This is a new area for me and I'm wondering if anyone could help me with this.  Here is a sample of my script.  I need to loop through the table since it's a cloud based database I am pulling from and it only gives me 1000 rows at a time.  For some reason my loop logic is messed up.  I can only get the first 1000 rows.  Can anyone see what I am missing?  I'm trying to pull from SAP's Successfactors. Thanks.

LET vURL = 'https://api8preview.sapsf.com:443/odata/v2/Candidate';

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=$(vURL);timeout=900;method=GET;sendExpect100Continue=True;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;authSchema=basic;skipServerCertificateValidation=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;queryParameters=$format%2json;PaginationType=None;allowResponseHeaders=false;allowHttpsOnly=false;XUserId=XKQJXJZIBCRaUEZJSBQIWQZOFLeIHZVMFDVCDHB;XPassword=PCRGRSUEQLYGXZdOPTcIWSIGXLZYWRdNNLZCDZMGUJNKSYMf;";

DO while LEN(vURL) > 0
RestConnectorMasterTable:
SQL SELECT
"__next",
"__KEY_d",
(SELECT
"candidateId",
"employeeJobTitle",
"serviceDate",
"cellPhone",
"address2",
"lastModifiedDateTime",
"homePhone",
"usersSysId",
"visibilityOption",
"publicIntranet",
"city",
"nickName",
"employeeServiceLine",
"externalCandidate",
"dateofAvailability",
"middleName",
"lastName",
"dataPrivacyId",
"primaryEmail",
"country",
"partnerSource",
"candidateLocale",
"employeeCostCenter",
"anonymized",
"partnerMemberId",
"consentToMarketing",
"privacyAcceptDateTime",
"lastLoginDateTime",
"firstName",
"candidateType",
"creationDateTime",
"zip",
"contactEmail",
"address",
"shareProfile",
"anonymizedDateTime",
"employeeNumber",
"__KEY_results",
"__FK_results",
(SELECT
"uri",
"type",
"__FK___metadata"
FROM "__metadata" FK "__FK___metadata"),
(SELECT
"__KEY_languages",
"__FK_languages",
(SELECT
"uri" AS "uri_u0",
"__FK___deferred"
FROM "__deferred" FK "__FK___deferred")
FROM "languages" PK "__KEY_languages" FK "__FK_languages"),
(SELECT
"__KEY_references",
"__FK_references",
(SELECT
"uri" AS "uri_u1",
"__FK___deferred_u0"
FROM "__deferred" FK "__FK___deferred_u0")
FROM "references" PK "__KEY_references" FK "__FK_references"),
(SELECT
"__KEY_jobsApplied",
"__FK_jobsApplied",
(SELECT
"uri" AS "uri_u2",
"__FK___deferred_u1"
FROM "__deferred" FK "__FK___deferred_u1")
FROM "jobsApplied" PK "__KEY_jobsApplied" FK "__FK_jobsApplied"),
(SELECT
"__KEY_prefix",
"__FK_prefix",
(SELECT
"uri" AS "uri_u3",
"__FK___deferred_u2"
FROM "__deferred" FK "__FK___deferred_u2")
FROM "prefix" PK "__KEY_prefix" FK "__FK_prefix"),
(SELECT
"__KEY_memberships",
"__FK_memberships",
(SELECT
"uri" AS "uri_u4",
"__FK___deferred_u3"
FROM "__deferred" FK "__FK___deferred_u3")
FROM "memberships" PK "__KEY_memberships" FK "__FK_memberships"),
(SELECT
"__KEY_candidateProfileExtension",
"__FK_candidateProfileExtension",
(SELECT
"uri" AS "uri_u5",
"__FK___deferred_u4"
FROM "__deferred" FK "__FK___deferred_u4")
FROM "candidateProfileExtension" PK "__KEY_candidateProfileExtension" FK "__FK_candidateProfileExtension"),
(SELECT
"__KEY_state",
"__FK_state",
(SELECT
"uri" AS "uri_u6",
"__FK___deferred_u5"
FROM "__deferred" FK "__FK___deferred_u5")
FROM "state" PK "__KEY_state" FK "__FK_state"),
(SELECT
"__KEY_education",
"__FK_education",
(SELECT
"uri" AS "uri_u7",
"__FK___deferred_u6"
FROM "__deferred" FK "__FK___deferred_u6")
FROM "education" PK "__KEY_education" FK "__FK_education"),
(SELECT
"__KEY_jobReqFwdCandidates",
"__FK_jobReqFwdCandidates",
(SELECT
"uri" AS "uri_u8",
"__FK___deferred_u7"
FROM "__deferred" FK "__FK___deferred_u7")
FROM "jobReqFwdCandidates" PK "__KEY_jobReqFwdCandidates" FK "__FK_jobReqFwdCandidates"),
(SELECT
"__KEY_outsideWorkExperience",
"__FK_outsideWorkExperience",
(SELECT
"uri" AS "uri_u9",
"__FK___deferred_u8"
FROM "__deferred" FK "__FK___deferred_u8")
FROM "outsideWorkExperience" PK "__KEY_outsideWorkExperience" FK "__FK_outsideWorkExperience"),
(SELECT
"__KEY_mobility",
"__FK_mobility",
(SELECT
"uri" AS "uri_u10",
"__FK___deferred_u9"
FROM "__deferred" FK "__FK___deferred_u9")
FROM "mobility" PK "__KEY_mobility" FK "__FK_mobility"),
(SELECT
"__KEY_tags",
"__FK_tags",
(SELECT
"uri" AS "uri_u11",
"__FK___deferred_u10"
FROM "__deferred" FK "__FK___deferred_u10")
FROM "tags" PK "__KEY_tags" FK "__FK_tags"),
(SELECT
"__KEY_customWorkSample",
"__FK_customWorkSample",
(SELECT
"uri" AS "uri_u12",
"__FK___deferred_u11"
FROM "__deferred" FK "__FK___deferred_u11")
FROM "customWorkSample" PK "__KEY_customWorkSample" FK "__FK_customWorkSample"),
(SELECT
"__KEY_community",
"__FK_community",
(SELECT
"uri" AS "uri_u13",
"__FK___deferred_u12"
FROM "__deferred" FK "__FK___deferred_u12")
FROM "community" PK "__KEY_community" FK "__FK_community"),
(SELECT
"__KEY_suffix",
"__FK_suffix",
(SELECT
"uri" AS "uri_u14",
"__FK___deferred_u13"
FROM "__deferred" FK "__FK___deferred_u13")
FROM "suffix" PK "__KEY_suffix" FK "__FK_suffix"),
(SELECT
"__KEY_candidateProfileConversionInfo",
"__FK_candidateProfileConversionInfo",
(SELECT
"uri" AS "uri_u15",
"__FK___deferred_u14"
FROM "__deferred" FK "__FK___deferred_u14")
FROM "candidateProfileConversionInfo" PK "__KEY_candidateProfileConversionInfo" FK "__FK_candidateProfileConversionInfo"),
(SELECT
"__KEY_certifications",
"__FK_certifications",
(SELECT
"uri" AS "uri_u16",
"__FK___deferred_u15"
FROM "__deferred" FK "__FK___deferred_u15")
FROM "certifications" PK "__KEY_certifications" FK "__FK_certifications"),
(SELECT
"__KEY_comments",
"__FK_comments",
(SELECT
"uri" AS "uri_u17",
"__FK___deferred_u16"
FROM "__deferred" FK "__FK___deferred_u16")
FROM "comments" PK "__KEY_comments" FK "__FK_comments")
FROM "results" PK "__KEY_results" FK "__FK_results")
FROM JSON (wrap off) "d" PK "__KEY_d"
;

[Candidate_MetaData]:
LOAD [uri],
[type],
[__FK___metadata] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK___metadata]);


[Languages]:
LOAD [__KEY_languages],
[__FK_languages] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_languages]);

[References]:
LOAD [__KEY_references],
[__FK_references] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_references]);

[JobsApplied]:
LOAD [__KEY_jobsApplied],
[__FK_jobsApplied] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_jobsApplied]);

[Prefix]:
LOAD [__KEY_prefix],
[__FK_prefix] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_prefix]);

[Memberships]:
LOAD [__KEY_memberships],
[__FK_memberships] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_memberships]);

[CandidateProfileExtension]:
LOAD [__KEY_candidateProfileExtension],
[__FK_candidateProfileExtension] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_candidateProfileExtension]);

[State]:
LOAD [__KEY_state],
[__FK_state] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_state]);

[Education]:
LOAD [__KEY_education],
[__FK_education] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_education]);

[JobReqFwdCandidates]:
LOAD [__KEY_jobReqFwdCandidates],
[__FK_jobReqFwdCandidates] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_jobReqFwdCandidates]);

[OutsideWorkExperience]:
LOAD [__KEY_outsideWorkExperience],
[__FK_outsideWorkExperience] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_outsideWorkExperience]);

[Mobility]:
LOAD [__KEY_mobility],
[__FK_mobility] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_mobility]);

[Tags]:
LOAD [__KEY_tags],
[__FK_tags] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_tags]);

[CustomWorkSample]:
LOAD [__KEY_customWorkSample],
[__FK_customWorkSample] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customWorkSample]);

[Community]:
LOAD [__KEY_community],
[__FK_community] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_community]);

[Suffix]:
LOAD [__KEY_suffix],
[__FK_suffix] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_suffix]);

[CandidateProfileConversionInfo]:
LOAD [__KEY_candidateProfileConversionInfo],
[__FK_candidateProfileConversionInfo] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_candidateProfileConversionInfo]);

[Certifications]:
LOAD [__KEY_certifications],
[__FK_certifications] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_certifications]);

[Comments]:
LOAD [__KEY_comments],
[__FK_comments] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_comments]);


Candidates:
LOAD
[candidateId] as Can_candidateId,
[employeeJobTitle] as Can_employeeJobTitle,
[serviceDate],
Mid([serviceDate],7,13) as ServiceDate,
len(trim([cellPhone])) as [CellNo_Length],
[cellPhone],
[address2] as Can_address2,
[lastModifiedDateTime],
Mid([lastModifiedDateTime],7,13) as ModifiedDate,
[homePhone] as Can_homePhone,
[usersSysId] as Can_usersSysId,
[visibilityOption] as Can_visibilityOption,
[publicIntranet] as Can_publicIntranet,
[city] as Can_city,
[nickName] as Can_nickName,
[employeeServiceLine] as Can_employeeServiceLine,
[externalCandidate] as Can_externalCandidate,
[dateofAvailability],
Mid([dateofAvailability],7,13) as AvailabilityDate,
[middleName] as Can_middleName,
[lastName] as Can_lastName,
[dataPrivacyId] as Can_dataPrivacyId,
[primaryEmail] as Can_primaryEmail,
[country] as Can_country,
[partnerSource] as Can_partnerSource,
[candidateLocale] as Can_candidateLocale,
[employeeCostCenter] as Can_employeeCostCenter,
[anonymized] as Can_anonymized,
[partnerMemberId] as Can_partnerMemberId,
[consentToMarketing] as Can_consentToMarketing,
[privacyAcceptDateTime],
Mid([privacyAcceptDateTime],7,13) as PrivacyAcceptDate,
[lastLoginDateTime],
Mid([lastLoginDateTime],7,13) as LastLoginDate,
[firstName] as Can_firstName,
lastName & ', ' & firstName as Candidate_Name,
[candidateType] as Can_candidateType,
creationDateTime,
Mid([creationDateTime],7,13) as CreateDate,
[zip] as Can_zip,
[contactEmail] as Can_contactEmail,
[address] as Can_address,
[shareProfile] as Can_shareProfile,
[anonymizedDateTime],
Mid([anonymizedDateTime],7,13) as AnonymizedDate,
[employeeNumber] as Can_employeeNumber,
[__KEY_results],
[__FK_results] AS [__KEY_d]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_results]);

STORE * FROM [RestConnectorMasterTable] into qvd\Candidates.QVD;


LET vCount = FieldValueCount('__next');
LET vURL_new = FieldValue('__next', vCount);

IF(vURL <> vURL_new) then
vURL = vURL_new;
ELSEIF(vURL = vURL_new) then
vURL = '';
end if

Trace $(vURL);

Loop

DROP TABLE RestConnectorMasterTable;

DROP TABLE Candidates;

EXIT SCRIPT;

[feg]

Hi, I am having the same problem, did you manage to solve this?