Elxis CMS Forum

Support => Database => Topic started by: muharihar on January 14, 2009, 02:35:44

Title: Oracle Error on Add new User
Post by: muharihar on January 14, 2009, 02:35:44

i found this error while try to Add New User:

oci8 Error [907] : EXECUTE

Code: [Select]

URI: index2.php
Message: ORA-00907: missing right parenthesis
SELECT (o.aro_id IS NOT NULL) AS object_exists FROM sie_core_acl_aro_sections s LEFT JOIN sie_core_acl_aro o ON (s.value=o.section_value AND o.value='104') WHERE s.value='users'
it happen because: i think Oracle does not support IS NOT NULL inside SELECT statement.

i'm try to fix it by:
replace script (function: add_object , file: gacl_api.class.php):

Code: [Select]

        $this->db->setQuery( '
SELECT (o.'. $object_type .'_id IS NOT NULL) AS object_exists
FROM '. $object_sections_table .' s
LEFT JOIN '. $table .' o ON (s.value=o.section_value AND o.value=\''. $this->db->getEscaped($value) .'\')
WHERE s.value=\''. $this->db->getEscaped($section_value). '\''
);
with:

Code: [Select]

        $ordrivers = array('oci8', 'oci805', 'oci8po', 'oracle');
        if (in_array($mosConfig_dbtype, $ordrivers)) {
            $this->db->setQuery( '
                SELECT        DECODE(o.'. $object_type .'_id,null,0,1) AS object_exists
                FROM        '. $object_sections_table .' s
                LEFT JOIN    '. $table .' o ON (s.value=o.section_value AND o.value=\''. $this->db->getEscaped($value) .'\')
                WHERE        s.value=\''. $this->db->getEscaped($section_value). '\''
            );   
        }
        else {
            $this->db->setQuery( '
                SELECT        (o.'. $object_type .'_id IS NOT NULL) AS object_exists
                FROM        '. $object_sections_table .' s
                LEFT JOIN    '. $table .' o ON (s.value=o.section_value AND o.value=\''. $this->db->getEscaped($value) .'\')
                WHERE        s.value=\''. $this->db->getEscaped($section_value). '\''
            );
        }

Note:
But, after modifying the code (above), you will found new SEQUENCE created:
[PREFIX_TABLE]_CORE_ACL_ARO_SEQ_10_SEQ --> Why ?

happy try  ;D