FW: [sqr-users] PDF Files created by SQR

["Penny J. Heeren" <PHeeren@newtonmfg.com>]

Actually this is pretty similar to how we USED to send our emails using
UUENCODE. We didn't have anything hard-coded, but would instead populate
everything from various values in tables or based on certain logic.
However, we were having issues when the attachments weren't always
getting the 'close' (crlf . crlf ?) indication, so the recipients email
server kept waiting for the last piece of the email to indicate that it
had the whole file and would time out. Wouldn't happen all of the time,
but about 10% of the time we would get a response back from the other
email server that the email 'timed out' and did not get delivered after
a couple of days. Since then, we simply created a text file with the
header and body of the email and a line 'attachement file:' with it's
path. And the ASP email program we're using opens the file, and creates
the email with the attachment and sends it. Now we've got about a 99%
success rate with only a few issues with a 'corrupt' PDF.

-----Original Message-----
From: Hins, Thomas D. (HSC) [mailto:Thomas-Hins@ouhsc.edu] 
Sent: Wednesday, August 04, 2004 3:59 PM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: RE: [sqr-users] PDF Files created by SQR


I just wrote an SQR and from Unix I email files to different users.  The
trouble is I hardcoded the email addresses, I didn't like that but I
would have liked to make a small table and load the filename, email
address to receive it and retrieve it from there each time but didn't
take the time to do that.  That way I could just update the table and
not change the sqr, add recepients if I want etc.
Here is the email code I used.  I'll probably change it in the future to
use a table to get who receives the different files.  You are also
suppose to be able to have a subj line, etc and I found I could have a
subject line and no attachment or an attachment and no subject line,
played around for awhile and couldn't get both to work if someone else
has experience and wants to share that would be appreciated. Positive
changes are also invited, code below is subject to your own testing for
your environment...   

Begin-REPORT
  do Init-DateTime
  do Get-Current-DateTime
  do STDAPI-Init
  Let $ReportID = 'OUFNDCK'
  do Open-File-Logic
  do Init-REPORT
  do Get-Rpt-Dates
  do Process-Main
  do stdapi-term     
  Display $Asoftoday
  Close 10  
  Close 20
  Close 30
  Do Email-File
  do Reset

end-REPORT

begin-procedure Init-Report

 ! Let $filedate =
SUBSTR($Asoftoday,1,2)||substr($Asoftoday,4,3)||substr($Asoftoday,8,4)
  Let $File1 = $ftpout||'badsprd.dat'
  let $File2 = $ftpout||'Badempearns.dat'
  let $File3 = $ftpout||'Badgrants.dat'
   #debug display $file1
   SHOW 'OUTFILE IS ' $FILE1
   Open $File1 as 10 for-writing record = 120:vary 
   Open $File2 as 20 for-writing record = 187:vary
   Open $File3 as 30 for-writing record = 187:vary
   
  if $prcs_process_instance = ''
     ! No Prompt
  end-if
  
  
end-procedure


Begin-Procedure Email-File
   IF #Inactivated > 0
       let $system = 'uuencode '|| $FILE1 ||' '|| $FILE1 ||'| mail
xxx1@xxxx.edu'
       show 'system variable is ' $system 
       CALL SYSTEM USING $system #unix_status 
        let $system = 'uuencode '|| $FILE1 ||' '|| $FILE1 ||'| mail
xxx2@xxxx.edu'
       show 'system variable is ' $system 
       CALL SYSTEM USING $system #unix_status 
   End-if
   IF #BAD_EMP_EARNS > 0
      let $system = 'uuencode '|| $FILE2 ||' '|| $FILE2 || '| mail
xxx1@xxxx.edu'
      show 'system variable is ' $system 
      CALL SYSTEM USING $system #unix_status
      let $system = 'uuencode '|| $FILE2 ||' '|| $FILE2 || '| mail
xxx2@xxxx.edu'
      show 'system variable is ' $system 
      CALL SYSTEM USING $system #unix_status
       let $system = 'uuencode '|| $FILE2 ||' '|| $FILE2 || '| mail
