CodeIgniter Forums
Execute stored procedure - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Execute stored procedure (/thread-26596.html)



Execute stored procedure - El Forum - 01-18-2010

[eluser]HugoA[/eluser]
Hi!
I'm executing a store procedure like this

Code:
$query = $this->db->query('CALL getPersona()');
but it just work one time (the first one), then, I have to restart the apache server because if I don't do it I get this error

Quote:A Database Error Occurred

Unable to select the specified database: todo

If I query mi database like this
Code:
$this->db->where('idpersona','1');
$query = $this->db->get('persona');

There's no problem at all...

What I'm doing wrong when I execute the stored procedure?

Thanks!


Execute stored procedure - El Forum - 01-18-2010

[eluser]flaky[/eluser]
it looks like your procedure is entering a loop somewhere and causing apache/mysql to crash
put the procedure code here


Execute stored procedure - El Forum - 01-18-2010

[eluser]HugoA[/eluser]
This is the stored procedure, is pretty simple, I'm just trying to learn how to execute it.

Code:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER //

CREATE DEFINER=`hugo`@`localhost` PROCEDURE `getPersona`()
BEGIN
   SELECT *  FROM persona where idpersona= 1 ;
   END//



Execute stored procedure - El Forum - 01-18-2010

[eluser]flaky[/eluser]
the stored procedure looks fine, have you checked the resources usage after running the procedure?


Execute stored procedure - El Forum - 01-18-2010

[eluser]danmontgomery[/eluser]
Are you using mysqli?


Execute stored procedure - El Forum - 01-19-2010

[eluser]jedd[/eluser]
What happens if instead of this:
[quote author="HugoA" date="1263851024"]
Code:
$query = $this->db->query('CALL getPersona()');
[/quote]

- you do this instead:
Code:
$query = $this->db->query('CALL getPersona()', FALSE);



Execute stored procedure - El Forum - 01-19-2010

[eluser]HugoA[/eluser]
I've replace this line
Code:
$db['default']['dbdriver'] = "mysql";
with this one
Code:
$db['default']['dbdriver'] = "mysqli";
and i just see a blanck page.

and if i replace this
Code:
$query = $this->db->query('CALL getPersona()');
whit this
Code:
$query = $this->db->query('CALL getPersona()',FALSE);
i can se the page just one time, the first time i load the page, then i get the error
Quote:A Database Error Occurred
Unable to select the specified database: todo

Thanks for your time and help.


Execute stored procedure - El Forum - 01-19-2010

[eluser]HugoA[/eluser]
doing this the problem is solved.
Code:
$db['default']['pconnect'] = FALSE;
by default it is set to TRUE.

So, you can use
Code:
$db['default']['dbdriver'] = "mysql";
and call your stored procedures like this
Code:
$query = $this->db->query('CALL getPersona()');

I have to do more tests, but the application is working now.


Execute stored procedure - El Forum - 03-15-2010

[eluser]jaRguS[/eluser]
i have still this kind of error "...can't return a result set in the given context"


Execute stored procedure - El Forum - 08-05-2010

[eluser]Unknown[/eluser]
[quote author="HugoA" date="1263953354"]doing this the problem is solved.
Code:
$db['default']['pconnect'] = FALSE;
by default it is set to TRUE.

So, you can use
Code:
$db['default']['dbdriver'] = "mysql";
and call your stored procedures like this
Code:
$query = $this->db->query('CALL getPersona()');

I have to do more tests, but the application is working now.[/quote]

THANK YOU, THANK YOU, THANK YOU!!!

I was finding virtually nothing concerning this problem until I saw your post. I just started using mySQL stored procedures with my CodeIgniter application... and shortly after got the deadly "Unable to select the specified database" message.

I opened my \application\config\database.php file and changed from:

$db['default']['pconnect'] = TRUE;

to

$db['default']['pconnect'] = FALSE;

and <knock on wood> haven't had the same problem since.