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

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!!

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

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?

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
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Jul 10, 2018 in Marketplace Apps

If you’re an Atlassian app developer, you’ll want to know about Atlas Camp!

This September 6-7, hundreds of Atlassian App developers will flock to Barcelona Spain to build skills, discover product roadmaps, meet face-to-face with the Atlassian team, and learn how to extend t...

172 views 0 4
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