TRY IT FREE FOR 30 DAYS
Full version  •  Quick installation  •  No spam

# Tech TalkLIGHTLY TECHNICAL OBSERVATIONS ON PEOPLESOFT, SQR, AND RELATED AND UNRELATED TOPICS

## Archives

 Sending Email from SQR KEVIN RESCHENBERG05-02-2007

Sending email through SQR is a common requirement. This should be easy, but it's often a little challenging. Today I'd like to give a couple of tips that should help you get past the problems.

If you are sending email from Unix without attachments, it's pretty simple—just call mailx, passing the body of the email (as a file) to mailx. If you want to include an attachment, it's similar—pass both the body and attachments. Try this—all on one line (build the command in a $variable and then do CALL SYSTEM):  (cat /path/to/body/file;uuencode /path/to/attachment /path/to/attachment) | mailx -s "Subject of email" -r sender@somewhere.com recipient1@somewhere.com recipient2@somewhere.com  Note that the attachment file is specified twice. (Google "mailx" for the details.) Under Windows, there are various options. Some people use BLAT.EXE, although I have never used it. There are probably many other possibilities. But today I'll just discuss the quirks of the PeopleSoft-delivered program, PSMAIL.EXE. PSMAIL accepts its parameters in two alternative ways. You can pass the email addresses, subject, etc. on the command line; or you can pass in one file that contains everything. I think that is the easiest and safest method ("safe" referring to the issue of line breaks). In general, you need two lines in a .BAT file which is passed to CALL SYSTEM. The first line is an environment variable SET, and then the next line is the call to PSMAIL:  SET PS_SERVER_CFG=\path\to\psprcs.cfg \path\to\psmail.exe /INPUT"\path\to\inputfile.txt" > \path\to\output\log.txt  Then execute the .BAT file in the usual way, under a separate command processor:  let$Command = 'cmd /c \path\to\bat\file.bat'
call system using \$Command #Status


Now to go back over a few details. First, the input file has a specific format. The contents should look like this:

   -TO"recipient@somewhere.com"
-FROM"sender@somewhere.com"
-SUBJECT"Subject of email"
-BODY"This is the
text to go into the email."
-FILE"\path\to\attachment\file"


Simple enough. Are we done? Not quite. There are a couple of quirks that I've seen at multiple installations, and some kludges that can get us past them.

First, the input file must be in Unicode format. You could fiddle with your SQR.INI settings to produce a Unicode file. But if you don't want to do that, or it doesn't work, try the clever trick posted by Brian Taylor here. Basically you can convert a regular text file to Unicode by doing a CALL SYSTEM with this command:

   cmd /c /u type \path\to\ascii\file > \path\to\new\unicode\file


That makes a Unicode copy of your file.

If you are sending only one email in your program, that may be everything you need to make it happen. But if you want to send multiple emails, another problem comes into play: There is some sort of timing issue. Each execution of PSMAIL must complete before you try another one, or only one of the emails will work and the rest will disappear. The process seems to take roughly 1 second, so the SQR must wait. For that, I suggest creating a separate file at the end of the process and then watching for that file to appear. You can do that by adding something like this to the end of the .BAT file:

   echo done > \path\to\flag_file.txt


This simply creates a file (flag_file.txt) containing the word "done". The contents don't matter. We just want to wait until the file is created, because this lets us know that PSMAIL has finished its work.

How do we tell SQR to wait? We could loop, calling EXISTS('\path\to\flag_file.txt') repeatedly until it returns a value of 0, indicating that the file was found. But there are two problems with that simple approach. First, if there was an error in the .BAT file, the file may never be created and the SQR would loop forever. The second problem is that looping on file system I/O like this can cause SQR to use a lot of your server's CPU time. I did a quick test and found the CPU spiking up to around 50% on one server during this loop. One way to avoid this is to have SQR do some SQL. That forces it to yield the CPU and wait. What SQL can be used? Well, you could do a SELECT to get the current time from the database. That's a very easy thing for the database server to provide, and it relieves the server SQR is running on. Use that opportunity to check for a timeout condition. For example, you could allow this loop to run no more than 2-3 seconds before ending and reporting an error. In this way, your program can wait long enough for the email to be sent, but not forever, and with almost no impact on any of the servers.