Zend Framework 2 Zend\Db\Sql\Sql - prepareStatementForSqlObject vs getSqlStringForSqlObject

Question

In reading the Zend Framework 2 Documentation: http://framework.zend.com/manual/2.0/en/modules/zend.db.sql.html two examples are given on how one might query data from the Database. For reference, they are:

To prepare (using a Select object):

use Zend\Db\Sql\Sql;
$sql = new Sql($adapter);
$select = $sql->select();
$select->from('foo');
$select->where(array('id' => 2));

$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();

To execute (using a Select object)

use Zend\Db\Sql\Sql;
$sql = new Sql($adapter);
$select = $sql->select();
$select->from('foo');
$select->where(array('id' => 2));

$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);

I was unable to find any further instruction in the documentation as to what advantages/disadvantages one method may have over the other in certain situations. I can see that if we needed to debug our SQL query, the getSqlStringForSqlObject would help us to do so. But beyond that, does anyone know a situation where one method may be better than the other?

Thanks.

score 3 · Accepted Answer · answered Feb 21 '13 at 10:20

I would certainly use prepareStatementForSqlObject() to prepare your statements to be executed.

prepareStatementForSqlObject() will return a StatementInterface type object.

getSqlStringForSqlObject() will get you back an actual raw SQL string.

prepareStatementForSqlObject() isn't used anywhere in the library, and probably not meant to be used externally anyway. I would stick to using the Statement Object for executing Queries if you can.

the second example is probably just to illustrate you can execute raw SQL strings if needed.

So it would appear that the second example "getSqlStringForSqlObject()" doesn't actually prepare the statement, but merely converts it to a string for direct execution against the DB. In that case, yes, I can easily see the difference between the two. Thanks for your insight. — Derrick, Feb 21 '13 at 15:58

score 0 · Answer 2 · answered Feb 21 '13 at 12:20

I think your doubt isn't directly related to these specific Zend methods, but comes more from not understanding exactly what prepared SQL statements are. Wikipedia has a good overview.

Summarized, I would expect prepareStatementForSqlObject() to use the database system's support for prepared queries, which have the combined advantage of a) offering better performance in case of multiple execution and b) helping to protect against SQL injection.

score 0 · Answer 3 · edited May 23 '17 at 12:08

ZF1 and ZF2 so different and sad part is confusion that most public documentation is ZF1

It pays to read PDO documentation as ZF2 DB sits largely as wrapper on PDO

I spent hours debugging until decided to trace the SQLs

If using MYSQLyou must trace SQLs to understand and debug ZF2

How can I view live MySQL queries? has it very easy

Following code works $sql = new Sql(self::getadapter()); $select = $sql->select(); $select->from('tbl_login');

$select ->where('logincd = ?', $logincd);
$select ->where('password = ?', $password);
$select ->where('active = ?', "Y");

$statement = $sql->prepareStatementForSqlObject($select);
// $result = $statement->execute(); // WRONG
$result = $statement->execute(array($logincd,$password,"Y")); // RIGHT

$rowset = new ResultSet;
$rowset->initialize($result);
$onerow=$rowset->current();

I had missed array($logincd,$password,"Y") and SQL reaching MSQL server was SELECT tbl_login.* FROM tbl_login WHERE logincd = NULL AND password= NULL AND active = NULL

When I put array parameter in execute it was SELECT tbl_login.* FROM tbl_login WHERE logincd = 'jnc' AND password = 'secret' AND active = 'Y'

Hwever tracing DID NOT help me solve a nightmarish bug I had coded

$dbtable=new TableGateway('tbl_event_log',self::getadapter());

$dbtable->insert( array(
  'logincd' => $logincd  ,
  'eventdttm' => date('Y-m-d H:i:s'),
  'storecd'   => "S01", // $_SESSION["storecd"],
  'eventcd'   => $eventcd ,
  'eventtxt'  => $eventtxt )
 );

BUT my mistake I had 1 extra space in 'logincd ' Got yards of PDO exeption and no trce!

After a lot pain changed adapter (Driver was Pdo_Mysql) to 'driver' => 'Mysqli' With a solution 'Unknown column 'logincd ' in 'field list'

In BOTH choices of Driver the SQL INSERT did not arrive a the Server; trace did not help

So my advice is use SQL Trace as well as flip driver to solve ZF2 DB issues

ZF2 DB is very nice despite these glitches

Jayanta @Kolkata

Zend Framework 2 Zend\Db\Sql\Sql - prepareStatementForSqlObject vs getSqlStringForSqlObject

3 Answers3