CodeIgniter Forums
working w/stored proc that return multiple result sets - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forum-5.html)
+--- Forum: Best Practices (https://forum.codeigniter.com/forum-12.html)
+--- Thread: working w/stored proc that return multiple result sets (/thread-63754.html)



working w/stored proc that return multiple result sets - ozzy mandiaz - 12-07-2015

Huh  I've been working on converting an old dot net application to php/mysql and CI3. 

the dot net application is using mysql with a number of stored procedures that for the most part work just fine when called using $this->db->query("call xxxx()"); However, a number of those stored procs return multiple result sets that  are relatively simple to use in the dot net/c# environment. not so much using this particular db interface. or at least, that I've discovered. 

Any ideas or suggestions as to how to accomplish this?  I do know that I can do this within a loop using the mysql pdo interface. is this something I should use just for this particular situation?

please be kind, as I'm new to 3.x and never really encountered this  when learning 2.x.. 

regards,


OM.


RE: working w/stored proc that return multiple result sets - skunkbad - 12-07-2015

You should be able to call more than one stored procedure, just by calling ->next_result(). I'm not sure if CI3 has ->next_result(), but if it doesn't you will need a hack:
PHP Code:
/*/system/database/drivers/mysqli/mysqli_result.php

This is the only system hack, and was necessary because CodeIgniter
does not currently allow more than one stored procedure to be run 
during the same request.

The following method was added to the CI_DB_mysqli_result class:*/


/**
 * Read the next result
 *
 * @return  null
 */
   
function next_result()
{
    if (is_object($this->conn_id))
    {
        return mysqli_next_result($this->conn_id);
    }
}

// -------------------------------------------------------------------- 



RE: working w/stored proc that return multiple result sets - ozzy mandiaz - 12-07-2015

(12-07-2015, 03:43 PM)skunkbad Wrote: You should be able to call more than one stored procedure, just by calling ->next_result(). I'm not sure if CI3 has ->next_result(), but if it doesn't you will need a hack:
PHP Code:
/*/system/database/drivers/mysqli/mysqli_result.php

This is the only system hack, and was necessary because CodeIgniter
does not currently allow more than one stored procedure to be run 
during the same request.

The following method was added to the CI_DB_mysqli_result class:*/


/**
 * Read the next result
 *
 * @return  null
 */
   
function next_result()
{
    if (is_object($this->conn_id))
    {
        return mysqli_next_result($this->conn_id);
    }
}

// -------------------------------------------------------------------- 

thanks! I'll give it a go and see what happens tonight...