Oracle and datatables 1.6.x BROKEN!!!

zajc

It works fine in version 1.5.5 with https://github.com/yajra/laravel-pdo-via-oci8.

Now I have installed editor 1.6.1 and now Oracle doesn't work, especially INSERT and UPDATE.

When INSERTING I'm getting

Fatal error: Uncaught Error: Call to a member function insertId() on boolean in /var/www/html/php/lib/Editor/Editor.php:1601 Stack trace: #0 /var/www/html/php/lib/Editor/Editor.php(1045): DataTables\Editor->_insert_or_update(NULL, Array) #1 /var/www/html/php/lib/Editor/Editor.php(900): DataTables\Editor->_insert(Array) #2 /var/www/html/php/lib/Editor/Editor.php(661): DataTables\Editor->_process(Array) #3 /var/www/html/php/table.cert_vzo.php(40): DataTables\Editor->process(Array) #4 {main} thrown in /var/www/html/php/lib/Editor/Editor.php on line 1601

When UPDATING all the values are replaced by primary key, for example

ID | COLUMN1 | COLUMN2
1  | 56546 | 414

after the UPDATE all the values are replaced by ID

ID | COLUMN1 | COLUMN2
1 | 1 | 1

SELECT is working fine.

allan

Hi,

The Oracle support in the PHP libraries for Editor 1.6 has changes a little form the beta integration in 1.5 in that they now use the oci* methods rather than PDO. From experience the PDO aspect caused more confusion that it was worth, while the oci methods were almost always available for those wishing to use Oracle.

Having said that, if you were using the yajra driver than you were also using the oci methods rather than PDO (just through a PDO gateway). So in theory this should now actually be simpler!

The error message sounds like the insert failed or an id wasn't returned.

Could you add ->debug( true ) before the ->process() method call so we can see the SQL being executed?

Thanks,
Allan

zajc

I have inserted ->debug( true )

When INSERTING
I'm getting the same error, without DEBUG data. Maybe the reason is that Oracle doesn't have a function to get a new sequence ID but you need to SELECT sequence.CURRVAL from DUAL to get the current ID.

When UPDATING (this is SERIOUS bug)
UPDATE seems fine in a debug but anyway all the columns are then replaced by ID.

When SELECTING
Everything is OK.

allan

When INSERTING [...]

Rather than selecting the sequence, Editor uses:

$sql .= ' RETURNING '.$pkey[0].' INTO :editor_pkey_value';

The pkey is defined by whatever you set it as in the Editor configuration (id by default). It needs to have a trigger to increment the sequence value and insert it into the primary key column.

UPDATE seems fine in a debug but anyway all the columns are then replaced by ID.

Wow - that is terrible, and not something I'm seeing unfortunately so it will be difficult to debug that one if the SQL appears to be correct. Does it show the binding parameters as correct as well?

Allan

zajc

I have prepared the test with ->debug( true) enabled.

https://test.zajc.xyz/form.test.php

INSERT doesn't work, UPDATE in this case is not replacing columns data as in my real case but it is not working anyway.

This works fine in 1.5.5 with https://github.com/yajra/laravel-pdo-via-oci8.

allan

Thank you! Can you give me a dump of your SQL table structure (and any triggers for that table) and also the PHP for the server-side and I'll attempt to recreate the problem and debug it.

Allan

zajc

I have attached the files. When I was creating Oracle table script I found out I have an error (I have DATA column instead VARCHAR2). Anyway UPADATING is now replacing all the columns to ID value what is not OK.You can check it at my test site.

allan

Thanks for the files!

I make one small change which was to add CREATE SEQUENCE test_id_seq;. With that it allows me to create new rows and successfully edit them.

I've made a small change to the Oracle Query.php file for Editor, which I've attached which improves its error handling. Instead of showing the fatal error it will now show a more useful SQL error if that happens.

