How do I send email from my database using GMail.

I was trying to solve this problem recently, indirectly, while setting up e-mail alerts from Breck Carter's Foxhound monitoring tool. Foxhound runs entirely in SQL Anywhere and uses xp_startsmtp for sending alerts, and I couldn't make it work - getting either nothing or smtp error 504. It turned out the answer was in the documentation the whole time, but not being well versed in the subject, I didn't recognise it. My solution posted below.

asked 02 Jun, 12:14

Justin%20Willey's gravatar image

Justin Willey
7.4k128172243
accept rate: 20%

edited 02 Jun, 12:27


First, you need an email "app password" for your Google Account - this avoids the problems of "less secured applications" etc. See https://support.google.com/accounts/answer/185833?hl=en This password can only be used for the purpose you specify when you set it up - it's not a general password for the given Google Account. NB - if you change you main Google password, all app passwords are disabled, and need to be re-set.

The key then is to use port 587 at smpt.gmail.com and to specify trusted_certificates='SMTPS=Yes;Secure=1' when calling xp_startsmtp. You don't need to download and trust public certificates / keys from Google or anything like that. This script, adapted from earlier posts on the subject, illustrates:

BEGIN
DECLARE @return_code           INTEGER;
DECLARE @smtp_sender           LONG VARCHAR;
DECLARE @smtp_server           LONG VARCHAR; 
DECLARE @smtp_port             INTEGER; 
DECLARE @timeout               INTEGER;
DECLARE @smtp_sender_name      LONG VARCHAR;
DECLARE @smtp_auth_username    LONG VARCHAR; 
DECLARE @smtp_auth_password    LONG VARCHAR;
DECLARE @trusted_certificates  LONG VARCHAR;
DECLARE @recipient             LONG VARCHAR;
DECLARE @subject               LONG VARCHAR;
DECLARE @message               LONG VARCHAR;

SET @smtp_sender          = 'whatever.you.want@myco.com';
SET @smtp_server          = 'smtp.gmail.com'; 
SET @smtp_port            = 587;   
SET @timeout              = 60;   -- default is 60 seconds
SET @smtp_sender_name     = 'Whatever You Want';
SET @smtp_auth_username   = 'actual.gmail.account@myco.com';
SET @smtp_auth_password   = 'ptgtsujskisdqwqe56thl'; -- "app password" NOT the Google Account password.
SET @trusted_certificates = 'SMTPS=Yes;Secure=1';
SET @recipient            = 'a.person@my-customer.com';
SET @subject              = STRING ( 'gmail test subject at ', CURRENT TIMESTAMP );
SET @message              = STRING ( 'gmail test message at ', CURRENT TIMESTAMP );

@return_code = CALL xp_startsmtp ( 
   smtp_sender          = @smtp_sender,  
   smtp_server          = @smtp_server,  
   smtp_port            = @smtp_port,  
   timeout              = @timeout,
   smtp_sender_name     = @smtp_sender_name,
   smtp_auth_username   = @smtp_auth_username, 
   smtp_auth_password   = @smtp_auth_password,
   trusted_certificates = @trusted_certificates );

MESSAGE STRING ( 'xp_startsmtp @return_code = ', @return_code ) TO CLIENT;
MESSAGE STRING ( 'xp_get_mail_error_code() = ',  xp_get_mail_error_code()      ) TO CLIENT;
MESSAGE STRING ( 'xp_get_mail_error_text() = "', xp_get_mail_error_text(), '"' ) TO CLIENT;

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

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

@return_code = CALL xp_stopsmtp();

MESSAGE STRING ( 'xp_stopsmtp @return_code = ', @return_code ) TO CLIENT;
MESSAGE STRING ( 'xp_get_mail_error_code() = ',  xp_get_mail_error_code()      ) TO CLIENT;
MESSAGE STRING ( 'xp_get_mail_error_text() = "', xp_get_mail_error_text(), '"' ) TO CLIENT;

EXCEPTION WHEN OTHERS THEN
   CALL xp_stopsmtp();

END;

[In the Foxhound setup the text "SMTPS=Yes;Secure=1" (without the quotes) goes in the box called "SMTP Certificate Filespec".]

permanent link

answered 02 Jun, 12:26

Justin%20Willey's gravatar image

Justin Willey
7.4k128172243
accept rate: 20%

edited 02 Jun, 12:35

Out of curiosity: Is that solution similar/related to Breck's older blog article - which you apparently had read then?

(02 Jun, 15:22) Volker Barth
Replies hidden
2

Yes, indeed... in fact, I'm hoping to use Justin's discoveries to get gmail working again for me ( the Foxhound 5 docs only talk about using SendGrid :)

(02 Jun, 16:18) Breck Carter
Comment Text Removed

I see some differences in parameters between this code and documentation of xp_startsmtp. Docs says: xp_startsmtp( smtp_sender = email-address ... [, trusted_certificates = { public-certificate | * } [, secure = { 1 | 0 } ]

Here in code you used 'Secure=1' as Key/Value in parameter trusted_certificates

Is documentation wrong ?

permanent link

answered 03 Jun, 04:26

Alex99's gravatar image

Alex99
76117
accept rate: 0%

Yes - you are quite right to point this out.

If you have SMTPS=Yes in trusted_certificates, then you seem to have to have Secure=1 in there as well. If you don't, you get return code 6 and the message "Unable to open certificate file 'SMTPS=YES'"

Having Secure=1 as a separate parameter makes no difference either way. Perhaps there is a parsing bug in the procedure? (Which is concerning if we are coding to a bug!)

NB It doesn't matter if you have SMTPS=Yes;Secure=1 or Secure=1;SMTPS=Yes - both work.

(03 Jun, 06:38) Justin Willey

Aggh - the plot thickens, red herrings abound. This works just as well, with no separate Secure=1 parameter:

SET @trusted_certificates = 'SMTPS=Yes;blah=blah';

however this doesn't (same error about opening certificate):

SET @trusted_certificates = 'SMTPS=Yes;';

so it looks like there is a parsing bug in xp_startsmtp

(03 Jun, 06:45) Justin Willey
Replies hidden

(03 Jun, 11:01) Breck Carter
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:

×185
×12
×3
×1

question asked: 02 Jun, 12:14

question was seen: 100 times

last updated: 10 Jun, 09:44