ZF2-503: getSqlStringForSqlObject() returns an invalid SQL statement with LIMIT and OFFSET clauses

Issue Type: Bug Created: 2012-08-26T13:04:44.000+0000 Last Updated: 2012-10-08T20:14:47.000+0000 Status: Closed Fix version(s): Reporter: Magnus Berglund (mberglund) Assignee: Ralph Schindler (ralph) Tags: - Zend\Db

Related issues: Attachments:


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

<pre class="highlight">
$adapter = $this->getDbAdapter();
$sql = new Sql($adapter);
$select = $sql->select();

$selectString = $sql->getSqlStringForSqlObject($select);

$selectString returns

<pre class="highlight">
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

<pre class="highlight">
SELECT `rules_questions`.* FROM `rules_questions` LIMIT 10

So when executing the query

<pre class="highlight">
$results = $this->getDbAdapter()->query($selectString, $adapter::QUERY_MODE_EXECUTE);

MySQL returns this exception:

<pre class="highlight">
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.


Posted by Magnus Berglund (mberglund) on 2012-08-26T15:08:31.000+0000

An attempt at a fix:

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:

Have you found an issue?

See the Overview section for more details.


© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.