This article shows how to execute a SELECT statement, and send the result in an email body.
The SELECT Statement
We use the tutorial metadata in this example.
Here is the statement. We include a "WHERE" clause for limiting the number of rows returned.
SELECT CUS_FIRST_NAME, CUS_LAST_NAME
FROM %x{$T_CUSTOMER/tech:physicalPath()}x%
WHERE CUS_ID <= 5
The process
Our email will be composed of 3 sections: the salutation, the data, and the signature.
Our process writes the email content in a single file, and then sends the file content as an email.
Action WriteTxtFile: contains the Salutation + the "<pre>" HTML element opening tag :
Hi, Here is the query result : <pre>
Action SQLFileExport: executes the query and appends it to the file. Note the "SQL_EXP_FILE_APPEND" parameter, set to true.
Action "Copy 1 of WriteTxtFile": closes the "<pre>" element and appends the signature. Note the TXT_WRITE_APPEND" parameter, set to 'after'.
</pre>
End of data. Bye !
Action SendMail: We checked the "Html Mode" parameter, and provided the file name into the "MAIL_CONTENT_FILE" parameter. The encoding to be used to read this file can be set with the "Mail content file encoding" parameter.