Reference

Table Projects

Description

No description available

Supported Operations

Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.
Method Supported Reference EndPoint
SELECT get_projects
INSERT create_project
UPDATE upsert_project
UPSERT upsert_project
DELETE delete_project
LOOKUP

Examples

SSIS

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

Read from Projects table using API Source

API Source - Jira
Read and write Jira data effortlessly. Track, manage, and automate issues, projects, worklogs, and comments — almost no coding required.
Jira
Projects
Optional Parameters
NextUrlAttributeOrExpr $.nextPage
MaxResults
SSIS API Source - Read from table or endpoint

Read/write to Projects table using API Destination

API Destination - Jira
Read and write Jira data effortlessly. Track, manage, and automate issues, projects, worklogs, and comments — almost no coding required.
Jira
Projects
Select
Optional Parameters
NextUrlAttributeOrExpr $.nextPage
MaxResults
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read issues using JQL query

<p>Gets issues that match a JQL (Jira Query Language) expression. Supply the JQL string in the <code>Jql</code> parameter; you can filter by status, project, date, assignee, text, and more.</p><p>See the commented examples in the code for common patterns (IDs, keys, dates, text search, etc.). JQL reference links are included in the snippet.</p> 

SELECT * FROM Issues WITH (Jql='status IN (Done, Closed) AND created > -5d' )
/*
Useful links:
  https://support.atlassian.com/jira-work-management/docs/jql-fields/
  https://www.atlassian.com/software/jira/guides/jql/tutorials#advanced-search
  https://www.atlassian.com/blog/jira/jql-the-most-flexible-way-to-search-jira-14
  
Other Possible JQL expressions:

Ids (IN):       key IN(10001, 10002, 10003);
Keys (IN):       key IN(CS-1, CS-2, CS-3);
Projects (IN):       project IN(PROJ1, PROJ2, PROJ3);
Status (EQUAL):       status='Done';
Date (Expression 1):       created >=-5d;
Date (Expression 2):       created >=startOfMonth() AND created <=now();
Date (Expression 3):       created >=startOfYear() AND created <=startOfDay();
Date (Static):       created >= '2008/12/31';
Date (Static with time):       created >= '2008/12/31 23:59';
Project and Status (AND + IN):       project=CS AND status NOT IN ('Done', 'Open', 'Closed');
Assignee and Created Date:    assignee is NOT EMPTY and created < -1d;
Text (Contains - Fuzzy):       Summary ~ 'some words' OR description ~ 'some words';
Text (Contains - Fuzzy Wildcard):       Summary ~ 'some*' OR description ~ 'some*';
Text (Contains - Exact):       Summary ~ '\"exact words\"' OR description ~ '\"exact words\"';
Text (Does Not Contain - Fuzzy):       Summary !~ 'some words' OR description !~ 'some words';
Empty OR Null:   fixVersion is empty OR fixVersion is null;
Is Not Empty OR Is Not Null:    fixVersion is not empty OR fixVersion is not null;
WAS Operator (previous value):       status WAS "Resolved" BY (jsmith,srogen) BEFORE "2019/02/02";
WAS IN Operator:   status WAS IN ("Resolved", "In Progress");
WAS NOT IN Operator:    status WAS NOT IN ("Resolved", "In Progress");
WAS + BY + DURING (date range):       status WAS "Resolved" BY (jsmith,srogen) DURING("2019/02/02", "2020/02/02");
CHANGED operator:    assignee CHANGED;
CHANGED operator (multiple):       status CHANGED FROM "In Progress" TO "Open"

*/

Read projects

<p>Gets all projects. Use this to list project keys and metadata before querying issues or creating new issues in a project.</p> 

SELECT * FROM Projects

Create a project

<p>Creates a single project. Supply <code>ProjectKey</code>, <code>Name</code>, <code>ProjectTypeKey</code>, <code>LeadAccountId</code>, and <code>AssigneeType</code>. Use <code>WITH (Output=1)</code> to return the created project.</p> 

INSERT INTO Projects(ProjectKey, Name, ProjectTypeKey, LeadAccountId, AssigneeType)
VALUES ('TEST', 'Test Project', 'software', '70122:XXXXXXXX-XXXX-XXXX-XXXX-c5da8c98b9e2', 'PROJECT_LEAD')
WITH (Output=1)

Update a project

<p>Updates an existing project. Set the columns to change (e.g. <code>Name</code>, <code>ProjectCategoryId</code>) and identify the project with <code>ProjectIdOrKey</code> in <code>WITH</code>.</p> 

UPDATE Projects
SET Name = 'My Test Kanban Project'
   ,ProjectCategoryId = 1
