How to call a method that queries the JIRA SQL server databse INSIDE a plugin

I have a Report Plugin for JIRA. Inside the .java file I have a method which queries the JIRA SQL server databse and retrieves a hash table of values. I'm c ertain that this function is working because it is executing succesfully in a seperate application. However when I attempt to call this function inside my velocity view template it is not getting executed whereas the other functions in this java class have no issues with execution. Here's a snippet of the code I'm attempting to execute:

public class Something implements Serializable

{

public Something()

{

super();

}

public static Hashtable<Long, String> JDBCconnectionMethod() throws SQLException

{

try {

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

Connection con = DriverManager.getConnection(

"jdbc:sqlserver://jirasql01;database=jiradb","jirauser",

"myJiraUser");

String queryString = "select v.PROJECTID as PROJECTID, v.VALUE as TargetEndDate from [jiradb].jiraschema.AO_00B950_PROJECT_VALUE as v left join [jiradb].jiraschema.AO_00B950_FIELD as f on v.FIELD_ID = f.ID where f.NAME = 'Target End Date'";

PreparedStatement ps = con.prepareStatement(queryString);

ResultSet rs = ps.executeQuery();

Hashtable<Long, String> targetDateTable = new Hashtable<Long, String>();

while (rs.next())

{

//System.out.println(targetDateTable);

targetDateTable.put(rs.getLong(1), rs.getString(2));

}

return targetDateTable;

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

return null;

}

4 answers

1 accepted

This widget could not be displayed.

Looks like a modification in the pom.xml might solve the problem. Since the jdbc-jtds-whatever.jar is an external reference that you had to add to your build path, It must be added as a dependency to the pom.xml. The fix is :

<dependency>

<groupId>net.sourceforge.jtds</groupId>

<artifactId>jtds</artifactId>

<version>1.2.4</version>

<scope>provided</scope>

</dependency>

Wow how did you manage to pinpoint my problem down so accurately on so little information?! You deffinetely deserve the karma reward and recognition as one of the top atlassian debuggers!!

This widget could not be displayed.

add this to your template

#foreach( $key in $context.keys )
    $key = $context.get($key)
#end

#foreach($p in $ctx.keySet().toArray())
  $p.toString() - $ctx.get($p).getClass().getName().toString()
#end

unless stated otherwise the content of the velocityt context can be found here :

also, increase verbosity in log4j and run your plugin with atlas-debug , you will then see if your code is effectively called by using a break point (default port is 5005).

have you trie to let your container handle the connection for you ?

&lt;Resource name="jdbc/CRMDS" auth="Container" type="javax.sql.DataSource"
username="confluence"
password="confluence"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://localhost/CRM"
/&gt;

and

import com.atlassian.jira.ofbiz.DefaultOfBizConnectionFactory;

ofBizFactory = new DefaultOfBizConnectionFactory();
Connection con;
ctx = new InitialContext();
ds = (DataSource) ctx.lookup(datasourceName);
if (ds != null) {
	con = ds.getConnection();
} else {
	con = ofBizFactory.getConnection();
}

datasourceName is the name of the jndi ressource set in your context.xml , as :

Context ctx = new InitialContext(); ctx.lookup("jdbc/xxxx")


best regards,

trey,
I tried making the modifications you said... I have a doubt though..
in ds = (DataSource) ctx.lookup(datasourceName) , what does the datasourceName correspond to? will it be the name of the database or the entire connection string " jdbc:jtds:sqlserver://jirasql01:1433/jiradb " ??
also, I added the <Resource> container to the atlassian-xml file. and made the following changes to my code :

public Hashtable<Long, String> JDBCconnectionMethod() throws SQLException

{

try {

DefaultOfBizConnectionFactory ofBizFactory = new DefaultOfBizConnectionFactory();

Connection con;

InitialContext ctx;

ctx = new InitialContext();

DataSource ds = (DataSource) ctx.lookup("jdbc:jtds:sqlserver://jirasql01:1433/jiradb");

if (ds != null)

{

con = ds.getConnection();

boolean connState = con.isClosed();

log.error("connection state is:" + connState);

}

else

{

con = ofBizFactory.getConnection();

}

String queryString = "select v.PROJECTID as PROJECTID, v.VALUE as TargetEndDate from [jiradb].jiraschema.AO_00B950_PROJECT_VALUE as v left join [jiradb].jiraschema.AO_00B950_FIELD as f on v.FIELD_ID = f.ID where f.NAME = 'Target End Date'";

PreparedStatement ps = con.prepareStatement(queryString);

ResultSet rs = ps.executeQuery();

Hashtable<Long, String> targetDateTable = new Hashtable<Long, String>();

while (rs.next())

{

targetDateTable.put(rs.getLong(1), rs.getString(2));

}

return targetDateTable;

}

catch (NamingException e)

{

// TODO Auto-generated catch block

e.printStackTrace();

}

catch (ClassNotFoundException e) {

e.printStackTrace();

}

catch (SQLException e) {

e.printStackTrace();

}

and in my vm file I have:

<tr><td nowrap> #foreach($p in $ctx.keySet().toArray())

$p.toString() - $ctx.get($p).getClass().getName().toString()

I really appreciate the help Trey! =)

hi,

what is the content of you velocity template ?

best regards

This widget could not be displayed.

After some testing I've concluded that the function is not making a succesful connection to the SQL database when it is being run on JIRA. However I have no problems making the connection when running the function outside of JIRA. Could someone please give me some suggestions?

This widget could not be displayed.
Do you get an error message? Where did you install the jtds driver? lib directory?

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Aug 22, 2018 in Marketplace Apps

How a Marketplace app tech team is achieving gender diversity

Hello! My name is Genevieve Blanch, and I'm the Marketing Manager at RefinedWiki, creators of apps to give teams the tools to customize Atlassian platforms. Currently, 44% of the tech team at Re...

477 views 3 17
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you