This document is to show you how to send email using xp_sendmail(). This test was conducted on a Windows 7(64bit)
The steps to be done are a little simple as below.
CALL xp_startsmtp( 'test_sender@mytestdomain.com', 'mymailserver.mytestdomain.com' );
CALL xp_sendmail( recipient='test_account@mytestdomain.com',subject='This is a test', query='sp_iqdbspace' );
CALL xp_stopsmtp( );
But, there is an obstacle for us to send the query result dynamically using xp_sendmail().
So, if you run the procedures above, you will be received an empty email without the query result in its mail body.
The xp_sendmail() procedure is derived from SQL Anywhere, and please take a look at its definition and the key argument below.
(Please see the detail explanation for other arguments through a link in "See Also" area.)
xp_sendmail(
recipient = mail-address
[, subject = subject ]
[, cc_recipient = mail-address ]
[, bcc_recipient = mail-address ]
[, query = sql-query ]
[, "message" = message-body ]
[, attachname = attach-name ]
[, attach_result = attach-result ]
[, echo_error = echo-error ]
[, include_file = filename ]
[, no_column_header = no-column-header ]
[, no_output = no-output ]
[, width = width ]
[, separator = separator-char ]
[, dbuser = user-name ]
[, dbname = db-name ]
[, type = type ]
[, include_query = include-query ]
[, content_type = content-type ]
)
* query This LONG VARCHAR is provided for Transact-SQL compatibility. It is not used by SQL Anywhere. The default is NULL.
But unfortunately, the "query" argument is not used by SQL Anywhere as above, which means it's impossible to send query results dynamically.
So attaching a file containing the query results instead may be a workaround.
The steps for the workaround are as follows.
=============================================================================
CALL xp_startsmtp( 'test_sender@mytestdomain.com', 'mymailserver.mytestdomain.com');
select * from sp_iqdbspace();
output to 'c:\temp\sp_iqdbspace.out' delimited by '|' ;
call xp_read_file('c:\temp\sp_iqdbspace.out') ;
CALL xp_sendmail( recipient='test_account@mytestdomain.com',
"message"='This text is the body of my email.\n',
subject='This is a test',
content_type = 'text/html',
include_file = 'c:\temp\sp_iqdbspace.out' );
CALL xp_stopsmtp( );
=============================================================================
There are a few things to keep in mind for implementing the workaround.
1. "OUTPUT TO " is not available inside "BEGIN .... END". Also, OUTPUT TO is only available from the client dbisql. It only works when the dbisql client can receive a result set from IQ/SQLA and is a client command only.
2. In order to attach a file to the mail, xp_read_file should be called.
3. Any attachment specified by the include_file parameter is sent as application/octet-stream MIME type, with base64 encoding, and must be present on the database server.
See also
** xp_sendmail()
http://dcx.sap.com/index.html#1201/en/dbreference/xp-sendmail-system-procedure.html
http://dcx.sap.com/index.html#sa160/en/dbreference/xp-sendmail-system-procedure.html
** xp_read_file()
http://dcx.sap.com/index.html#1201/en/dbreference/xp-read-file-system-procedure.html
http://dcx.sap.com/index.html#sa160/en/dbreference/xp-read-file-system-procedure.html
Best regards,
Jerry
