ScriptRunner ClassNotFoundException Error During Workflow

[MikeS]

ScriptRunner 3.0.14

JIRA 6.3.14

I wrote a script to query a DB (I've already reviewedhttps://scriptrunner.adaptavist.com/latest/jira/recipes/misc/connecting-to-databases.html) and when i run it as part of my workflow I get the following error:

2016-04-27 13:15:45,913 http-bio-443-exec-2 WARN j1014151 795x298x1 14we0u0 10.47.4.82 /secure/CommentAssignIssue.jspa [jmwe.plugins.functions.SetFieldValueFunction] Error while executing SetFieldValueFunction: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

However when I run it from the Built In Scripts as an escalation service it finds the Class just fine. Where do I need to drop the sqljdbc4.jar so it is picked up or what setting is needed to be changed so that I can load the class correctly.

import groovy.sql.Sql;
import java.sql.Driver;
def driver = Class.forName('com.microsoft.sqlserver.jdbc.SQLServerDriver').newInstance() as Driver
def props = new Properties()
props.setProperty("user", "my_user")
props.setProperty("password", "my_pass")
def conn = driver.connect("jdbc:sqlserver://my_server:1433;databaseName=my_db", props)
def sql = new Sql(conn)
def statement =" select foo from bar"
def ced = sql.firstRow(statement);
return ced.foo.toString();

[Adam Markham [Adaptavist]]

You should place the jar file under the "<JIRA installation directory>/lib/" directory of where JIRA is installed.

If you installed the JIRA distribution for Windows, the SQL server driver should already come with it but I assume this is not the case. See link here.

[MikeS]

Thanks for the response.  I did put the JAR there but its still not found during workflow execution only.  If I remove it then the escalation service does not run so even though the Jar is distributed its still not being found.  

[Jamie Echlin [Adaptavist]]

does it work in script console?

In the workflow function, is it an inline script or a file?

[MikeS]

Thanks for the response, it is an inline script and yes it does work from the console.

[Jamie Echlin [Adaptavist]]

Does simply Class.forName('com.microsoft.sqlserver.jdbc.SQLServerDriver') work as a workflow function?

[MikeS]

It does not, i get the same error from the original post.

[MikeS]

As another test I switched over to use a jtds connection and again it works perfect from the Script Console but cannot load the class when called via a Workflow.

def driver = Class.forName('net.sourceforge.jtds.jdbc.Driver').newInstance() as Driver
def props = new Properties()
props.setProperty("user", "my_user")
props.setProperty("password", "my_pass")
def conn = driver.connect("jdbc:jtds:sqlserver://my_server:1433/my_db", props)
def sql = new Sql(conn)

To be more clear, when I am attempting to run it from a workflow I am selecting "Set Field Value to constant or Groovy expression" from the Post Function list.  

[Jamie Echlin [Adaptavist]]

Can you attach a screenshot of the workflow function config. Often people think they are using a ScriptRunner function, when it fact it's from some other plugin.

[MikeS]

@Jamie Echlin [Adaptavist] You were right!  Because it was a scripting post function I made the huge blunder of assuming it was ScriptRunner, it was in fact "JIRA Misc Workflow Extension".  Thank you so much for the help and I'm sorry I wasted your time. 

[Jamie Echlin [Adaptavist]]

No worries, it's not the first time someone has made that mistake