However, I don't see how that will resolve the issue you are seeing with the update writing the id into multiple columns. That is not something I'm seeing with the test case locally I'm afraid. I'm using 11g Express (11.2.0.2.0).

The debug information that is being returned on your server appears to show that the UPDATE query is being executed with the correct parameters:

{
    "query": "UPDATE  test SET  column1 = :column1, column2 = :column2 WHERE id = :where_0 ",
    "bindings": [{
        "name": ":column1",
        "value": "allan1",
        "type": null
    }, {
        "name": ":column2",
        "value": "allan3",
        "type": null
    }, {
        "name": ":where_0",
        "value": "13",
        "type": null
    }]
}

Which suggests that there is something else overwriting them after they have been updated initially. Are there any other triggers you have on your table?

Allan

zajc

This is config.php

$sql_details = array(
    "type" => "Oracle",  
    "user" => "xxxxx",   
    "pass" => "xxxxx",          
    "host" => "xxx.xxx.xxx.xxx",
    "port" => "1521",       
    "db"   => "xxxxx",      
    "dsn"  => "charset=utf8"
);

I wiped everything. I downloaded 1.6.1. I have used the code from the datatables generator. Now INSERT is working, but UPDATE have the same problems. All the columns are replaced by ID. The table "test" have only 1 trigger, for sequence.

I found out that special characters (č枊ĐČĆŽŠĐ) when INSERTED are changed to "?????????" (see the attachment). Also debug for UPDATE shows nothing unusual.

I'm using php 7.0.8, oci8-2.1.1, Oracle instant client 11.2.0.4, Ubuntu 16.04 (in Docker container).

It works fine in 1.5.5.

You can see it on my test site. https://test.zajc.xyz/form.test.php

zajc

I also have found out if 1 column have special character (čćžšđČĆŽŠĐ), all the columns are saved (INSERTED) as "???????"

For example INSERT INTO test (column1, column2) values ('ABCD','ČĆŽĐ'); the result is '???????','???????'.

allan

Just sent you a PM .

Allan

zajc

I have solved the problem. This is a must for Oracle databases which are not UTF8. You must add this to manual and fix the Query.php file.

First change the config.php dsn parameter charset=utf8 to utf8

$sql_details = array(
    "type" => "Oracle", 
    "user" => "xxxxx",  
    "pass" => "xxxxx",         
    "host" => "xxx.xxx.xxx.xxx",
    "port" => "1521",      
    "db"   => "xxxxx",     
    "dsn"  => "utf8"
);

or maybe you add the new variable charset which is more logical as the 4th oci_connect paramter is charset.

Here is my proposal.

$sql_details = array(
    "type" => "Oracle", 
    "user" => "xxxxx",  
    "pass" => "xxxxx",         
    "host" => "xxx.xxx.xxx.xxx",
    "port" => "1521",      
    "db"   => "xxxxx",     
    "dsn"  => "",
    "charset" => "utf8"
);

You must fix the Query.php the line:

$conn = @oci_connect($user, $pass, $host.$port.'/'.$db);

to

$conn = @oci_connect($user, $pass, $host.$port.'/'.$db, $dsn);

or more logical

$conn = @oci_connect($user, $pass, $host.$port.'/'.$db, $charset);

but then you must also fix the function connect.

Now it works perfectly. You can check on https://test.zajc.xyz/form.test.php.

allan

Absolutely awesome - thanks for posting back.

And that change stopped it from writing the primary id to all columns?! Wow.

Allan

zajc

I guess the reason is how oci8 deals whith the charset conversion. My database has (still) WIN1250 charset and without charset paramter oci_connect doesn't work as expected. I suspect this is oci8 2.x bug.

I also checked https://github.com/yajra/pdo-via-oci8 and the Oci8.php has this in the code @oci_connect($username, $password, $dsn, $charset);

allan

I'll add some documentation into the code and manual to highlight this. Thanks for letting me know your findings!

Allan