How to print exact sql query before execute in Zend Framework 2

Question

I am working on an application using Zend framework 2. I'm using TableGateway to select, insert, update and delete query.

1. My question is how to print exact sql query before executing INSERT, UPDATE and DELETE statement? For SELECT statement here is my code which is working for me.

$selectedTable = new TableGateway($this->tblName, $this->dbAdapter);
$sql = $selectedTable->getSql();
$select = $sql->select();

if ($trace) {
    echo "<br>" . $sql->getSqlstringForSqlObject($select) . "<br>";
    exit;
} 
else {
    $resultSet = $selectedTable->selectWith($select);
    unset($selectedTable);
    return $resultSet;
}

2. For last inserted id I'm using this code and working fine.

$selectedTable = new TableGateway($this->tblName, $this->dbAdapter);
$selectedTable->insert($dataArray); 
$insertId = $selectedTable->adapter->getDriver()->getConnection()->getLastGeneratedValue();
unset($selectedTable);
return $insertId;

But for UPDATE how to get last updated id? and for DELETE how to get affected row? Because for UPDATE and DELETE this code is not working.

Can anyone suggest how to do these job?

Answer 1

1. There should be no difference nor difficulties, do it exaclty the same way on your $insert object. Here how I perform Sql insert and get the SQL string:

$sql = new Sql($this->dbAdapter);
$insert = $sql->insert('table');
[...]
$sqlString = $insert->getSqlString($this->dbAdapter->getPlatform());

2. When you insert a value, you do not know what will be the generated value id before insertion, but you will only know it ater insertion. That's why there is the getLastGeneratedValue-) method for inserted values.

But when you update or delete a value, its id is already defined and you can read it. So all you have to do is to read it from your database. Perform a select before updating or deleting your(s) objetct(s) and you will know all the ids you want.