Model::findFirst() with conditions and always getting 0 result on Oracle

I'm using PhalconPHP 2.0.7's \Phalcon\Mvc\Model::findFirst() with conditions on Oracle database and always getting 0 result. When I attached event manager to log SQL to a file, it seems that PhalconPHP adds "ROWNUM <= 0" to the SQL, which is of course means 0 result. Adding limit parameter doesn't help.

Sample SQL (table structure omitted):

SELECT * FROM (SELECT Z1.*, ROWNUM PHALCON_RN FROM (/***** effective SQL *****/) Z1 WHERE ROWNUM <= 0)

PHP statement:

Resource::findFirst(array(
    'STAFF_NO = :staff:',
    'bind' => array(
        'staff' => $staffNo
    )
));

How to fix this? Is this a bug that I need to lodge somewhere? TQ

I think I might have found the bug. It's in query builder's limit() method because when I changed to using query builder, the following works:

Resource::query()
            ->where('STAFF_NO = :staff:', array( 'staff' => $staffNo ))
            ->execute()
            ->getFirst();

But the following doesn't:

Resource::query()
            ->where('STAFF_NO = :staff:', array( 'staff' => $staffNo ))
            ->limit(1)
            ->execute()
            ->getFirst();

And neither does the following:

Resource::query()
            ->where('STAFF_NO = :staff:', array( 'staff' => $staffNo ))
            ->limit(100)
            ->execute()
            ->getFirst();

Both the 2nd and 3rd statements produce "ROWNUM <= 0" which means the limit() method might be hard-coded to use 0, ignoring the parameter.

Please assist.

Anyone please assist?

I'm suspecting the use of int combined with trim in the limit function in https://github.com/phalcon/cphalcon/blob/master/phalcon/db/dialect/oracle.zep is causing the provided number to be parsed to 0 but that should only happen if I simply call ->limit(1).

I wish I could try and remove the (int) part and test but I'm developing on Windows.



34.2k

I have the same problem but I'm developing on Linux platform so I do not think the problem is the operating system platform, and have the problem with the code generated with the developer tools so it's no problem writing or something

No, I mean I want to help fix the code and test, but since I'm running on Windows it's hard for me to recompile phalcon.

Since you are using linux, can you please try fixing the code (remove the (int) casting), recompile and test.

I have the same problem but I'm developing on Linux platform so I do not think the problem is the operating system platform, and have the problem with the code generated with the developer tools so it's no problem writing or something



34.2k

Edit this part of the code and recompile

public function limit(string! sqlQuery, var number) -> string
    {
        var limit, offset = 0;

        if typeof number == "array" {

            if isset number[1] {
                let offset = (int) trim(number[1], "'");
            }

            let limit = (int)trim(number[0], "'") + offset;
        } else {
            let limit = (int)trim(number, "'");
        }

        let sqlQuery = "SELECT * FROM (SELECT Z1.*, ROWNUM PHALCON_RN FROM (" . sqlQuery . ") Z1 WHERE ROWNUM <= " . limit . ")";

        if offset != 0 {
            let sqlQuery .= " WHERE PHALCON_RN >= " . offset;
        }

        return sqlQuery;
    }

Does it fix the issue? I can't find the difference between the code you posted and the one in github ..

Like I mentioned, I can't recompiled nor test any phalcon source code modification because I'm developing on Windows, using the pre-compiled DLL file from Phalcon download page.



34.2k

No, I edited, downloaded and compiled twice followed by eliminating the source code of the int but still have the same problem that I find information



34.2k
edited Dec '15

In the current version already they got this problem solving

git clone  git://github.com/phalcon/cphalcon.git
cd cphalcon/ext
git checkout 2.0.x
sudo ./install(edited)