Insight Automation Groovy Script SQL Database Connection

Jason Whitaker October 27, 2017

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 votes
Alexey Matveev
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 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. 

Jason Whitaker October 27, 2017

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 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.

Jason Whitaker October 28, 2017

Hi Matveev,

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

Alexander Sundström
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 1, 2017

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

Alexander Sundström
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 1, 2017

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

Jason Whitaker November 5, 2017

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)

 

Jason Whitaker November 13, 2017

Hi Alex any ideas?

Alexander Sundström
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 14, 2017

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

Jason Whitaker November 19, 2017

Hi Alex,

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

Regards, Jason

Alexander Sundström
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 20, 2017

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
TAGS
AUG Leaders

Atlassian Community Events