nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Error sending email

Nagappan Murugappan

I have a script which is written for some reporting purpose, we store the data in JIRA database and while sending the email we fetch the data from the table and send it..

For example

def dataRecord= sql.rows("select userName, userEmail from jira.UserData where userId= ${UserId}")

String emailId= dataRecord.userEmail

I am able to fetch the required data from above.

String sendAddress = emailId+otherSendersEmailId

When i am trying to send the email with the above data, i get the below exception, i think the String return value with [] is the problem, but i am not sure how to solved this, could someone please help me ??

com.atlassian.mail.MailException: javax.mail.internet.AddressException: Local address contains illegal character in string ``[abc@gmail.com]''

Accepted answers

Ram Kumar Aravindakshan _Adaptavist_

@Nagappan Murugappan,

May I know what are you using to send the email? Is it the post-function or the listener?

If you could share your script, it would be helpful, and I can provide some feedback.

Thank you and Kind Regards,

Ram

Nagappan Murugappan

Hello Ram

Thanks for your detailed explanation, i am completely new to JIRA and Groovy, could you please explain me what is does the below statement means

output.each {
   result = it as Map<String,String>
}


?

Nagappan Murugappan

Thanks a lot for your help.

All comments

Ram Kumar Aravindakshan _Adaptavist_

@Nagappan Murugappan,

May I know what are you using to send the email? Is it the post-function or the listener?

If you could share your script, it would be helpful, and I can provide some feedback.

Thank you and Kind Regards,

Ram

Nagappan Murugappan

Hello Ram

please find the full scruipt

import groovy.sql.Sql
import java.sql.Driver
import com.atlassian.mail.Email
import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.web.bean.PagerFilter
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface
import java.sql.Connection
import java.util.Date
import java.time.*
import java.lang.String
import java.util.Calendar;
import java.text.SimpleDateFormat;
import java.text.ParseException;
import com.atlassian.jira.jql.parser.JqlQueryParser

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)
String helperName = delegator.getGroupHelperName("default")
def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def searchService = ComponentAccessor.getComponent(SearchService)
def issueManager = ComponentAccessor.getIssueManager()
def user = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser()

def query = jqlQueryParser.parseQuery("filter=99999")
def search = searchService.search(user, query, PagerFilter.getUnlimitedFilter())

if (search.results) {

search.results.each { documentIssue ->

def issue = issueManager.getIssueObject(documentIssue.id)
def emailSubject = "JIRA Ticket CHECK"
def emailBody = "Test CHECK ${search.results} "
String emailAddress = "abc@gmail.com"

Connection conn1 = ConnectionFactory.getConnection(helperName)
Sql sql2 = new Sql(conn1)
def sqlRecord = sql2.rows("select emaild1 from jira.emailListTable where username = 'xyz' ")

String fullEmailList = sqlRecord.emailid1 + emailAddress

def mailServer = ComponentAccessor.getMailServerManager().getDefaultSMTPMailServer()
if (mailServer) {

def email = new Email(fullEmailList)
email.setMimeType("text/html")
email.setFrom("xyz@zyz.com")
email.setSubject(emailSubject)
email.setBody(emailBody)
mailServer.send(email)

sql2.close()

} else {}
}

}

Exception :

