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

[Stefan Niedermann]

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?

[Jamie Echlin · Accepted solution]

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

[Stefan Niedermann]

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

[Stefan Niedermann]

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.

[JamieA]

ah yeah, my bad.

[Mehmet Kazgan]

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

[Deleted user]

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.

[JamieA]

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.

[pelizza]

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?

[petry]

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,

[Stefan Niedermann]

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

[Jamie Echlin]

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