[SOLVED] - Interesting MySQLi issue - Stored Procedures with Multiple Results.

[eluser]Tim Brownlaw[/eluser]
I can't for the life of me - figure this one out. So I'm hoping someone will have an "Aha" moment!

The Story.
I use Stored Procedures that return two Result Sets ie have two Select statements.
1. The result and a message and
2. A record set of information.

I've been developing my new website happily on my own local development system without a hitch. So I have been getting both result sets without any issue.

The issue...
THEN... I threw the site up on a HostGator Account AND the 2nd result from the SP is nowhere to be found.

Now what have I done to date?
I created a number of sample PHP Scripts using garden variety PHP and MySQLI commands.

These sample PHP Scripts start off with using direct SQL to calling a Stored Procedure using the very same code and table structure I'm using in my CI application.

ALL of these sample scripts WORK showing that HostGator is configured correctly to allow Stored Procedures with multiple result sets.

I even hooked directly into the same SP I'm using in the application and Bingo - it works. So it's not the SP causing the issue.

Now as I stated earlier - My CI app works as expected on my local system But not on the HostGator hosting.

Of course there is something different between my setup and the HG Hosting, but what that is, is a mystery.

CI is using the same mysqli commands as I demonstrated working outside of CI on the host.

I'm 99% sure ( always some doubt) that the files are the same on both systems. Installation was performed by zipping up the system folder and uploaded and extracted using the Cpanel File Mangler.

I've put some debug info in the next_result method in mysqli_result.php and it's not getting there on HG. It certainly does on my local system.

This what I have added into the mysqli_result.php file

Code:

function next_result()
    {
       return mysqli_next_result($this->conn_id);
    }
    /**
    * Prepare next result
    *
    * @return    mysqli object
    */
// Added by TB
    function use_result()
    {
      if($this->next_result())
      {
        $this->result_id=mysqli_store_result($this->conn_id);
        if($this->result_id)
        $this->result_array=array($this->_fetch_assoc());
        return TRUE;
      }
      else
        return FALSE;
    }

Can you see any issues?

Remember it works on one location but not the other and the generic Mysqli calls work fine outside of CI.

To date I've not yet been able to test this out on any other hosting and I'm not blaming HG. In fact they have been very supportive of my Dilemma.

It'll be something soooo simple I'm missing it.

The Sample Code is up and running at Demonstration Code for Mysqli and Stored Procedures and has the table and SP SQL with the sample scripts and their outputs so you can see that all working.

Cheers
Tim