2021-05-06 10:54:29,864 ERROR [common.UserScriptEndpoint]: Script console script failed: com.atlassian.mail.MailException: javax.mail.internet.AddressException: Missing ']' in string ``[abc@gmail.com'' at position 35 at com.atlassian.mail.server.impl.SMTPMailServerImpl.sendWithMessageId(SMTPMailServerImpl.java:222) at com.atlassian.mail.server.impl.SMTPMailServerImpl.send(SMTPMailServerImpl.java:174) at com.atlassian.mail.server.SMTPMailServer$send.call(Unknown Source) at Script3899$_run_closure1.doCall(Script3899.groovy:61) at Script3899.run(Script3899.groovy:34) Caused by: javax.mail.internet.AddressException: Missing ']' in string ``[abc@gmail.com'' at position 35 at com.atlassian.mail.MailUtils.parseAddresses(MailUtils.java:143) at com.atlassian.mail.server.impl.util.MessageCreator.updateMimeMessage(MessageCreator.java:41) at com.atlassian.mail.server.impl.SMTPMailServerImpl.sendWithMessageId(SMTPMailServerImpl.java:185) ... 4 more

Ram Kumar Aravindakshan _Adaptavist_

Hi @Nagappan Murugappan,

Thanks for sharing your code.

After reviewing it, below are my suggestions.

Instead of using the ConnectionFactory etc., to establish the DB Connection, you can use the ScriptRunner DatabaseUtil, i.e. com.onresolve.scriptrunner.DB.DatabaseUtil as shown below:

import com.onresolve.scriptrunner.db.DatabaseUtil

def output = []
def result = [:]

DatabaseUtil.withSql('local_db') { sql ->
    output = sql.rows("select email from email_list where username = ?", loggedInUser.name)
}

In the sample code above, I am using ScriptRunner's Resources to set up the DB Connection. You can configure it using the Local Database your Jira instance is using, as shown in the print screens below:-

and just set the connection pool name. The connection pool name I am using in the code is local_db, as shown below:-

Alternatively, if you want to connect to an alternative DB, you can use the Database Configuration option as shown below and specify your DB connection details:-

If you are using the rows query, i.e. sql.rows, you will first need to iterate the result and pass the value to a Map as shown below:-

def output = []
def result = [:]

DatabaseUtil.withSql('local_db') { sql ->
    output = sql.rows("select email from email_list where username = ?", loggedInUser.name)
}


output.each {
   result = it as Map<String,String>
}

If you directly pass the output to the Email object, you are actually passing a List instead of a String. This is why you are encountering the Error.

If you view the com.atlassian.jira.mail.Email class, you will notice that the constructor of the class is taking a single String parameter or multiple String parameters and not a List as shown below:-

Next, you will need to pass the value from the Map to the Email object using the values().findAll option as shown below:-

result.values().findAll { emailAddress ->
    def email = new Email(emailAddress.toString())
     .
     .
     .
}

Lastly, when you are triggering the Mail, you will need to use the contextClassLoader from the Thread to be able to deliver the mail as shown below:-

result.values().findAll { emailAddress ->
    def email = new Email(emailAddress.toString())
    email.setSubject(subject)
    email.setBody(body)
    email.setMimeType("text/html")
    def threadClassLoader = Thread.currentThread().contextClassLoader
    Thread.currentThread().contextClassLoader = mailServer.class.classLoader
    mailServer.send(email)
    Thread.currentThread().contextClassLoader = threadClassLoader
}

Below is a complete working sample code for your reference:-

import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.jql.parser.JqlQueryParser
import com.atlassian.jira.mail.Email
import com.atlassian.jira.web.bean.PagerFilter
import com.onresolve.scriptrunner.db.DatabaseUtil

def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def searchService = ComponentAccessor.getComponent(SearchService)
def loggedInUser = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def mailServerManager = ComponentAccessor.mailServerManager

def query = jqlQueryParser.parseQuery("filter=sample_query")
def search = searchService.search(loggedInUser, query, PagerFilter.unlimitedFilter)
def mailServer = mailServerManager.defaultSMTPMailServer

def output = []
def result = [:]

DatabaseUtil.withSql('local_db') { sql ->
    output = sql.rows("select email from email_list where username = ?", loggedInUser.name)
}


output.each {
   result = it as Map<String,String>
}

if(search.results) {
        search.results.each {
        def subject = "JIRA Ticket CHECK"
        def body = "Test CHECK ${it.key} ${it.summary} "

        result.values().findAll { emailAddress ->
            def email = new Email(emailAddress.toString())
            email.setSubject(subject)
            email.setBody(body)
            email.setMimeType("text/html")
            def threadClassLoader = Thread.currentThread().contextClassLoader
            Thread.currentThread().contextClassLoader = mailServer.class.classLoader
            mailServer.send(email)
            Thread.currentThread().contextClassLoader = threadClassLoader
}
    }
}

Please note, this example code is not 100% exact to your environment. Hence, you will need to make the required modifications.

I hope this helps to answer your question.

Thank you and Kind regards,

Ram

Ram Kumar Aravindakshan _Adaptavist_

Hi @Nagappan Murugappan,

Thanks for sharing your code.

After reviewing it, below are my suggestions.

Instead of using the ConnectionFactory etc., to establish the DB Connection, you can use the ScriptRunner DatabaseUtil, i.e. com.onresolve.scriptrunner.DB.DatabaseUtil as shown below:

import com.onresolve.scriptrunner.db.DatabaseUtil

def output = []
def result = [:]

DatabaseUtil.withSql('local_db') { sql ->
output = sql.rows("select email from email_list where username = ?", loggedInUser.name)
}

In the sample code above, I am using ScriptRunner's Resources to set up the DB Connection.

You can configure it using the Local Database your Jira instance is using ScriptRunner's Resources, as shown in the print screens below:-

and just set the connection pool name. The connection pool name I am using in the code is local_db, as shown below:-

Alternatively, if you want to connect to an alternative DB, you can use the Database Configuration option as shown below and specify your DB connection details:-

If you are using the rows query, i.e. sql.rows, you will first need to iterate the result and pass the value to a Map as shown below:-

def output = []
def result = [:]

DatabaseUtil.withSql('local_db') { sql ->
  output = sql.rows("select email from email_list where username = ?", loggedInUser.name)
}


output.each {
  result = it as Map<String,String>
}

If you directly pass the output to the Email object, you are passing a List instead of a String. This is why you are encountering the Error.

Next, you will need to pass the value from the Map to the Email object using the values().findAll option as shown below:-

result.values().findAll { emailAddress ->
  def email = new Email(emailAddress.toString())
  .
  .
  .
}

Lastly, when you are triggering the Mail, you will need to use the contextClassLoader from the Thread to be able to deliver the mail as shown below:-

result.values().findAll { emailAddress ->
  def email = new Email(emailAddress.toString())
  email.setSubject(subject)
  email.setBody(body)
  email.setMimeType("text/html")
  def threadClassLoader = Thread.currentThread().contextClassLoader
  Thread.currentThread().contextClassLoader = mailServer.class.classLoader
  mailServer.send(email)
  Thread.currentThread().contextClassLoader = threadClassLoader
}

Below is a complete working sample code for your reference:-

import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.jql.parser.JqlQueryParser
import com.atlassian.jira.mail.Email
import com.atlassian.jira.web.bean.PagerFilter
import com.onresolve.scriptrunner.db.DatabaseUtil

def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def searchService = ComponentAccessor.getComponent(SearchService)
def loggedInUser = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def mailServerManager = ComponentAccessor.mailServerManager

def query = jqlQueryParser.parseQuery("filter=sample_query")
def search = searchService.search(loggedInUser, query, PagerFilter.unlimitedFilter)
def mailServer = mailServerManager.defaultSMTPMailServer

def output = []
def result = [:]

DatabaseUtil.withSql('local_db') { sql ->
    output = sql.rows("select email from email_list where username = ?", loggedInUser.name)
}


output.each {
   result = it as Map<String,String>
}

if(search.results) {
        search.results.each {
        def subject = "JIRA Ticket CHECK"
        def body = "Test CHECK ${it.key} ${it.summary} "

        result.values().findAll { emailAddress ->
            def email = new Email(emailAddress.toString())
            email.setSubject(subject)
            email.setBody(body)
            email.setMimeType("text/html")
            def threadClassLoader = Thread.currentThread().contextClassLoader
            Thread.currentThread().contextClassLoader = mailServer.class.classLoader
            mailServer.send(email)
            Thread.currentThread().contextClassLoader = threadClassLoader
}
    }
}

Please note, this example code is not 100% exact to your environment. Hence, you will need to make the required modifications.

I hope this helps to answer your question.

Thank you and Kind regards,

Ram

Nagappan Murugappan

Hello Ram

Thanks for your detailed explanation, i am completely new to JIRA and Groovy, could you please explain me what is does the below statement means

output.each {
   result = it as Map<String,String>
}


?

Nagappan Murugappan

Thanks a lot for your help.

Ram Kumar Aravindakshan _Adaptavist_

Hi @Nagappan Murugappan

When the DB is first queried, the query's output is returned in a List<Map>, i.e. List of Maps format.

DatabaseUtil.withSql('local_db') { sql ->
    output = sql.rows("select email from email_list where username = ?", loggedInUser.name)
}

And to extract the value from it, you will need to Iterate the List and the Map.

The statement below is iterating the List, i.e. output and passing the value of that List to a Map, i.e. result.

output.each {
   result = it as Map<String,String>
}

Once the value is passed to the Map, it is iterated again here:-

if(search.results) {
        search.results.each {
        def subject = "JIRA Ticket CHECK"
        def body = "Test CHECK ${it.key} ${it.summary} "

        result.values().findAll { emailAddress ->
            def email = new Email(emailAddress.toString())
            email.setSubject(subject)
            email.setBody(body)
            email.setMimeType("text/html")
            def threadClassLoader = Thread.currentThread().contextClassLoader
            Thread.currentThread().contextClassLoader = mailServer.class.classLoader
            mailServer.send(email)
            Thread.currentThread().contextClassLoader = threadClassLoader
}
    }
}