AS/400 Embedded SQL Dates

1380 pts.
Tags:
AS/400 SQLRPGLE Jobdate
Our normal QDATFMT is *MDY.  (Arrrgh!!!)

I am running an SQLRPGLE that does this:

**----Set Option must be first code in program.----------------* /free                                                               EXEC SQL SET OPTION                                                          DatFmt     = *ISO,                                              Commit     = *NONE,                                             CloSqlCsr  = *ENDMOD;                          /end-free                                            


The SQLRPGLE builds a file which is copied in the clp to the IFS as a CSV (excel) file and then the CSV is emailed (using our MFG software package commands).

This program/application runs fine interactively.  I am trying to submit it with a self submitting clp. The job is submitted, the file is built, I have data on the IFS.  The CSV is not emailed due to a message from the canned software (which we do not have source for this program).

From what I see in the joblog.

RTVJOBA DATFMT(&FMT)

RETURN

CPF3C17    Message . . . . :   Error occurred with input data parameter.                   Cause . . . . . :   An error occurred while copying information from the input    data parameter. Recovery  . . . :   Do one of the following and try the         request again: -- Verify that the input data parameter is correctly             specified. -- Verify that the value for the length of data parameter is         valid.                                                                       

I do not see this message if I run the program interactively.  I am wondering if the SQL set option for the date format stays with the job when the program ends.  But that does not make sense either.  If it was a case of the date being in a different format than the software expects, I would get that message when I run interactively.

 



Software/Hardware used:
AS400 SQLRPGLE

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    First: I am running an SQLRPGLE that does this: Then: The SQLRPGLE builds a file which is copied in the clp... Finally: This program/application runs fine interactively. I am trying to submit it with a self submitting clp. How many CL programs (or modules, I suppose) are involved? Does the RPG call into the CL? Or does a CL call into the RPG? Are there two CL programs (modules?)? Exactly what is "submitted"? Can we see the actual SBMJOB as well as the parameter definitions from whatever is called in the submitted job? Tom
    125,585 pointsBadges:
    report
  • NickHutcheson1
    I have decided that the problem is the email address which is picked up from a file and passed along the way as the programs are called. Somehow, somewhere, it is getting a HEX value. Message . . . . : 9800 - UTEMAIL ADRFR(X'D5C8E4E3C3C8C5E2D6D57CD7D9C1E7C9E2C3D6D4D7C1D5C9C5E24BC3D6D440404000 00000000000000000000000000000000') ADRTO(X'D5C8E4E3C3C8C5E2D6D57CD7D9C1E7C9E2C3D6D4D7C1D5C9C5E24BC3D6D440404000 00000000000000000000000000000000') SUBJECT(' Warranty Reporting') MESSAGE('Warranty Reporting Tool') ATTACH('/HOME/WTRTPRTR.CSV') If I hard code the address it works and the joblog shows my email address instead of the HEX representation. CLP1 builds a temp lib, overrides files to the temp lib, calls RPG1. RPG1 gets the email address from a file, displays a prompt screen, does some other calls to build work files, calls CLP2 passing the templib, emailadr. CLP2 checks the job atribute for interactive/batch. If interactive, sbmjob cmd(call clp2 parm &templib &emailadr). Perform overrides to the templib, call sqlrpgle (no parms). sqlrpgle builds a file. CLP2 copies the built file to the IFS. Rn the Email command. How many CL programs (or modules, I suppose) are involved? Does the RPG call into the CL? Or does a CL call into the RPG? Are there two CL programs (modules?)? CLP - 2 outside of the package program to email. RPG - 16 program flow above. First clp calls two rpg's. Second RPG calls 15 different RPG's-one of which is the self submitting CLP. Self submit calls the SQLRPGLE, then the CLP to email.
    PGM        PARM(&TEMPLIB &IEMAIL)
    DCL        VAR(&TEMPLIB) TYPE(*CHAR) LEN(10)  
    DCL        VAR(&IEMAIL) TYPE(*CHAR) LEN(50)   
    
    RTVJOBA    TYPE(&TYPE)                                     
    IF         COND(&TYPE *EQ '1') THEN(DO)     
                   SNDPGMMSG  MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA(&IEMAIL) +
                  TOPGMQ(*EXT)                               
    SBMJOB     CMD(CALL PGM(WTRT02C) PARM(&TEMPLIB +           
                 &IEMAIL)) JOB(WTRT02C) JOBQ(QBATCH) LOG(4 +   
                 0 *SECLVL) LOGCLPGM(*YES)                     
                                                               
    RETURN                                                     
    ENDDO           
    OVRDBF  blah blah
    call SQLRPGLE
    CPYTOIMPF
    UTEMAIL    ADRFR(&IEMAIL) ADRTO(&IEMAIL)
    
          
    I have a slight rememberance of something like this getting me years ago. Something about bouncing from clp to rpg to clp to rpg,,,, screws up parms. In the interactive step, sndbrkmsg, the &iemail is correct. Nick
    1,380 pointsBadges:
    report
  • NickHutcheson1
    report
  • NickHutcheson1
    opps, here is the article. http://wiki.midrange.com/index.php/Parameter_passing http://wiki.midrange.com/index.php/Parameter_passing
    1,380 pointsBadges:
    report
  • TomLiotta
    DCL VAR(&IEMAIL) TYPE(*CHAR) LEN(50) SBMJOB CMD(CALL PGM(WTRT02C) PARM(&TEMPLIB + &IEMAIL)) At least part of the problem is the combination of those two lines. The &IEMAIL variable will only be reliable when there are exactly 50 significant characters in the value. If there are any trailing blanks beyond byte (32), their values will be unpredictable. When a job is submitted, it has a totally separate address space. There can be no pointer that connects back to the variable in the submitting program such as happens with a non-submitted CALL. The submitting job might not even exist any more by the time the submitted job starts. The CMD() parameter of SBMJOB has no way of communicating the data attributes of parms for any CALL command since CALL has no strong typing of its PARM values, so the system must use default definitions that are based in the values of the parms -- for totally numeric parms, the default definition is *DEC (15 5); and for everything else it's *CHAR (32) or *CHAR (n) where 'n' is the number of significant characters when there are more than 32. So, if the value is 'ABCDE', then the value is stored in a *CHAR (32) temporary variable padded with blanks. If you try to access memory beyond those 32 bytes, you'll get whatever was in those bytes effectively at random. When your WTRT02C program goes after 50 bytes, it gets whatever it gets at the end of the variable. You can create a *CMD object to use in place of the CALL command. By doing that, you have a way of passing PARM() information to the submitted job. A *CMD almost acts like a "prototype" for CL. Or you can construct the RQSDTA() parameter instead of using the SBMJOB CMD() parameter. By doing that, you build the actual request message that will include all trailing blanks between quote marks where your &IEMAIL parm would go. Passing a fully-quoted value will cause space to be reserved for the entire length. Or you can pass a new variable defined as *CHAR (51). Put &IEMAIL into the first 50 bytes and some non-blank character into the last byte to force the significant characters to be at least one more than your maximum length. That's a minor hack, but it's usually easy. Tom
    125,585 pointsBadges:
    report
  • NickHutcheson1
    Thanks Tom. N ick
    1,380 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following