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

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Volker Stöffler

    Unless this has changed recently, OUTPUT TO not only ‘is not available inside “BEGIN …. END”.’, it is only available within a dbisql session, and it writes to a client side file. Which means that the dbisql session must run on the server machine because otherwise the client side OUTPUT file would be out of reach for xp_sendmail, which, as you stated, requires the file on the server side.

    I would expect the sequence to work more universally when using the TEMP_EXTRACT_… set of database options to create the result file.

    Or did I miss something?

    Cheers,

    Volker

    (0) 
    1. Jong-kil Park Post author

      Hi Volker,

      Yes, it’s a more recommended way to use TEMP_EXTRACT_… than OUTPUT TO command.

      I tested this on a Windows(64bit).

      Unfortunately, in case of TEMP_EXTRACT feature, it’s not supported in dbisql GUI tool.

      When using it in dbisql GUI, it runs fine but it doesn’t generate a file.

      For the additional test, I modified the OUTPUT TO command to TEMP_EXTRACT_* to extract the result set from the script I used. But, it didn’t generate a file.

      It seems like it has something to do with “.isqlPreferences12_64” to be set when extracting data.

      But, I couldn’t find a way of setting it on Windows environment.

      Could you please let me know whether there is a way to use TEMP_EXTRACT_* feature to be able to generate a file on Windows environment ?

      Best regards,

      Jerry

      (0) 
      1. Chris Baker

        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.

        TEMP_EXTRACT* options are available on the IQ server only.  When used, the result set is not sent to the client at all.  This is a much faster way to extract data as there is no network transfer to the client (especially if you are saving millions of rows).

        The best way to allow a Windows client to see the results of TEMP_EXTRACT is to set the directory used to a location where you can FTP/SFTP the results to the Windows machine, or to an NFS mount that can then be accessed from Windows.  Remember, the directory path will be an absolute path from the IQ server perspective and must have the permissions set to allow the IQ server start user to write to the directory.

        Chris

        (0) 

Leave a Reply