Display contents of Oracle table into HTML format mail using tSendmail

Five Stars

Display contents of Oracle table into HTML format mail using tSendmail

Hi,

 

I have been trying with the below approach but not getting the data in proper email format . Could you please help !!

 

Design.JPG

 

tOracleDBInput:

SELECT
'<tr><td>'||TABLE_NAME.KEY_NUMBER||'</td>'
||'<td>'||TABLE_NAME.TBL_NAME||'</td>'
||'<td>'||TABLE_NAME.SCHEMA_1_COUNT||'</td>'
||'<td>'||TABLE_NAME.SCHEMA_2_COUNT||'</td>'
||'<td>'||TABLE_NAME.COUNT_DIFFERENCE||'</td>'
||'<td>'||TABLE_NAME.UPDATED_TIME||'</td></tr>'
FROM TABLE_NAME;

 

tJavaRow:

context.email = context.email + "<tr><td>" + input_row.KEY_NUMBER + "</td><td>" + input_row.TBL_NAME + "</td><td>" + input_row.SCHEMA_1_COUNT+ "</td><td>" + input_row.SHEMA_2_COUNT + "</td><td>" + input_row.COUNT_DIFFERENCE + "</td><td>" + input_row.UPDATED_TIME+"" + "</td></tr>" ;

 

tSendmail:

 

"<head><meta http-equiv=Content-Type content=text/html;
charset=iso-8859-15><style type=text/css>table,TD, TH{
white-space: nowrap;
border:1px solid CornflowerGreen ;
font-size:12px;
font-family: Arial;
}
th{
white-space:nowrap;
background-color:CornflowerGreen;
color:Black;
}
<style><head>Hi Team<p><table><th>KEY_NUMBER</th><th>
<TBL_NAME/th><th>SCHEMA_1_COUNT</th><th>SCHEMA_2_COUNT</th><th>COUNT_DIFFERENCE</th><th>UPDATED_TIME</th><tr>"+context.email+"</tr></table>"

 

I am getting only the Headers in the table. Could anyone guide me to get the table data as well.

 

Email Format:

HTML_Mail.JPG

Table Format

tABLE_fORMAT.JPG

I will have more than 50 rows in table and the count will vary. What would be the best possible solution to get the data in HTML format thru tSendmail ? 

Tags (1)
Ten Stars

Re: Display contents of Oracle table into HTML format mail using tSendmail

Im not entirely sure on the tSendmail component... it clearly reads the body as an entire string or it auto-escape the string ... so maybe escape quotes using multiple double quotes or slashes to force/trick it ... look at the code view in stead of design view to see whats going on (select the tSendMail componet first before changing the view) ?

Also Suggest use a global var and maybe a tJavaFlex where you set a global var in the 'post/after' section in the component: globalMap.put("tableBody", tableRows).
in the Main section you build up your rows in var tableRows.
in the tSendMail get it like : (String)globalMap.get("tableBody")
Five Stars

Re: Display contents of Oracle table into HTML format mail using tSendmail

Hi, you need to use tFlowtoIterate before tJava_row. For all record in input you need to iterate and write the current record into you context.message. i Hope this can help you.

What’s New for Talend Spring ’19

Join us live for a sneak peek!

Sign up now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch