ZF2-503: getSqlStringForSqlObject() returns an invalid SQL statement with LIMIT and OFFSET clauses
Description
The getSqlStringForSqlObject() function returns a SQL statement where the integers for the LIMIT and OFFSET clauses are quoted, which is not correct and MySQL returns an exception.
In the model/mapper
$adapter = $this->getDbAdapter();
$sql = new Sql($adapter);
$select = $sql->select();
$select->from($this->tableName);
$select->limit(10);
$selectString = $sql->getSqlStringForSqlObject($select);
$selectString returns
SELECT `rules_questions`.* FROM `rules_questions` LIMIT '10'
That seems to be an incorrect SQL statement, there should be no quotations around the LIMIT integer
SELECT `rules_questions`.* FROM `rules_questions` LIMIT 10
So when executing the query
$results = $this->getDbAdapter()->query($selectString, $adapter::QUERY_MODE_EXECUTE);
MySQL returns this exception:
Message:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use
near ''10'' at line 1
The same thing happens when adding an OFFSET clause, the integer is incorrectly quoted.
Comments
Posted by Magnus Berglund (mberglund) on 2012-08-26T15:08:31.000+0000
An attempt at a fix: https://github.com/zendframework/zf2/pull/2244
Posted by Abdul Malik Ikhsan (samsonasik) on 2012-09-13T19:12:31.000+0000
i think ->execute() func can solve this
$statement = $sql->prepareStatementForSqlObject($select); $result = $statement->execute();
Posted by Ralph Schindler (ralph) on 2012-10-08T20:14:47.000+0000
This issue has been closed on Jira and moved to GitHub for issue tracking. To continue following the resolution of this issues, please visit: https://github.com/zendframework/zf2/issues/2545