CODEWITHSUNDEEP
phpmysqlcodeigniter
KLee1
KLee1

Reputation: 6178

Codeigniter DB errors after stored procedure call

I have a database that has a few stored procedures in it that I would like to call via CodeIgniter. I have the following code in my Model class:

$sql = "CALL `stored_proc`(1)";
$query = $this->db->query($sql); //This call breaks the DB :(

$this->db->select('status');
$this->db->where('id', $id);
$query = $this->db->get('table');

print($query->num_rows()); //line 1116

When I run this code, I get the following error:

Fatal error: Call to a member function num_rows() on a non-object in C:\server\apache\htdocs\application\models\let_model.php on line 1116

If I remove the query line, the select works properly. Also, if I replace the call to a stored procedure with say a SELECT command, it also works properly.

Is there something obvious I'm missing for why I'm getting this error? If there isn't a good answer, is there a way to work around this problem?

Thanks for your time!

Edit: After delving a little deeper into the problem, it seems that this error will occur if my stored procedure contains a SELECT command. UPDATES seem to work properly. Perhaps this problem has something to do with how CodeIgniter deals with SELECT results?

Upvotes: 0

Views: 2572

Answers (3)

eric.itzhak
eric.itzhak

Reputation: 16072

Since this question appears first on google, i've managed to solve this by using :

$this->db->simple_query($query);

Instead of the regular query function.

Upvotes: 1

Nishant Jani
Nishant Jani

Reputation: 1993

use only

$query->num_rows instead of $query->num_rows()

Upvotes: 0

KLee1
KLee1

Reputation: 6178

The problem was that the SELECTs in the stored procedure were causing problems in CodeIgniter. By making sure that all of the SELECTs were directed (i.e. with the INTO clause), the stored procedure was able to run successfully.

For example,

-- This causes an error in CodeIgniter when inside a stored procedure.
SELECT GET_LOCK('lock1',0);  

-- This does not.
DECLARE _lock_result INT;
SELECT GET_LOCK('lock1',0) INTO _lock_result;

I am still unaware of the underlying causes of why this causes an error, but this solution will suffice for my current work.

Upvotes: 0

Related Questions