PHP Oracle schema prefix for tables ignores UPADTE when leftJoin in place

hapi

As it took me quite long today to find the issue, I am posting my findings here.

I use PHP library and an Oracle database. I recently upgrade the library, in my old version it was working, in my new version not anymore.

I had a simple Editor setup with leftJoin, simplified code:

Editor::inst( $db, 'ORACLE_SCHEMA.MAIN_TABEL', 'ID')
...
->leftJoin( 'ORACLE_SCHEMA.JOIN_TABLE','MAIN_TABEL.id = JOIN_TABLE.ID' )

Querying data was no issue, but editing ended up in ignoring the update. No error, no exception, no trace in the debug.

PHP returned after action=edit just the debug information about the its version.

I was able to identify the part which was causing the UPDATE to be ignored. Also DELETES are I think affedted.

// Does this field apply to this table (only check when a join is
// being used)
if (count($this->_leftJoin) && $tablePart !== $tableAlias) {
    continue;
}

The issue here is, that is was comparing 'ORACLE_SCHEMA.MAIN_TABEL' = 'MAIN_TABEL', this is never true for the right table and hence ignored. So also no error message and no UPDATE query in the debug information.

The solution is simple, use an alias for table names:

Editor::inst( $db, 'ORACLE_SCHEMA.MAIN_TABEL MT', 'ID')
...
->leftJoin( 'ORACLE_SCHEMA.JOIN_TABLE JT','MT.id = JT.ID' )

Maybe it helps somebody to save some time.

hapi

Since I don't want to add alias on all my existing editors, the fix for me is in lib/Editor.php:

private function _alias($name, $type = 'alias') {
...
// START, remove Oracle schema prefix for non aliased tables
if ($type === 'alias') return ($dotPosition = strpos($name, '.')) !== false ? substr($name, $dotPosition + 1) : $name;
// END

return $name;
}

This solved my issue of ignored UPDATES and DELTES.

hapi

Ohh... long time ago I fixed it already but didn't remember after 4 years.

https://datatables.net/forums/discussion/63899/