Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i am trying to download data from JIRA
user REST connection
My URL
i use basic authentication
below is my script
IssueKeyTable:
LOAD
Issues.id as IssueID,
Issues.key as IssueKey
FROM [lib://Downloads:Data/15snynMfhN-l13mnfje4cuw9jT9MlDRW7/JIRA_Issues.qvd]
(qvd);
let username = 'abc@productsup.com';
let password = 'asadasdasfas';
LET vAuthorization = 'Basic ' & '$(username)' & ':' & '$(password)';
LIB CONNECT TO 'JIRA_DATA_DOWNLOAD:JIRA_COMMENTS';
for i = 0 to FieldValueCount('IssueID')-1;
LET vFileName = Peek('IssueID',i,'$(IssueKeyTable)');
RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT
"self" AS "self_u1",
"id" AS "id_u0",
"created",
"updated",
"jsdPublic",
"__KEY_comments",
"__FK_comments",
(SELECT
"self",
"accountId",
"emailAddress",
"displayName",
"active",
"timeZone",
"accountType",
"__KEY_author",
"__FK_author",
(SELECT
"48x48",
"24x24",
"16x16",
"32x32",
"__FK_avatarUrls"
FROM "avatarUrls" FK "__FK_avatarUrls")
FROM "author" PK "__KEY_author" FK "__FK_author"),
(SELECT
"version",
"type" AS "type_u3",
"__KEY_body",
"__FK_body",
(SELECT
"type" AS "type_u2",
"__KEY_content",
"__FK_content",
(SELECT
"type" AS "type_u1",
"text" AS "text_u1",
"__KEY_content_u0",
"__FK_content_u0",
(SELECT
"url",
"shortName",
"id",
"text",
"accessLevel",
"__FK_attrs"
FROM "attrs" FK "__FK_attrs"),
(SELECT
"type",
"__FK_marks"
FROM "marks" FK "__FK_marks"),
(SELECT
"type" AS "type_u0",
"text" AS "text_u0",
"__FK_content_u1"
FROM "content" FK "__FK_content_u1")
FROM "content" PK "__KEY_content_u0" FK "__FK_content_u0")
FROM "content" PK "__KEY_content" FK "__FK_content")
FROM "body" PK "__KEY_body" FK "__FK_body"),
(SELECT
"self" AS "self_u0",
"accountId" AS "accountId_u0",
"emailAddress" AS "emailAddress_u0",
"displayName" AS "displayName_u0",
"active" AS "active_u0",
"timeZone" AS "timeZone_u0",
"accountType" AS "accountType_u0",
"__KEY_updateAuthor",
"__FK_updateAuthor",
(SELECT
"48x48" AS "48x48_u0",
"24x24" AS "24x24_u0",
"16x16" AS "16x16_u0",
"32x32" AS "32x32_u0",
"__FK_avatarUrls_u0"
FROM "avatarUrls" FK "__FK_avatarUrls_u0")
FROM "updateAuthor" PK "__KEY_updateAuthor" FK "__FK_updateAuthor")
FROM "comments" PK "__KEY_comments" FK "__FK_comments")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(Url "https://productsup.atlassian.net/rest/api/3/issue/$(vFileName)/comment"
HTTPHEADER "Authorization" "Basic $(vAuthorization)" );
// [avatarUrls]:
// LOAD [48x48],
// [24x24],
// [16x16],
// [32x32],
// [__FK_avatarUrls] AS [__KEY_author]
// RESIDENT RestConnectorMasterTable
// WHERE NOT IsNull([__FK_avatarUrls]);
[author]:
LOAD [self],
[accountId],
[emailAddress],
[displayName],
[active],
[timeZone],
[accountType],
[__KEY_author],
[__FK_author] AS [__KEY_comments]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_author]);
//Outer Join
[attrs]:
LOAD [url],
[shortName],
[id],
[text],
[accessLevel],
[__FK_attrs] AS [__KEY_content_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_attrs]);
//Outer Join
[marks]:
LOAD [type],
[__FK_marks] AS [__KEY_content_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_marks]);
//Outer Join
[content]:
LOAD [type_u0] AS [type_u0],
[text_u0] AS [text_u0],
[__FK_content_u1] AS [__KEY_content_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_content_u1]);
//Outer Join
[content_u0]:
LOAD [type_u1] AS [type_u1],
[text_u1] AS [text_u1],
[__KEY_content_u0],
[__FK_content_u0] AS [__KEY_content]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_content_u0]);
//////
//outer Join
[content_u1]:
LOAD [type_u2] AS [type_u2],
[__KEY_content],
[__FK_content] AS [__KEY_body]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_content]);
//Outer Join
[body]:
LOAD [version],
[type_u3] AS [type_u3],
[__KEY_body],
[__FK_body] AS [__KEY_comments]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_body]);
// [avatarUrls_u0]:
// LOAD [48x48_u0] AS [48x48_u0],
// [24x24_u0] AS [24x24_u0],
// [16x16_u0] AS [16x16_u0],
// [32x32_u0] AS [32x32_u0],
// [__FK_avatarUrls_u0] AS [__KEY_updateAuthor]
// RESIDENT RestConnectorMasterTable
// WHERE NOT IsNull([__FK_avatarUrls_u0]);
//Outer Join
[updateAuthor]:
LOAD [self_u0] AS [self_u0],
[accountId_u0] AS [accountId_u0],
[emailAddress_u0] AS [emailAddress_u0],
[displayName_u0] AS [displayName_u0],
[active_u0] AS [active_u0],
[timeZone_u0] AS [timeZone_u0],
[accountType_u0] AS [accountType_u0],
[__KEY_updateAuthor],
[__FK_updateAuthor] AS [__KEY_comments]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_updateAuthor]);
//Outer Join
$(vFileName):
LOAD [self_u1] AS [self_u1],
[id_u0] AS [id_u0],
[created],
[updated],
[jsdPublic],
[__KEY_comments],
[__FK_comments] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_comments]);
DROP TABLE RestConnectorMasterTable;
STORE $(vFileName) into [lib://JIRA_DATA_DOWNLOAD:DataFiles/Comments_$(vFileName).qvd];;
drop table $(vFileName);
next i;
i am not sure what is going wrong but i am getting below error
The following error occurred:
(Connector error: Unexpected lexem 'HttpHeader' at (84,11). Expected 'CloseBracket'. Check SELECT STATEMENT syntax with documentation.)
1 Solution
Accepted Solutions
I copied your code and used it with my JIRA connector. I believe the issue to be that you did not enclose your JIRA key in single quotes:
Not working:
WITH PROPERTIES (issueIdOrKey=$(vFileName));
Should work:
WITH PROPERTIES (issueIdOrKey='$(vFileName)');
Either it's a wrong URL or no comment exists for that issue.
Which version of Qlik Sense are you using? Have you tried the integrated Jira connector?
That will probably be much easier to handle then creating the REST handler yourself.
