topic Re: Netezza Result set on Message body of tSendMail in Talend Studio

Netezza Result set on Message body of tSendMail

_AnonymousUser — Thu, 06 Dec 2012 04:07:31 GMT

Hi Team-
I have to send my Audit info through email. Information is stored in Netezza table. How can I use result set of quey like select * from Table_name; in my message body.
Is this done by tFlowiterate, if so what would be the syntax for that?
I tried another option to write in excel file and attached in the mail. but can we modify excel font or write in PDF.
Hope a favorable and early response.
Regrads,
Siegfried

Re: Netezza Result set on Message body of tSendMail

Anonymous — Fri, 07 Dec 2012 06:09:29 GMT

Hi
You can write the result set to an excel file or a PDF file, then attach the file in the email, the job looks like:
tNetezzaInput--main--tFileOutputExcel ( or tFileOutputPDF)
|
onsubjobok
|
tSendMail
Note: tFileOutputPDF is custom component, shared by community users on Talend Exchange, you need to download and install it into Talend Studio before using it.
or write the result set in the body message of tSendMail, the job as seen below:
tNetezzaInput--main(row1)--tJavaFlex
|
onsubjobok
|
tSendMail
in the begin part of tJavaFlex, define a string, eg:
String result_set="";
in the main part of tJavaFlex,
result_set=result_set+row1.column1+row1.column2+"\n";
note: if you have more columns, append all the columns you want to send in the email.
in the end part of tJavaFlex, put the result_set to a global variable, eg:
globalMap.put("result",result_set);

in the body of tSendMail, write the body message as below, for example:
"Hello, this is the result set queried from Netezza table, see below: "+"\n"+(String)globalMap.get("result")

Shong

Re: Netezza Result set on Message body of tSendMail

Anonymous — Wed, 08 May 2013 15:52:29 GMT

I'm new to Talend but am also having a slow day which isn't helping...
I've set this up with a query against my oracle DB using tOracleInput, I've connected that to a tJavaFlex and added the code as you described but get the error "row1 cannot be resolved to a variable" - I've attached a screen show of the job and settings.
My query in the tOracleInput component is
select trunc(day_id)day_id, sum(trans_count)count
from xxxxx
where day_id > sysdate-5
group by trunc(day_id)
so I would have expected that I'd need to change row1.column1 to reflect my data but I can't seem to crack the correct combination for this - please can you advise....

Re: Netezza Result set on Message body of tSendMail

Anonymous — Wed, 08 May 2013 21:01:18 GMT

you will need to use the Talend schema names-- not the database names. if you click the "Edit schema ..." button in the tJavaFlex, you should see the current talend schema columns for that component-- your reference to "row1.columnName" will need to match one of those exactly.

Re: Netezza Result set on Message body of tSendMail

Anonymous — Thu, 09 May 2013 09:15:30 GMT

Thanks for your reply JohnGarrettMartin - Your right in that i hadn't bought the columns through in the schema, I've done that now but I still can't get it and its really frustrating me as I know it must be straightforward.
My columns are day_id and count - as far as i can see the schema name is the same as this (I've attached a screenshot in case that helps) - so what would the syntax look like in the 'Main code' box? I've tried the following
result_set=result_set+row1.columnName+row1.columnName+"\n";
result_set=result_set+row1.day_id+row1.count+"\n";
result_set=result_set+day_id+count+"\n";
The error is that the field cannot be resolved into a variable. Thanks!

Re: Netezza Result set on Message body of tSendMail

Anonymous — Thu, 09 May 2013 13:09:04 GMT

getting closer
make sure that the row connected to the tJavaFlex is named "row1" you will need to use whatever it is named exactly.
another potential issue is that you might not have declared the "result_set" variable-- I might suggest you create a context variable for this as you can then re-use it outside the scope of the tJavaFlex-- like in the tSendMail
then you would just use:
context.result_set += row1.day_id+row1.count+"\n"
edit:
If you are not aware of the "Code" tab (at the bottom of the canvas)-- clicking it will generate the code and show it to you-- it will highlight problems in red along the scroll bar-- this can help zero in on any syntax problems you may have.

Re: Netezza Result set on Message body of tSendMail

Anonymous — Thu, 09 May 2013 16:12:35 GMT

Thanks John that's working now. A combination of having the correct row identifier (e.g. row2) and the case of the column names.
appreciate all your help
Malcolm.