How can I use a trigger export HTML and send mail

-6

Questions: when the table into A table (trigger) when the sum (a.ar_amt) > B.limi to send A fine warning sign that E-mail to me. The current problems are: the trigger does not support using the "output to html" , I can't export subsidiary If the export after so I can put the file name as a variable, because have a different trans_no. Cannot use triggers, stored procedures, the export file send mail, please give me some advice: thank you

 A table: TRANS_No    part_id  quantity   unit_price     ar_amt       customer_id
         TR001       A001    10                30          300      Customer001

         TR001       A004    20                 40          800      customer001
  B tabel:

          customer_id     limit

          customer001       900

Declare tot integer;    
DECLARE @result  LONG VARCHAR;  
Declare lim integer;  
    Select sum(amt) into tot where customer_id = newrow.customer_id;     
    Select limit into lim where customer_id = newrow.customer_id;
If tot > lim then
    ---If so start send result to   mail
                  CALL xp_startsmtp( smtp_sender = 'mfkpie8@163.com', smtp_server='smtp.163.com', smtp_port='25',
timeout=240, smtp_auth_username='name', smtp_auth_password='password');
CALL xp_sendmail( recipient='mfkpie8@163.com',
      subject='ssssssss',  content_type = 'text/html',
     include_file = 'c:\\outputtableA_trans_no.html' 
);
call xp_stopmail();

endif

updata new question( 2013-11-30): alt text

en:Production of HTML format for this statement (bease is wrong result with my code) ：

<html><head><title>test</title><meta http-equiv="content-type" content="text/html;charset=GBK"/></head><body>trans_nopart_idquantityunit_pricear_amtcustomer_id<table_x0020_border>TR001TR001204080customer001TR001A004204080000customer001</table_x0020_border></body></html>

Q：

Q1: en:I am now trying to increase "< table border > < / table >" encountered problems, Spaces are escaped (the red into space) please see pictures) beacase: Spaces convert to "x0020"

Q2. chang </table_x0020_border> to The correct HTML for the below :

I want the result is: <html><head><title>测试</title><meta http-equiv="content-type" content="text/html;charset=GBK"/></head><body>trans_nopart_idquantityunit_pricear_amtcustomer_id

TR001	TR001	20	40	80	customer001
TR001	A004	20	40	80000	customer001

</body></html>

email sample-code

asked 10 Oct '13, 06:57

mfkpie8
273●66●70●75
accept rate: 12%

edited 29 Nov '13, 12:45

You have been asked on multiple questions to post more (and complete) information when asking a question and I will do so again here:

Please tell us:

What version and build number of software are you using?
Give us an example of what you have tried.
What error(s) are you getting?
What output are you expecting?

And specifically for this question: Huh? What is your question?

(10 Oct '13, 08:06) Mark Culp

We want (and need) a real question to give any advice - otherwise it doesn't make sense to react on your postings.

As far as I can see, you have asked a lot of vague "questions" and have nevertheless got much response from the community. Sadly, you have not given any positive feedback that these answers have helped you - there's not even one "accepted" answer on any of your questions. That just doesn't feel fair for us voluntary contributors.

(10 Oct '13, 08:11) Volker Barth

Replies hidden

I'm starting to think that mfkpie8 is a bot that asks questions that contain semi ramdon words! The bot does not seem to learn from our responses and requests for more useful information.

(10 Oct '13, 08:14) Mark Culp

So a not too smart bot?

(Sidenote: The bot suspicion could be used by the SCN "community needs moderation" advocates - Breck will know:)

(10 Oct '13, 08:42) Volker Barth

So we're doing a kind of Turing test here?

(10 Oct '13, 11:14) Volker Barth

Well Google suggests he/she may be real.

"Based in China, mfkpie8 has been an eBay member since Oct 01, 2005."

We have folks here who can read Chinese and this forum can handle Chinese SQL ANYWHERE 12四大关键新特性_百度文库.

(11 Oct '13, 10:09) JBSchueler

Replies hidden

There is a photo of mfkpie8 here: http://bbs.windows7en.com/space-uid-1943613.html

(11 Oct '13, 10:12) JBSchueler

Well, then I suggest him/her to buy the Chinese edition of Breck's legendary book "SQL Anywhere Studio 9 Developer's Guide" (Wordware)...

I hope it's still available (and sorry, no, I have just 2 English editions...)

(11 Oct '13, 10:36) Volker Barth

Replies hidden

Maybe someone who can write Chinese (at Waterloo?) could post a polite note to mfkpie8 here to explain where he is going wrong in using the forum?

(11 Oct '13, 11:45) Justin Willey

I mean if set up the subsidiary results sent to the email,

how to write SQL

(20 Nov '13, 09:33) mfkpie8

But the trigger out statements cannot be used to export HTML format:

Statements allowed in procedures, triggers, events, and batches Most SQL statements are acceptable in batches, with the exception of the following: •Interactive SQL statements such as INPUT or OUTPUT

Since I can't use triggers, stored procedures, the export file send mail, please give me some advice: thank you

(27 Nov '13, 10:52) mfkpie8

That's not true - see my comment on your identical comment above one of the answers:)

(27 Nov '13, 11:50) Volker Barth

showing 4 of 12 show all flat view

3 Answers:

active answers oldest answers newest answers popular answers

You are correct - OUTPUT TO cannot be used directly in a trigger (as it is only available in Interactive SQL). The closest thing you can do is use the UNLOAD SQL statement from the server-side, but this does not support HTML formatting - only raw data exports.

You will need to create your own HTML result set from the results you're interested in and then e-mail the result. It wasn't clear from your question whether you actually need to write out the HTML file first to a temporary file on the file system first, or if you're only doing this in order to reference it in the include_file - not writing out the file out to the file system and generating the HTML in-memory will be faster:

Example data:

create table a_table (
   trans_no varchar(64),
   part_id varchar(64),
   quantity int,
   unit_price int,
   ar_amt int,
   customer_id varchar(128)
 );

insert into a_table values ('TR001', 'A001', 10, 30, 300, 'customer001');

create table b_table (
   customer_id varchar(128),
   "limit" int
);

insert into b_table values ('customer001', 900);
commit;

Now that we have the data, we can build up a trigger to check the amounts and send the appropriate HTML message out directly in the SMTP e-mail message:

create or replace trigger check_max_limit
before insert, update
on a_table
referencing new as new_row
for each row
begin
  declare @curr_limit int;
  declare @tot_limit int;
  declare @html_result long varchar;

  -- get current limit results for the incoming row customer_id 
  select sum(ar_amt), "b_table"."limit"
    into @curr_limit, @tot_limit
    from a_table, b_table
   where a_table.customer_id = new_row.customer_id
         and a_table.customer_id = b_table.customer_id
   group by "limit";

  -- check the limit
  if (@curr_limit + new_row.ar_amt > @tot_limit ) then

    -- generate HTML using "xmlelement" from results
    select xmlelement( name "html",
           (xmlelement( name "body",
            (xmlelement( name "table",
              xmlelement( name "thead", 
                xmlelement( name "tr", 
                  xmlelement( name "th", 'trans_no' ),
                  xmlelement( name "th", 'part_id' ),
                  xmlelement( name "th", 'quantity' ),
                  xmlelement( name "th", 'unit_price' ),
                  xmlelement( name "th", 'ar_amt' ),
                  xmlelement( name "th", 'customer_id' )
                )
             ),
             xmlelement( name "tbody",
                xmlagg( xmlelement( name "tr", 
                          xmlelement( name "td", trans_no ),
                          xmlelement( name "td", part_id ),
                          xmlelement( name "td", quantity ),
                          xmlelement( name "td", unit_price ),
                          xmlelement( name "td", ar_amt ),
                          xmlelement( name "td", customer_id )
                      ) ) ) ) ) ) )
    ) into @html_result
      from a_table
     where customer_id = new_row.customer_id;

    -- send e-mail with HTML content
    call xp_startsmtp( 'doe@sample.com', 'corporatemail.sample.com' );
    call xp_sendmail( recipient='jane.smith@sample.com',
                       subject='This is my subject line',
                      "message"=@html_result ); 
    call xp_stopsmtp( );

    -- (Optional) rollback current operation that triggered limit check
    rollback trigger; 
  end if;
end;

Finally to trigger the limit check and send an e-mail:

insert into a_table values ('TR001', 'A004', 20, 40, 800, 'customer001');

The trigger fires any time there is a record inserted or updated on a_table and ensures that the summed ar_amt always is lower than the b_table."limit" value. I used the XMLELEMENT function to generate the HTML text.

permanent link

answered 27 Nov '13, 13:58

Jeff Albion
10.8k●1●71●175
accept rate: 25%

edited 27 Nov '13, 14:03

thank for jeff: I now to put forward some questions want to ask you for help(Please check my main headings:）

I want to increase your excel under the same frame but found some problems, please check for me the picture of the latest update, thank you: Question:

http://itpubpic.img168.net/forum/201311/30/0120355p36ii0ttz2zbc32.png

Q1: I am now trying to increase "< table border > < / table >" encountered problems, Spaces are escaped (the red into space) please see pictures) link pictures: http://itpubpic.img168.net/forum/201311/30/011421l2l434llxcl24ccf.png

Q2: chang </table_x0020_border> to The correct HTML for the below

The correct HTML for the picture.

http://itpubpic.img168.net/forum/201311/30/0120355p36ii0ttz2zbc32.png

(29 Nov '13, 12:34) mfkpie8

Replies hidden

You need to use an XMLATTRIBUTES argument to XMLELEMENT if you wish to add attributes (e.g. 'border', 'width', etc.) to the XML elements that I demonstrated. As I linked in my original answer, there is an example using HTML in the documentation for this usage.

select xmlelement( name "html",
       (xmlelement( name "body",
        (xmlelement( name "table",
          xmlattributes( '1' as "border", '10' as "cellpadding", '0' as "cellspacing" ),
          xmlelement( name "thead", 
 ...

Result:

 <html><body><table border="1" cellpadding="10" cellspacing="0"><thead>
 ...

(29 Nov '13, 13:52) Jeff Albion

Give you a complete example for a table with border = "1". It looks like below:

select xmlelement(name "html",
xmlelement (name "head",
xmlelement(name "title", 't表转换'),
xmlelement(name "META", xmlattributes('Content-Type' as "http-equiv" , 'text/html;charset=GBK' as "content"))
),
(xmlelement (name "body",
xmlelement(name "table", xmlattributes('1' as "border")),
xmlelement(name "thead",
xmlelement(name "tr",
xmlelement(name "th", 'id'),
xmlelement(name "th", 'col2')
)
),
xmlelement(name "tbody",
xmlagg( xmlelement (name "tr",
xmlelement(name "td", id),
xmlelement(name "td", col2)
)
)
)
)
)
) from t;

It generates the following content:

<html>

  <head>

    <title>t表转换</title>

    <META http-equiv="Content-Type"

    content="text/html;charset=GBK" />

  </head>

  <body>

    <table border="1" />

    <thead>

      <tr>

        <th>id</th>

        <th>col2</th>

      </tr>

    </thead>

    <tbody>

      <tr>

        <td>1</td>

        <td>中国</td>

      </tr>

      <tr>

        <td>2</td>

        <td>spring</td>

      </tr>

    </tbody>

  </body>

</html>

(29 Nov '13, 16:53) Xiong He

    ALTER TRIGGER "check_max_limit" 
after insert, update
on a_table
referencing new as new_row
for each row
begin
  declare @curr_limit int;
  declare @tot_limit int;
  declare @html_result long varchar;
  declare @mailid     long varchar;

  -- get current limit results for the incoming row customer_id 
  select sum(ar_amt), "b_table"."limit",'1310636398@qq.com'
    into @curr_limit, @tot_limit,@mailid
    from a_table, b_table
   where a_table.customer_id = new_row.customer_id
         and a_table.customer_id = b_table.customer_id
   group by "limit";

  -- check the limit
  if (@curr_limit + new_row.ar_amt > @tot_limit ) then

    -- generate HTML using "xmlelement" from results
    select xmlelement(name "html",  
            xmlelement (name "head",   
                xmlelement(name "title", 't表转换'),  
                xmlelement(name "META", xmlattributes('Content-Type' as "http-equiv" , 'text/html;charset=GBK' as "content"))  
            ),  
            (xmlelement (name "body",    
             xmlelement(name "table", xmlattributes('1' as "border"),
                xmlelement(name "thead", 
                    xmlelement(name "tr",  
                    xmlelement(name "th",  'trans_no'),  
                    xmlelement(name "th", 'part_id')  
                )  
                ),  
                xmlelement(name "tbody",  
                    xmlagg( xmlelement (name "tr",  
                       xmlelement(name "td", trans_no),  
                       xmlelement(name "td", part_id)  
                    ))))))) into @html_result from a_table  where customer_id = new_row.customer_id;

    -- send e-mail with HTML content
    call xp_startsmtp(  smtp_sender = 'mfkpie8@163.com', smtp_server='smtp.163.com', smtp_port='25',
timeout=240, smtp_auth_username='mfkpie8', smtp_auth_password='password' );
    call xp_sendmail( recipient=@mailid,
                       subject='This is my subject line',
                      "message"=@html_result,content_type = 'text/html'); 
    call xp_stopsmtp( );

    -- (Optional) rollback current operation that triggered limit check
    rollback trigger; 
  end if;
end

Thank you very much for reply my friends problem, completed the trigger to send HTML email

(30 Nov '13, 21:21) mfkpie8

Comment Text Removed

" convert into " &quot"?

is the message conversion is not in the correct format to how to deal with single quotes follows: 101212.00

xmlelement (name "td", xmlattributes (. 'mso-number-format: "#, # # 0.00";' as style), shn quantity shipped)

But when the mail html is received is processed into
""<td style="mso-number-format:&quot;#,##0.00&quot;;">""

(04 Dec '13, 01:59) mfkpie8

Replies hidden

That is correct HTML (XML) syntax: you cannot put double quotes inside an HTML value without escaping the value as an HTML Entity, or specifying the value as a CDATA field.

Sidenote: Excel and HTML formatting note

You don't need double quotes around this syntax - you can just directly specify the formatting field, as it is already contained by the double-quotes:

xmlelement (name "td", xmlattributes ('mso-number-format:#,##0.00' as style), shn quantity shipped)

Result:

<td style="mso-number-format:#,##0.00">

It's more important to specify the double-quotes if you're using this in a Cascading Style Sheet (CSS) definition (which is also a more efficient way of specifying formatting across multiple cells):

e.g.

<head>
   <style>
      .myNumFormat {
         mso-number-format: "#,##0.00";
      }
   </style>
</head>
<body>
  ...
   <td class="myNumFormat"> ...

(04 Dec '13, 11:40) Jeff Albion

Thank you very much for your reply: Now I met some escape characters AScII table: such as:

Char (39) as'

Char (34) as ""

but

Char (38) as &

SQL code:

Xmlelement (name "a", xmlattributes (' tencent: / / message /? Uin = 1310636398 = QQNAME 'site + char (38) +' Menu = yes' as "href", "blank" as "href"),

< a href = "tencent: / / message /? Uin = 1310636398 site = QQNAME&amp Menu = yes" href = "blank" >

But HTMl compile he became "& amp;"

Like this kind of coding specifications have documentation can reference, for me it's really hard to find on the Internet ASA material, online document also can not find about escape character

Or can you tell me where I can learn very complete information about the ASA, we beginners can learn faster thanks for all

(04 Dec '13, 12:19) mfkpie8

Replies hidden

Character Entities are common to SGML - XML defines them as 'Predefined entities', and HTML defines them as 'character entities'.

The XML predefined entities are the values you listed:

Name	Character	Unicode code point (decimal)	Standard	Description
quot	"	U+0022 (34)	XML 1.0	double quotation mark
amp	&	U+0026 (38)	XML 1.0	ampersand
apos	'	U+0027 (39)	XML 1.0	apostrophe (apostrophe-quote)
lt	<	U+003C (60)	XML 1.0	less-than sign
gt	>	U+003E (62)	XML 1.0	greater-than sign

SQL Anywhere is respecting the fact that you are trying to construct HTML (XML) source and you are trying to put character values in the fields that cannot be directly represented by HTML.

Replacing & with & in a URL link inside the HTML source is the "correct thing to do" - the browser will render the &amp as & at run time and the link will work correctly.

Or can you tell me where I can learn very complete information about the ASA, we beginners can learn faster thanks for all

What you're describing above isn't related to SQL Anywhere - while the behaviour from XMLELEMENT may surprise you, for those who work with XML and HTML regularly, the replacement of HTML entities is a regular data operation and is expected for XML correctness.

If you're looking for more information about HTML entities and why they're important to be replaced in the source, I would highly recommend doing some research on other websites for information.

(04 Dec '13, 12:40) Jeff Albion

should be how write SQL Success and failure of mail to send back these message's thanks

(08 Dec '13, 11:43) mfkpie8

showing 5 of 9 show all flat view

I'm going to take a random guess that you are wanting to send email from the database server.

Did you look at the examples in the documentation for xp_sendmail? You should be able to cut-and-paste and then modify as needed any of the four examples to get a working solution.

Example: Here is the first example in the v16 documention:

CALL xp_startsmtp( 'doe@sample.com', 'corporatemail.sample.com' );
CALL xp_sendmail( recipient='jane.smith@sample.com',
                  subject='This is my subject line',
                  "message"='This text is the body of my email.\n' ); 
CALL xp_stopsmtp( );

permanent link

answered 10 Oct '13, 09:28

Mark Culp
24.9k●10●141●297
accept rate: 41%

Comment Text Removed

flat view

Oh, Just saw this question on this forum. I think the author of this message want to ask the following question. The parameter "query"="select * from

" in the store procedure xp_sendmail() can't really send the result of the query to the recipient.

I just did the same test in my local environment of ASA12.0.1. It really can't work.

Here is the test script: create table t(id int primary key, col2 varchar(32)); insert into t values(1 ,'wang'); commit;

CALL xp_startsmtp( smtp_sender = '<userid>@163.com', smtp_server='smtp.163.com', smtp_port='25', timeout=240, smtp_auth_username='<userid>', smtp_auth_password='<password>');

CALL xp_sendmail( recipient='iihero@qq.com',subject='test_result',"message"='Oh, I hope there is query result as well',query='select * from t');

call xp_stopmail();

After that, the received email only contains the message content of "Oh, ....", but there isn't anything about the query result.

The ASA document doesn't give more details about how to ensure the query result be sent to the target recipient.

Could anybody help answer this question if this is an user error? Otherwise, this should be a bug of ASA. Thanks.

permanent link

This answer is marked "community wiki".

answered 22 Nov '13, 21:56

Xiong He
16●2
accept rate: 0%

As stated in the documentation, the "query" parameter is not used by SQL Anywhere.

(22 Nov '13, 23:23) Graeme Perrow

Replies hidden

:-) Thanks. I misunderstood the document.

(23 Nov '13, 00:05) Xiong He

Comment Text Removed

But the trigger out statements cannot be used to export HTML format:

Since I can't use triggers, stored procedures, the export file send mail, please give me some advice: thank you

(27 Nov '13, 10:51) mfkpie8

You cannot use OUTPUT in code blocks, as OUTPUT (like INPUT) is an ISQL command, not a SQL statement.

However, you can certainly output data to files via the UNLOAD statement (either directly into a file or into a variable and then write that to a file with xp_write_file) - just look for samples on UNLOAD in this forum or the docs. And these are SQL statements or procedures and surely can be used within code blocks such as triggers.

(27 Nov '13, 11:46) Volker Barth

@Volker Barth Please check for the latest updates

(29 Nov '13, 12:53) mfkpie8

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

email ×13
sample-code ×10

question asked: 10 Oct '13, 06:57

question was seen: 7,566 times

last updated: 08 Dec '13, 11:43

SAP SQL Anywhere

How can I use a trigger export HTML and send mail

Follow this question

Related questions