VOOZH about

URL: https://www.cdata.com/kb/tech/jira-odata-powerbuilder.rst

⇱ Consume Jira OData Feeds in PowerBuilder

Consume Jira OData Feeds in PowerBuilder

👁 Jerod Johnson
Jerod Johnson
Director, Technology Evangelism
This article demonstrates how to consume Jira data as OData feeds from PowerBuilder using the API Server.

The CData API Server produces secure feeds of Jira data that you can consume in PowerBuilder DataWindows. The API Server supports the major Web services, including OData, Atom, JSON, HTML, CSV, TSV, and RSS. It also supports the major authentication schemes and SSL.

This article shows how to create a DataWindow that uses the CData API Server to retrieve data.

About Jira Data Integration

CData simplifies access and integration of live Jira data. Our customers leverage CData connectivity to:

  • Gain bi-directional access to their Jira objects like issues, projects, and workflows.
  • Use SQL stored procedures to perform functional actions like changing issues status, creating custom fields, download or uploading an attachment, modifying or retrieving time tracking settings, and more.
  • Authenticate securely using a variety of methods, including username and password, OAuth, personal access token, API token, Crowd or OKTA SSO, LDAP, and more.

Most users leverage CData solutions to integrate Jira data with their database or data warehouse, whether that's using CData Sync directly or relying on CData's compatibility with platforms like SSIS or Azure Data Factory. Others are looking to get analytics and reporting on live Jira data from preferred analytics tools like Tableau and Power BI.

Learn more about how customers are seamlessly connecting to their Jira data to solve business problems from our blog: Drivers in Focus: Collaboration Tools.


Getting Started


Set Up the API Server

Follow the steps below to begin producing secure Jira OData services:

Deploy

The API Server runs on your own server. On Windows, you can deploy using the stand-alone server or IIS. On a Java servlet container, drop in the API Server WAR file. See the help documentation for more information and how-tos.

The API Server is also easy to deploy on Microsoft Azure, Amazon EC2, and Heroku.

Connect to Jira

After you deploy the API Server, provide authentication values and other connection properties needed to connect to Jira by clicking Settings -> Connections and adding a new connection in the API Server administration console.

To connect to JIRA, provide the User and Password. Additionally, provide the Url; for example, https://yoursitename.atlassian.net.

You can then choose the Jira entities you want to allow the API Server access to by clicking Settings -> Resources.

Additionally, click Settings -> Server and change the following settings for compatibility with PowerBuilder:

  • Default Format: Select XML (Atom) in the menu.
  • Default Version: Select 2.0 in the menu.

Authorize API Server Users

After determining the OData services you want to produce, authorize users by clicking Settings -> Users. The API Server uses authtoken-based authentication and supports the major authentication schemes. Access can also be restricted based on IP address; by default, only connections to the local machine are allowed. You can authenticate as well as encrypt connections with SSL.

Create a Profile for Jira

Follow the steps below to use the Database Painter tool to create a database profile for the OData API of the API Server. In the Database Painter, you can graphically manipulate data as well as execute SQL queries.

  1. Click Tools -> Database Painter.
  2. Right-click the OData node and click New Profile.
  3. In the Database Profile Setup dialog, enter the following:
    • Profile Name: Enter a user-friendly name for the profile.
    • URI: Enter the URL to the OData endpoint of the API Server. This URL will resemble the one below: 
      http://MyServer:8080/api.rsc
    • Supply User Id and Password: Click this option to use HTTP Basic authentication. Note that the API Server also supports Windows authentication.
    • User Id: Enter the name of an user in the API Server.
    • Password: Enter the authtoken of an user in the API Server.
    👁 The profile for the OData feed. (Salesforce is shown.)
  4. To view and modify a table, right-click a table and then click Edit Data -> Grid.
👁 Modify database objects in the Database Painter. (Salesforce is shown.)

Using Jira Data with PowerBuilder Controls

You can use standard PowerBuilder objects to connect to OData feeds and execute queries. The following example shows how to retrieve Jira data into a DataWindow.

You can add the following code to the open method:

SQLCA.DBMS = "ODT"
SQLCA.DBParm = "ConnectString='URI=http://MyServer:8080/api.rsc;UID=MyAPIUser;PWD=MyAuthtoken'";
CONNECT USING SQLCA;
dw_issues.SetTransObject(SQLCA);
dw_issues.Retrieve();