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.)
Exactly, that's what I was getting at in one of my previous posts. You don't even have to use the Web Connector package any longer, there is now an integrated JIRA connector:
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)');
