Problem using SQL function db2_fetch_array

The place for general PHP questions and hints for PHP on IBM i

Problem using SQL function db2_fetch_array

Postby greg9886 on Mon Jan 25, 2016 3:41 am

I have a PHP program that worked on an older Zend server CE but not on Zend server 8.5.1 Enterprise Trial. This is a brand new Zend server install with all the defaults.

The error message I am getting is: Warning: db2_fetch_array(): Fetch Failure in /www/zendsvr6/htdocs/ss/dashboards/ShowDashboardSQL.php on line 42

The code generating this error is:
Code: Select all
$conn_resource = db2_connect("*LOCAL", "", "");
   
    /* Construct the SQL statement */
    $sql = "SELECT DBTITLE, gregc.getdashboard(dbname) FROM gregc.dshhdrm where dbname = '" . $pdbname . "' for fetch only";

   /* Prepare, bind and execute the DB2 SQL statement */
    $stmt= db2_prepare($conn_resource, $sql);
   
    $flds = db2_num_fields($stmt);
   
    //Execute statement
    $result = db2_execute($stmt);
   
    if (!$result) {
        echo 'The db2 execute failed. ';
        echo 'SQLSTATE value: ' . db2_stmt_error();
        echo ' Message: ' .   db2_stmt_errormsg();
        echo '<br>' . $sql;
    }
    else
    {
       
     $row = db2_fetch_array($stmt);


The SQL statement runs fine if I run it from a 5250 screen or from RDI.

It also works in PHP if I remove the function gregc.getdashboard(dbname) from the SQL statement.

The function returns a 32,000 byte field that contains an HTML code fragment that is created by an SQLRPGLE procedure.

I created the function with the following SQL statement:
Code: Select all
create function getdashboard (dbname char(128))             
                returns char(32000)                         
                language rpgle                             
                not deterministic                           
                reads sql data                             
                external name 'GREGC/DSH0110R(GETDASHBOARD)'
                parameter style general                     
                program type sub                           


Any ideas why I might be getting the Fetch Failure?
Is there a setting that truns on and off SQL functions in the server?

Thank you for any avice you can provide.
greg9886
 
Posts: 4
Joined: Wed Oct 14, 2015 3:20 pm

Re: Problem using SQL function db2_fetch_array

Postby scottgcampbell on Mon Jan 25, 2016 7:20 pm

Are you sure where it was created? Just wondering if this is a new install and you didn't specify where to create it (as no library was specified in the SQL) if it created in QGPL instead of GREGC?

Code: Select all
Message . . . . :   GETDASHBOARD was created, changed, or dropped, but object
  not modified.                                                             
Cause . . . . . :   The routine or global variable GETDASHBOARD in QGPL was 


The SQL statement runs fine if I run it from a 5250 screen or from RDI.


Are you specifying the library in these?

Scott
scottgcampbell
 
Posts: 187
Joined: Wed Apr 22, 2009 2:29 pm
Location: Edmonton, AB, Canada

Re: Problem using SQL function db2_fetch_array

Postby greg9886 on Tue Jan 26, 2016 4:29 am

Hi Scott,

When running the SQL on the green screen, I copied the exact SQL with the library names. So you got me thinking. I then changed my current librry to QGPL and created the same function in QGPL. So it now exists in both librarys. I get the same result when I specify QGPL. I get an error on the prepare stating that the function can't be found when I omit the library.

So I know it is seeing the function. So now I am thinking perhaps it might be the content of the fields. I will try to create some test functions that return a simple ten character value and see if that works better.

Thanks,
greg9886
 
Posts: 4
Joined: Wed Oct 14, 2015 3:20 pm

Re: Problem using SQL function db2_fetch_array

Postby greg9886 on Wed Jan 27, 2016 4:18 am

Resolved - One of the programs in the call stack was in a library that was not in the library list and could not be resolved and was causing the function to fail. No error was issued about the failing call.
greg9886
 
Posts: 4
Joined: Wed Oct 14, 2015 3:20 pm


Return to PHP Questions / Hints

Who is online

Users browsing this forum: No registered users and 1 guest

cron