Insight Automation Groovy Script SQL Database Connection

[Jason Whitaker]

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)

[Alexey Matveev]

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]

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]

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]

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]

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]

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]

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]

Hi Alex any ideas?

[Alexander Sundström]

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]

Hi Alex,

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

Regards, Jason

[Alexander Sundström]

Glad that it worked :)

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

Best Regards
Alexander