Oracle and datatables 1.6.x BROKEN!!!
Oracle and datatables 1.6.x BROKEN!!!
zajc
Posts: 67Questions: 10Answers: 2
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.
This discussion has been closed.
Replies
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 theoci
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
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.
Rather than selecting the sequence, Editor uses:
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.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
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.
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
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.
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: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
This is config.php
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
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 '???????','???????'.
Just sent you a PM .
Allan
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
parametercharset=utf8
toutf8
or maybe you add the new variable
charset
which is more logical as the 4th oci_connect paramter is charset.Here is my proposal.
You must fix the Query.php the line:
to
or more logical
but then you must also fix the
function connect
.Now it works perfectly. You can check on https://test.zajc.xyz/form.test.php.
Absolutely awesome - thanks for posting back.
And that change stopped it from writing the primary id to all columns?! Wow.
Allan
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);
I'll add some documentation into the code and manual to highlight this. Thanks for letting me know your findings!
Allan