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

This widget could not be displayed.

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

 

This widget could not be displayed.

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

 

 

This widget could not be displayed.

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
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

281 views 5 0
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