Connect from Jira Plugin to SQL Server DB using JDBC

[Frederik Bosler]

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 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 exactly I now need to add which code.

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

For completness:

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 static ArrayList<Map<String, Object>> getRows(String conn, String query) throws SQLException 
{ 
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; 
}

}

[Deleted user]

Hi,

May I know how did you connect to Jira and able to fetch the basic issue details?

Did you use any sql+jql driver plugin for this?

Can you please help me out if you have any idea on how to query the jira database using SQL+JQL driver

Thanks in Advance,

Bindhu