SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Data Virtualization Tutorials and Guides
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




Connect JIRA REST API using Data Virtuality Web Service Connector


Using JIRA REST API as an endpoint in Data Virtuality Web Service connector, data virtualization architects and developers can add JIRA Web API as a new data source and access projects, issues, tasks, dashboard data, etc in tabular format using standard SQL codes. This Data Virtuality tutorial shows how to add your JIRA server as a Web Service data source and access to projects list as a sample case.

JIRA Issue and Project Tracking software is one of the most popular task and project tracking application especially for agile teams. All JIRA items like dashboards, projects, issues, tasks, task types, status details, etc which are accessible via JIRA API can be added into your data virtualization environment as a virtual view or by creating a virtual procedure easily on Data Virtuality software.

For Data Virtuality system administrators, the easiest solution will be installing the build-in Data Virtuality JIRA connector for accessing your JIRA server using REST API and integrate JIRA data.

In order to connect to JIRA Issue Tracker using JIRA API, following procedure can be implemented on Data Virtuality server via the Data Virtuality Studio.

Data Virtuality JIRA Rest API Connection using Web Service Connector

All we need is an end point to the JIRA server Web API and a valid username and password that can connect to JIRA issue tracker application and query data.

First of all, launch Data Virtuality Studio and connect to the traget Data Virtuality server.
On Data Sources right click and select Add data source

add new data source to Data Virtuality server

When the data source type selection screen is displayed, filter Web service data source template for the JIRA API

add web service as new data source to data virtualization application

When web service the data parameters are requested for the JIRA API, I selected Basic authentication and filled other requires values are seen in below screenshot. The JIRA server Web API endpoint is important and identifies the JIRA server which you are going to connect.

Data Virtuality JIRA Web API connector setting

Following screen will let you identify whether statistics data about the data source will be collected or not as similar case for all data sources created on Data Virtuality.
Click Finish to complete adding JIRA REST API as a new Data Virtuality data source.

If all is fine and you can connect to the JIRA Web Service endpoint using the credentials provided in the connector template, following success message will be displayed.

Connection successful
Data source added.

data connection to JIRA Web API is successful

As an administrator or data modeller, if you check the node by a drill-down on JIRA data source, you can see two procedures created by default for every web service: invoke() and invokeHttp()
Developers can call the REST API methods using invokeHttp procedure

For example, to get the list of projects defined on JIRA, the "project" action or API method can be called as follows

CALL "JIRA.invokeHttp" (
 action => 'GET',
 contentType => 'application/json',
 endpoint => 'project',
 requestContentType => 'application/json',
 failOnHttpError => false
);;

Please note that to fetch the returned or response content which is in JSON format, we have to read the returned result output parameter of the invokeHttp procedure

Since the result parameter is in blob data type, we have to convert it into a readable format

Web Service invokeHttp procedure parameters

This type conversion can be managed by using TO_CHARS() function on th returned result output parameter as follows

SELECT
 to_chars(result, 'UTF-8') as result
FROM (
 CALL "JIRA.invokeHttp" (
  action => 'GET'
  ,contentType => 'application/json'
  ,endpoint => 'project'
  ,requestContentType => 'application/json'
  ,failOnHttpError => false
 )
) as p;;

JIRA API call response with projects list in JSON format

Programmers can format the returned JSON response data for more readability using an online JSON Viewer application. Here is what I got when I use http://jsonviewer.stack.hu/

display JIRA Web API call response on JSON Viewer

Of course to convert the semi-structured JSON format data into a tabular structured data format, we need to apply a conversion. To parse the JSON into a tabular format, JSONTOXML and XMLTABLE functions can be used. This format change will enable data model developers easily consume the projects API method call.

SELECT
 id,
 key,
 name
FROM (
 SELECT
  to_chars(result, 'UTF-8') as result
 FROM (
  CALL "JIRA.invokeHttp" (
   action => 'GET'
   ,contentType => 'application/json'
   ,endpoint => 'project'
   ,requestContentType => 'application/json'
   ,failOnHttpError => false
  )
 ) as p
) w,
XMLTABLE ('/root/root/.'
 PASSING
  JSONTOXML('root', w.result)
 COLUMNS
  id string,
  key string,
  name string
) x;;

list of JIRA projects using Data Virtuality Web Service connector

This code can be converted and encapsulated as a virtual procedure too

CREATE VIRTUAL PROCEDURE JIRA_VS.Projects_List_Response (
)
RETURNS (
 id string,
 key string,
 name string
)
AS
BEGIN

/*
SELECT
 to_chars(result, 'UTF-8') as result
FROM (
 CALL "JIRA.invokeHttp" (
  action => 'GET'
  ,contentType => 'application/json'
  ,endpoint => 'project'
  ,requestContentType => 'application/json'
  ,failOnHttpError => false
 )
) as p;
*/

SELECT
 id,
 key,
 name
FROM (
 SELECT
  to_chars(result, 'UTF-8') as result
 FROM (
  CALL "JIRA.invokeHttp" (
   action => 'GET'
   ,contentType => 'application/json'
   ,endpoint => 'project'
   ,requestContentType => 'application/json'
   ,failOnHttpError => false
  )
 ) as p
) w,
XMLTABLE ('/root/root/.'
 PASSING
  JSONTOXML('root', w.result)
 COLUMNS
  id string,
  key string,
  name string
) x;

END;

And this procedure is called as follows

call "JIRA_VS.Projects_List_Response"();;

Of course for data virtualization layer SQL developers, it is also possible to create a view in a virtual schema in Data Virtuality which lists all projects created under connected JIRA server.

For the sake of simplicity, I gave the example of projects in this Data Virtuality Web Service connector tutorial for connecting JIRA Web API. Programmers can extend to other JIRA data objects including issues and more to build a complete data model in Data Virtuality.

In addition to Web Service connector, Data Virtuality shipped a build-in Data Virtuality JIRA connector for JIRA software as I mentioned before. It is possible to request and apply the new JIRA REST API connector in a few clicks on Data Virtuality server. For more details please refer to Data Virtuality Connectors: JIRA official documentation.





DV Tutorials

Data Virtualization










Copyright © 2004 - 2020 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems