Sending Email From SQL Anywhere 12 Via Google's SMTP Server

SQL Anywhere lets you send email via MAPI and SMTP from inside your SQL code by calling system procedures like xp_sendmail(). However, many shops don't allow MAPI messages to be sent from within program code, and they don't run SMTP servers at all.

One solution is to use a Google Mail account to send emails via Google's SMTP server smtp.gmail.com.

(This article is based on Eric Farrar's original announcement of Gmail support in SQL Anywhere 12
and Mark Culp's update on the subject of using the Equifax certificate.)

In order to use smtp.gmail.com you need to call xp_startsmtp and provide three pieces of identification:

1. a Google Mail account,

2. the password for that account and

3. the SSL certificate currently used by Google Mail.

Items 1 and 2 are easy to get; here's how to get number 3:

• Choose a folder to store the SSL certificate; e.g., c:\temp

• Ask around to see if anyone knows what SSL certificate Google is currently using for smtp.gmail.com, and where you can get it from.

• Today's answers are "Equifax Secure Certificate Authority, and you can get if from GeoTrust".

• [skipping ahead] Open this link in your browser: Download - Equifax Secure Certificate Authority (Base-64 encoded X.509)

  

• Click right-mouse - Save As... anywhere on the page of carp stuff you see ( the page you get, not the image above :)

• Choose a File name: c:\temp\Equifax_Secure_Certificate_Authority.cer

• Choose a Save as type: Web Page, Complete

• Click on Save, then

• Edit the following script to specify the certificate file specification: SET @trusted_certificates = 'c:\\temp\\Equifax_Secure_Certificate_Authority.cer';

CREATE PROCEDURE send_email_via_gmail (
   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 @smtp_sender_name      LONG VARCHAR;
DECLARE @smtp_auth_username    LONG VARCHAR; 
DECLARE @smtp_auth_password    LONG VARCHAR;
DECLARE @trusted_certificates  LONG VARCHAR;

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

SET @smtp_sender          = 'your.name@gmail.com';
SET @smtp_server          = 'smtp.gmail.com';
SET @smtp_port            = 587;  -- 25 for standard SMTP, 587 for gmail
SET @timeout              = 10;   -- default is 60 seconds
SET @smtp_sender_name     = 'Your Name';
SET @smtp_auth_username   = 'your.name@gmail.com';
SET @smtp_auth_password   = 'your.password';
SET @trusted_certificates = 'c:\\temp\\Equifax_Secure_Certificate_Authority.cer';

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

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

You will also have to change a bunch of other "your.name" stuff; I'll leave that up to you.

Here's how to call that procedure from inside your SQL code:

CALL send_email_via_gmail (
   'breck.carter@gmail.com',
   'Testing...',
   'Hello, World!' );

You can also call it via dbisql to send an email from a Windows command file:

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  CALL send_email_via_gmail (^
  'breck.carter@gmail.com',^
  'Testing...',^
  'Hello, World!' )

The backup command files from Demonstrating Full and Incremental Backups can be modified as follows to send OK / FAILED email messages; see the dbisql commands at lines 43 and 59 in 05_run_dbbackup12_full.bat:

REM ******************************************************************
REM Create empty backup\generation_temp subfolder
MD backup
CD backup
RD /S /Q generation_temp
MD generation_temp
CD ..

ECHO ********************************************^
*********************************************>>backup\generation_temp\dbbackup_log.txt
ECHO Full dbbackup started >>backup\generation_temp\dbbackup_log.txt
DATE /T >>backup\generation_temp\dbbackup_log.txt
TIME /T >>backup\generation_temp\dbbackup_log.txt

"%SQLANY12%\bin32\dbbackup.exe"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  -o backup\generation_temp\dbbackup_log.txt^
  -x^
  backup\generation_temp

IF ERRORLEVEL 1 GOTO backup_failed

REM ******************************************************************
REM Move backup to generation10 subfolder
CD backup
RD /S /Q generation1
RENAME generation2 generation1
RENAME generation3 generation2
RENAME generation4 generation3
RENAME generation5 generation4
RENAME generation6 generation5
RENAME generation7 generation6
RENAME generation8 generation7
RENAME generation9 generation8
RENAME generation10 generation9
RENAME generation_temp generation10
CD ..
DIR /S backup\generation10\*.* >>backup\generation10\dbbackup_log.txt
ECHO Full dbbackup OK >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  CALL send_email_via_gmail (^
  'breck.carter@gmail.com',^
  'Full dbbackup OK - ddd12',^
  'See folder %CD%\backup\generation10' )

GOTO end

:backup_failed
REM ******************************************************************
REM Backup failed
ECHO Error: Full dbbackup failed >>backup\generation_temp\dbbackup_log.txt
DATE /T >>backup\generation_temp\dbbackup_log.txt
TIME /T >>backup\generation_temp\dbbackup_log.txt

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  CALL send_email_via_gmail (^
  'breck.carter@gmail.com',^
  'Full dbbackup FAILED - ddd12',^
  'See diagnostics in %CD%\backup\generation_temp\dbbackup_log.txt' )

GOTO end

:end

Also see the dbisql commands at lines 42 and 58 in 07_run_dbbackup12_incremental.bat:

ECHO ********************************************^
*********************************************>>backup\generation10\dbbackup_log.txt
ECHO Incremental dbbackup started >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt

REM ******************************************************************
REM Make sure a full backup exists
IF EXIST "backup\generation10\ddd12.db" ( GOTO check_full_log_backup ) ELSE ( GOTO full_backup_is_missing )

:check_full_log_backup
IF EXIST "backup\generation10\ddd12.log" ( GOTO full_backup_exists ) ELSE ( GOTO full_backup_is_missing )

:full_backup_is_missing
ECHO Create a full backup before an incremental backup. >>backup\generation10\dbbackup_log.txt
GOTO backup_failed

:full_backup_exists
REM ******************************************************************
REM Backup to generation10\logs subfolder
CD backup\generation10
MD logs
CD ..\..

"%SQLANY12%\bin32\dbbackup.exe"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  -o backup\generation10\dbbackup_log.txt^
  -n^
  -t^
  -x^
  backup\generation10\logs

IF ERRORLEVEL 1 GOTO backup_failed

REM ******************************************************************
REM Backup OK
DIR /S backup\generation10\*.* >>backup\generation10\dbbackup_log.txt
ECHO Incremental dbbackup OK >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  CALL send_email_via_gmail (^
  'breck.carter@gmail.com',^
  'Incremental dbbackup OK - ddd12',^
  'See folder %CD%\backup\generation10\logs' )

GOTO end

:backup_failed
REM ******************************************************************
REM Backup failed
ECHO Error: Incremental dbbackup failed >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  CALL send_email_via_gmail (^
  'breck.carter@gmail.com',^
  'Incremental dbbackup FAILED - ddd12',^
  'See diagnostics in %CD%\backup\generation10\dbbackup_log.txt' )

GOTO end

:end

Here's what the "OK" emails look like: