AS400 – Editing an FTP script in CL

65 pts.
Tags:
AS/400
CL Program
FTP
FTP Script
I have written a CL routine to send a file via FTP using the OVFDBF commands for INPUT and OUTPUT. This works fine, however I need to be able to edit my FTP script file somehow in order to change the name of the file in the PUT command. Clearly you cannot pass a variable to this but is there anyway to change the file name within a script through a CL program? The only way I can see you can change the filename in the PUT command is to manually edit beforehand, which is not a working solution for a daily job. Any ideas around this problem would be greatly received.

Answer Wiki

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

If you are overriding to a source member for the Input, just build that member on-the-fly in your CL. You can use RUNSQL doing an INSERT or use an RPG program to build you member.

Discuss This Question: 13  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
  • phil755

    Thanks. The RUNSQL on-the-fly method sounds promising. Can you pass a variable from the CL using an INSERT? Also, do you have an example I could see, just so I can work out how SQL works.

    RPG is not an option, as I know nothing about RPG programming.

     

    65 pointsBadges:
    report
  • phil755

    I would also like to add I am doing something like this is my script.

    profile

    password

    bin

    namefmt 1

    put /library/file data.set.file    

    I need file to be a &var as this would be the only part of the script that needs to be changed.

    How could this be done using RUNSQL? Thanks

    65 pointsBadges:
    report
  • CharlieBrowne

    Do you have access to a Source PF to use for this exercise?

    I can then be more specific in giving you instruction in an example of the code

    41,430 pointsBadges:
    report
  • CharlieBrowne
    Can you show me the complete script you are using now?
    41,430 pointsBadges:
    report
  • phil755

    I'm using a simple CL programme.

    PGM

    OVRDBF FILE(INPUT) TOFILE(LIB/QCLSRC) MBR(FTPCMDS)

    OVRDBF FILE(OUTPUT) TOFILE(LIB/QCLSRC) MBR(FTPLOG)

    FTP  RMTSYS(XXX.XXX.XXX.XXX)

    ENDPGM

     

    65 pointsBadges:
    report
  • TomLiotta

    In order to incorporate a CL variable, there are three general alternatives.

    First, you can create a QM query that runs an UPDATE statement and pass the variable as a parm for the STRQMQRY command. This works on all AS/400s going back to version 2 of the OS.

    Second, you can build the UPDATE statement in a *CHAR variable and include your &var variale as part of the statement string. Use the constructed *CHAR variable to pass to the QShell db2 utility. This works on AS/400s that have the QShell option installed and are running V5R3 or later.

    Third, you can pass the constructed *CHAR variable to the RUNSQL command. This works on i 6.1 or later (if your PTFs are reasonably up to date).

    Also, there are various utilities you can download that give a similar capability to the native RUNSQL command if you have a pre-6.1 system. (Or you can create your own.)

    As of V5R4, you can also do some fairly sophisticated coding that calls the SQL CLI APIs. These are effectively the same APIs used for ODBC on almost any platform. (See Example replacing CPYFRMIMPF with CL for one code sample.) You could also use the HSF APIs to build a script in a QDOC file, then use CPYFRMPCD or CPYFRMSTMF to put the script into a source member. In addition, it's possible to call the various C library functions for database I/O if you wish.

    Assuming that you had a [put] statement such as you showed for your script, the UPDATE statement might be something like this:

    UPDATE MYLIB/QFTPSRC
       SET SRCDTA = replace(SRCDTA, '/file', '/var')
       WHERE SRCDTA like 'put%'

    I had to use "/file" as the source to replace because you had ".file" in your result file name. You would use the content of your &var variable to set the replacement text value.

    The UPDATE statement assumes a source file named QFTPSRC with your script in the first or only member. If there are multiple members, you'll need either to OVRDBF to the correct member or to CREATE ALIAS for the member.

    Tom

    125,585 pointsBadges:
    report
  • phil755

    Many thanks Tom. Some great info to get stuck into. Just to explain, I am sending a file from an AS400 to an MVS system hence the change from /file to a dataset name of .file.

     

    65 pointsBadges:
    report
  • TomLiotta

    Note that your example statement was:

    put /library/file data.set.file 

    If it was:

    put /library/XXXXXXXX data.set.file 

    ...or some other unique value, it would be easier to replace the single element. However, if you want both occurrences of the name to change, then "file" in both places works well. The SQL REPLACE() function wouldn't need to use "/file". Just "file" would work.

    The STRSQL command helps test the UPDATE statement.

    Tom

    125,585 pointsBadges:
    report
  • phil755

    Following on from your suggestions I am having great difficulties in getting this to work.

    All I want to do is pass a CL variable to a script file.

    I have create a QM query called varpgm. Content below:

    UPDATE mylib/qqmqrysrc

    SET SRCDTA = replace(SRCDTA, 'test', &FILE)

    WHERE SRCDTA like 'PUT%'

    The problem is the &FILE variable is not being understood.

    My CL PGM is as follows:

    PGM PARM(&FILE)

    DCL VAR(&FILE) TYPE(*CHAR) LEN (8)

    STRQMQRY QMQRY(mylib/varpgm) SETVAR((FILE &FILE))

    My script is the only member in qqmqrysrc:

    PUT /mylib/test /mylib/test

     

    What I am trying to do is to change 'test' in the PUT statement to the value of &FILE.

    I can normally work these problems out, but I've given this hours and hours but cannot find a solution. I think I'm close but there must be something wrong in my logic.

    Any help would be greatly appreciated.

    65 pointsBadges:
    report
  • CharlieBrowne

    Try this

      On my system I created a library called LIB and put a QCLSRC file in it with a member name of FTPCMDS. Then I wrote a program called ITKE. This program receives a parameter for you filename.  

     

    In the program you will need to changes the data in the VALUE parameter for the DCL statements that are needed for the FTP process.       

     

    PGM   &pFile                                    

                                                                 

         /* Define fields needed for the FTP Process  */          

                 DCL &Profile  *Char 32  VALUE('Profile')        

                 DCL &Password *Char 32  VALUE('Password')       

                 DCL &Bin      *Char 32  VALUE('bin')            

                 DCL &Format   *Char 32  VALUE('namefmt 1')       

                 DCL &Put      *Char  4  VALUE('Put ')           

                 DCL &InFile   *Char 32  VALUE(' /library/file ')

                 DCL &OutFile  *Char 32  VALUE(' data.set.')     

                                                                 

         /* Define fields needed for the FTP Process  */         

                 DCL &pFile    *Char 32                          

                 DCL &pVar     *Char 80                          

                 DCL &vSQL     *Char 128                         

                                                                  

          /* Clear out old data from last run  */                

                 ClrPFM LIB/QCLSRC FTPCMDS          

                

         /* Insert the required lines for the FTP process  */                     

                ChgVar &vSQL      +                                               

                ('Insert into LIB/INPUT (SrcDta) Values(''' || &Profile || ''')') 

                Runsql &vSQL    Commit(*NC)                                       

                                                                                  

                ChgVar &vSQL      +                                               

                ('Insert into LIB/INPUT (SrcDta) Values(''' || &Password || ''')')

                Runsql &vSQL    Commit(*NC)                                       

                                                                                  

                ChgVar &vSQL      +                                               

                ('Insert into LIB/INPUT (SrcDta) Values(''' || &Bin || ''')')     

                Runsql &vSQL    Commit(*NC)                                       

                                                                                   

                ChgVar &vSQL      +                                               

                ('Insert into LIB/INPUT (SrcDta) Values(''' || &Format || ''')')  

                Runsql &vSQL    Commit(*NC)                                       

                                                                                  

                Chgvar  &pVAR (&Put *TCAT &InFile *TCAT &OutFile *TCAT &pFile)    

                ChgVar &vSQL      +                                                

                ('Insert into LIB/INPUT (SrcDta) Values(''' || &pVar   || ''')')       

                Runsql &vSQL    Commit(*NC)      

                

    OVRDBF FILE(INPUT) TOFILE(LIB/QCLSRC) MBR(FTPCMDS)

    OVRDBF FILE(OUTPUT) TOFILE(LIB/QCLSRC) MBR(FTPLOG)

    FTP  RMTSYS(XXX.XXX.XXX.XXX)

                        

    EOJ:         EndPgm  

     

    Contents of LIB/QCLSRC MBR(FTPCMDS) after I did Call ITKE qwerty                                                                 

    Profile                          

    Password                         

    bin                              

    namefmt 1                         

    Put /library/file data.set.qwerty

     

    41,430 pointsBadges:
    report
  • TomLiotta
    SET SRCDTA = replace(SRCDTA, ‘test’, &FILE)

    That statement has a problem. You have apostrophes around 'test', but none around &FILE, and the statement syntax requires apostrophes.

    However, you can't put apostrophes in the script because that would turn '&FILE' into a literal rather than a variable. Instead, you have to pass the apostrophes in as part of the value.

    I usually do it this way:

    SET SRCDTA = replace(SRCDTA, ‘test’, &q&FILE&q)

    And my STRQMQRY statement is changed to look like this:

    STRQMQRY QMQRY(mylib/varpgm) SETVAR((q '''') (FILE &FILE))

    By using two apostrophes inside a quoted value, a single apostrophe gets passed in as QM replacement value &q. That lets QM put a single apostrophe before and after the &FILE replacement value. You can see that &q only needs to be passed in once, and you can use it in the query as many times as you need.

    Tom

    125,585 pointsBadges:
    report
  • phil755

    Brilliant guys. This has fixed it. Very useful information Charlie and I'm going to have a look at the code.

    Tom, superb mate. I've entered that little fix with the quotes, and it's worked. I would never have thought of that, so thanks very much.

    Have a great weekend.

    65 pointsBadges:
    report
  • PutzGrilla

    Charlie and Tom are the best.

     

    795 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