topic Display contents of Oracle table into HTML format mail using tSendmail in Talend Studio

Display contents of Oracle table into HTML format mail using tSendmail

Anonymous — Fri, 07 Sep 2018 05:20:38 GMT

Hi,

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

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:

Table Format

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 ?

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

Jesperrekuh — Mon, 10 Sep 2018 13:57:25 GMT

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")

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

Anonymous — Mon, 14 Jan 2019 13:51:09 GMT

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.