Pages

Wednesday, March 27, 2024

Send email with SQL

We have been able to send email from our IBM i partitions for several releases, using IBM commands and various third party tools. I use the Send SMTP Email command, SNDSMTPEMM, that comes within the IBM i operating system. Within the latest round of Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, comes another way, with SQL.

A new scalar function was added to the SYSTOOLS library called SEND_EMAIL. It has four parameters:

  1. TO_EMAIL:  Recipients email address. Mandatory
  2. SUBJECT:  Email's subject line. Mandatory
  3. BODY:  Email's body. Mandatory
  4. ATTACHMENT: IFS path name of the file to be sent as an attachment. Optional, if omitted no attachment is sent

For example:

01  VALUES SYSTOOLS.SEND_EMAIL
02            (TO_EMAIL => 'simon@email.com',
03             SUBJECT => 'Subject line',
04             BODY => 'Email sent using SYSTOOLS.SEND_EMAIL')

SEND_EMAIL returns a return code value. If statement is successful '1' is returned. If unsuccessful '-1' is returned.

When the above statement was run the following was returned:

00001
------
     1

As the email address, subject, and body parameters are mandatory I cannot omit them. I also cannot pass a blank email address, see line 2 below:

01  VALUES SYSTOOLS.SEND_EMAIL
02            (TO_EMAIL => '',
03             SUBJECT => 'Subject line',
04             BODY => 'Email sent using SYSTOOLS.SEND_EMAIL')

The above statement returns a SQL code of -438, which is the same as the SQL0438 message.

But I can have blank subject and body parameters:

01  VALUES SYSTOOLS.SEND_EMAIL
02            (TO_EMAIL => 'simon@email.com',
03             SUBJECT => '',
04             BODY => '')

The scalar function returns a successful return code.

00001
------
     1

I can look in my job's job log, using the JOBLOG_INFO SQL Table function, to see whether my statements were successful:

01  SELECT MESSAGE_ID AS "Msg id",
02         MESSAGE_TYPE AS "Type",
03       MESSAGE_SUBTYPE "Subtype",
04         MESSAGE_TIMESTAMP AS "Timestamp",
05         MESSAGE_TEXT AS "Text"
06    FROM TABLE(QSYS2.JOBLOG_INFO('*')) 
07  WHERE MESSAGE_TEXT LIKE 'Send E-mail%' 

Lines 1 – 5: The columns I want returned in my results.

Line 6: As the parameter passed to JOBLOG_INFO is '*' this means the current job.

Line 7: I only want rows returned where the message text starts with "Send E-mail".

My job log entries for the above statements returns:

Msg id   Type        Subtype            Timestamp
-------  ----------  -----------------  --------------------------
TCP5091  COMPLETION  <NULL>             2024-03-09 13:02:08.526958
TCP5092  ESCAPE      EXCEPTION HANDLED  2024-03-09 13:03:10.067946
TCP5091  COMPLETION  <NULL>             2024-03-09 13:05:29.868575

Text
-----------------------
Send E-mail Successful.
Send E-mail Failed.
Send E-mail Successful.

The last row was my first statement, which was successful. The middle row was the one with the blank email address, which failed. And the top one was with the blank subject and body, which was successful.

And what about attachments? As I said above those can be sent too.

01  VALUES SYSTOOLS.SEND_EMAIL
02            (TO_EMAIL => 'simon@email.com',
03             SUBJECT => 'Email with attachment 1',
04             BODY => 'Email sent with attachment',
05             ATTACHMENT => '/home/MyFolder/test.csv')

I receive a success return code:

00001
------
     1

What about if I use path that is not valid?

01  VALUES SYSTOOLS.SEND_EMAIL
02            (TO_EMAIL => 'simon@email.com',
03             SUBJECT => 'Email with attachment 2',
04             BODY => 'Email sent using SYSTOOLS.SEND_EMAIL',
05             ATTACHMENT => '/bad_directory_name/somefile.txt')

As the folder "bad_directory_name" does not exist the SQL code -438, SQL0438, is returned.

When I rerun the JOBLOG_INFO statement I can see one successful send, the first statement, and one failed, the second statement:

