Page 1 of 1

Problem using SQL function db2_fetch_array

Posted: Mon Jan 25, 2016 3:41 am
by greg9886
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.

Re: Problem using SQL function db2_fetch_array

Posted: Mon Jan 25, 2016 7:20 pm
by scottgcampbell
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

Re: Problem using SQL function db2_fetch_array

Posted: Tue Jan 26, 2016 4:29 am
by greg9886
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,

Re: Problem using SQL function db2_fetch_array

Posted: Wed Jan 27, 2016 4:18 am
by greg9886
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.

Re: Problem using SQL function db2_fetch_array

Posted: Fri Apr 20, 2018 7:16 am
by allo2060