topic Re: how to create a HTML-Table in Email with input of Database query in Talend Studio

how to create a HTML-Table in Email with input of Database query

Anonymous — Mon, 28 May 2018 18:30:26 GMT

i have a query on a Database which give me Infomation like this:

i want to create a HTML Table with Information of the query above and send it to Email of Developer as Error Notification. Can you help me with the HTML code?. Thankyou alot!

Re: how to create a HTML-Table in Email with input of Database query

TRF — Mon, 28 May 2018 19:00:23 GMT

As a strating point you case copy/paste the following piece of Java code into a tJavaRow connected to to your data flow (for ex. tOracleInpput-->tJavaRow).

String msg = ((String)globalMap.getOrDefault("mailMessage", "<TABLE border=0 cellspacing=1px>" + "<TR><TD>ID</TD><TD>Error</TD><TD>Date</TD></TR>"));
msg = msg + "<TR><TD>" + row1.ID + "</TD><TD>" + row1.Error + "</TD><TD>" + row1.Date + "</TD></TR>"
globalMap.put("mailMessage", msg);

When the subjob is finish (onSubjobOK), a tSendMail will send the email using the content of the global variable "mailMessage" as as the message using the following syntax:

((String)globalMap.get("mailMessage"))

You can enrich the table with colors and so on.

Re: how to create a HTML-Table in Email with input of Database query

Anonymous — Tue, 29 May 2018 08:40:39 GMT

Thankyou very much, it worked for me.
Can you please show me how to format it like a table?with border line?

Re: how to create a HTML-Table in Email with input of Database query

TRF — Tue, 29 May 2018 08:49:25 GMT

Great!

For the rest, this is a HTML question, not a Talend one.

However, have a look at "TABLE border" parameter.

Re: how to create a HTML-Table in Email with input of Database query

Anonymous — Tue, 29 Jan 2019 08:24:53 GMT

Hi,
I Tried to to develop a job through talend using the below java code. But i face the below issues

Can you please help me to know the configuration of tJavaRow and tSendMail

Re: how to create a HTML-Table in Email with input of Database query

Anonymous — Tue, 07 Jan 2020 22:45:30 GMT

Hi @SwatiShashwati in the original post the second line in the code

msg = msg + "<TR><TD>" + row1.ID + "</TD><TD>" + row1.Error + "</TD><TD>" + row1.Date + "</TD></TR>"

is missing an ';' at the end. to complete the Java statement.

As an aside I'm wondering why @TRF you didn't use a tJavaFlex component so the this would then become something like this ( note not tested directly )

Start Code

String msg = ((String)globalMap.getOrDefault("mailMessage", "<TABLE border=0 cellspacing=1px>" + "<TR><TD>ID</TD><TD>Error</TD><TD>Date</TD></TR>"));

Main Code

msg = msg + "<TR><TD>" + row1.ID + "</TD><TD>" + row1.Error + "</TD><TD>" + row1.Date + "</TD></TR>";

(note additional ';' at end )

End Code

globalMap.put("mailMessage", msg);

Re: how to create a HTML-Table in Email with input of Database query

Anonymous — Tue, 21 Apr 2020 11:47:05 GMT

Hi Sir,

Can you help me to reset or clear the table for every iterate?

tDBInput row1 (main) -> tFlowIterate -> tDBInput row2 (main) -> tJavaRow1 - >tsendMail

My problem is the datatable in email are embedded from the previous data.

Itry to put msg =""; in javarow code but I get the same output.

Thank you

String msg = "";
msg =((String)globalMap.getOrDefault("mailMessage", "<TABLE border=1 cellspacing=1px>" + "<TR><TD>EMPLOYEE_NO</TD><TD>EMPLOYEE_NAME</TD><TD>EVENT</TD><TD>DATE</TD><TD>TIME</TD><TD>VOID_LINK</TD></TR>"));
msg = msg + "<TR><TD>" + row1.EMPLOYEE_NUMBER + "</TD><TD>" + row1.EMPLOYEE_NAME + "</TD><TD>" + row1.EVENT + "</TD><TD>" + row1.DATE_TRANS + "</TD><TD>" + row1.TIME + "</TD><TD>" + row1.VOIDLINK + "</TD></TR>";
globalMap.put("mailMessage", msg);

flow.PNG

Re: how to create a HTML-Table in Email with input of Database query

Anonymous — Wed, 20 May 2020 13:59:20 GMT

Because your setting the value into the globalMap - it will persist across iterations. If you don't want this you might have

1) use the index of the iteration in the name of the field ( i.e. "mailMessage_" + index ) or

2) store an arraylist in the globalMap, and use the index of iteration to retrieve the correct value for the current iteration. This will depend on how many iterations you are running - maybe bad idea if doing lots. or

3) issue here is that you probably should just construct your string in tJavaRow1 and just save it to => globalMap.put("mailMessage", msg) i.e. don't do a globalMap.getOrDefault as this will return the value created via the first iteration for every subsequent iteration. You don't need it. just construct new string and globalMap.put it - every iteration will overwrite the previous.

Hope this helps.....