Is it possible to add another sql driver in jira?

[RandyD]

This is my goal i need to update the external database whenever a ticket is raise in jira.
Event has been triggered then script will run to update a table in MS SQL server . Anyone know how to do it or to put the jdbc for sql server in JIRA files?

note: Our Jira is in Mysql.

[Alexey Matveev · Accepted solution]

What plugin do you use from quering your database? Usually you put additional libraries to the lib folder of you Jira.

[RandyD]

Hi alex,
I will use a groovy script runner.

import groovy.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

def sql = Sql.newInstance('jdbc:sqlserver://localhost:port;databaseName=dbName', 'username',
         'password', 'com.microsoft.jdbc.sqlserver.SQLServerDriver')
      sql.execute("INSERT INTO table(testcolumn) VALUES ('test value')");
      sql.close()


and here is the error:

2018-03-09 14:37:48,323 WARN [common.UserScriptEndpoint]: Script console script failed: java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver not found by com.onresolve.jira.groovy.groovyrunner [198] at org.apache.felix.framework.BundleWiringImpl.findClassOrResourceByDelegation(BundleWiringImpl.java:1532) at org.apache.felix.framework.BundleWiringImpl.access$400(BundleWiringImpl.java:75) at org.apache.felix.framework.BundleWiringImpl$BundleClassLoader.loadClass(BundleWiringImpl.java:1955) at Script21.run(Script21.groovy:7)


My external db is MS SQL SERVER 2008 R2

and i use sqljdbc64.jar i place it under JIRA_HOME/lib directory

what was wrong with this?

Thank you,
Randy

[Alexey Matveev]

You need to put your driver into the lib folder of you Jira and restart your Jira.

Kindly have a look at the page on how to query databases from ScriptRunner:

https://scriptrunner.adaptavist.com/5.3.7/jira/recipes/misc/connecting-to-databases.html

[RandyD]

i also tried that sample and it is still got the same error.
Im just curious where to put the driver is it in JIRA_HOME/lib? or maybe in different lib?

[Alexey Matveev]

As far as I understand you created the lib directory? I do not have access right now to Jira. But I think the lib folder is situated in the Jira_installation folder. It must be already available in your Jira instances. And there must be already drivers for some databases.

[RandyD]

No i didn't create a directory. sorry for the misunderstanding i just put the driver in the jira_installation lib directory

here is the driver where i download it:https://www.microsoft.com/en-us/download/details.aspx?id=56615

and the jar file in the extracted folder is the only file that i move to JIRA\lib

and here is my code for the scriptrunner sample

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

def driver = Class.forName('com.microsoft.jdbc.sqlserver.SQLServerDriver').newInstance() as Driver

def props = new Properties()
props.setProperty("user", "username")
props.setProperty("password", "password")

def conn = driver.connect("jdbc:sqlserver://localhost:port;databaseName=dbName", props)
def sql = new Sql(conn)

try {
    sql.eachRow("select * from table_name") {
        log.debug(it)
    }
} finally {
    sql.close()
    conn.close()
}


and i got this error:
2018-03-09 15:17:54,946 WARN [common.UserScriptEndpoint]: Script console script failed: java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver at Script33.run(Script33.groovy:4)


Thank you,
Randy

[Alexey Matveev]

Kindly read the following post

https://community.atlassian.com/t5/Jira-Core-questions/JIRA-Scriptrunner-Sql-Server-configuration/qaq-p/461274

The lib folder in

C:\Program Files\Atlassian\JIRA\lib directory

[RandyD]

I already read that link but didnt see the bottom page. haha thanks bro now i have a different error but i think i can handle it now because i already know that the driver is now working.

Error

The TCP/IP connection to the host prot/dbname, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".