Zend Stored Procedure

Question

I have this code which calls a stored procedure within ZEND. Is there a way to print the SQL command that will get executed?

$sp = $dbAdapter->prepare('CALL updateResultsByEvent(?, ?, ?, ?)');
$sp->bindParam(1,$id);
$sp->bindParam(2,$event->name);
$sp->bindParam(3,$event->distance);
$sp->bindParam(4,$event->unit);
// print $sp;????
$sp->execute();

I'm trying to verify the parameter values are passed correctly.

#0 C:\projects\ABC\library\1.9.2\Zend\Db\Statement.php(284): Zend_Db_Statement_Mysqli->_execute(NULL)
#1 C:\projects\ABC\application\controllers\AdminController.php(53): Zend_Db_Statement->execute()
#2 C:\projects\ABC\library\1.9.2\Zend\Controller\Action.php(513): AdminController->updateleagueresultsbyeventAction()
#3 C:\projects\ABC\library\1.9.2\Zend\Controller\Dispatcher\Standard.php(289): Zend_Controller_Action->dispatch('updateleagueres...')
#4 C:\projects\ABC\library\1.9.2\Zend\Controller\Front.php(946): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#5 C:\projects\ABC\library\1.9.2\Zend\Application\Bootstrap\Bootstrap.php(77): Zend_Controller_Front->dispatch()
#6 C:\projects\ABC\library\1.9.2\Zend\Application.php(346): Zend_Application_Bootstrap_Bootstrap->run()
#7 C:\projects\ABC\public\index.php(29): Zend_Application->run()
#8 {main}

Answer 1

MySQL outputs to its general query log a mockup of the SQL query with parameter values interpolated into it. But other than that, you can't get the SQL as it is executed, combined with parameter values. You need to understand how prepared queries work:

• During prepare, an SQL query is sent to the RDBMS server, where it is parsed and optimized and stored in an internal representation -- not typically a SQL statement anymore. The SQL that is prepared in your example is 'CALL updateResultsByEvent(?, ?, ?, ?)'.

• During execute, the parameter values are sent to the RDBMS server, where they are combined with the internal query representation. The RDBMS keeps the human-readable SQL separate from the executable query internally. This makes it easier to substitute different parameter values if you choose to execute the statement again. It also prevents any chance that a parameter value can result in an SQL injection error.

In general, you can't get the "real" SQL query in human-readable form, because the parameters are never combined with the query in the form that is executed.