CRUD action with Oracle error ORA-00920

[imonteiro]

Original author: [email protected] (January 18, 2011 17:54:22)

What steps will reproduce the problem?
1.When editing a record with CRUD operations, I get the error "Error updating record: 920 ORA-00920: invalid relational operator SELECT z2.* FROM ( SELECT z1., ROWNUM AS "zend_db_rownum" FROM ( SELECT GB_PERSON. FROM GB_PERSON WHERE (GB_PERSON.PERSON_ID) *) z1 ) z2 WHERE z2."zend_db_rownum" BETWEEN 1 AND 1"

What is the expected output? What do you see instead?
The record should be updated.

Please insert the appropriate values;
Zend Framework version: 1.10.8
ZFDatgrid Version (Bvb_Grid::getVersion()): 1549 2011-01-10 07:58:32Z
Operating system: Windows
PHP Version: 5.2.6
Database Server and version: Oracle
Source Adatapter: Oracle

Please provide any additional information below.
Valid relational operators in Oracle are
=, !=, ^=, <>, >, <, >=, <=, ALL, ANY, [NOT] BETWEEN, EXISTS, [NOT] IN, IS [NOT] NULL, or [NOT] LIKE in the condition.

When I don't change any of the values and click save, I get the error "Validation failed" instead.

Original issue: http://code.google.com/p/zfdatagrid/issues/detail?id=560

[imonteiro]

From [email protected] on January 26, 2011 02:01:56
Hi,

This does not seams a ZFDatagrid error, because the error message printed is define in Zend_Db_Adapter_Oracle

[QUOTE]

    /**
     * Oracle does not implement the LIMIT clause as some RDBMS do.
     * We have to simulate it with subqueries and ROWNUM.
     * Unfortunately because we use the column wildcard "*",
     * this puts an extra column into the query result set.
     */
    $limit_sql = "SELECT z2.*
        FROM (
            SELECT z1.*, ROWNUM AS \"zend_db_rownum\"
            FROM (
                " . $sql . "
            ) z1
        ) z2
        WHERE z2.\"zend_db_rownum\" BETWEEN " . ($offset+1) . " AND " . ($offset+$count);

[/QUOTE]

Have you tried to use the PDO adapter instead?

Best Regards,
Bento Vilas Boas

[imonteiro]

From [email protected] on February 11, 2011 20:25:32
Sorry it took so long to get back to you. I just tried out the PDO adapter, and get basically the same error:

"Error updating record: SQLSTATE[HY000]: General error: 920 OCIStmtExecute: ORA-00920: invalid relational operator (ext\pdo_oci\oci_statement.c:146)"

[imonteiro]

From [email protected] on February 16, 2011 21:11:39
I changed the source of the grid from a Table to a Select, and it fixed the problem. I wish I could tell you why.