|
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; |
|
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;
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
|
