Insight Automation Groovy Script SQL Database Connection

I'm trying to use a groovy script in insight automation to update an external database when an object is updated. The code I am using follows:

import groovy.sql.Sql
import java.sql.Driver


def driver = Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance() as Driver

def props = new Properties()
props.setProperty("user", "usernamehere")
props.setProperty("password", "passwordhere")

def conn = driver.connect("jdbc:jtds:sqlserver://sql01:52199/MDT", props)
def sql = new Sql(conn)

try {
    sql.eachRow("Update ComputerIdentity Set UUID = 'test3' where ID = 8") {
        log.debug(it)
    }
} finally {
    sql.close()
    conn.close()
}

Looking at the Jira logs the following error occurs:

 [c.r.j.p.i.s.automation.action.AutomationRuleGroovyScriptAction] AutomationRuleGroovyScriptAction, Unexpected error: net.sourceforge.jtds.jdbc.Driver
java.lang.ClassNotFoundException: net.sourceforge.jtds.jdbc.Driver
 at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
 at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
 at groovy.lang.GroovyClassLoader.loadClass(GroovyClassLoader.java:677)
 at groovy.lang.GroovyClassLoader$InnerLoader.loadClass(GroovyClassLoader.java:425)
 at groovy.lang.GroovyClassLoader.loadClass(GroovyClassLoader.java:787)
 at groovy.lang.GroovyClassLoader.loadClass(GroovyClassLoader.java:775)
 at java.lang.Class.forName0(Native Method)
 at java.lang.Class.forName(Class.java:264)
 at mdt_update.run(mdt_update.groovy:5)
 at groovy.lang.GroovyShell.evaluate(GroovyShell.java:591)
 at groovy.lang.GroovyShell.evaluate(GroovyShell.java:638)
 at com.riadalabs.jira.plugins.insight.services.groovy.GroovyScriptExecutor.executeWithFile(GroovyScriptExecutor.java:65)
 at com.riadalabs.jira.plugins.insight.services.automation.action.AutomationRuleGroovyScriptAction.doActionSafe(AutomationRuleGroovyScriptAction.java:68)
 at com.riadalabs.jira.plugins.insight.services.automation.action.AbstractInsightAutomationAction.doAction(AbstractInsightAutomationAction.java:103)
 at com.riadalabs.jira.plugins.insight.services.automation.rule.impl.AutomationRuleEngineImpl.executeAction(AutomationRuleEngineImpl.java:589)
 at com.riadalabs.jira.plugins.insight.services.automation.rule.impl.AutomationRuleEngineImpl.executeActions(AutomationRuleEngineImpl.java:580)
 at com.riadalabs.jira.plugins.insight.services.automation.rule.impl.AutomationRuleEngineImpl.matchConditionAndExecuteActions(AutomationRuleEngineImpl.java:374)
 at com.riadalabs.jira.plugins.insight.services.automation.rule.impl.AutomationRuleEngineImpl.handleAsyncEvent(AutomationRuleEngineImpl.java:192)
 at com.riadalabs.jira.plugins.insight.services.automation.event.AutomationRuleEventListener.onAsyncEvent(AutomationRuleEventListener.java:90)
 ... 3 filtered
 at java.lang.reflect.Method.invoke(Method.java:498)
 at com.atlassian.event.internal.SingleParameterMethodListenerInvoker.invoke(SingleParameterMethodListenerInvoker.java:36)
 at com.atlassian.event.internal.AsynchronousAbleEventDispatcher$1$1.run(AsynchronousAbleEventDispatcher.java:48)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
 at java.lang.Thread.run(Thread.java:745)

 

 

 

 

1 answer

0 vote
Alexey Matveev Community Champion Oct 27, 2017

Hello, 

If you want to use a driver to connect to a database,  this driver should be in classpath of the plugin. If you use server or datacenter version of Jira you can download the driver in the internet and put it into lib folder in Jira installation folder. After it you have to restart Jira. 

Hi Matveev,

I have stored the driver in c:\Program Files\Atlassian\JIRA\lib and restarted my Jira instance but the error still occurs.

Alexey Matveev Community Champion Oct 28, 2017

There is also Lib folder in web-inf folder. Try to put it there and restart. This works for Adaptivist Scriptrunner which also uses groovy scripts but of course plugins were developed by different developers and insight developers could just ignore extending classpath. If nothing works you can ask developers of insight plugin.

Hi Matveev,

I followed your advice but still the same issue. Thanks for your help. Hopefully the Insight people will reply soon.

Hi Jason,

I have tried this out locally and found the same issue. I will investigate this further and get back to you when I have more information.

Best Regards
Alexander

Hi Jason,

Could you try this and adopt it to your code and let us know the result.

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Properties;
import com.atlassian.core.util.ClassLoaderUtils;
import com.riadalabs.jira.plugins.insight.services.core.dal.dao.sql.AbstractAOService;
def driver = "net.sourceforge.jtds.jdbc.Driver";
def url = "jdbc://...";
def username = "username";
def password = "password";
Class dbClass = ClassLoaderUtils.loadClass(driver, AbstractAOService.class);
Driver driverInstance = (Driver) dbClass.newInstance();
DriverManager.registerDriver(driverInstance);
Properties info = new Properties();
info.put("user", username);
info.put("password", password);
Connection connection = driverInstance.connect(url, info);

Also: c:\Program Files\Atlassian\JIRA\lib should be the right directory

Best Regards
Alexander

Hi Alex,

I get the following error:

Unexpected error: No signature of method: java.lang.String.connect() is applicable for argument types: (java.lang.String, java.util.Properties) values: [jdbc:jtds:sqlserver://sql01:000000/database, [user:insightsvc, ...]]
Possible solutions: collect(), concat(java.lang.String), collect(java.util.Collection, groovy.lang.Closure), collect(groovy.lang.Closure), next(), toSet()
groovy.lang.MissingMethodException: No signature of method: java.lang.String.connect() is applicable for argument types: (java.lang.String, java.util.Properties) values: [jdbc:jtds:sqlserver://sql01:000000/database, [user:insightsvc, ...]]
Possible solutions: collect(), concat(java.lang.String), collect(java.util.Collection, groovy.lang.Closure), collect(groovy.lang.Closure), next(), toSet()
at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:58)
at org.codehaus.groovy.runtime.callsite.PojoMetaClassSite.call(PojoMetaClassSite.java:49)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:133)

 

Hi Jason,

Sorry for the delay, the last line needs to look like this instead:

Connection connection = driverInstance.connect(url, info);

I updated the original groovy script to avoid confusion.

 Let me know if that fixed it.

Best Regards
Alexander

Hi Alex,

Thanks that has worked. Don't know why I couldn't pick that out myself.

Regards, Jason

Glad that it worked :)

If you feel like this question has been answered, please mark the answer as accepted.

Best Regards
Alexander

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Bridget Sauer
Published Thursday in Marketplace Apps

Calling all developers––You're invited to Atlas Camp 2018

 Atlas Camp   is our developer event which will take place in Barcelona, Spain  from the 6th -7th of   September . This is a great opportunity to meet other developers and get n...

290 views 0 6
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