Not able to connect to external Microsoft SQL Server using groovy sql

Hi,

I am new to groovy and OO programming. I have to develop a groovy script to connect to an external Microsoft sql server by executing a post function in JIRA. I am using script runner plugin for the execution of script.

After an exhaustive study on various posts related to groovy, I developed 2 scripts as below, however, none of then is working.

Script 1:

-----------------------------------

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

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
def sql = Sql.newInstance("jdbc:sqlserver://servername:1433/Valid_DB_Name", "Valid user", "valid password", "com.microsoft.sqlserver.jdbc.SQLServerDriver")

------------------------------------

Error: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

 

------------------------------------------------------------------------------------------------------------------------------------------

Script 2:

 

-----------------------------------

 

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

this.class.classLoader.rootLoader.addURL(new URL("file:///C:/TEMP/sqljdbc4.jar"))

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

def sql = Sql.newInstance("jdbc:sqlserver://servername:1433/Valid_DB_Name", "Valid user", "valid password", "com.microsoft.sqlserver.jdbc.SQLServerDriver")

 

------------------------------------

 

Error: java.lang.NullPointerException: Cannot invoke method addURL() on null object

 

------------------------------------------------------------------------------------------------------------------------------------------

 

I have tried various ways to fix it, however, no success. It seems, I am not able to set the classpath correctly here. I have declared it as an environment variable even (CLASSPATH=.;C:/TEMP/sqljdbc4.jar"). But, results is still the same.

Please help. I am stuck here for more than a week nowsad.

 

Best regards,

Nishant

2 answers

1 vote

The first error occurs because the database driver is not installed anywhere that the Tomcat running your JIRA can find it.

The second error is occurring because this.class.classLoader.rootLoader.addURL is not a valid method - by the time you get to the rootLoader part of it, you have a null.  The null may be further up, but the whole thing really depends on what the "this" object is.

 

Regarding the first error, "the database driver is not installed anywhere that the Tomcat running your JIRA can find it.". Could you please let me know, where shall I place the JDBC jar for sql server and how to call it in the script?

Best regards,

Nishant

Last time I did this, <tomcat>/lib worked.  For PostGreSQL.  Make sure you get the ownership and permissions on the file correct too (look at the files already in there, set it to the same)

I got the SQLjdbc jar placed under <jira-application-dir>/atlassian-jira/WEB-INF/lib/

and modified the script as below-

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

//check the database driver class

def driver = Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance() as Driver
def props = new Properties()

//database connection credentials

props.setProperty("database", "Valid DB")
props.setProperty("user", "Valid User")
props.setProperty("password", "Valid Password")

//The JDBC connection string
def conn = driver.connect("jdbc:sqlserver://ServerName:1433", props)
def sql = new Sql(conn)

----------------------------------------------------------

Error: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver  

Help Pls...


Best regards,

Nishant

Could you try putting it in the place that worked for me instead?

I didn't find location <tomcat>/lib. Instead, in my JIRA installation, I have following structure:

JIRA Installation-1.pngJIRA Installation-2.PNG


Please tell me, where shall i place the jar in this structure?

I guess it would be Atlassian/JIRA/lib according to what Nic said. Also did you restart JIRA after copying the file?

Tomcat is the application server within which JIRA runs.  So yes, Vijay is probably right, as a default installation will place the atlassian-jira directory immediately beneath the root of the Tomcat installation

Thanks Nic and Vijay. It seems, it is working now.

On executing the script, it is giving "groovy.sql.Sql@2232956e" as output.

Is it correct?

Another help required. After connecting to the database, I have to execute a simple SELECT statement like below and assign the outcome to a custom field in JIRA.

SELECT X FROM Y WHERE Z= 'A'

I tried to work it out with "sql.Execute", "sql.eachRow" and "sql.Rows" functions in groovy sql to get the value via above sql statement, However, all it is returning is "NULL".

Could you please guide me here? And, how to assign it to a custom field in JIRA?

I got first part working...fetching value from database. I used ".rows" function.

starting working on 2nd part...smile

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...

193 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