Result of sql query in mail body

sarnusch · Post by **sarnusch** » Wed Oct 09, 2024 9:23 am

Hello,
I am searching for a method to send sql query results via mail to a user. In my example I want to inform a user about new products.
What I need is that the fieldnames and filedvalues are displayed in the mail body as a table.
At the moment I use 'Dataset Loop' and create a CSV file that is attached to the mail.
But in my opinion it is too much effort for a few lines that also can be displayed directly in the mail body.
With other actions like 'SQL Query to CSV file' I can just get the field values and not get the field names.
Does a functionality like this exist?

Post by **Oleg** » Wed Oct 09, 2024 7:49 pm

What I need is that the fieldnames and filedvalues are displayed in the mail body as a table.

You need to create some HTML text from your data and send this text as HTML email
look at my example
My test table contains 4 fields. So the table has 4 columns

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|444033236
Comment=STRINGLIST
ContinueOnError=INTEGER|0
DisableOnError=INTEGER|0
DoNotStopWhenShutdown=INTEGER|0
ExternalName=STRING|"Task1474"
Hide=INTEGER|0
ID=INTEGER|-1526922036
LogOnAsUser=INTEGER|1
Name=STRING|"Send SQL data as table"
OnErrorTaskID=INTEGER|0
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
StepPause=INTEGER|0
ToLog=INTEGER|3
UnicodeFormat=INTEGER|1
WriteGeneralLog=INTEGER|0

[Actions]
Action1=FOLDER
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
Action8=FOLDER

[Actions\Action1]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""filename"" with value ""{TempDir}\table.html"""
Params=FOLDER

[Actions\Action1\Params]
_rt_variables_produced=STRING|"filename"
expand=STRING|"0"
linecount=STRING|"1"
varname=STRING|"filename"
varvalue=STRING|"{TempDir}\table.html"

[Actions\Action2]
ActionID=STRING|"A_FILE_TEXTWRITE"
Enabled=INTEGER|-1
Name=STRING|"Create text file {filename}"
Params=FOLDER

[Actions\Action2\Params]
encode=STRING|"0"
fileexists=STRING|"0"
filname=STRING|"{filename}"
line0=STRING|"<html>"
line1=STRING|"<body>"
line2=STRING|"<table style=""width: 100%"">"
line3=STRING|"<tr>"
line4=STRING|"<td style=""width: 25%"">Field 1</td>"
line5=STRING|"<td style=""width: 25%"">Field 2</td>"
line6=STRING|"<td style=""width: 25%"">Field 3</td>"
line7=STRING|"<td style=""width: 25%"">Field 4</td>"
line8=STRING|"</tr>"
linecount=STRING|"9"
suppress=STRING|"0"
withoutprefix=STRING|"1"

[Actions\Action3]
ActionID=STRING|"A_LOOP_DATASET"
Enabled=INTEGER|-1
Name=STRING|"Dataset Loop"
Params=FOLDER

[Actions\Action3\Params]
_rt_variables_produced=STRING|"data,fld"
commatext=STRING|"0"
connectionstring=STRING|"Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DATABASE=testdata;DB=testdata;DSN=testdata;NO_CATALOG=;NO_SCHEMA=1;PORT=3306;PREFETCH=;SERVER=127.0.0.1;"""
override=STRING|"1"
paramcount=STRING|"0"
password=STRING|"3516836025370673578335260"
sql=STRING|"""select * from datatable  where <some_condition>"""
timeout=STRING|"60"
userid=STRING|"username"
var1=STRING|"fld"
var2=STRING|"data"
withmemo=STRING|"0"

[Actions\Action4]
ActionID=STRING|"A_FILE_TEXTWRITE"
Enabled=INTEGER|-1
Name=STRING|"Create text file {filename}"
Params=FOLDER

[Actions\Action4\Params]
encode=STRING|"0"
fileexists=STRING|"1"
filname=STRING|"{filename}"
line0=STRING|"<tr>"
line1=STRING|"<td style=""width: 25%"">{data(0)}</td>"
line2=STRING|"<td style=""width: 25%"">{data(1)}</td>"
line3=STRING|"<td style=""width: 25%"">{data(2)}</td>"
line4=STRING|"<td style=""width: 25%"">{data(3)}</td>"
line5=STRING|"</tr>"
linecount=STRING|"6"
suppress=STRING|"0"
withoutprefix=STRING|"1"

[Actions\Action5]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"

[Actions\Action6]
ActionID=STRING|"A_FILE_TEXTWRITE"
Enabled=INTEGER|-1
Name=STRING|"Create text file {filename}"
Params=FOLDER

[Actions\Action6\Params]
encode=STRING|"0"
fileexists=STRING|"1"
filname=STRING|"{filename}"
line0=STRING|"</table style=""width: 100%"">"
line1=STRING|"</body>"
line2=STRING|"</html>"
linecount=STRING|"3"
suppress=STRING|"0"
withoutprefix=STRING|"1"

[Actions\Action7]
ActionID=STRING|"A_GENERAL_RUN_PROG"
Enabled=INTEGER|-1
Name=STRING|"Run ""{filename} """
Params=FOLDER

[Actions\Action7\Params]
ifnonzero=STRING|"0"
program=STRING|"{filename}"
runas=STRING|"0"
wait=STRING|"0"

[Actions\Action8]
ActionID=STRING|"A_INET_SENDMAIL"
Enabled=INTEGER|0
Name=STRING|"SMTP Send Email"
Params=FOLDER

[Actions\Action8\Params]
attachcount=STRING|"0"
auth=STRING|"0"
charset=STRING|"utf-8"
from=STRING|"user@host.com"
host=STRING|"Myhost.com"
html=STRING|"0"
msg0=STRING|"{TextFile({filename})}"
msgcount=STRING|"1"
password=STRING|"2488925201249412596972562"
port=STRING|"25"
priority=STRING|"1"
receipt=STRING|"0"
SSL=STRING|"2"
subject=STRING|"New data"
tlsmode=STRING|"0"
tlsver=STRING|"2"
to=STRING|"target@host.com"
xmailer=STRING|"RoboTask"

But I think that sending CSV file is not bad Idea

With other actions like 'SQL Query to CSV file' I can just get the field values and not get the field names.

use the option "Use Field Names" (see screenshot)

: SQL Query to CSV File.png (23.61 KiB) Viewed 34443 times

sarnusch · Post by **sarnusch** » Thu Oct 10, 2024 7:28 am

Good morning,
thanks for your quick reply and the great help.
I just tested it and it works fine.

RoboTask forum

Result of sql query in mail body

Result of sql query in mail body

Re: Result of sql query in mail body

Re: Result of sql query in mail body