We help IT Professionals succeed at work.

stored procedure call not working from PHP on iseries

iskibinska
iskibinska asked
on
hi , i have a simple stored procedure to retrieve customer name , it works fine in interactive
SQL, but when i use it in php it does not work,

   $Sql1 = "SELECT fld1,fld2,fld3,
          custname(fld2,fld3)       
           from  $dtalib.ORDERS";
                   
          var_dump($Sql1);                                              
         
        $result =  db2_exec($QSYS2_conn, $Sql2);
           
       var_dump($result);


The result is false, the stored procedure is in QGPL which is in ZEND library list, how can i get more diagnostic here
Comment
Watch Question

Gary PattersonVP Technology / Senior Consultant

Commented:
I'm not a PHP expert, so please bear with me!

1) Is your db2_conn call (not shown) succeeding?  Does the user profile that you are running under have rights to read the ORDERS table?  What connections options are you using, if any?

2) You are setting and dumping $Sql1 in the example, but then passing $Sql2 on the db2_exec call.  Is that what you mean to do?  What's in $Sql2?

3) You can get the specific error message using the db2_stmt_error and db2_stmt_errormsg functions:

http://www.php.net/manual/en/function.db2-stmt-error.php
http://www.php.net/manual/en/function.db2-stmt-errormsg.php

- Gary Patterson

Author

Commented:
Hi Gary,

It works fine, when I remove custname, so everything is fine in tha respect, connection is fine,
it is like this actually (it was a typeo)

 $Sql2 = "SELECT fld1,fld2,fld3,
          custname(fld2,fld3)      
           from  $dtalib.ORDERS";
                   
          var_dump($Sql2);                                              
         
        $result =  db2_exec($QSYS2_conn, $Sql2);
           
       var_dump($result);

The error says CUSTNAME in *LIBL type *N not found. SQLCODE=-204, but as I run the sql2 interactively
everything is fine



VP Technology / Senior Consultant
Commented:
Well, you are using *SQL naming convention (connection option i5_naming = db2_i5_naming_off), so the error message is misleading.  

I suspect that the database connector is only searching the library (if it exists) that is named the same as the user that the job is running under.  So if the job us is running under MYUSERID, the only library getting searched for the "custname" function is MYUSERID.

Several options:

1) Qualify your call to custname, and it should work just fine. Assuming you created it in schema QGPL, then:  qgpl.custname(...

2) In V5R4 and later, you can use the i5_libl connection option to specify a list of libraries to use to resolve unqualified references.

3) Another option is to turn on i5_naming (=db2_i5_naming_on) in your connection, and change your library delimiters from "."s to "/"s.  This switches to *SYSTEM naming format, and then the job's full library list will be searched.

http://www.php.net/manual/en/function.db2-connect.php

- Gary Patterson

Author

Commented:
Thanks so much it worked.

Author

Commented:
With qualified name I wanted to add
Gary PattersonVP Technology / Senior Consultant

Commented:
Great.  Thanks for posting back with that.

- Gary