ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

the CL command SNDSMTPEMM

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    You haven't said how the EDIPF.xls file was created. Being in QTEMP, this is a physical file and not an xls file. Maybe you have created a flat file with the necessary data to make it an xls file, but I would think that is unlikely. Xls files would normally be created in the /Root file system as they're not database files.
    I failed to mention that EDIPF.xls is something I made up from searching online, which obviously seem to be wrong, so we can ignore this.

    It seems like that may be a better option to send the email as it will automatically convert a PF to a csv file for emailing which I'm guessing is what you are really trying to do. The ID sending the email though will still need to be enrolled in the system distribution directory via e.g. WRKDIRE.
    Using this EMAIL command is not an option as it's a custom command for a different client. you say that QTEMP is exactly where I should put this file, so how do I get it out of QTEMP and into a file which can be sent out as an attached file in an email??? From what I gathered here, it is a 2 step process, first: use CPYTOIMPF to convert(?) to a import file, then send it out using SNDDST or SNDSMTPEMM? My mentor has not seen the latter and advised me to use SNDDST, but I think the latter is a more appropriate option for my case, no? In either case, I am not sure what to put in for the arguments...

    following RDKells instructions, I have:
    Then you need a file with data in, i.e; RAREPUSRW2: for me this would be EDIPF?
    Next you need a location on the IFS to copy this file to, earlier in the program I build an IFS location/File Name (&IFSP), i.e; \reports\userreports\inactiveusers201708.csv: not sure how I "build" a location. Do I just set a variable equal to a path?
    Copy the iSeries file to the IFS; this part is where you indicate the stream file? TOSTMF()

    any help would be very appreciated!!!

    Comment


    • #17
      Ok, as far as I understand your issue is that you want to e-mail a file from QTEMP as an excel file?

      I don't know how to create an .xls file, but a .csv file would be;

      You need an IFS directory to put the data, I don't know how your system is setup and there are a hundred ways to do this, I usually just do it through windows explorer but you can do it on the iSeries via;

      Code:
      CRTDIR DIR('\reports\rdkells\myreports')
      (you may need to change the defaults etc. on this command)

      Now you want to copy the QTEMP iSeries file into the IFS directory you just created - this also creates the file with the name you specify and is done via the CPYTOIMPF command;

      Code:
      CPYTOIMPF FROMFILE(QTEMP/EDIPF) +
      TOSTMF('\reports\rdkells\myreports\edipf.csv') +
      MBROPT(*REPLACE) FROMCCSID(285) +
      STMFCCSID(*PCASCII) RCDDLM(*CRLF) +
      RMVBLANK(*BOTH)
      (again; the options selected by this command may not be relevant to you)

      What you should have now is a file called edipf.csv sitting in \reports\rdkells\myreports\

      Then just e-mail the file via;

      Code:
      SNDSMTPEMM RCP(('RDKells@foobar.com')) SUBJECT('An Email')
      ATTACH(('\reports\rdkells\myreports\edipf.csv' *PLAIN *BIN))

      I know it's not exactly what you're after, maybe others know how to generate a .xls file from an iSeries file, but hopefully it gives you an idea of how to go about using SNDSMTPEMM to send stuff.

      Comment


      • #18
        John Sev99 said: "It is local to that job only, so only it can see/access the contents."

        Not entirely correct - do a WRKJOB on any job on the system, then option 13, then place a 5 next to the QTEMP library entry...you can certainly see the contents of QTEMP for that job.

        Cheers,

        Emmanuel

        Comment


        • #19
          Originally posted by RDKells View Post
          Ok, as far as I understand your issue is that you want to e-mail a file from QTEMP as an excel file?

          I don't know how to create an .xls file, but a .csv file would be;

          You need an IFS directory to put the data, I don't know how your system is setup and there are a hundred ways to do this, I usually just do it through windows explorer but you can do it on the iSeries via;

          Code:
          CRTDIR DIR('\reports\rdkells\myreports')
          (you may need to change the defaults etc. on this command)

          Now you want to copy the QTEMP iSeries file into the IFS directory you just created - this also creates the file with the name you specify and is done via the CPYTOIMPF command;

          Code:
          CPYTOIMPF FROMFILE(QTEMP/EDIPF) +
          TOSTMF('\reports\rdkells\myreports\edipf.csv') +
          MBROPT(*REPLACE) FROMCCSID(285) +
          STMFCCSID(*PCASCII) RCDDLM(*CRLF) +
          RMVBLANK(*BOTH)
          (again; the options selected by this command may not be relevant to you)

          What you should have now is a file called edipf.csv sitting in \reports\rdkells\myreports\

          Then just e-mail the file via;

          Code:
          SNDSMTPEMM RCP(('RDKells@foobar.com')) SUBJECT('An Email')
          ATTACH(('\reports\rdkells\myreports\edipf.csv' *PLAIN *BIN))

          I know it's not exactly what you're after, maybe others know how to generate a .xls file from an iSeries file, but hopefully it gives you an idea of how to go about using SNDSMTPEMM to send stuff.
          The process is very clear to me now, thank you!!!! Did you mean to use '\' instead of '/' in your path names?

          so for me, am I correct in doing this?
          Code:
          CRTDIR DIR('/ISE/EDIPF')   <-- I made a PF in ISE library called EDIPF which has the same fields as the QTEMP/EDIPF
          
          CPYTOIMPF  FROMFILE(QTEMP/EDIPF) +           
                                  TOSTMF('/ISE/EDIPF/edipf.csv') +
                                  MBROPT(*REPLACE)                
          
          
          
          SNDSMTPEMM RCP((&EMAIL)) SUBJECT(&SUBJECT1) +
                                       ATTACH(('/ISE/EDIPF/edipf.csv'))

          Comment


          • #20
            Yes, you should use "/" instead of "". Backward slashes are a windows thing, you should use proper forward slashes for directory (or folder if you want to speak windows) names. I have in the past seen many posts from people asking how to delete a file with a name like "x\y" because the system has taken the backward slash as part of the file name and not a directory name.

            Code:
            CRTDIR DIR('/ISE/EDIPF')   <-- I made a PF in ISE library called EDIPF which has the same fields as the QTEMP/EDIPF
            You don't need to create this and you shouldn't. A csv file is a plain text file not a DB file. The CPYTOIMPF command will automatically create the file.

            Code:
            CPYTOIMPF  FROMFILE(QTEMP/EDIPF) +           
                                    TOSTMF('/ISE/EDIPF/edipf.csv') +
                                    MBROPT(*REPLACE)
            You should use the code RDKells posted though I'm uncertain about the FROMCCSID parameter mentioned. It may be better to leave that as the default *FILE unless the file has a CCSID of 65535 in which case you will need to specify the encoding you are using. You may also want to change the RMVBLANK parm as well depending on whether you want to retain leading and trailing spaces (normally trailing spaces would be removed). e.g.:
            Code:
            CPYTOIMPF FROMFILE(QTEMP/EDIPF) +
            TOSTMF('/ISE/EDIPF/edipf.csv') +
            MBROPT(*REPLACE) +
            STMFCCSID(*PCASCII) RCDDLM(*CRLF) +
            RMVBLANK(*TRAILING)
            I've not used the SNDSMTPEMM command but looks alright remembering the issue where the user that is sending the email needs to be enrolled in the system distribution directory.
            Last edited by john.sev99; August 27, 2017, 03:35 PM.

            Comment


            • #21
              I am getting a Error number 3025 for "No such path or directory" error on the CPYTOIMPF command, meaning that /ISE/EDIPF/edipf.csv does not exist?
              also, dumb me included EDIPF which is actually not a path, it is a PF, so I took it out.
              Code:
              CPYTOIMPF FROMFILE(QTEMP/EDIPF) +  
              TOSTMF('/ISE/edipf.csv') +                  
              MBROPT(*REPLACE) +                
              STMFCCSID(*PCASCII) RCDDLM(*CRLF) +
              RMVBLANK(*TRAILING)
              here is a part of my job log for the code above
              Code:
              The query access plan has been rebuilt.          
              Arrival sequence access was used for file EDIPF.  
              Query options used to build the query access plan.
              **** Ending debug message for query .            
              ODP created.                                      
              Blocking used for query.                          
              Cursor SQLCURSOR000000003 opened.                
              ODP deleted.                                      
              Cursor SQLCURSOR000000003 was closed.            
              File system error occurred.  Error number 3025.  
              Connection to relational database S106D1BT ended.
              DISCONNECT completed.                            
              Copy command ended because of error.
              I also tried
              Code:
              PYTOIMPF FROMFILE(QTEMP.LIB/EDIPF.FILE) +  
              TOSTMF('/ISE/edipf.csv') +  
              MBROPT(*REPLACE) +                
              STMFCCSID(*PCASCII) RCDDLM(*CRLF) +
              RMVBLANK(*TRAILING)
              because according to the IBM website, it has to be in the name.object-type format...
              then my job log looks like this.
              Code:
              ODP created.                                                
              Blocking used for query.                                    
              Cursor SUB_MAIL_CSR opened.                                  
              Data conversion required on FETCH or embedded SELECT.        
              1 rows fetched from cursor SUB_MAIL_CSR.                    
              Data conversion required on FETCH or embedded SELECT.        
              1 rows fetched from cursor SUB_MAIL_CSR.                    
              1 rows fetched from cursor SUB_MAIL_CSR.                    
              1 rows fetched from cursor SUB_MAIL_CSR.                    
              Row not found for SUB_MAIL_CSR.                              
              Ownership of object QCPEXTEMPS in QTEMP type *USRSPC changed.
              File EDIPF.FILE in library QTEMP.LIB not found.              
              Copy command ended because of error.
              also for this code, higher in my job log actually says "45 rows inserted into EDIPF in QTEMP" whereas the first code does not say that...

              Comment


              • #22
                I don't know what I did, but it no longer throws this error.
                I did wrklnk '/ISE/edipf.csv' and it contains the correct content of QTEMP/EDIPF in csv form!

                edit: I know what I did. I created the directory /ISE/EDIPF via CRTDIR!

                Comment


                • #23
                  Yep, as you've found, the directory structure needs to exist first.
                  I'm not sure if you're aware (you may be as you found the error description) but for the error numbers returned (3025 in this case), you can use DSPMSGD CPE<message number> to display the system error code. As you've noted CPE3025 shows "No such path or directory."
                  Last edited by john.sev99; August 28, 2017, 03:06 PM.

                  Comment


                  • #24
                    Apologies, I didn't mean to use '\', I did meant to use '/' but I copied the location from Windows explorer initially and then re-used it for the other examples... oops.

                    I've not seen anyone define the iSeries libraries/files with extensions before, within a non-ftp environment, I would just do it as;

                    Code:
                    CPYTOIMPF FROMFILE(QTEMP/EDIPF) +
                              TOSTMF('/ISE/edipf.csv') +  
                              MBROPT(*REPLACE) +
                              STMFCCSID(*PCASCII) RCDDLM(*CRLF) +
                              RMVBLANK(*TRAILING)
                    As mentioned, the parameters I chose in my example might not be relevant to you, I put a from CCSID of 285 because sometimes I find the output is a load of random symbols - specifying 285 (which is a UK code page) resolved this issue and also didn't affect working files so I just left it in place.

                    Comment

                    Working...
                    X