Plugin: Connecting custom field to external database

[Viktor Mirzoyan]

I'm having touble pulling data from an external database to a customfield.

I'm using the Microsoft JDBC 3.0 driver to connect to a SQL Server. I simply added the .jar file to my class build path. When I run my "database" code outside the plugin context, I'm able to pull data just fine, but once I integrate it into the plugin, my customfield doesn't even show up on the page anymore. Perhaps there's something that I don't know. I tried to attach a remote debugger to the plugin to see what's going on but I wasn't able to figure out how to do that.

Anyone have any ideas or suggestions on how to properly make this database connection in a plugin?

[Wim Deblauwe]

You can use the Database Values Plugin to do this. If there is missing functionality, you are welcome to contribute. You can also look at the source code to see how it is done.

[Viktor Mirzoyan]

I've considered your plugin before, but I decided to go ahead and make our own. May I ask how you imported the various database drivers? I'm currently trying to use a JDBC driver for sqlserver, but I'm having a hard time importing the driver into the plugin for use. Can you help?

[Wim Deblauwe]

It depends on what type of plugin you use. I am using the plugins 1 system (no OSGi). The JDBC driver can be loaded in a normal way, by using Class.forName() and putting the correct jar in WEB-INF/lib. If you use a plugins 2 system plugin (with OSGi), then you need to add the appropriate things in the manifest. See this question on the old forums. This is basically what you need to add to your pom.xml:

<plugin>

<groupId>com.atlassian.maven.plugins</groupId>

<artifactId>maven-jira-plugin</artifactId>

<version>3.2.3</version>

<extensions>true</extensions>

<configuration>

<productVersion>${jira.version}</productVersion>

<productDataVersion>${jira.data.version}</productDataVersion>

<instructions>

<!-- OSGi intructions go there -->

<Import-Package>org.hsqldb.*</Import-Package>

</instructions>

</configuration>

</plugin>

Make sure you use the correct package for the driver(s) you want to support.

[Viktor Mirzoyan]

So I need to replace "org.hsqldb.*" with my package name? I'm trying to use this driver: http://www.microsoft.com/download/en/details.aspx?id=21599. The driver comes in a .jar file, which I've unpackaged and the folders go as "com.microsoft.sqlserver.jdbc" Would this be my package name? If so, does this folder need to be in a sepcific location in my plugin directory? Thanks for the help

[Wim Deblauwe]

You don't need to create a folder. Just replace:

<Import-Package>org.hsqldb.*</Import-Package>

with

<Import-Package>com.microsoft.sqlserver.jdbc.*</Import-Package>

[Viktor Mirzoyan]

I've tried that... but where is the ".jar" supposed to be located? It must have a reference to it somewhere. Also do i load the driver as usual by Class.forName()?

[Viktor Mirzoyan]

<Import-Package>com.microsoft.sqlserver.jdbc.*</Import-Package>

Gives a warning in manifest:

"Could not find matching referal for com.microsoft.sqlserver.jdbc.*"

Is this potentially the problem?

[Viktor Mirzoyan]

I should add that the build finishes and the host application becomes available, but the plugin fails to load. Says that the MultiSelectCF class is missing. This problem does not occur when i exclude importing the package.

[Jeremy Largman]

Think of this in two stages - compile and run.

To get it to compile, you have to add it in your pom.xml. The pom.xml should, if your proxy is configured correctly, download the driver file from maven's repository and put it in your .m2/repository folder. Then it should compile.

To get it to run, you have to add the driver jar to your /WEB-INF/lib folder in your installation, where your plugin is running.

[Ellen Feaheny [AppFusions]]

Have you tried nFeed?

https://marketplace.atlassian.com/plugins/com.valiantys.jira.plugins.SQLFeed

[Ramses Palomo]

I wanted to add a couple of question for Viktor.

1. Did you setup a JNDI for your connection string?

2. How does the code for connecting to the database look like? I'm having problems doing an InitialContext() it throws an exception

Thanks for any tips.

[Viktor Mirzoyan]

This isn't exactly the perfect solution, but I was able to make a workaround since I was having a hard time getting this to work.

For those of you in the future who may struggle with this as much as I have:

My solution was to use the jTDS sqlserver driver from http://ebr.springsource.com/repository/app/bundle/version/detail?name=com.springsource.net.sourceforge.jtds&version=1.2.2&searchType=bundlesByName&searchQuery=JDBC

The SpringSource Bundle repository has ready-to-use bundles, which are added as a dependency in your pom.xml ...

example:

<dependency>

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

<artifactId>com.springsource.net.sourceforge.jtds</artifactId>

<version>1.2.2</version>

</dependency>

This will retrieve and load the jTDS driver from their repository and have it available to your Plugin.

How to use the driver can be found here http://jtds.sourceforge.net/faq.html

[Viktor Mirzoyan]

Really stuck on this one, can anyone help?