Connecting to External MS SQL Database from JIRA using Groovy SQL Edited

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 :/

3 answers

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()
   }
}

Screen Shot 2017-05-10 at 3.40.27 PM.png

 

Hope this gets you started.

 

Cheers

Chander

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:

 2017-05-11_09h49_45.png

 

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()
}

 

 

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

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Oct 16, 2018 in Jira

Looking for anyone who made the switch to Data Center

The Jira Marketing team is putting together an ebook on migrating to Data Center. We're looking for pro tips on how you staffed your project team and organized your Proof of Concept. Share yo...

64 views 0 2
Join discussion

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