Integrat all SQL + CL + SQL Variable in one program - IBM AS400

This is a discussion on Integrat all SQL + CL + SQL Variable in one program - IBM AS400 ; Can I integrate SQL + CL + Variable in SQLPKG? The logic is like: 1. Read 2 column(TblName, Library) from a file TblList 2. From that file, each record pass variable to CL command(CLRPFM) to clear file 3. Then loop ...

+ Reply to Thread
Results 1 to 9 of 9

Thread: Integrat all SQL + CL + SQL Variable in one program

  1. Integrat all SQL + CL + SQL Variable in one program

    Can I integrate SQL + CL + Variable in SQLPKG?

    The logic is like:

    1. Read 2 column(TblName, Library) from a file TblList
    2. From that file, each record pass variable to CL command(CLRPFM) to
    clear file
    3. Then loop until EOF based on that TblList

    All I one is put all this in one program, instead call CL, Call RPG,
    Call SQL........put all in one program, is this possible?
    ______________________

    C/EXEC
    SQL
    C+ Select TblName, Library From TBLList
    C/END-
    EXEC


    Do Until EOF

    CLRPFM(Variable/Variable)

    Read TBLList

    Loop

    _____________________

  2. Re: Integrat all SQL + CL + SQL Variable in one program

    Why not just do it all in CL? Pseudo-code:

    DCLF TblList
    RCVF TblList
    DO until EOF
    CLRPFM &lib & tab
    RCVF TblList
    END-DO

  3. Re: Integrat all SQL + CL + SQL Variable in one program

    Thanks walker, is this command available for CL V5R4?

    "EXECUTE SQL('SELECT......."

    but my version can't, contain syntax error

  4. Re: Integrat all SQL + CL + SQL Variable in one program

    You mention SQLPGK--connecting from a client and wanting to run SQL from
    there?

    Anyway, one way to do this from inside SQL is to use the CALL statement and
    call QCMDEXC to execute CL commands. Here are a few links you might look
    at:

    http://www.itjungle.com/mgo/mgo080803-story01.html

    http://www.mcpressonline.com/tips-te...rver-page.html

    http://publib.boulder.ibm.com/infoce...bam6/execp.htm

    Another way would be to create a UDF to which you pass the library and file
    name, but showing you how is beyond the scope of a message and my available
    time.

    Sam

    "Sphenix" wrote in message
    news:8a8f367d-0630-454a-af86-6300044b4190@w8g2000prd.googlegroups.com...
    > Can I integrate SQL + CL + Variable in SQLPKG?
    >
    > The logic is like:
    >
    > 1. Read 2 column(TblName, Library) from a file TblList
    > 2. From that file, each record pass variable to CL command(CLRPFM) to
    > clear file
    > 3. Then loop until EOF based on that TblList
    >
    > All I one is put all this in one program, instead call CL, Call RPG,
    > Call SQL........put all in one program, is this possible?
    > ______________________
    >
    > C/EXEC
    > SQL
    > C+ Select TblName, Library From TBLList
    > C/END-
    > EXEC
    >
    >
    > Do Until EOF
    >
    > CLRPFM(Variable/Variable)
    >
    > Read TBLList
    >
    > Loop
    >
    > _____________________




  5. Re: Integrat all SQL + CL + SQL Variable in one program

    Sphenix wrote:
    > Can I integrate SQL + CL + Variable in SQLPKG?
    >
    > The logic is like:
    >
    > 1. Read 2 column(TblName, Library) from a file TblList
    > 2. From that file, each record pass variable to CL command(CLRPFM) to
    > clear file
    > 3. Then loop until EOF based on that TblList
    >
    > All I one is put all this in one program, instead call CL, Call RPG,
    > Call SQL........put all in one program, is this possible?
    > ______________________
    >
    > C/EXEC
    > SQL
    > C+ Select TblName, Library From TBLList
    > C/END-
    > EXEC
    >
    >
    > Do Until EOF
    >
    > CLRPFM(Variable/Variable)
    >
    > Read TBLList
    >
    > Loop
    >
    > _____________________


    I solved this kind of issue this way. I created a function, DOCMD ("Do
    Command"):

    create function mylib.docmd (varchar(2000)) returns int
    external name 'QSYS/QC2SYS(system)'

    The service program QSYS/QC2SYS is a bunch of C procedures; the
    procedure 'system' is a call to QCMDEXC.

    Then, assuming I have a file TblList as you describe above, I use the
    function like this:

    select TblName, Library,
    mylib.docmd('clrpfm ' || rtrim(Library) || '/' || TblName)
    from TblList

    This will return values as

    Tblname, Library, <0|1>

    where the third column is a 0 if the command run by DOCMD completed
    successfully, or a 1 if it failed.

  6. Re: Integrat all SQL + CL + SQL Variable in one program

    You don't need the SELECT, the RCVF command will read the file. (It
    will return all columns, but you can simply ignore those you are not
    interested in.)

  7. Re: Integrat all SQL + CL + SQL Variable in one program

    Thanks guys, I got the solution ard by using "QCMDEXC", seems like got
    many alternate way.
    But I dont get one thing

    ______________________________

    FGLPAR1 O E K DISK

    DCMD1 C CONST('CLRPFM TBLTEST')

    C CMDPAR PLIST
    C PARM QCMD 256
    C PARM 256 CMDLEN 15 5

    C MOVEL CMD1 QCMD
    C CALL 'QCMDEXC' CMDPAR

    C MOVE '1' *INLR
    _______________________________

    C PARM 256 CMDLEN 15 5
    The CMDLEN is in 15 and decimal 5, however my CL cmd ('CLRPFM
    TBLTEST') only 14.
    When I run other than "15 5" it will hit error. Why must "15 5"?

  8. Re: Integrat all SQL + CL + SQL Variable in one program

    You're confusingh the definition of a variable and its content.
    15 5 is the parameter definition expected by QCMDEXC, the length of
    the command (14 in your case) is the value held in CMDLEN.
    (An analogy: 01-WS-COMMAND_LENGTH PIC 9(6) VALUE 14. would be a
    COBOL working storage variable that can hold a six digit number, with
    the initial value set to 14.)

  9. Re: Integrat all SQL + CL + SQL Variable in one program


    "Sphenix" skrev i en meddelelse
    news:8a8f367d-0630-454a-af86-6300044b4190@w8g2000prd.googlegroups.com...
    > Can I integrate SQL + CL + Variable in SQLPKG?
    >
    > The logic is like:
    >
    > 1. Read 2 column(TblName, Library) from a file TblList
    > 2. From that file, each record pass variable to CL command(CLRPFM) to
    > clear file
    > 3. Then loop until EOF based on that TblList
    >
    > All I one is put all this in one program, instead call CL, Call RPG,
    > Call SQL........put all in one program, is this possible?
    > ______________________
    >
    > C/EXEC
    > SQL
    > C+ Select TblName, Library From TBLList
    > C/END-
    > EXEC
    >
    >
    > Do Until EOF
    >
    > CLRPFM(Variable/Variable)
    >
    > Read TBLList
    >
    > Loop
    >
    > _____________________


    Seems to me that you can do all that in SQL. Starting from V5R3 the SQL
    command DELETE FROM lib/table will try to execute a CLRPFM - if it fails it
    will delete all records instead.

    You can execute a dynamic SQL statement directly:

    Eval SQLstmt = 'DELETE FROM ' + %TrimR(Lib) + '/' + Table

    C/EXEC SQL
    C+ EXECUTE IMMEDIATE SQLstmt
    C/END-EXEC

    Ref.:
    http://publib.boulder.ibm.com/iserie...afzmstexim.htm




+ Reply to Thread