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

[tommy guo]

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;

}

[mahalakshmi balasubramanian · Accepted solution]

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>

[tommy guo]

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 :

• https://developer.atlassian.com/pages/viewpage.action?pageId=4227172
• https://developer.atlassian.com/display/JIRADEV/Contents+of+the+Velocity+Context

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 ?

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

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,

[tommy guo]

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

[codelab expert]

Do you get an error message? Where did you install the jtds driver? lib directory?

[tommy guo]

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?