Msg id   Type        Subtype            Timestamp
-------  ----------  ---------------    --------------------------
TCP5091  COMPLETION  <NULL>             2024-03-09 13:21:30.812608
TCP5092  ESCAPE      EXCEPTION HANDLED  2024-03-09 13:22:29.049959

Text
-----------------------
Send E-mail Successful.
Send E-mail Failed.

How might I use this is a real world scenario. I have an unknown number of CSV files in my IFS folder and I want to email some of them to myself. I can see how many there are by using the IFS_OBJECT_STATISTICS Table function:

01  SELECT PATH_NAME
02    FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(
03                 START_PATH_NAME => '/home/MyFolder',
04                 SUBTREE_DIRECTORIES => 'NO',
05                 OBJECT_TYPE_LIST => '*STMF',
06                 IGNORE_ERRORS => 'YES')) 
07   WHERE PATH_NAME LIKE '%/values_%' 
08   ORDER BY PATH_NAME DESC

Line 1: I am only interested in the path name of the files.

Line 5: I only want results returned from the Table function that are stream files.

Line 7: And only for those files that contain this string.

There are three files that meet the criteria:

PATH_NAME
------------------------------------
/home/MyFolder/values_2024-03-10.csv
/home/MyFolder/values_2024-03-09.csv
/home/MyFolder/values_2024-03-08.csv

When I add the SEND_EMAIL Table function to the above statement when IFS_OBJECT_STATISTICS returns a row the file in the path name will be emailed to my email address:

01  SELECT PATH_NAME,
02         SYSTOOLS.SEND_EMAIL('simon@email.com','Values report',
03           'Values report is attached',PATH_NAME)
04           AS "Success"
05  FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(
06                START_PATH_NAME => '/home/MyFolder',
07                SUBTREE_DIRECTORIES => 'NO',
08                OBJECT_TYPE_LIST => '*STMF',
09                IGNORE_ERRORS => 'YES'))
10  WHERE PATH_NAME LIKE '%/values_%' 
11  ORDER BY PATH_NAME DESC

Lines 2 – 4: This is the SEND_EMAIL scalar function. I have not bother with the parameter names as, IMHO, they would make this statement too long. Notice that the last parameter, line 3, is the path for the file to attach which is the path name from the IFS_OBJECT_STATISTICS.

When the above is run the following is displayed as the results:

PATH_NAME                             Success  
------------------------------------  --------
/home/MyFolder/values_2024-03-10.csv         1
/home/MyFolder/values_2024-03-09.csv         1
/home/MyFolder/values_2024-03-08.csv         1

I can confirm if these emails were successfully sent by checking JOBLOG_INFO using the statement I did before:

Msg id   Type        Subtype            Timestamp
-------  ----------  ---------------    --------------------------
TCP5091  COMPLETION  <NULL>             2024-02-09 13:29:50.076756
TCP5091  COMPLETION  <NULL>             2024-02-09 13:29:50.128666
TCP5091  COMPLETION  <NULL>             2024-02-09 13:29:50.215508

Text
-----------------------
Send E-mail Successful.
Send E-mail Successful.
Send E-mail Successful.

Therefore, I have successfully three emails, each with a different file attached.

 

You can learn more about the SEND_EMAIL scalar function from the IBM website here.

 

This article was written for IBM i 7.5 TR3 and 7.4 TR9.

4 comments:

  1. Interesting but why do they perpetuate the old Mail Server Framework stuff and then top it with SQL. I would not choose to use this technique due to the dated email sending mechanism used. Unless the old SMTP server has been modernized under the covers perhaps ?

    ReplyDelete
  2. Simon please note that PATH_NAME in select expression may contains pointer value, so it better be casted to varchar type variable

    ReplyDelete
  3. Hello. Is it possible to send email with more than one attachment? If yes, would you mind giving us an example? Thank you!

    ReplyDelete
    Replies
    1. That functionality is coming on June 14, 2005, within the Technology Refreshes IBM i TR4 and 7.4 TR10.
      When I get a chance to "play" with it I will write about it on this web site.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.