LIMIT in PHQL when "'phqlLiterals' => false"

[mapsi]

Hello peoples,

Should PHQL allow for the value of LIMIT to be passed in through a bound parameter?

I'm trying to run

$phql = "SELECT * FROM BoolTestGetSet WHERE string = :string: LIMIT :slimit: ";
$result = $di->get('modelsManager')->executeQuery($phql, array('string'=>'yes', 'slimit'=>1));

but I'm getting the following fatal

Fatal error: Uncaught exception 'Phalcon\Mvc\Model\Exception' with message 'Syntax error, unexpected token STRING PLACEHOLDER(slimit), near to ' ', when parsing: SELECT * FROM BoolTestGetSet WHERE string = :string: LIMIT :slimit: (68)' in /home/mapsi/repos/phalcon-micro/index.php on line 76

The documentation doesn't mention anything about this case and basically it doesn't seem logical to me not being able to bind the parameter.

Obviously when I try

$phql = "SELECT * FROM BoolTestGetSet WHERE string = :string: LIMIT 1";
$alan = $di->get('modelsManager')->executeQuery($phql, array('string'=>'yes'));

I get...

Fatal error: Uncaught exception 'Phalcon\Mvc\Model\Exception' with message 'Literals are disabled in PHQL statements' in /home/mapsi/repos/phalcon-micro/index.php on line 74

Any help please?
Thanks!

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[mapsi]

This is the case when using the the query builder too. When doing a

$query->limit(10);

It doesn't work as the "literals are disabled" is displayed and there is no way of binding the value. :(

[dreamsxin]

Hi, i test it, in my code:

$phql = "SELECT * FROM Accounts WHERE phone = :phone: LIMIT 1";
$result = $this->di->get('modelsManager')->executeQuery($phql, array('phone'=>'13606511531'));
var_dump($result);

It's OK.
Use it:

$phql = 'SELECT * FROM Accounts WHERE phone = :phone: LIMIT :limit:';
$result = $this->di->get('modelsManager')->executeQuery($phql, array('phone'=>'13606511531', 'limit'=>1));
var_dump($result);

report error:
Syntax error, unexpected EOF

[mapsi]

@dreamsxin This is the behaviour when you don't use it with "'phqlLiterals' => false". Correct?

[dreamsxin]

If use:

Phalcon\Mvc\Model::setup(array('phqlLiterals' => false));

Will, report "Literals are disabled in PHQL statements"

[phalcon]

A fix has been added to 1.3.0 to fix this issue, can you try with that version?

[mapsi]

phalcon

Phalcon Framework => enabled
Phalcon Version => 1.3.0

Just checked out 1.3.0 and ran a couple of tests.

$phql = "SELECT * FROM BoolTestGetSet WHERE string = :string: LIMIT 1";
$result = $di->get('modelsManager')->executeQuery($phql, array('string'=>'yes'));

This returns a 'Literals are disabled in PHQL statements'

The following two tests (binding integer and then string)

$phql = "SELECT * FROM BoolTestGetSet WHERE string = :string: LIMIT :slimit: ";
$alan = $di->get('modelsManager')->executeQuery($phql, array('string'=>'yes', 'slimit'=>1));

$phql = "SELECT * FROM BoolTestGetSet WHERE string = :string: LIMIT :slimit: ";
$alan = $di->get('modelsManager')->executeQuery($phql, array('string'=>'yes', 'slimit'=>'1'));

returned

PDOException: 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 ''1'' at line 1 in /home/mapsi/repos/phalcon-micro/index.php on line 76

[ghost]

-$alan = $di->get('modelsManager')->executeQuery($phql, array('string'=>'yes', 'slimit'=>1));
+$alan = $di->get('modelsManager')->executeQuery($phql, array('string'=>'yes', 'slimit'=>1), array('slimit' => \Phalcon\Db\Column::BIND_PARAM_INT));

MySQL expects limit and offset to be integers and therefore you have to explicitly specify the type of the bound parameter as PDO defaults to a string.

Here is an example: https://github.com/phalcon/cphalcon/blob/1.3.0/unit-tests/ModelsQueryExecuteTest.php#L410

[mapsi]

Ah ok, I see. It works now. Thank you.

I have to admit, it's not the solution I was expecting as I would have expected that the type of the bound parameter would be the one used for when actually binding the parameter. Also, this kind of exposes Db stuff in the ORM layer.

But this is my opinion. I might be wrong.

Thank you for looking into this matter.

[ghost]

@mapsi Automatic type detection has been implemented, could you please check?

[baszczewski]

Unfortunately automatic type detection doesn't works as expected with Phalcon 1.3.3.

My example:

$phql = "
    SELECT 
        s.*
    FROM 
        \Laguna\Models\StatisticsProducts as s
    ORDER BY
        s.date_created DESC
    LIMIT :limit: OFFSET 0";

$result = $this->modelsManager->executeQuery($phql, array(
    'limit' => 3
), array(
    'limit' => \Phalcon\Db\Column::BIND_PARAM_INT
));

To not receive the error (Syntax error or access violation...), I still need to use BIND_PARAM_INT.