CodeIgniter Forums

Full Version: Execute stored procedure
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[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!

El Forum

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

El Forum

[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//

El Forum

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

El Forum

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

El Forum

[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);

El Forum

[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.

El Forum

[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.

El Forum

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

El Forum

[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.