Table Comments

Parameters

Supported Operations

Examples

SSIS

Use Jira Connector in API Source component to read data or in API Destination component to read/write data:

Read from Comments table using API Source

Jira

Comments

Read/write to Comments table using API Destination

Jira

Comments

Select

ODBC application

Use these SQL queries in your ODBC application data source:

List comments for all issues

List comments for all issues

SELECT * FROM Comments

List comments for a specific issue Issue Key or Id

List comments for a specific issue

SELECT * FROM Comments Where IssueId=10003 --OR WITH(Key='10003')

List comments for a specific issue by Key

List comments for a specific issue by key

SELECT * FROM Comments WITH(Key='CS-1')

List comments for issues (search by JQL)

List all comments for issues returned from a JQL search expression (refer to previous example to learn about JQL - see Search issues using Advanced JQL query expression)

SELECT * FROM Comments WITH (Jql='status IN (Done, Closed) AND created > -5d' )

Create a new Issue Comment (Plain Text Body)

This example creates a new plain text comment for a given Issue Id. You can use Issue Key or Id as an input value.

INSERT INTO Comments(IssueId,Body) VALUES('CS-2', 'Commented at <<FUN_NOW>>')

Create a new Issue Comment (Formatted Body)

This example creates a new formatted text comment for a given Issue Id. You can use Issue Key or Id as an input value.

INSERT INTO Comments(IssueId,BodyFormatted) VALUES('CS-3', '{ "content": [
    { "content": [
        { "text": "This is a valid ADF formatted comment.",
          "type": "text" }
           ], 
           "type": "paragraph"
        }
       ], 
       "type": "doc", 
       "version": 1 
     } 
}')

Update an exising Comment (Plain Text Body)

This example updates an exising comment for a given Issue Id and Comment Id. You can use Issue Key or Id as an input value.

UPDATE Comments
 SET Body='Updated at <<FUN_NOW>>'
WHERE Id=10004
WITH(IssueIdOrKey='CS-2')

Delete an exising Comment

This example deletes an exising comment for a given Issue Id and Comment Id. You can use Issue Key or Id as an input value.

DELETE FROM Comments WHERE Id=10004 WITH(IssueIdOrKey='CS-2')

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

List comments for all issues

List comments for all issues

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Comments';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_JIRA_IN_DATA_GATEWAY];

List comments for a specific issue Issue Key or Id

List comments for a specific issue

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Comments Where IssueId=10003 --OR WITH(Key=''10003'')';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_JIRA_IN_DATA_GATEWAY];

List comments for a specific issue by Key

List comments for a specific issue by key

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Comments WITH(Key=''CS-1'')';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_JIRA_IN_DATA_GATEWAY];

List comments for issues (search by JQL)

List all comments for issues returned from a JQL search expression (refer to previous example to learn about JQL - see Search issues using Advanced JQL query expression)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Comments WITH (Jql=''status IN (Done, Closed) AND created > -5d'' )';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_JIRA_IN_DATA_GATEWAY];

Create a new Issue Comment (Plain Text Body)

This example creates a new plain text comment for a given Issue Id. You can use Issue Key or Id as an input value.

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Comments(IssueId,Body) VALUES(''CS-2'', ''Commented at <<FUN_NOW>>'')';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_JIRA_IN_DATA_GATEWAY];

Create a new Issue Comment (Formatted Body)

This example creates a new formatted text comment for a given Issue Id. You can use Issue Key or Id as an input value.

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Comments(IssueId,BodyFormatted) VALUES(''CS-3'', ''{ "content": [
    { "content": [
        { "text": "This is a valid ADF formatted comment.",
          "type": "text" }
           ], 
           "type": "paragraph"
        }
       ], 
       "type": "doc", 
       "version": 1 
     } 
}'')';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_JIRA_IN_DATA_GATEWAY];

Update an exising Comment (Plain Text Body)

This example updates an exising comment for a given Issue Id and Comment Id. You can use Issue Key or Id as an input value.

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Comments
 SET Body=''Updated at <<FUN_NOW>>''
WHERE Id=10004
WITH(IssueIdOrKey=''CS-2'')';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_JIRA_IN_DATA_GATEWAY];

Delete an exising Comment

This example deletes an exising comment for a given Issue Id and Comment Id. You can use Issue Key or Id as an input value.

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Comments WHERE Id=10004 WITH(IssueIdOrKey=''CS-2'')';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_JIRA_IN_DATA_GATEWAY];