xxx3@xxxx.edu'
      show 'system variable is ' $system 
      CALL SYSTEM USING $system #unix_status
       let $system = 'uuencode '|| $FILE2 ||' '|| $FILE2 || '| mail
xxx4@xxxx.edu'
      show 'system variable is ' $system 
      CALL SYSTEM USING $system #unix_status
   End-if 
   IF #Bad_Emp_Grants > 0
      let $system = 'uuencode '|| $FILE3 ||' '|| $FILE3 || '| mail
xxx1@xxxx.edu'
      show 'system variable is ' $system 
      CALL SYSTEM USING $system #unix_status
      let $system = 'uuencode '|| $FILE3 ||' '|| $FILE3 || '| mail
xxx2@xxxx.edu'
      show 'system variable is ' $system 
      CALL SYSTEM USING $system #unix_status
      let $system = 'uuencode '|| $FILE3 ||' '|| $FILE3 || '| mail
xxx3@xxxx@ouhsc.edu'
      show 'system variable is ' $system 
      CALL SYSTEM USING $system #unix_status
       let $system = 'uuencode '|| $FILE3 ||' '|| $FILE3 || '| mail
xxx4@xxxx.edu'
      show 'system variable is ' $system 
      CALL SYSTEM USING $system #unix_status
   End-if 

Tom Hins
Information Technology - Application Solutions
University of Oklahoma Health Sciences Center
P.O. Box 26901
Oklahoma City, OK  73190
Phone:(405) 271-2262 Ext. 50213
Fax: (405) 271-2352
(The information transmitted is intended only for the person or entity
to which it is addressed and may contain confidential and/or privileged
material. If you are not the intended recipient of this message you are
hereby notified that any use, review, retransmission, dissemination,
distribution, reproduction or any action taken in reliance upon this
message is prohibited. If you received this in error, please contact the
sender and delete the material from any computer. Any views expressed in
this message are those of the individual sender and may not necessarily
reflect the views of the company.)



-----Original Message-----
From: sqr-users-bounces+thomas-hins=ouhsc.edu@sqrug.org
[mailto:sqr-users-bounces+thomas-hins=ouhsc.edu@sqrug.org]On Behalf Of
Penny J. Heeren
Sent: Wednesday, August 04, 2004 3:45 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] PDF Files created by SQR



We are creating several PDF files from various SQR programs with the
following code section:

  declare-report report7          ! Image file report .pdf format
    printer-type=pd
    layout=layout7
  end-declare

This file is then picked up by another process, and emailed to a
recipient. Occasionally, the file received by the user is unable to be
opened by Adobe Reader, with an error "Unable to open file, file has
been corrupted". If we rerun the SQR program to generate the file again,
the emailed file works this time. Since my manager has handled this in
the past and not gotten me involved, I haven't had a chance to look at
the file before it gets rewritten to see if it's corrupted on our server
before it goes out in the email or if it's corrupted by the email/by
sending the email/by receiving the email. I've made sure he knows that I
want to see the file before it's replaced the next time this happens, as
it's entirely possible this isn't an SQR issue, but an email issue...
however, he wanted me to ask for feedback from the user group in case
someone else has encountered this.

Additionally, if the recipient has a Yahoo mail account, that software
treats the attached file as part of the body and displays the binary
data in the body of the file. I'm 99.9% certain that has nothing to do
with SQR nor the content of the file, but the way the file is attached.
It's been a problem for some other email programs, but, for example,
works fine consistently on Hotmail. The program that actually identifies
that there is a file to be emailed and generates the email is a custom
application built by a coworker using ASP running on Windows 2000, and
interfaces with Microsoft Exchange to create the email from data stored
in a flat text file that contains sender, recipient, subject, body, and
if there is an attachment, the file path for the attachment file.

SQR version is 8.0, running on RedHat Advanced Server Linux box.

Has anyone else had issues like this or any ideas of a better way to do
this? 

Penny Heeren
DBA/Programmer Analyst
Newton Manufacturing Co
e-mail: pheeren@newtonmfg.com
phone: 641-791-4242

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users