We are using SQL Anywhere 16 build 2270. I created a 'send_mail' procedure with code below. Mail is not being sent however and the return code for xp_startsmtp is 5 and the return code for xp_get_mail_error_code is 10060. Any thoughts on what the problem might be? I can send mail ok using the same IP from our web application that uses this database but nothing can be sent directly from the database at the moment. Thanks, Tom

ALTER PROCEDURE "IFS"."send_email"(
   IN @recipient       LONG VARCHAR,
   IN @subject         LONG VARCHAR,
   IN @message         LONG VARCHAR )
BEGIN

DECLARE @return_code           INTEGER;
DECLARE @smtp_sender           LONG VARCHAR;
DECLARE @smtp_server           LONG VARCHAR; 
DECLARE @smtp_port             INTEGER; 
DECLARE @timeout               INTEGER;
DECLARE @err_code              INTEGER;
DECLARE @err_text              LONG VARCHAR;

MESSAGE STRING ( '@recipient = "', @recipient, '"' ) TO CLIENT;
MESSAGE STRING ( '@subject = "', @subject, '"' ) TO CLIENT;
MESSAGE STRING ( '@message = "', @message, '"' ) TO CLIENT;

SET @smtp_sender = 'mailout@address.com';
SET @smtp_server = '10.10.10.10';
SET @smtp_port   = 25;  -- 25 for standard SMTP, 587 for gmail
SET @timeout     = 30;   -- default is 60 seconds

@return_code = CALL xp_startsmtp ( 
   smtp_sender          = @smtp_sender,  
   smtp_server          = @smtp_server,
   smtp_port            = @smtp_port );

@err_code = CALL xp_get_mail_error_code();
@err_text = CALL xp_get_mail_error_text();

MESSAGE STRING ( 'xp_startsmtp @return_code = ', @return_code ) TO CLIENT ;
MESSAGE STRING ( 'xp_startsmtp @err_code = ', str(@err_code) ) TO CLIENT;
MESSAGE STRING ( 'xp_startsmtp @err_text = ', @err_text ) TO CLIENT;

@return_code = CALL xp_sendmail ( 
   recipient    = @recipient,  
   subject      = @subject,  
   "message"    = @message );

MESSAGE STRING ( 'xp_sendmail @return_code = ', @return_code ) TO CLIENT;

CALL xp_stopsmtp(); -- do not bother to check return code

EXCEPTION WHEN OTHERS THEN
   CALL xp_stopsmtp();

END;

asked 05 Aug '16, 16:52

Tom%20Rolseth's gravatar image

Tom Rolseth
191878
accept rate: 0%

edited 05 Aug '16, 17:59

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050


That code looks a lot like this old blog post :)

Since you're using SQL Anywhere 16 you might want to look at the error diagnostic code in this newer blog post.

In your case, return code 5 is "connect error" and 10060 is "socket error".

FWIW the Foxhound 3 docs contain a list of return codes.

Also FWIW Foxhound 4 has improved diagnostics; for example, this is what an actual error message looks like:

Test result: SMTP start test Alert email failed with return code 5 when sending "Foxhound Test Alert Email (2016-08-05 18:21:24)". Connect error 10047 [Google: smtp error 10047]

Continuing FWIW, here is a snippet of the actual code from down inside Foxhound 4...

   WHEN 'smtp' THEN

      IF COALESCE ( @smtp_auth_username, '' ) = '' THEN

         @return_code = CALL xp_startsmtp ( 
            smtp_sender        = @smtp_sender,  
            smtp_server        = @smtp_server,  
            smtp_port          = @smtp_port,  
            timeout            = @smtp_timeout,
            smtp_sender_name   = 'Foxhound Test' );

      ELSEIF COALESCE ( @smtp_certificate_filespec, '' ) = '' THEN

         @return_code = CALL xp_startsmtp ( 
            smtp_sender        = @smtp_sender,  
            smtp_server        = @smtp_server,  
            smtp_port          = @smtp_port,  
            timeout            = @smtp_timeout,
            smtp_sender_name   = 'Foxhound Test',
            smtp_auth_username = @smtp_auth_username, 
            smtp_auth_password = @smtp_auth_password );

      ELSE

         @return_code = CALL xp_startsmtp ( 
            smtp_sender          = @smtp_sender,  
            smtp_server          = @smtp_server,  
            smtp_port            = @smtp_port,  
            timeout              = @smtp_timeout,
            smtp_sender_name     = 'Foxhound Test',
            smtp_auth_username   = @smtp_auth_username, 
            smtp_auth_password   = @smtp_auth_password,
            trusted_certificates = @smtp_certificate_filespec );

      END IF;

      IF @return_code <> 0 THEN

         SET @email_status = STRING ( 
            'SMTP start test ',
            @test_type,
            ' email failed with return code ', 
            @return_code, 
            ' when sending "', 
            @subject,
            '"',
            rroad_mail_error_diagnostic ( @email_type, @return_code ) );

      END IF;
