Problem connecting Jira Plugin to second external DB

Hi

I’m currently stuck with the following problem. I read a lot, I tried a lot, but so far I still could not figure out how to solve it. I also reached out to the Atlassian community (not developer) and on stackoverflow but so far no success.

I would like to achieve the following:

  • Jira issue tab panel plugin that
  • connects via JDBC to a SQL Server instance and
  • retrieves data from a table which
  • is then displayed in the plugin

Further potentially useful information:

  • JIRA Build: 7.13.0
  • Application Server: Apache Tomcat/8.5.35 - Servlet API 3.1
  • Java Version: 1.8.0_212 - AdoptOpenJDK
  • atlassian-plugin-sdk: 8.0.7
  • apache-maven: 3.5.4
  • amps-dispatcher-maven-plugin: 8.0.0

The issue tab panel plugin itself already worked and I managed to display some basic data (issue and user-data) but no data from SQL was retrieved. The error message in the log was the following:

java.sql.SQLException: No suitable driver found for jdbc:sqlserver://servername\LOCAL;databaseName=dbname;user=user;password=password;

In the Jira plugin project I then I added the following dependencies in the pom.xml:

<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
  <version>7.0.0.jre8</version>
</dependency>


<dependency>
  <groupId>com.microsoft.aad</groupId>
  <artifactId>adal4j</artifactId>
  <version>0.0.2</version>
</dependency>

I ran atlas-clean and afterwards atlas-debug again.

The error message I got was (with 7.0.0.jre8):

Cannot start plugin: com.atlassian.tutorial.myPlugin
[INFO] [talledLocalContainer] Unresolved constraint in bundle com.atlassian.tutorial.myPlugin [171]:
Unable to resolve 171.0: missing requirement [171.0] osgi.wiring.package; (osgi.wiring.package=com.microsoft.azure)

I partly rebuild the code of the plugin in a maven project in Eclipse and everything works fine. I am able to retrieve the data.
In Eclipse I use driver: mssql-jdbc-7.2.2.jre11.jar which is under referenced libraries. The the java code itself works.

I also tried the following (without success obviously):

  • Different sql-server jdbc driver versions (jre8 versions & jre11)
  • I also tried to add this mssql.jar the following folders:
    • \myPlugin\target\container\tomcat8x\apache-tomcat-8.5.35\lib
    • \myPlugin\target\jira\webapp\WEB-INF\lib

I read that I need to add datasources in different xml-files (e.g. dbconfig) but I am confused where to add which code.

If anybody has some suggestions what I could still try, I would be very happy.

For completeness:

See below the class that I use to connect to SQL. This works in Eclipse. Inputs are the conncetion-string (see above) and a simple SQL-query.

public class CommentsFromSQL{

public Connection con ; 
public Statement stmt ; 
public ResultSet rset ;
public ResultSetMetaData rsmd ;
public int columncount ;
public static ArrayList<Map<String, Object>> queryResult;	
	
public static ArrayList<Map<String, Object>> getRows(String conn, String query)
    throws SQLException 
{		
	try {
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	} catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
    
	try (Connection con = DriverManager.getConnection(conn); Statement stmt = con.createStatement();) {
	    
    ResultSet rset = stmt.executeQuery(query);
    ResultSetMetaData rsmd = rset.getMetaData();
    int columncount = rsmd.getColumnCount();
    
    ArrayList<Map<String, Object>> queryResult = new ArrayList<Map<String, Object>>();
    
    while (rset.next()) {
        Map<String, Object> row = new HashMap<String, Object>();
        for (int i = 1; i <= columncount; i++) {
            row.put(rsmd.getColumnName(i), rset.getObject(i));
        }
        queryResult.add(row);
        
    }
    con.close();
	return queryResult;
    
    }
    // Handle any errors that may have occurred.
    catch (SQLException e) {
        e.printStackTrace();
    }		
	return queryResult;                 
}

}

Although this might not be the answer you are looking for, but have you considered creating a separate REST API service which exposes the data and which is consumed by your plugin using HTTP(S) calls? This might save you some headache trying to get this to work.

1 Like

Yes, this will probably come in the future. But for now I would like to get this to work as described and I’m pretty sure it’s possible, the question just is: how? :slight_smile:

1 Like

The problem is more than likely the js congress driver you’re embedding in your jar has osgi stuff in it which is then being picked up by the plugin system. I believe you can do some spring scanned magic. @mpaisley would know better.

Have you tried making the db jars scoped as provided and then adding them to the lib folder of tomcat? (I can’t remember if the db connection stuff respects p2).

2 Likes

Do you mean putting the mssql-driver.jar into folder:
\myPlugin\target\container\tomcat8x\apache-tomcat-8.5.35\lib

Is this what you mean?

What’s the preferred way to make a RESTful call within a Jira plugin?

In Java there are quite some different opinions, see:

Is there a good example for making REST API calls from within a Jira plugin?

It looks like this is going to be the path I will follow rather sooner than later :D.

There are actually two ways of doing it: server-side and client-side. If you want to have the data available within the context of the Velocity templates, you will have to do it server-side. I always use Apache HttpClient (https://hc.apache.org/httpcomponents-client-ga/index.html), but I’m not a hardcore Java programmer, so maybe there are newer packages available.

To retrieve the data client-side (in Javascript), you can use Axios (https://www.npmjs.com/package/axios). Please make sure set the appropriate CORS headers in your API.

1 Like

Thanks for your quick reply, appreciated.

The data should be available within the context of Velocity templates, therefore I will go with Apache HttpClient which looks good to me :+1:

1 Like

We had similar issues that we opened this question with: How can I provide an SQL Driver in the SDK to develop against?

We were able to get a MSSQL driver loaded in the SDK via the method described in our thread, and we relied on the system driver that was deployed on production.

2 Likes

sfbehnke, you really made my day!

Adding the following entry in pom.xml inside of the <Import-Package> declaration.

*;version="0";resolution:="optional"

solved it for me. The dependency to the sql-driver, I already had in the pom.xml.
Another thing I needed to add was (in the java class that uses the sql driver, at the very top):

  try {
  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
  } catch (ClassNotFoundException e1) {
  // TODO Auto-generated catch block
  e1.printStackTrace();
  }

Thank you so much sfbehnke for the link you provided :+1::+1:

2 Likes

Yepp. Don’t know if it will resolve it or not but worth a try.

Frederik, thanks! Really the praise should go to my compatriot, @jason.connor – He figured it out when I could not!

1 Like

@sfbehnke You pointed me to the solution which @jason.connor provided, so thanks to both of you :slight_smile:

What still would be interesting for me is what the entry
*;version="0";resolution:="optional" actually does.
Does anybody know?

My osgi is is a bit rusty, but:
Import everything, starting at version 0 (osgi likes semantic versioning), but make it all optional so if you can’t find it - don’t blow up (until you try to use it).

2 Likes

Thanks @danielwester!