Is it possible to use REST API to run SQL queries directly on the database??

[David So]

Hi Team,

According to https://jira.atlassian.com/browse/JRASERVER-36046, this is not possible but since this is 9 years ago like to confirm if is still the case. 

In addition is there any plan this would be consider??

Regards,

DS

[Fabio Racobaldo _Catworkx_]

Hi @David So ,

there's no way to run a SQL query through a REST API using builtin features.

Fabio

[Carlos Garcia Navarro]

Hi @David So ,

What queries specifically would you like to run on the database? This other post may be helpful: https://community.atlassian.com/t5/Jira-Service-Management/integration-between-JIRA-Service-Management-cloud-and-MS-SQL-DB/qaq-p/1797241

[David So]

Hi Carlos,

Is quite a big query, not sure if the KB https://community.atlassian.com/t5/Jira-Service-Management/integration-between-JIRA-Service-Management-cloud-and-MS-SQL-DB/qaq-p/1797241 works for Jira software, on top the response from Ravi Sagar _Sparxsys was to use scriptunner which is NOT feasible for us, any other options? I found add-on like https://marketplace.atlassian.com/apps/1217627/sql-reporter-for-jira?tab=overview&hosting=datacenter but is probably last resort if can be done from the app itself.

Summary, i.Assignee as Assignee, i.reporter as Reporter, priority, ISS.pname as Ticket_Status, Rez.pname as Resolution , Created, i.Updated, cv12.datevalue RESOLUTIONDATE, cv.DATEVALUE as DueDate,'' as analyst, cv4.DateValue as CompletionDate,
cv3.DATEVALUE as StartDate, cv2.DATEVALUE as OriginalDueDate , cv5.StringValue as EpicName, co4.customvalue as Dept, cv7.StringValue as Metric, co.customvalue as Percentagecompleted, co3.customvalue as DataAsset, co2.customvalue as PriorityLevel, cv11.STRINGVALUE as SME

from jiraissue I
LEFT JOIN
issuetype IT ON I.issuetype = IT.ID
LEFT JOIN
issuestatus ISS ON I.issuestatus = ISS.ID
LEFT JOIN
project Prj ON I.project = Prj.ID
LEFT JOIN
resolution Rez ON I.Resolution = Rez.ID
LEFT JOIN customfieldvalue cv on cv.ISSUE = I.ID and cv.customfield = 19565 – DueDate
LEFT JOIN customfieldvalue cv2 on cv2.ISSUE = I.ID and cv2.customfield = 21001 --OriginalDueDate
LEFT JOIN customfieldvalue cv3 on cv3.ISSUE = I.ID and cv3.customfield = 21040 --StartDate
left join (select max(display_name) display_name, user_name from cwd_user group by user_name) u on u.user_name = i.Assignee
left join (select max(display_name) display_name, user_name from cwd_user group by user_name) u2 on u2.user_name = i.REPORTER
LEFT JOIN customfieldvalue cv4 on cv4.ISSUE = I.ID and cv4.customfield = 21016 --Completion Date
LEFT JOIN customfieldvalue cv5 on cv5.ISSUE = I.ID and cv5.customfield = 10004 – EpicName
LEFT JOIN customfieldvalue cv6 on cv6.ISSUE = I.ID and cv6.customfield = 21041 – Dept
LEFT JOIN customfieldvalue cv7 on cv7.ISSUE = I.ID and cv7.customfield = 16324 – Metric
LEFT JOIN customfieldvalue cv9 on cv9.ISSUE = I.ID and cv9.customfield = 21069 – DataAsset
LEFT JOIN customfieldvalue cv8 on cv8.ISSUE = I.ID and cv8.customfield = 21011 – Percentagecompleted
LEFT JOIN customfieldvalue cv10 on cv10.ISSUE = I.ID and cv10.customfield = 21044 – Priority
LEFT JOIN customfieldvalue cv11 on cv11.ISSUE = I.ID and cv11.customfield = 21060 – SME
LEFT JOIN customfieldvalue cv12 on cv12.ISSUE = I.ID and cv12.customfield = 14933 – resolved
left join customfieldoption co on co.CUSTOMFIELD = cv8.customfield and cast(cv8.STRINGVALUE as integer) = co.ID
left join customfieldoption co2 on co2.CUSTOMFIELD = cv10.customfield and cast(cv10.STRINGVALUE as integer) = co2.ID
left join customfieldoption co3 on co3.CUSTOMFIELD = cv9.customfield and cast(cv9.STRINGVALUE as integer) = co3.ID


where I.Project = 10000
and Created > CURRENT_DATE + INTERVAL '-1 year

 

Regards,

David

[Carlos Garcia Navarro]

Yeah... unfortunately there is not a way to run the query using REST API in the native Jira.