Improvements to the SQL scalar function that sends email

As part of the latest Technology Refreshes a number of improvements have been added to the SEND_EMAIL scalar function.

What are these:

• New parameters for carbon copy (CC) and blind carbon copy (BCC)
• Up to twenty email addresses in the email, cc email, and bbc email parameters
• Up to ten attachments from the IFS can be attached to the email

All of which are useful additions to SEND_EMAIL.

Let me jump right to giving an example of using more than one email address and the new cc and bbc email parameters:

01 VALUES SYSTOOLS.SEND_EMAIL( 02 TO_EMAIL => 'simon@email.com,raymond@email.com', 03 CC_EMAIL => 'maria@email.com,geoff@email.com', 04 BCC_EMAIL => 'francesca@email.com,tony@email.com', 05 SUBJECT => 'Test 1', 06 BODY => 'This is a test')

Line 1: As SEND_EMAIL is a scalar function I can execute it using the Values statement.

Line 2: The to email has two email addresses in it. The comma ( , ) is the separator character used to separate the email addresses.

Line 3: The new parameter CC_EMAIL is used for the carbon copy email. Multiple email addresses are separated by a comma.

Line 4: It is not surprising that the blind carbon copy parameter is called BCC_EMAIL.

Line 5: This is the subject line of the email.

Line 6: This text is included in the body of the email.

When I execute this statement the following is returned:

00001 ------ 1

This is the return code from the scalar function. "1" denotes that the email was sent successfully.

When I first tried SEND_EMAIL it made sense that for this to be valid there must be an email address in the to email parameter. Is that still true? Can I just have an email address in one of the other email address parameters? Well, there is only way to find out:

01 VALUES SYSTOOLS.SEND_EMAIL( 02 CC_EMAIL => 'maria@email.com', 03 BCC_EMAIL => 'francesca@email.com', 04 SUBJECT => 'Test 2', 05 BODY => 'This is a test')

Alas, the above fails with the following error:

Message: [SQ20484] Parameter 1 required for routine SEND_EMAIL in SYSTOOLS.

It makes sense that the a email address must be present in the TO_EMAIL parameter for SEND_EMAIL to work.

The next improvement is to be able to send up to twenty attachments to an email. I am not going to experiment with that large a number, I am just going to use two attachments.

I always test in ACS's "Run SQL Scripts", RSS, and this is the statement I used:

01 VALUES SYSTOOLS.SEND_EMAIL( 02 TO_EMAIL => 'simon@email.com', 03 SUBJECT => 'Test 3', 04 BODY => 'This is a test with attachments', 05 ATTACHMENT => '/home/MyFolder/text_file_1.txt,/home/MyFolder/text_file_2.txt')

Line 5: You can see there are two files from my folder in the IFS I have attached to this email.

When I execute this statement the following is returned, which indicates that the email was sent:

00001 ------ 1

No-one is ever going to run the SEND_EMAIL in ACS's RSS. They are going to do it in a program, probably a RPG program. That could look like this:

01 **free 02 dcl-s ReturnCode int(3) ; 03 exec sql SET :ReturnCode = 04 SYSTOOLS.SEND_EMAIL( 05 TO_EMAIL => 'simon@email.com', 06 SUBJECT => 'Test 4', 07 BODY => 'This is a test with attachments in a RPG', 08 ATTACHMENT => '/home/MyFolder/text_file_1.txt, /home/MyFolder/text_file_2.txt' ) ; 09 dsply ('Return code = ' + %char(ReturnCode)) ; 10 *inlr = *on ;

Line 2: I am defining the variable I am going to use for the returned Return Code as an integer.

Lines 3 - 8: I am using the SQL Set statement so that the return code from SEND_EMAIL will be placed in the RPG variable for the Return Code.

Line 8: This is the only line that is different from my previous examples. I have listed the two files I want to attach to the email on separate lines.

When I execute this statement the following error is returned:

Message: [SQL0438] Integrated File System File Not Found.

IMHO the message is at best cryptic. I guess that the program that the scalar function calls cannot handle the CRLF character that is at the end of the first line of attachments.

How to overcome this? How about concatenating the two attachment file names together. Will that work?

01 **free 02 dcl-s ReturnCode int(3) ; 03 exec sql SET :ReturnCode = 04 SYSTOOLS.SEND_EMAIL( 05 TO_EMAIL => 'simon@email.com', 06 SUBJECT => 'Test 4', 07 BODY => 'This is a test with attachments in a RPG', 08 ATTACHMENT => '/home/MyFolder/text_file_1.txt,' || '/home/MyFolder/text_file_2.txt' ) ; 09 dsply ('Return code = ' + %char(ReturnCode)) ; 10 *inlr = *on ;

Line 8: This is the only difference from the previous example. Here each file name is treated as its own string, starts and ends with the apostrophe ( ' ), and they are joined by a concatenation. The concatenation is denoted by the double pipe symbol ( || ) short cut.

When called this program displays the following:

Send E-mail Successful. DSPLY Return code = 1

Which means that the email was sent.

I could also use a RPG variable for the list of attachments:

01 **free 02 dcl-s ReturnCode int(3) ; 03 dcl-s Attachments varchar(1000) ; 04 Attachments = '/home/MyFolder/text_file_1.txt,+ 05 /home/MyFolder/text_file_2.txt' ; 06 exec sql SET :ReturnCode = 07 SYSTOOLS.SEND_EMAIL( 08 TO_EMAIL => 'simon@email.com', 09 SUBJECT => 'Test 4', 10 BODY => 'This is a test with attachments in a RPG', 11 ATTACHMENT => :Attachments) ; 12 dsply ('Return code = ' + %char(ReturnCode)) ; 13 *inlr = *on ;

The differences in this program compared to the earlier ones are:

Line 3: This variable will contain the list of IFS files to attach to this email.

Lines 4 and 5: I am populating the variable Attachments with the paths of the IFS files.

Line 11: Here I am using the RPG variable Attachments in the ATTACHMENT parameter.

When I call this program the following is displayed:

Send E-mail Successful. DSPLY Return code = 1

Which means that the email was successfully sent.

The ability to have up to twenty recipients and up to ten attachments is an especially useful addition to this scalar function. Making it more like the Send SMTP email command, SNDSMTPEMM.

 

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

 

This article was written for IBM i 7.5 TR4 and 7.4 TR10.