WITH (ProjectIdOrKey = 'MYPRJCT', Output=1, ContinueOn404Error=0)

Delete a project

<p>Deletes a single project. Identify the project with <code>ProjectIdOrKey</code> in the <code>WITH</code> clause. Use with care; deletion may be restricted by Jira configuration.</p> 

DELETE FROM Projects
WITH (ProjectIdOrKey = '10020', Output=1, ContinueOn404Error=0)

SQL Server

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

Read issues using JQL query

<p>Gets issues that match a JQL (Jira Query Language) expression. Supply the JQL string in the <code>Jql</code> parameter; you can filter by status, project, date, assignee, text, and more.</p><p>See the commented examples in the code for common patterns (IDs, keys, dates, text search, etc.). JQL reference links are included in the snippet.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH (Jql=''status IN (Done, Closed) AND created > -5d'' )
/*
Useful links:
  https://support.atlassian.com/jira-work-management/docs/jql-fields/
  https://www.atlassian.com/software/jira/guides/jql/tutorials#advanced-search
  https://www.atlassian.com/blog/jira/jql-the-most-flexible-way-to-search-jira-14
  
Other Possible JQL expressions:

Ids (IN):       key IN(10001, 10002, 10003);
Keys (IN):       key IN(CS-1, CS-2, CS-3);
Projects (IN):       project IN(PROJ1, PROJ2, PROJ3);
Status (EQUAL):       status=''Done'';
Date (Expression 1):       created >=-5d;
Date (Expression 2):       created >=startOfMonth() AND created <=now();
Date (Expression 3):       created >=startOfYear() AND created <=startOfDay();
Date (Static):       created >= ''2008/12/31'';
Date (Static with time):       created >= ''2008/12/31 23:59'';
Project and Status (AND + IN):       project=CS AND status NOT IN (''Done'', ''Open'', ''Closed'');
Assignee and Created Date:    assignee is NOT EMPTY and created < -1d;
Text (Contains - Fuzzy):       Summary ~ ''some words'' OR description ~ ''some words'';
Text (Contains - Fuzzy Wildcard):       Summary ~ ''some*'' OR description ~ ''some*'';
Text (Contains - Exact):       Summary ~ ''\"exact words\"'' OR description ~ ''\"exact words\"'';
Text (Does Not Contain - Fuzzy):       Summary !~ ''some words'' OR description !~ ''some words'';
Empty OR Null:   fixVersion is empty OR fixVersion is null;
Is Not Empty OR Is Not Null:    fixVersion is not empty OR fixVersion is not null;
WAS Operator (previous value):       status WAS "Resolved" BY (jsmith,srogen) BEFORE "2019/02/02";
WAS IN Operator:   status WAS IN ("Resolved", "In Progress");
WAS NOT IN Operator:    status WAS NOT IN ("Resolved", "In Progress");
WAS + BY + DURING (date range):       status WAS "Resolved" BY (jsmith,srogen) DURING("2019/02/02", "2020/02/02");
CHANGED operator:    assignee CHANGED;
CHANGED operator (multiple):       status CHANGED FROM "In Progress" TO "Open"

*/';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

Read projects

<p>Gets all projects. Use this to list project keys and metadata before querying issues or creating new issues in a project.</p> 

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

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

Create a project

<p>Creates a single project. Supply <code>ProjectKey</code>, <code>Name</code>, <code>ProjectTypeKey</code>, <code>LeadAccountId</code>, and <code>AssigneeType</code>. Use <code>WITH (Output=1)</code> to return the created project.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Projects(ProjectKey, Name, ProjectTypeKey, LeadAccountId, AssigneeType)
VALUES (''TEST'', ''Test Project'', ''software'', ''70122:XXXXXXXX-XXXX-XXXX-XXXX-c5da8c98b9e2'', ''PROJECT_LEAD'')
WITH (Output=1)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

Update a project

<p>Updates an existing project. Set the columns to change (e.g. <code>Name</code>, <code>ProjectCategoryId</code>) and identify the project with <code>ProjectIdOrKey</code> in <code>WITH</code>.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Projects
SET Name = ''My Test Kanban Project''
   ,ProjectCategoryId = 1
WITH (ProjectIdOrKey = ''MYPRJCT'', Output=1, ContinueOn404Error=0)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

Delete a project

<p>Deletes a single project. Identify the project with <code>ProjectIdOrKey</code> in the <code>WITH</code> clause. Use with care; deletion may be restricted by Jira configuration.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Projects
WITH (ProjectIdOrKey = ''10020'', Output=1, ContinueOn404Error=0)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];