Email notification - Process

[MRPR]

Hi All

 

I'm writing a process to get an email with details from a query. I've structured my query like this: 

- Select query (multiple row data)

- Email tag start

- Pass in the select query data (using for each to display multiple lines)

- Close the email tag

But i can't get the email format correct. Can you please advise on how can i read in multiple row data in single email? 

 

Thanks in advance. 

 

Monica

[Eugene Greiff]

You are most likely going to need to process your results set into a string for the body of the email.  Here is a simple example to list each record and the column values.

 

 

<?xml version="1.0" encoding="utf-8"?>
<gel:script xmlns:core="jelly:core"
 xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
 xmlns:sql="jelly:sql"
 xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

     <core:new className="java.lang.StringBuilder" var="stringBuilder" />

     <sql:setDataSource url="jdbc:sqlserver://localhost;databaseName=niku;" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" user="sa" password="password" var="clarityDS" />
     <sql:query dataSource="${clarityDS}" var="queryOne" sql="select id, user_name, EMAIL_ADDRESS from niku.CMN_SEC_USERS" />

     <core:forEach trim="true" items="${queryOne.rowsByIndex}" var="row">
          <core:invoke method="append" on="${stringBuilder}">
               <core:arg type="java.lang.Object" value="&#xA;"/>
          </core:invoke>
          <core:forEach var="columnName" items="${queryOne.getColumnNames()}" indexVar="i">
               <core:invoke method="append" on="${stringBuilder}">
                    <core:arg type="java.lang.Object" value="${columnName} = ${row[i]}&#xA;"/>
               </core:invoke>
          </core:forEach>
     </core:forEach>

     <core:set var="body" value="&#xA;${stringBuilder.toString()}" />
     <gel:log>${body}</gel:log>

</gel:script>

 

The body tag contains:

 

 

V/r,

Gene

[MRPR]

Hi gcubed , thank you for the response. This helps me to append the text data, but the new line character does not seems to be working. I get the data returned as concatenated text, but not new line separated. I tried with tab space too, neither does that work. Any other recommendation on it? 

[Eugene Greiff]

If you are sending HTML email, try <br/> for line feed.  For plain text message try the java.lang.System.lineSeparator method into a var and append it to the stringbuilder.

 

V/r,

Gene

[MRPR]

hi gcubed, the stringBuilder itself worked good for line space when enclosed within <pre> tabs in <gel:email>. If not used, there is no line space is showing up in the email content.