Hi, All
I've been working with zend framework for couple of months.

Now what I did is When I run the SQL Query it runs fine with zend framework. But I stuck with problem mean while I’m going to execute the Stored Procedure in Zend framework, it doesn’t allow me. I’m using Zend_Db_Mysqli_Adapter and what I got is an error(s).I also tried to use the PDO adeapter but the problem is when I execute multiple stored procedure it says me something like “query should be buffered using PDO::MYSQL_ATTR_USE_BUFFERED_QUERY” even I set the following snippets in my boot-strap file(index.php).

=============================

$pdoParams = array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true);

//Load the config

$configArray = array(

'development' => true,

'database' => array(

'host' => 'localhost',

'port' => '',

'dbname' => 'mydb',

'username' => 'root',

'password' => '',

'driver_options' => $pdoParams,

'profiler' => Custom_Debug::check(Custom_Debug:B_PROFILE)

)

);

$config = new Zend_Config($configArray);

$registry->set('config', $config);

unset($configArray);



$db = Zend_Db::factory('pdo_mysql', $config->database->toArray()); // I also tried Mysqli

=============================



And to run the Store-procedure in my controller what I write is



$stmt=$this->dbObj->query("call sp1('param');");

$stmt->fetchAll();



This runs fine for PDO(for one sp). But then If I try to run another SP, it’s give me error.And for the Mysqli adapter it shows me error.



Little Help Appreciate to run the stored procedure in zend framework. I gave the millions of hit to the Google but I found nothing.Even there isn’t any example given in Zend manual documentation to run database stored procedure.



Thanks,

Are you running on windows? If so I've run into the same basic problem you have and it seems to be a bug with the PDO extension:

PHP Bugs: #39759: Can't use stored procedures from PDO on Windows

To get around this on windows I have the following code:

A hack for sure, but it works .

Hope that helps

Hi,brian
Ye I using the PDO with Windows.But I'm not getting what exactly this code does mean how does it useful to me in my case.

if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
$this->dbObj->closeConnection();
}
This snippet mean close the database connection if the runtime plateform in "Window" Right?
Script runs fine if there is only one stored procedure to be executed using Zend-PDO adapter.But what i want is executing the multiple stored procedure...

Does Zend_db have similar functionality as shown below?
$query ="my stored procedure"
if ($mysqli->multi_query($query)) {
do {
/* store first result set */
if ($result = $mysqli->store_result()) {
while ($row = $result->fetch_assoc()) {
$resultset[]=$row;
}
$result->close();
}

/* print divider */
if ($mysqli->more_results()) {
printf("-----------------\n");
}
} while ($mysqli->next_result());
}

Hi,

Using the code you provided in the original post I would try changing the calls to the stored procedure this way:

then try to call the sp again.

What does $this refer to in your code? Where are you trying to make this call from? In the above I assume that $this->db gets you a Zend_Db_Adapter_Abstract object.

Hope this helps,

Brian

Hi,Brian
This gonna be fine now.ThAnKs a LOT......
Your above given code is working.I'm curious to know that

1)Is this the problem only with "WIN" plateform? If yes, then that means multiple stored procedure works fine for "LINUX" without using closeConnection() on database object(here $dbObj is an object of Zend_Db_Adapter_Pdo_Mysql if I use the PDO adapter). Right ?

2)Why do I need to closeConnection() in "WIN"?

3)Why does this snippets of code not working for "Mysqli" Adapter? Is there any way to execute the stored procedure using Mysqli adapter in Zend framework?

4) Is there any perform enhancement by using Mysqli over PDO_Mysql?

Sorry for harassing you again by asking quests but it would really enhance my knowledge...

Thank you again..

Sach

1. The problem is isolated to the windows platform. You are able to call multiple stored procedures on other platforms without a problem.

2. I'm not sure but it works around the bug.

3. You can always call and configure the mysqli in your code, but the ZF is based on top of PDO. So if you use Zend_Db your code will go through the PDO_Mysql adapter.

4. I don't know, but I'm sure you can search for some info on this.

Hi brian,

I got this error while running multiple stored procedures on Linux.I just got the result of first stored procedure and the rest of the output is as below.

May be connection to Mysql server closed after executing one stored procedure.

"Fatal error: Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query' in /www/MYco_zend/framework/Zend/Db/Statement/Pdo.php:68 Stack trace: #0 /www/MYco_zend/framework/Zend/Db/Statement.php(109): Zend_Db_Statement_Pdo->_prepare('call sp_lup_sel...') #1 /www/MYco_zend/framework/Zend/Db/Adapter/Pdo/Abstract.php(156): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Pdo_Mysql), 'call sp_lup_sel...') #2 /www/MYco_zend/application/public_html/index.php(80): Zend_Db_Adapter_Pdo_Abstract->prepare('call sp_lup_sel...') #3 {main} thrown in /www/MYco_zend/framework/Zend/Db/Statement/Pdo.php on line 68"

Can you tell how can I fix?

Thanks

Hi Sach,

I am also facing the similar problem. I am using Zend 1.5 and Php 5.2.0. I am facing problem while I am trying to get the records from the database. I am using stored procedures with Mysqli.

My code is:-

In config file, I wrote:-

; Database Settings
db.adapter = Mysqli
db.dbname = test
db.hostname = localhost
db.username = root
db.password = test

In bootstrap file, I wrote:-
// setup database
$params = array(
'host' => $config->db->hostname,
'username' => $config->db->username,
'password' => $config->db->password,
'dbname' => $config->db->dbname,
'driver_options' => ''
);

$db = Zend_Db::factory($config->db->adapter,$params);
Zend_Db_Table::setDefaultAdapter($db);
Zend_Registry::set('db', $db);

In the main model file where I am trying to fetch the record, I just called write these statements:-
/*
* Open the database registry
*/
$db = Zend_Registry::get('db');

$stmt = $db->prepare("call uspSelectUsers()");
$stmt->execute();
$result = $stmt->fetch();

It gives me warning messages and doesn't give any result.

Warning: Invalid argument supplied for foreach() in /home/test/public_html/test/library/Zend/Db/Statement/Mysqli .php on line 289

Warning: array_combine() expects parameter 1 to be array, null given in /home/test/public_html/test/library/Zend/Db/Statement/Mysqli .php on line 299

When I print the mysql error, there is nothing in the string means there is no mysql error.

When I wrote the complete sql statement in the prepare statement rather than calling a stored procedure, I got the results. But I didn't get any result by using stored procedure.

Also, the interesting thing is when I use the same to insert a record in the table by using stored proceudre, the record inserted successfully. To insert a record I just used the simple query in my model file

/*
* Open the database registry
*/
$db = Zend_Registry::get('db');

$stmt = $db->prepare("call uspInsertUsers($name, $email)");
$stmt->execute();

The record inserted successfully in the table.

So, please help me to sort out this issue. I searched all over the net but I didn't get any useful result. So any little help is appreciable.

I am looking forward for your quick response.

Thanks,
San

This could be the server side error.

__________________
Affordable Web Hosting Coupons Free Domains
Hosting Coupons Promo Web Hosting Choices