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

[Nishant Kansal]

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

Best regards,

Nishant

[Nic Brough -Adaptavist-]

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.

[Nishant Kansal]

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

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]

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

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

[Nishant Kansal]

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]

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

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]

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]

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]

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

starting working on 2nd part...