[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

[eluser]Tim Brownlaw[/eluser]

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

Hmm time to get out the towels to wipe the mud from my face.

Some dozey programmer who had a changes file to his database and DIDN'T install them needs a good kicking up the backside - OUCH! that hurt!

Why hadn't I put the changes into the main tables file........I have now!

But good did come from all this...

1. proves that my additions to mysqli_result to handle multiple result sets works.
2. proves a days rest in between attempts when the obvious becomes oblivious, gets rid of the mental fog.
3. I refined my "sample php code documenter" to make it very uesful.

I have to add that the Folk at HostGator were most supportive wanting to dive in and help out once I got the code together. Which resulted in point 3 above! So a big thankyou to the Hostgator Crew!

The Moral of the story is...
When something don't work somewhere that does somewhere else - most likely you've forgotten to install / load something.

Unfortunately Stored Procedures - unless you tell them to explicilty - won't complain much - they'll just come back with no result. Which is exactly what was happening.

I hope you get a good chuckle out of this.

Now I can get some work done!

Cheers
Tim

[eluser]jipod[/eluser]
can you give me a demo once, because your link demo account has been suspend.. thanks..

[eluser]Tim Brownlaw[/eluser]
Hi jipod,

It's back up again in a new home.

If there is something that's not clear - please let me know and I'll polish it up a bit.

Cheers
Tim

[eluser]Unknown[/eluser]
Too bad I didn't find this before I started hacking on a solution myself.

I used your old code as a base and came up with this:

Code:

function next_result($store_result = TRUE)
{
    if (is_object($this->conn_id))
    {
        // Free old result
        $this->free_result();
        
        // Check if there are more results
        $has_result = @mysqli_next_result($this->conn_id);
        if ($has_result)
        {
            // Clear the existing result data so we can get the new data
            $this->result_object = array();
            $this->result_array = array();
            
            if ($store_result)
            {
                // Set the new result id
                $this->result_id = @mysqli_store_result($this->conn_id);
                $this->num_rows = $this->num_rows();
            }
        }
        
        return $has_result;
    }
}

Which can simply be used like this:

Code:

$storedProc = 'CALL fetchMultipleResultSets()';
$parameters = array();
$query = $this->db->query($storedProc, $parameters);

if ($query->num_rows() > 0)
{
    foreach ($query->result() as $row)
    {
        // Do stuff
    }
}

// False if you simply want to discard the result
if ($query->next_result())
{
    // Same again, but with the new result set
    if ($query->num_rows() > 0)
    {
        foreach ($query->result() as $row)
        {
            // Do stuff
        }
    }
}

// Clear the status result set generated by the stored procedure
$query->next_result(FALSE);

By clearing the result_object and result_array, the existing functions are used to populate these during the next call to result(), row(), etc.
This code also sets the num_rows field, and returns TRUE or FALSE depending on if additional result sets where found.
By using next_result(FALSE); you get the behaviour from your old post, where one simply discards the result set to get stored procedures working.