Script Runner and SQL

[Eitan Yomtovian]

Hi,

I try to write a script runner (in post function), that assigns automatically in the sub-task by the value in the component (in the "source issue).

The value of the assignee (in the sub-task) needs to return from a SQL query.

import com.atlassian.jira.component.ComponentAccessor

import com.atlassian.jira.event.issue.link.IssueLinkCreatedEvent

import com.atlassian.jira.event.issue.link.IssueLinkDeletedEvent

import com.atlassian.jira.issue.link.IssueLink

import com.atlassian.jira.issue.changehistory.ChangeHistory

import com.atlassian.jira.issue.history.ChangeItemBean

import com.atlassian.jira.event.type.EventDispatchOption

import com.atlassian.jira.issue.Issue

import com.onresolve.scriptrunner.runner.customisers.PluginModule

import com.onresolve.scriptrunner.runner.customisers.WithPlugin

import groovyx.net.http.HTTPBuilder

import groovy.json.JsonSlurper

import groovy.transform.Field

import com.atlassian.jira.component.ComponentAccessor

import groovy.sql.Sql

import org.ofbiz.core.entity.ConnectionFactory

import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

import groovy.sql.Sql

import java.sql.Connection

import com.atlassian.jira.component.ComponentAccessor

import com.atlassian.jira.user.ApplicationUser

def fieldManager = ComponentAccessor.getFieldManager()

def componentfieldBug =sourceIssue.components.toString()

//def componentfieldBug = customFieldManager.getCustomFieldObjects(sourceIssue).find {it.name == 'Component/s'}

def sqlStatement =  """

                    SELECT VALUE

                    FROM [JDCPreProd].[dbo].[AO_E8E966_PROP_VALUE] as prop_value,[JDCPreProd].[dbo].[component] as component

                    WHERE prop_value.REF_ITEM_ID  = component.ID AND component.ID = ${componentfieldBug[1]}

                    """

Connection conn = ConnectionFactory.getConnection(helperName)

    Sql sql = new Sql(conn)

    try {

        sql.eachRow((GString)sqlStmt) { row->

        ApplicationUser user = ComponentAccessor.getUserManager().getUserByName(row.VALUE)

        issue.setAssignee(user)

        }

    }

    finally {

        sql.close()

        conn.close()

    }

Someone can overview on the code and tell me what I did wrong?

Please advise,

Eitan.

[Ismael Jimoh]

Hi Eitan

May I ask why you are checking the source issues assigned components via the database rather than the Java api?

Also looking at your query, may I ask why you are parsing issue.setAssignee within your database connection?

I will expect that you store the data from the result of your query in an array or hash set and then iterate over this in a subsequent method that you use for setting your assignee. (In other words, issue.setassignee() should come after your connection close for the database.)

Regards.

[Nic Brough -Adaptavist-]

My very grumpy answer to this is:

>Someone can overview on the code and tell me what I did wrong?

Used SQL.

It's not a friendly answer, for which I apologise, but it is accurate.  Why are you using SQL when the API can serve you faster and better?  And the component/s field is not even kept in AO tables?