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

Nishant Kansal March 27, 2016

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
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 27, 2016

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.

 

0 votes
Nishant Kansal March 28, 2016

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

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 28, 2016

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)

Nishant Kansal March 28, 2016

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

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 28, 2016

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

Nishant Kansal March 29, 2016

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?

Vijay Khacharia
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.
March 29, 2016

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

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 29, 2016

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

Nishant Kansal March 29, 2016

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?

Nishant Kansal March 29, 2016

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?

Nishant Kansal March 29, 2016

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

Atlassian Community Events