How to send email from MySQL 5.1

by

If you have an SMTP service running, you can outfile to the drop directory. If you have high volume, you may result with duplicate file names, but there are ways to avoid that.

Otherwise, you will need to create a UDF.

Here’s a sample trigger solution:

CREATE TRIGGER test.autosendfromdrop BEFORE INSERT ON test.emaildrop
FOR EACH ROW BEGIN
      /* START THE WRITING OF THE EMAIL FILE HERE*/      
      SELECT  concat("To: ",NEW.To),
              concat("From: ",NEW.From),
              concat("Subject: ",NEW.Subject),
              NEW.Body
          INTO OUTFILE 
                   "C:\\inetpub\\mailroot\\pickup\\mail.txt" 
              FIELDS TERMINATED by '\r\n' ESCAPED BY '';            
END;

To markup the message body you will need something like this…

CREATE FUNCTION `HTMLBody`(Msg varchar(8192)) 
    RETURNS varchar(17408) CHARSET latin1 DETERMINISTIC
BEGIN
  declare tmpMsg varchar(17408);
  set tmpMsg = cast(concat(
      'Date: ',date_format(NOW(),'%e %b %Y %H:%i:%S -0600'),'\r\n',
      'MIME-Version: 1.0','\r\n',
      'Content-Type: multipart/alternative;','\r\n',
      ' boundary=\"----=_NextPart_000_0000_01CA4B3F.8C263EE0\"','\r\n',
      'Content-Class: urn:content-classes:message','\r\n',
      'Importance: normal','\r\n',
      'Priority: normal','\r\n','','\r\n','','\r\n',
      'This is a multi-part message in MIME format.','\r\n','','\r\n',
      '------=_NextPart_000_0000_01CA4B3F.8C263EE0','\r\n',
      'Content-Type: text/plain;','\r\n',
      '  charset=\"iso-8859-1\"','\r\n',
      'Content-Transfer-Encoding: 7bit','\r\n','','\r\n','','\r\n',
      Msg,
      '\r\n','','\r\n','','\r\n',
      '------=_NextPart_000_0000_01CA4B3F.8C263EE0','\r\n',
      'Content-Type: text/html','\r\n',
      'Content-Transfer-Encoding: 7bit','\r\n','','\r\n',
      Msg,
      '\r\n','------=_NextPart_000_0000_01CA4B3F.8C263EE0--'
      ) as char);
  RETURN tmpMsg;
END ;

More Related Contents:

  1. How to create email notification with login users on PHP an MySQL [duplicate]
  2. Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT
  3. When I want to install MySQL I faced this issue “root account password” I forgot it
  4. Restoring MySQL database from physical files
  5. MySQL Trigger after update only if row has changed
  6. MySQL “WITH” clause
  7. MySQL Query – Records between Today and Last 30 Days
  8. MySQL “Group By” and “Order By”
  9. MySQL Query to select data from last week?
  10. Rename a column in MySQL
  11. How to change collation of database, table, column?
  12. Difference Between Schema / Database in MySQL
  13. Node.js can’t authenticate to MySQL 8.0
  14. Mysql 1050 Error “Table already exists” when in fact, it does not
  15. Which rows are returned when using LIMIT with OFFSET in MySQL?
  16. How to transform a MSSQL CTE query to MySQL?
  17. Looping Over Result Sets in MySQL
  18. How to get the latest record in each group using GROUP BY? [duplicate]
  19. MySQL Data Source not appearing in Visual Studio
  20. MySQL JSON: How to find object from key value
  21. MYSQL into outfile “access denied” – but my user has “ALL” access.. and the folder is CHMOD 777
  22. Advanced MySql Query: Update table with info from another table
  23. Commit data in a mysql container
  24. How do I count columns of a table
  25. Mysqldump launched by cron and password security
  26. MySQL Trigger cannot update table – getting ERROR 1442
  27. Where I can find a list of “mysqldump” exit codes?
  28. Unserialize through query at database level itself
  29. Detecting utf8 broken characters in MySQL
  30. django cannot connect mysql in docker-compose

Leave a Comment