| Sending Email from SQR | KEVIN RESCHENBERG
05-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.
|
