create jql function which executes sql and puts results to the output

hi,

i want to create a jql function which executes a sql-statement and puts anything on the result-list that is delivered by sql.

if i try this:

import com.atlassian.jira.ComponentManager
import groovy.sql.Sql
import java.sql.Connection
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface
 
ComponentManager componentManager = ComponentManager.getInstance()
delegator = (DelegatorInterface) componentManager.getComponentInstanceOfType(DelegatorInterface.class)
String helperName = delegator.getGroupHelperName("default");
 
def sqlStmt = "select * from jirauser1.jiraissue where pkey = 'PEP-11'"
 
Connection conn = ConnectionFactory.getConnection(helperName);
Sql sql = new Sql(conn)
StringBuffer sb = new StringBuffer()
sql.eachRow(sqlStmt) {
    sb << "${it.pname}\t${it.Kount}\n"
}
sb.toString()

and test, i get this error msg:

No signature of method: script1365748680401342839937.setFunctionName() is applicable for argument types: (java.lang.String) values: [test] A stacktrace has been logged.

any idea what i'm doing wrong?

3 answers

1 accepted

This widget could not be displayed.

Here is an example of what I think you're trying to do, which is some sql query to get the list of issues to be shown in the issue navigator:

// package com.onresolve.jira.groovy.jql

import com.atlassian.jira.ComponentManager
import com.atlassian.jira.issue.search.filters.IssueIdFilter
import com.atlassian.jira.jql.query.QueryCreationContext
import com.atlassian.query.clause.TerminalClause
import com.atlassian.query.operand.FunctionOperand
import groovy.sql.Sql
import org.apache.lucene.search.ConstantScoreQuery
import org.apache.lucene.search.Query
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface
import java.sql.Connection

class SampleDbFunction extends AbstractScriptedJqlFunction{

    Query getQuery(QueryCreationContext queryCreationContext, FunctionOperand operand, TerminalClause terminalClause) {

        ComponentManager componentManager = ComponentManager.getInstance()
        def delegator = (DelegatorInterface) componentManager.getComponentInstanceOfType(DelegatorInterface.class)
        String helperName = delegator.getGroupHelperName("default");

        def sqlStmt = "select * from jiraissue where pkey = 'VPP-35'"
        Set issueIds = new HashSet()

        Connection conn = ConnectionFactory.getConnection(helperName);
        Sql sql = new Sql(conn)
        try {
            StringBuffer sb = new StringBuffer()
            sql.eachRow(sqlStmt) {
                log.debug(it)
                issueIds << it.id.toString()
            }
            sb.toString()
        } finally {
            sql.close()
        }

        new ConstantScoreQuery(new IssueIdFilter(issueIds))
    }
}

aah - got it (package was commented out). thank you very much!

Hi,

yes, this is what i need. But where comes AbstractScriptedJqlFunction from? where do you import it?

startup failed: script1366003612543377374778.groovy: 13: unable to resolve class AbstractScriptedJqlFunction @ line 13, column 1. class SampleDbFunction extends AbstractScriptedJqlFunction{ ^ 1 error A stacktrace has been logged.

ah yeah, my bad.

Jamie,

I am trying to do this with Sprint End Date but getting Invalid Column name id.

ANy ideas?

package com.onresolve.jira.groovy.jql
import com.atlassian.jira.ComponentManager
import com.atlassian.jira.issue.search.filters.IssueIdFilter
import com.atlassian.jira.jql.query.QueryCreationContext
import com.atlassian.query.clause.TerminalClause
import com.atlassian.query.operand.FunctionOperand
import groovy.sql.Sql
import org.apache.lucene.search.ConstantScoreQuery
import org.apache.lucene.search.Query
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface
import java.sql.Connection
class SampleDbFunction extends AbstractScriptedJqlFunction{
 
    Query getQuery(QueryCreationContext queryCreationContext, FunctionOperand operand, TerminalClause terminalClause) {
 
        ComponentManager componentManager = ComponentManager.getInstance()
        def delegator = (DelegatorInterface) componentManager.getComponentInstanceOfType(DelegatorInterface.class)
        String helperName = delegator.getGroupHelperName("default");
 
        def sqlStmt = "select AO_60DB71_SPRINT.END_DATE from AO_60DB71_SPRINT"
        Set issueIds = new HashSet()
 
        Connection conn = ConnectionFactory.getConnection(helperName);
        Sql sql = new Sql(conn)
        try {
            StringBuffer sb = new StringBuffer()
            sql.eachRow(sqlStmt) {
                log.debug(it)
                issueIds << it.id.toString()
            }
            sb.toString()
        } finally {
            sql.close()
        }
 
        new ConstantScoreQuery(new IssueIdFilter(issueIds))
    }
}

Hi,

since a JQL-Query is returning issues, i think you will have change your SQL-Query. You will have to select at least the issue id column., because in the try-catch the issues which should be returned are discovered by the ID-Column.

Yeah.. you need to join on the jiraissue table. You can only show issues in the issue navigator, not arbitrary items from the database. Perhaps you need a report, or a dashboard gadget.

Hello guys,

Great solution! Just one question:

In this line:

String helperName = delegator.getGroupHelperName("default");

Where does the "default" argument come from? Is it safe to use or it depends on some JIRA configuration?

This widget could not be displayed.

You need to implement all the methods... start with one of the samples. I'll try to give an example later.

This widget could not be displayed.

Hey Stefan,

I'm not sure why you need this JQL, but as a side note, depending on what you are trying to achieve, this plugin might be helpful:

https://marketplace.atlassian.com/plugins/org.deblauwe.jira.plugin.database-values-plugin

Hope that helps.

Cheers,

hi, we're using db values already, but we need to get access via jql function - and not in a custom field.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Aug 22, 2018 in Marketplace Apps

How a Marketplace app tech team is achieving gender diversity

Hello! My name is Genevieve Blanch, and I'm the Marketing Manager at RefinedWiki, creators of apps to give teams the tools to customize Atlassian platforms. Currently, 44% of the tech team at Re...

487 views 3 18
Read article

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