ScriptRunner and JNDI database access

[Mario Prada Arroyo]

Hi!!

I m trying to use ScriptRunner to validate a custom field against a database.

In my groovy script, I don t like store the user and password , and use JNDI looks a better approach.

So I had defined a JNDI in my context.xml like this:

<Resource name="JIRA_SupportDB"
      auth="Container" type="javax.sql.DataSource"
      username="sqljira"
      password="*******"
      driverClassName="net.sourceforge.jtds.jdbc.Driver"
      url="jdbc:jtds:sqlserver://RICSQLPRE003/JIRA_Support;instance=INFOCAJA:1433"
 />

And in my script I have this sentences:

JndiTemplate template = new JndiTemplate();
DataSource ds = (DataSource)template.lookup("JIRA_SupportDB");

But when I execute the validator, a javax.naming.NameNotFoundException raises

I'm doing something wrong? is posible to use JNDI in Script Runner?

Thanks in advance

Mario Prada

[Henning Tietgens · Accepted solution]

The Resource definition looks good for me, the name must be preceded with "java:comp/env/" in your script, so "java:comp/env/JIRA_SupportDB" should do the trick.

I use a resource in the following way.

try {
	// Get Context to connect to database as a datasource
	Context ctx = new InitialContext()
	if (ctx == null) {
		throw new Exception("No Context found!")
	}
	DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MYDB")
	if (ds) {
		sql = new Sql(ds)
...

In this case the name in the Resorce is "jdbc/MYDB". Be aware of that the <Resource> part must be inside of the <context> in the context.xml

Henning

[Mario Prada Arroyo]

Hi Henning!!

that s right, I look like a newbie

Thanks for your time :-)

[JamieA]

Don't forget to close your connections in a finally{} block...

[Robin Knowlton]

What import was required to resolve the Context class?

When I tried the above code in my groovy script, I got the following error:

Script11.groovy: 96: unable to resolve class Context

@ line 96, column 14.

Context ctx = new InitialContext()

[Henning Tietgens]

import javax.naming.Context

[Robin Knowlton]

Thank-you very much !

[Jeff Louwerse]

I am attempting to upgrade from JIRA 5 (don't ask)  and am hitting this issue with scripts that have run for years.  Does this still work in the console either as a script or a file on scriptrunner 3.0.16 and JIRA 6.3.15

my scripts still run as services but if I try to run the files in the console I get 

Cannot instantiate class: org.apache.naming.java.javaURLContextFactory

even this simply block won't run.

import javax.naming.InitialContext
import groovy.sql.Sql
def ds = (new InitialContext()).lookup("java:/comp/env/jdbc/JIRAAUX")
def sql = new Sql(ds)

but as I mentioned, put this into a script and run it as a service.. works fine.

I really would prefer to use this method over defining the connection as this allows me to copy files directly from development to production without worrying about changing the connection details.

[Henning Tietgens]

I think this is the same problem as described here: https://jamieechlin.atlassian.net/browse/GRV-544

[Jeff Louwerse]

Thanks Henning. With a little more googling I found this link https://jamieechlin.atlassian.net/wiki/display/GRV/Connecting+to+Databases which lead me down the path to creating an abstract class for creating the connections and for now I am loading that in each script instead of the JNDI. Just wondering everyone else is doing this?