Connecting to External MS SQL Database from JIRA using Groovy SQL

[Jan Sękara]

Hi,

I'm trying to display  information on issue, based on customfield value.

Example:

Customfield XXXX has value "4"

I want to select external database table (Columns: X,Y)for a row(s) Y when row X = 4

I tried to use Database Custom Field 4.0 but failed (no such possibility)

So...maybe by groovy?

But how do I connect to external database (SQL) assuming that I already have drivers (DCF 4.0 works with selecting but i can see data only while editing).

Can anyone help with this issue?

I've found some information on the web, but nothing worked yet :/

[Chander Inguva]

Hey Jan,

Not sure, how much this helps, but i do have a sql query that gives me custom field values on a jira issue

select cfv.STRINGVALUE,ji.issuenum,p.pkey from customfieldvalue cfv
JOIN customfield cf ON
cf.ID = cfv.CUSTOMFIELD
JOIN jiraissue ji ON cfv.ISSUE = ji.ID
JOIN project p ON ji.PROJECT = P.ID
WHERE cfv.ISSUE IN (select ji.ID from jiraissue ji where PROJECT = 14701) AND cfv.CUSTOMFIELD = 14611 AND cfv.STRINGVALUE IS NOT NULL ORDER BY ji.issuenum ASC

[Jan Sękara]

OK i have a script, but printing does not work properly. It sends a query to SQL but nothing is displayed.

Seccond thing, how to put value from customfield to SQL query?

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

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

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

def conn = driver.connect("jdbc:sqlserver://srv-erp.office.xxxxxxxxxxx.com:1433;DatabaseName=xxxxxxxxxxxxxxxxxxxxx", props)
def sql = new Sql(conn)

try {
sql.eachRow("SELECT * from table") {
log.debug(it)
}
println "Hello world!"

} finally {
sql.close()
conn.close()
}

 

[Chander Inguva]

Did you try this ?

import java.sql.*;
import groovy.sql.Sql
  
class Example {
   static void main(String[] args) {
      // Creating a connection to the database
      def sql = Sql.newInstance('jdbc:jtds:sqlserver://localhost:1433/atlstatus', 'sa',
         'xxxxxxxx', 'net.sourceforge.jtds.jdbc.Driver') 
             
      sql.eachRow('select * from Status') {
         tp ->
         println([tp.ID,tp.APPLICATION,tp.INCIDENTDATE])
      } 
         
      sql.close()
   }
}

Hope this gets you started.

Cheers

Chander

[Jan Sękara]

Chandler thanks, but.. .i did it like below and result is null...

import java.sql.*;
import groovy.sql.Sql
 
class Example {
 static void main(String[] args) {
 // Creating a connection to the database
 def sql = Sql.newInstance('jdbc:sqlserver://srv-erp.office.xxxxxxxx.com:1433', 'jira',
 'xxxxxx', 'com.microsoft.sqlserver.jdbc.SQLServerDriver') 
 
 sql.eachRow('SELECT * FROM KntKonta') {
 tp ->
 println(tp.KKT_OKRESID, tp.KKT_KKSNUMER)
 } 
 
 sql.close()
 }
}

 I get errors: