# How to write re-usable function that can be used by multiple script JQL function's scripts **URL:** \ **Category:** Jira Server\ **Tags:** scriptrunner\ **Created:** [May 27, 2019, 8:38am UTC](https://community.developer.atlassian.com/t/how-to-write-re-usable-function-that-can-be-used-by-multiple-script-jql-functions-scripts/29513 "2019-05-27T08:38:29Z")\ **Posts on this page:** 1\ **Page:** 1
**Author:** ![nalluohoz](https://avatars.discourse-cdn.com/v4/letter/n/977dab/32.png) [@nalluohoz](https://community.developer.atlassian.com/u/nalluohoz)\ **Post date:** [May 27, 2019, 8:38am UTC](https://community.developer.atlassian.com/t/how-to-write-re-usable-function-that-can-be-used-by-multiple-script-jql-functions-scripts/29513/1 "2019-05-27T08:38:29Z")
I have written a common function to connect to DB. This function should be used by multiple script JQL functions. But the script function getting removed from JIRA applicationn when i try to access that common function which is resides at different script. Here is what I am doing, 1. _STEP 1:_ Written a class called ’ _DBUtils_ . Inside that class, defined function called executeQuery(). I have added this script under ’ _/var/atlassian/application-data/jira/scripts/com/onresolve/jira/groovy/jql_’ directory. ```auto import com.atlassian.jira.component.ComponentAccessor; 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; import org.apache.log4j.Logger; /** * This script is to connect to JIRA DB and execute the give query * @param sqlStmt query to be executed * @return results as list of HashMaps Ex: [{key, value}, {key, value}]. Whoever call this function, has to parse the output and fetch the required input based on the given query * * @since 27th March, 2019 * @version 1.0 */ class DBUtils { def executeQuery(String sqlStmt) { // Defining arrayList to store result returns by query. def results = []; Logger log = Logger.getLogger("Connecting to DB"); if(sqlStmt.split(' ')[0].toLowerCase() == 'select') { // Connecting to the database. def componentManager = ComponentManager.getInstance(); def delegator = (DelegatorInterface) componentManager.getComponentInstanceOfType(DelegatorInterface.class); String helperName = delegator.getGroupHelperName("default"); Connection conn = ConnectionFactory.getConnection(helperName); Sql sql = new Sql(conn); // Execute the given query try { results = sql.rows(sqlStmt) } catch (Exception e) { log.error("Getting error while Script JQL Function trying to connect to DB and execute the query : ${sqlStmt}" + e.getMessage() + "\n"); } finally { sql.close(); } } return results } } ``` **STEP 2:** Have written separate JQL function script and tried using **DBUtils** class’s function (executeQuery()) by creating a object (new DBUtils().executeQuery(query)). But This JQL function was removed after adding object creation step in the script. ```auto package com.onresolve.jira.groovy.jql; import com.atlassian.jira.bc.issue.search.SearchService; import com.atlassian.jira.component.ComponentAccessor; import com.atlassian.jira.ComponentManager; import com.atlassian.jira.jql.parser.JqlQueryParser; import com.atlassian.jira.jql.query.LuceneQueryBuilder; import com.atlassian.jira.jql.query.QueryCreationContext; import com.atlassian.jira.jql.validator.NumberOfArgumentsValidator; import com.atlassian.jira.user.ApplicationUser; import com.atlassian.jira.util.MessageSet; import com.atlassian.query.clause.TerminalClause; import com.atlassian.query.operand.FunctionOperand; import DBUtils; import groovy.util.logging.Log4j; /** * The ChangedFrom program is implemented to get the list of feature issues which had given value for the given custom field. * This program will enable history search for the custom fields on feature issue. * * @arguments It accepts two arguments. * Field Name : Name of the custom field * Changed from value : Value of the custom field * * @since 19th March, 2019 * @version 1.0 **/ @Log4j class ChangedFrom extends AbstractScriptedJqlFunction implements JqlQueryFunction { // Defining an objects to use JQL query parser def queryParser = ComponentAccessor.getComponent(JqlQueryParser); def luceneQueryBuilder = ComponentAccessor.getComponent(LuceneQueryBuilder); def searchService = ComponentAccessor.getComponent(SearchService); def applicationUser = ComponentAccessor.getJiraAuthenticationContext().getUser(); /** * This method is to get the description of the script JQL function. * This description will be displayed when we search for the ChangedFrom() function in issue navigator in jira application. **/ @Override String getDescription() { "Return feature issues which had the given value for the given custom field" } /** * validate function is to validate the operand and JQL query which is formed based on operand. * @return It return the error message if operand is incorrect/null **/ @Override MessageSet validate(ApplicationUser user, FunctionOperand operand, TerminalClause terminalClause) { def messageSet = new NumberOfArgumentsValidator(2, getI18n()).validate(operand); if (messageSet.hasAnyErrors()) { return messageSet; } // Forming the JQL and validating it def field = '"' + operand.args.first() + '"'; def value = '"' + operand.args.last() + '"'; def queryStr = "${field} = ${value}"; def query = queryParser.parseQuery(queryStr); messageSet = searchService.validateQuery(user, query); messageSet } /** * getArguments() function is to define the description and property of the arguments. * @return argument property as a List **/ @Override List getArguments() { [ [ description: "Field Name", optional: false, ], [ description: "Changed From Value", optional: false, ] ] } // To define the function name for script JQL function. @Override String getFunctionName() { "changedFrom" } /** * getQuery function is to get the query context which is entered in issue navigator, perform defined logic/operation to get deseried outcome, * form outcome and return the JQL result * * @return list of feature issues in issue navigator in JIRA application **/ @Override Query getQuery(QueryCreationContext queryCreationContext, FunctionOperand operand, TerminalClause terminalClause) { //log.setLevel(org.apache.log4j.Level.DEBUG); //Uncomment this code to enable debugging logs // Grep custom field name and it's value from operand def fieldName = operand.args.first().toString(); def value = operand.args.last().toString(); //log.debug("Field Name = ${fieldName} - - - Value = ${value}"); //Uncomment this code to enable debugging logs // Defining arrayList to store feature keys. def featureLinkList = []; // SQL query to fetch value from db def sqlStmt = "select some query"; if(sqlStmt.split(' ')[0].toLowerCase() == 'select') { // Connecting to JIRA DB and execute given query def dbUtils = new DBUtils(); def temp = dbUtils.executeQuery(sqlStmt); temp.each { featureLinkList.add("${it.featurekey}"); } } //log.debug("SQL Query : ${sqlStmt}"); //Uncomment this code to enable debugging logs /** * Framing the Query(using the data fetched from DB) to fetch Feature issues through JQL * if the SQL doesn't return anything, then it will return 'No Issues found' */ if(featureLinkList) { def queryTemp = featureLinkList.unique().join(',').toString(); def secondQuery = "key in (" + queryTemp + ")"; def secondquery = queryParser.parseQuery(secondQuery); luceneQueryBuilder.createLuceneQuery(queryCreationContext, secondquery.whereClause); } else { def secondQuery = "key in (FEATURE-0)"; def secondquery = queryParser.parseQuery(secondQuery); luceneQueryBuilder.createLuceneQuery(queryCreationContext, secondquery.whereClause); } } }//End of the script; ``` What I am doing wrong? Please help me.