ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

qcmdexc from PHP to call CL Program with parameters

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

  • qcmdexc from PHP to call CL Program with parameters

    I am all googled out on how to get this to work, nothing I try seems to do anything but give an error.

    I need to call a CL program from PHP and pass parameters, some of which require quotes around them, see below:

    call qsys.qcmdexc('call pgm(stklibr/qarc101) parm('01/03/2012' '31/09/2012' 'stock' '22222' '000' 'email@mycompany.com')',0000000104.00000)

    This is the value of the parameter I am passing to db2_exec.

    I get the following error if I go this:

    42601 - Token 01 was not valid. Valid tokens: ) ,. SQLCODE=-104

    I know my example has single quotes around the parameters as well as around the 1st parm for qcmdexc. I tried no quotes - no good because of the reuqired data formats, 2 quotes - errors as well.

    Any ideas on how to correctly code this, or is there a better way please?
    Poddys Rambles On

  • #2
    Re: qcmdexc from PHP to call CL Program with parameters

    Hi Poddys:

    This article (Those Stupid Quotation Marks! )may help:
    I am attempting to use QCMDEXC to run Open Query File within an RPG IV program but the compiler generates errors RNF0312 (A right parenthesis is expected but is not found) and RNF5347 (An assignment operator is expected with the EVAL operation). Do you see anything wrong with my code? –PG Here’s the free-format RPG


    Best of Luck
    GLS
    The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

    Comment


    • #3
      Re: qcmdexc from PHP to call CL Program with parameters

      do you have zend server installed?
      All my answers were extracted from the "Big Dummy's Guide to the As400"
      and I take no responsibility for any of them.

      www.code400.com

      Comment


      • #4
        Re: qcmdexc from PHP to call CL Program with parameters

        Originally posted by Poddys
        call qsys.qcmdexc('call pgm(stklibr/qarc101) parm('01/03/2012' '31/09/2012' 'stock' '22222' '000' 'email@mycompany.com')',0000000104.00000)
        What you're doing here is calling the QCMDEXC API as a stored procedure, and then passing it a command string to call a program. Seems rather convoluted to me... why not just create a stored procedure interface for QARC101?

        Comment


        • #5
          Re: qcmdexc from PHP to call CL Program with parameters

          Thanks everyone, and thanks for responding Scott, you at least know this area of the AS400 inside out. While I have worked on AS400 for 13 years, my use has until recently been green screen, and as far as RPG goes my knowledge of stored procedures is virtually non-existant.

          Will see if I can find how to create a stored procedure, I am sure it is the simplest way.

          @GLS will see if the link helps me.

          @jamief We have an old version of Zend, but not Zendserver on this AS400. It's an old machine that I am trying to get the software ported off onto the new box, but I'm so busy it might be another 6-12 months before I can make the changes. Lots of hard coding in there thanks to my predecessor.
          Poddys Rambles On

          Comment


          • #6
            Re: qcmdexc from PHP to call CL Program with parameters

            I'm getting somewhere, thanks Scott.

            I created a stored procedure (more by the seat of my pants than anything else) using STRSQL, and the call:

            call stklibr.qasp101 ('01/01/2012','31/12/2012','stock','22222','000','myemail@domain.com')

            was successful.

            I'm not sure if this was created the correct way though, using STRSQL, and on creation I got the following message:

            CREATE PROCEDURE STKLIBR/QASP101(IN from CHAR (10 ), IN to CHAR (10
            ), IN filter CHAR (8 ), IN value CHAR (15 ), IN benchmark CHAR (3
            ), IN email CHAR (30 )) LANGUAGE CL NOT DETERMINISTIC NO SQL CALLED
            ON NULL INPUT EXTERNAL NAME STKLIBR/QARC101 PARAMETER STYLE GENERAL
            Routine QASP101 was created, but cannot be saved and restored.

            Does this mean it has been saved, or is it going to disappear when the AS400 is re-IPL'd etc?

            Our Operations Navigator options are really limited, we never use it. I saw something about going in through there to see the procedures. I can't believe that's the only way though.
            Poddys Rambles On

            Comment


            • #7
              Re: qcmdexc from PHP to call CL Program with parameters

              If procedure are close to functions then the only way I can see them is by using ops nav. Even If i drill down by using wrklnk /qsys.lib/mylib.lib I do not see my functions, just my service program that was used to make them.

              Ops nav shows them in the database/schemas/library/procedures.
              Hunting down the future ms. Ex DeadManWalks. *certain restrictions apply

              Comment


              • #8
                Re: qcmdexc from PHP to call CL Program with parameters

                Originally posted by Poddys View Post
                I'm not sure if this was created the correct way though, using STRSQL, and on creation I got the following message:

                CREATE PROCEDURE STKLIBR/QASP101(IN from CHAR (10 ), IN to CHAR (10
                ), IN filter CHAR (8 ), IN value CHAR (15 ), IN benchmark CHAR (3
                ), IN email CHAR (30 )) LANGUAGE CL NOT DETERMINISTIC NO SQL CALLED
                ON NULL INPUT EXTERNAL NAME STKLIBR/QARC101 PARAMETER STYLE GENERAL
                It looks okay to me. There are many options you can specify to do different things... hard to say whether they are "right" or "wrong", it depends on what you want to do.

                If I were you, however, I'd consider saving this "CREATE PROCEDURE" statement in a file or source member. What I do is have a source file named QSQLSRC in my normal source code library, and I create a member that has the command to create the procedure. Then, I can run that statement using the RUNSQLSTM command. I think of this create procedure statement as source code, so I like to save it. Then, if someone wants to change it, they don't have to start from square one, they can just change my source member, drop the procedure, and re-run it.

                Originally posted by Poddys View Post
                Routine QASP101 was created, but cannot be saved and restored.

                Does this mean it has been saved, or is it going to disappear when the AS400 is re-IPL'd etc?
                No, it means exactly what it says. (It cannot be saved or restored.) Surely you don't restore your system from a backup tape (or similar) every time you IPL?! That's what this is saying. You can't save the procedure to your backup media or restore it from backup media.

                There are several reasons why you might get this message... hit help on it to see which reason caused it. But.. a little background... what the system tries to do is store some additional info inside the program object (in this case, STKLIBR/QARC101) so that the system knows that it's being used as a stored procedure. The idea is what when you save to backup media, it will also save the "create procedure" info to backup media. When you restore from backup media, it'll automatically re-create the procedure definition. (Like re-running the "CREATE PROCEDURE"). But, for some reason, it was unable to save the additional info into the *PGM object like it wanted to, so that's why it sent you this message "cannot be saved or restored".

                Like I said, hit help on the message and it'll give you the reason code why it occurred. I know I always get this message if I do the "CREATE PROCEDURE" before I create the program itself (so the program does not yet exist.) Other reasons might be authority, or someone has the object locked, or something like that.

                Worst case scenario, you'll have to re-run the CREATE PROCEDURE if you restore your system from backup media. That's another reason why you might want to store the 'CREATE PROCEDURE' statement in a source member.

                Originally posted by Poddys View Post
                Our Operations Navigator options are really limited, we never use it. I saw something about going in through there to see the procedures. I can't believe that's the only way though.
                It's not the only way, no... but it's definitely the most user-friendly way.

                The 'CREATE PROCEDURE' statement creates information about how to call your program by putting it into some special database files known as the "system catalog". So if you don't want to use iNav, you could do something like this from STRSQL:
                Code:
                select trim(procschema) concat '/' concat trim(procname) 
                from sysprocs where procschema not in ('QSYS', 'QSYS2', 'SYSIBM')
                And this would give you a list of procedures. Obviously, that's just an example... these are normal files, so you can query them using whatever SELECT Statement you need to, depending on what you're looking for. Take a look at the SYSPROCS file as well as others like SYSROUTINE, it should be pretty easy to figure out all of the nifty things you can do to query the procedures you have, et al.

                Comment


                • #9
                  Re: qcmdexc from PHP to call CL Program with parameters

                  So, Stored procedures are perfectly fine, especially if you are accessing business logic from off platform. So now that you have them, consider them stable. However, there is no need to create a SP if you are staying on platform and why manage another object sort of thing if you don't have to. below is an example of how you can achieve what you were trying to do with a toolkit call in PHP. The toolkit is implemented automatically with Zend Server and can be added manually via the YoungiProfessionals website on other platforms like Linux and Windows. First the CL Program:

                  Code:
                  Code:
                  PGM  (&FDATE &TDATE &TYPE &ACCT &EXT &EMAIL)
                                                              
                  DCL        VAR(&FDATE) TYPE(*CHAR) LEN(10)  
                  DCL        VAR(&TDATE) TYPE(*CHAR) LEN(10)  
                  DCL        VAR(&TYPE)  TYPE(*CHAR) LEN(5)   
                  DCL        VAR(&ACCT)  TYPE(*CHAR) LEN(5)   
                  DCL        VAR(&EXT)   TYPE(*CHAR) LEN(3)   
                  DCL        VAR(&EMAIL) TYPE(*CHAR) LEN(50)  
                                                              
                  DMPCLPGM                                    
                                                              
                                                              
                  CHGVAR     VAR(&EMAIL) VALUE('**OK**')      
                                                              
                  ENDPGM
                  As you can see the CL simply receives the values and changes the last parm. now the PHP code:

                  Code:
                  <pre><?php 
                  
                  /*
                   * 
                   * Sample script to call CL program...
                   * 
                   */
                  
                  require_once 'ToolkitService.php';
                  $db='SALES1';  $user = 'PHPUSER'; $pass = '*******';  $extension='ibm_db2';  
                  $Tks = ToolkitService::getInstance($db, $user, $pass, $extension);
                  
                  $fromDate='01/03/2012'; $toDate='31/09/2012';
                  $type='stock'; $acct='22222'; $ext='000'; $email='email@mycompany.com';
                  
                  $param[] = $Tks->AddParameterChar('both', 10,'FDATE', 'FDATE', $fromDate);
                  $param[] = $Tks->AddParameterChar('both', 10,'TDATE', 'TDATE', $toDate);
                  $param[] = $Tks->AddParameterChar('both', 5,'TYPE', 'TYPE', $type);
                  $param[] = $Tks->AddParameterChar('both', 5,'ACCT', 'ACCT', $acct);
                  $param[] = $Tks->AddParameterChar('both', 3,'EXT', 'EXT', $ext);
                  $param[] = $Tks->AddParameterChar('both', 50,'EMAIL', 'EMAIL', $email);
                  
                  $result = $Tks->PgmCall("QARC101", "MPAVLAK", $param, null, null);
                  
                  if($result){
                  	print_r($result['io_param']);
                  }
                  
                  ?></pre>
                  The PHP code employs the open source toolkit to wrap the program call from PHP. The first step is to create an object from the toolkit classes. Then set up your parameters in the $param array and call the program. Next you can interrogate the results via the $return array. In this case I was calling a CL program. But you can also call RPG programs and sub procedures in service programs. The last parm on the program call is the procedure name and the second to last parm would contain values that are "returned" by the sub procedure. Don't worry, you do not need to be an OO guru to use the classes. But it is a nifty way to learn a little OO if you are so inclined as the classes are right there! No stored procedures required and you can use this style of logic for virtually any program call. Also, you can access native IBM i artifacts like data areas, data queues, spooled files and more. I'm not knocking SP's but they can be a bit cumbersome so please consider both approaches. Folks like Alan Seiden have worked quite hard on the toolkit to make like easier. And since it is open source, you can add you own touch to anything that is missing and contribute the code back to the team! The output looks like this:

                  Code:
                  Array
                  (
                      [FDATE] => 01/03/2012
                      [TDATE] => 31/09/2012
                      [TYPE] => stock
                      [ACCT] => 22222
                      [EXT] => 000
                      [EMAIL] => **OK**
                  )
                  As you can see, the return from the CLP includes all the values you set in but the last value has been replaced by the CLP. This proves that you can not only call a CLP but have that program manipulate the data and all without the overhead of managing stored procedures! Again, this examples works with ll flavors of program calls and it is nice to have choices!!!

                  Regards,

                  Mike Pavlak
                  mike.p@zend.com

                  Comment


                  • #10
                    Re: qcmdexc from PHP to call CL Program with parameters

                    The toolkit is implemented automatically with Zend Server and can be added manually via the YoungiProfessionals website on other platforms like Linux and Windows.
                    Can you clarify that? Does it mean that the 'toolkit' already is present as part of Zend Server on i?

                    Tom
                    Tom

                    There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

                    Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

                    Comment


                    • #11
                      Re: qcmdexc from PHP to call CL Program with parameters

                      What's the mimimum OS for that Mike - 6.1?

                      I know on 5.3 Zend isn't an option ... (and I'm still nagging the bosses to change that for us ...)
                      Greg Craill: "Life's hard - Get a helmet !!"

                      Comment


                      • #12
                        Re: qcmdexc from PHP to call CL Program with parameters

                        Hello, yes, the toolkit is included with Zend Server for IBM i and automatically set up upon installation.

                        Zend Server 6 requires IBM i 6.1 or higher. However, if you are on V5R4 you can install Zend Server 5.6. V5R3 has no support. The toolkit is automatically installed with Zend Server 5.6, but requires the PTF update to work properly.

                        Regards,

                        Mike

                        Comment


                        • #13
                          Re: qcmdexc from PHP to call CL Program with parameters

                          Thanks for the additional responses, and very detailed too, handy for an "old-timer" like me who still finds working with the new features on the AS400 often daunting - no peers to learn from no training courses etc.

                          I think I am going to do what you suggested Scott, save the Create Procedure code in a file.
                          It's always handy to have an archive of SQL scripts.
                          Poddys Rambles On

                          Comment


                          • #14
                            Re: qcmdexc from PHP to call CL Program with parameters

                            Oooooh - finally got this working (as per Mike's example) with a local program here. Nice, proof of concept good so far.

                            Cheers Mike!
                            Greg Craill: "Life's hard - Get a helmet !!"

                            Comment

                            Working...
                            X