...
CREATE FUNCTION rroad_mail_error_diagnostic
   ( IN @email_type     VARCHAR ( 10 ),
     IN @return_code    INTEGER )
   RETURNS LONG VARCHAR
BEGIN

DECLARE @error_code    INTEGER;
DECLARE @error_text    LONG VARCHAR;
DECLARE @http_pos      INTEGER;
DECLARE @blank_pos     INTEGER;
DECLARE @url           LONG VARCHAR;

SET @error_code = COALESCE ( xp_get_mail_error_code(), 0 );

CASE

   WHEN @return_code = -1 THEN

      SET @error_text = COALESCE ( xp_get_mail_error_text(), '' );
      SET @http_pos   = LOCATE ( @error_text, ' http' );

      IF @http_pos > 0 THEN
         SET @blank_pos = LOCATE ( @error_text, ' ', @http_pos + 1 );
         IF @blank_pos > 0 THEN
            SET @url = SUBSTR ( @error_text, @http_pos + 1, @blank_pos - @http_pos - 1 );
         ELSE
            SET @url = SUBSTR ( @error_text, @http_pos + 1 );
         END IF;
         SET @error_text = STUFF (
            @error_text,
            @http_pos + 1, 
            LENGTH ( @url ),
            STRING ( '', @url, '' ) );
      END IF;

      RETURN STRING ( '. Error code/text ', @error_code, '/', @error_text );

   WHEN @return_code = 1 THEN

      RETURN '. An invalid parameter was supplied';

   WHEN @return_code = 2 THEN

      RETURN '. Out of memory';

   WHEN @return_code = 3 THEN

      RETURN '. xp_startmail or xp_startsmtp was not called';

   WHEN @return_code = 4 THEN

      RETURN '. Bad host name, or no internet connection';

   WHEN @return_code = 5 AND @error_code = 0 THEN

      RETURN '. Connect error';

   WHEN @return_code = 5 AND @error_code <> 0 THEN

      IF @email_type = 'smtp' THEN

         RETURN STRING ( 
            '. Connect error ', 
            @error_code, 
            ' [Google: smtp error ',
            @error_code,
            ']' );

      ELSE

         RETURN STRING ( 
            '. Connect error ', 
            @error_code, 
            ' [Google: mapi error ',
            @error_code,
            ']' );

      END IF;

   WHEN @return_code = 6 AND @error_code = 0 THEN

      RETURN '. Secure connection error';

   WHEN @return_code = 6 AND @error_code <> 0 THEN

      IF @email_type = 'smtp' THEN

         RETURN STRING ( 
            '. Secure connection error ', 
            @error_code, 
            ' [Google: smtp error ',
            @error_code,
            ']' );

      ELSE

         RETURN STRING ( 
            '. Secure connection error ', 
            @error_code, 
            ' [Google: mapi error ',
            @error_code,
            ']' );

      END IF;

   WHEN @return_code = 7 AND @error_code = 0 THEN

      RETURN '. MAPI functions are not available';

   WHEN @return_code = 7 AND @error_code <> 0 THEN

      RETURN STRING ( '. MAPI functions are not available ', @error_code );

   ELSE

      RETURN '';

END CASE;

EXCEPTION WHEN OTHERS THEN
   RETURN '';

END;
permanent link

answered 05 Aug '16, 18:54

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 05 Aug '16, 18:57

Yeah, the code I posted was lifted from your blog and modified. Main difference being that I am not providing the user name or password (it has never been required before).

(05 Aug '16, 19:25) Tom Rolseth

Also, the IP is for a new email relay server that our MIS team wants put into production. I'm wondering if the port is blocked...

(05 Aug '16, 19:27) Tom Rolseth
Your answer
toggle preview

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

Question tags:

×13
×10

question asked: 05 Aug '16, 16:52

question was seen: 3,010 times

last updated: 05 Aug '16, 19:27