Create CL program Update in AS/400

150 pts.
Tags:
AS 400
CL Program
DB2
Dear All,
I am learning to write CL program in AS/400 for DB2, maybe call new programmer.
I have a file1 include ( field 1 ->10) to have value as:
Field 1 Field 2 Field 3 Field 4 Field 5 Field 6 Field 7 Field 8 Field 9
112118 P I 1 A B C D E
I want to create CL program update file 1 in AS/400
to change field 3 = Null
with condition Enduser key field 1 = 112118 and field2 = P
Then result in File1 as:
Field 1 Field 2 Field 3 Field 4 Field 5 Field 6 Field 7 Field 8 Field 9
112118 P 1 A B C D E
Please help me.
Thanks all.
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

CL programs are “control language” they are not designed nor have the flexibility to add or update individual records in a file. You are better off using RPGLE or SQL to do the task. The code will be much cleaner. 

Discuss This Question: 18  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.
  • Splat
    From what you've described I'd recommend using SQL.  

    CL/CLLE is somewhat limited as it was never intended to manipulate data at a discrete level.
    12,915 pointsBadges:
    report
  • Hvphong0311
    Thanks ToddN2000,
    If I using Code SQL, it's ok but Enduser don't use, I want to write CL after I will into functional for Enduser input to update.
    Again Thanks for your advice
    150 pointsBadges:
    report
  • Hvphong0311
    Thanks Splat! Please can you tell me, It still write CL for this situation?
    150 pointsBadges:
    report
  • Splat
    I believe you can use CL/CLLE to update data in a file. But again, it's really not something I'd recommend.

    Is there a particular reason not to write a CL/CLLE program that executes SQL code?
    12,915 pointsBadges:
    report
  • ToddN2000
    As far as I know, native CL/CLLE cannot update data in a record directly. You can things like data areas because they are stand alone objects. You can read records from a file with the RCVF command but you cannot update that data after it's read or delete that specific data  record.
    134,845 pointsBadges:
    report
  • Splat
    ToddN2000, I thought I'd read somewhere that it could be done. My mistake.
    12,915 pointsBadges:
    report
  • Hvphong0311
    Thanks Splat and ToddN2000,
    In the case I dont use CMD SQL to update, Can you show me hoe to create a PRGLE to update
    Thanks So much
    150 pointsBadges:
    report
  • Splat
    Hvphong0311, post your code.
    12,915 pointsBadges:
    report
  • Hvphong0311
    Dear Splat,
    This is my Code but it not work when I compile 
    please you see my code and help me

    ==============
     PGM
     DCL VAR(&fieldday) TYPE(*CHAR) LEN(6)

     DCLF FILE(File1)
     CHGVAR VAR(&fieldday) VALUE('ddmmyy')

     OVRDBF FILE(file1) +
                 POSITION(*KEYAE 1 *N &fieldday) +
                 OVRSCOPE(*CALLLVL)

      RCVF
      IF COND(&field1 = &fieldday) THEN(+
           RUNSQL SQL('UPDATE file1 +
                          SET field3 = '' '' +
                       WHERE field1 = ''' || &fieldday || ''' ') +
                    COMMIT(*NC))
      ENDPGM
    ==============
    Thanks So much
    150 pointsBadges:
    report
  • ToddN2000
    Are you getting any error messages? Have you tried debugging the program?
    134,845 pointsBadges:
    report
  • ToddN2000
    @Hvphong0311: Why even use the RCVF? why not just use something like this. The test is being done inside the SQL statement.

    PGM
    
    DCL VAR(&fieldday) TYPE(*CHAR) LEN(6)
    
    CHGVAR VAR(&fieldday) VALUE('ddmmyy')
    
    RUNSQL SQL('UPDATE file1 +
           SET field3 = '' '' +
           WHERE field1 = ''' || &fieldday || ''' ') +
           COMMIT(*NC))
    
    ENDPGM
    134,845 pointsBadges:
    report
  • Hvphong0311
    Dear ToddN2000,
    Thanks so much, I will try it

    150 pointsBadges:
    report
  • Hvphong0311
    Dear ToddN2000
    it still error,
    121753      SQL9010             30        ESC     RUNSQLSTM or RUNSQL com QSQSCHEM         1876        KKINUSER         0013     
                                                      mand failed.                                                                   
    121753      CPF9999             40        ESC     Function check. SQL9010 QMHUNMSG         0000        KKINUSER         0013     
                                                       unmonitored by KKINUSE                                                        
                                                      R at statement 400, ins                                                        
                                                      truction X'0013'.                                                              
                                                                Variables                                                            
    Variable               Type        Length         Value                          Value in Hexadecimal                            
                                                       *...+....1....+....2....+     * . . . + . . . . 1 . . . . + . . . . 2 . . . . 
    &NGAYPO                  *CHAR            6       '      '                       404040404040                          
    Please help me to fix it
    Thanks so much
    150 pointsBadges:
    report
  • Hvphong0311
    Dear ToddN2000,
    It still error, this is message SQL9010 received by KKINUSER at 400
    Thanks So much
    150 pointsBadges:
    report
  • ToddN2000
    Try adding the library before the file name and it may not work if the file has journaling turned on.
    134,845 pointsBadges:
    report
  • TheRealRaven
    Since V5R4, ILE CL can access/update database files essentially the same as ILE C, i.e., by calling the same library functions (procedures). That makes it difficult to assert unambiguously the CL can't do database operations.

    Of course, calling those procedures successfully requires a step beyond the understanding needed to call them from C.
    36,095 pointsBadges:
    report
  • Hvphong0311
    Thanks TheRealRaven
    Dear ToddN2000,
    VAR(&fieldday) TYPE(*CHAR) LEN(6) => this is keyvalue of Enduser input, But in database field1(field type = open). Can you show me how to convert to same same
    Thanks So much
    PS: pls, can you give me your email.
    150 pointsBadges:
    report
  • TheRealRaven
    @Hvphong0311:

    In this case, it seems that the problem isn't a capability for database I/O. But rather the problem is that CL has no concept of FOR MIXED DATA fields (assuming that that's what you mean by "open"). After all, CL is a process 'Control Language' and not a database language.

    I suppose you could try embedding the usual <shift-in>/<shift-out> characters. I've never attempted to use CL for this, so I can't guess results.
    36,095 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.

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

Following

Share this item with your network: