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 Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,203 views 13 19
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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot