Oracle - insert and edit problem on joined table

johann_mader · January 2017

Hi all,

i have a problem with one of my tables. It has joined data on an oracle-db, when trying to edit a row or insert a new one.

Here are my codes:

PHP

<?php

/*
 * Editor server script for DB table STG_TAB_ARTIKEL
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

    
    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'STG_TAB_ARTIKEL a', 'a.PK_ARTIKEL_ID' )
        ->debug(true)
        ->fields(
            Field::inst( 'a.ARTNR' )
                ->validator( 'Validate::unique' )
                ->validator( 'Validate::required' )
                ->validator( 'Validate::numeric' ),
            Field::inst( 'a.FK_STG_TAB_ARTKAT' )
                ->options( Options::inst()
                    ->table( 'STG_TAB_ARTIKELKATEGORIE' )
                    ->value( 'ARTKATNR' )
                    ->label( 'ARTKAT' )
                )
                ->validator( 'Validate::dbValues' ),
            Field::inst( 'a.ARTBEZ' )
                ->validator( 'Validate::notEmpty' ),
            Field::inst( 'a.ARTMAß' ),
            Field::inst( 'ak.ARTKAT' )
        )
        ->leftJoin( 'STG_TAB_ARTIKELKATEGORIE ak', 'ak.ARTKATNR', '=', 'a.FK_STG_TAB_ARTKAT')
        ->process( $_POST )
        ->json();


<?php
>
```
?>




JS

/*
* Editor client script for DB table STG_TAB_KUNDEN
* Created by http://editor.datatables.net/generator
*/

(function($){

$(document).ready(function() {
var editor = new $.fn.dataTable.Editor( {
ajax: 'sources/table.STG_TAB_ARTIKEL.php',
table: '#STG_TAB_ARTIKEL',
fields: [
{
"label": "Artikel:",
"name": "a.ARTNR"

        },
        {
            "label": "Artikelkategorie:",
            "name": "a.FK_STG_TAB_ARTKAT",
            "type": "select",
            "placeholder" :  "Bitte Kategorie auswählen.."
        },
        {
            "label": "Bezeichnung:",
            "name": "a.ARTBEZ"
        },
        {
            "label": "Maße:",
            "name": "a.ARTMAß"
        }
    ]
} );


var table = $('#STG_TAB_ARTIKEL').DataTable( {
    dom: 'Bfrtip',
    ajax: 'sources/table.STG_TAB_ARTIKEL.php',
            serverSide: true,
    scrollY:        '70vh',
    scrollCollapse: true,
    paging:         false,
    cache: true,
    columns: [
        {
            "data": "a.ARTNR"
        },
        {
            "data": "ak.ARTKAT"
        },
        {
            "data": "a.ARTBEZ"
        },
        {
            "data": "a.ARTMAß"
        }
    ],
    select: "single",
    lengthChange: false,
    buttons: [
        { extend: 'create', editor: editor }
        ,{ extend: 'edit',   editor: editor }
    ]
} );

} );

}(jQuery));
```

On debbuging the data-Header seems correct:

data[row_5760][a][ARTNR]:-2
data[row_5760][a][FK_STG_TAB_ARTKAT]:23
data[row_5760][a][ARTBEZ]:HTV - Einweg
data[row_5760][a][ARTMAß]:0x0

But on the debugSql-array on Preview Page doesn't start an update:
(see attachment) [2]query: SELECT a.PK_ARTIKEL_ID as "a.PK_ARTIKEL_ID", a.ARTNR as "a.ARTNR", a.FK_STG_TAB_ARTKAT as "a.FK_STG_TAB_ARTKAT", a.ARTBEZ as "a.ARTBEZ", a.ARTMAß as "a.ARTMAß", ak.ARTKAT as "ak.ARTKAT" FROM STG_TAB_ARTIKEL a LEFT JOIN STG_TAB_ARTIKELKATEGORIE ak ON ak.ARTKATNR = a.FK_STG_TAB_ARTKAT WHERE a.PK_ARTIKEL_ID = :where_0

Don't know why it isn't working, i have another table w/o the leftJoin() param which is working perfectly.

Looking forward to any help!
Thanks in advance

Br Toni

allan · January 2017

Hi,

Could you clarify in what way it isn't working please? Or specifically what happens a the client-side? You click submit and then? Does the Editor form disappear, or does it stay where it is?

Any information shown in your server's error log?

Thanks,
Allan

johann_mader · January 2017

Hi Allan,

the Editor form disappears after clicking submit and acts like it has done everything as expected. It also refreshes the data. But no insert nor update statement on use of the Insert / Edit Button is triggered.

The debugSql Array shows no insert/update statement, i hope i understand this array correctly, because i think this is the result of the whole db-communication, isn't it?

I can't find any errors on the server's log.

Br Toni

allan · January 2017

i think this is the result of the whole db-communication, isn't it?

It should be yes.

Are you able to give me a link to the page so I can attempt to debug the issue please?

Thanks,
Allan

johann_mader · January 2017

Hi Alan,

i will send you a Teamviewer for debugging.

Thanks in Advance,
Toni

allan · January 2017

Hi Toni,

Could you try the attached Query.php for the Oracle driver in Editor please? It won't solve any issues as such, but it does include better error handling. So if there is an SQL error that will be correctly reported now.

Thanks,
Allan

johann_mader · January 2017

Hi Allan,

thank you really much! It seems that i have trouble with the name of a coloumn.. Someone thought it was a good idea to use special characters like "ß" for the column.

On the initialization datatables is sending this as "?" to the db. So there is my error i guess

Do I have any chance of getting it fixed by editing datatables or do i have to edit the database itself?
(There is a big process flow behind the table which causes a lot of time to edit..)

Again - thank you really much for the help
Toni

allan · January 2017

Could you have a look at what DataTables is sending to the server using the browser's dev tools? That part at least should really support UTF-8. I'm not entirely sure what character set Oracle expects on its interface - I would have assumed UTF-8.

Allan

johann_mader · January 2017

Hi Allan,

well there is no encoding on the headers, as far as i can see. Or do I look in the wrong place?

Teamviewer would be up again, if you want to look for yourself.

Thank you,
Toni

allan · January 2017

It might be worth having a look at zajc's last post in this thread which suggests a possible fix. It sounds like it might be the same issue you are running into.

Allan

johann_mader · February 2017

Hi Allan,

I did the change on the connect with the charset parameter - now it is showing the data as expected and I have no more problem with the letter.

Unfortunately, the edit button does continue to select data from the db and does not try to update it.

debugSql is showing only selects as statements.

Thank you for your help,
Toni

johann_mader · September 2017

Hi Allan,

the problem still exists.
We get our data as exprected. Everything is showing up correct.
But we can't create new entries or edit/delete existing entries.
If i try to delete an entry, the entry will no be showed in the datatable till i refresh the page.
I got no errors on client or server.

If i build this without leftjoin() and only show the field a.FK_STG_TAB_ARTKAT it works.

PHP

<?php

/*
 * Editor server script for DB table STG_TAB_KUNDEN
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

    
    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'STG_TAB_ARTIKEL a', 'a.PK_ARTIKEL_ID' )
        ->debug(true)
        ->fields(
            Field::inst( 'a.ARTNR' )
                ->validator( 'Validate::unique' )
                ->validator( 'Validate::required' )
                ->validator( 'Validate::numeric' ),
            Field::inst( 'a.FK_STG_TAB_ARTKAT' )
                ->options( Options::inst()
                    ->table( 'STG_TAB_ARTIKELKATEGORIE' )
                    ->value( 'ARTKATNR' )
                    ->label( 'ARTKAT' )
                )
                ->validator( 'Validate::dbValues' ),
            Field::inst( 'a.ARTBEZ' )
                ->validator( 'Validate::notEmpty' ),
            Field::inst( 'a.ARTMAß' ),
            Field::inst( 'ak.ARTKAT' )
                ->set(false)
        )
        ->leftJoin( 'STG_TAB_ARTIKELKATEGORIE ak', 'ak.ARTKATNR', '=', 'a.FK_STG_TAB_ARTKAT')
        ->process( $_POST )
        ->json();


<?php
>
```
JS
```
/*
 * Editor client script for DB table STG_TAB_ARTIKEL
 * Created by http://editor.datatables.net/generator
 */
?>


(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'sources/table.STG_TAB_ARTIKEL.php',
        table: '#STG_TAB_ARTIKEL',
        fields: [
            {
                "label": "Artikel:",
                "name": "a.ARTNR"
                
            }
            ,{
                "label": "Artikelkategorie:",
                "name": "a.FK_STG_TAB_ARTKAT",
                "type": "select",
                "placeholder" :  "Bitte Kategorie auswählen.."
            }
            ,{
                "label": "Bezeichnung:",
                "name": "a.ARTBEZ"
            }
            ,{
                "label": "Maße:",
                "name": "a.ARTMAß"
            }
        ]
    } );
    

    var table = $('#STG_TAB_ARTIKEL').DataTable( {
        dom: 'Bfrtip',
        ajax: 'sources/table.STG_TAB_ARTIKEL.php',
        scrollY:        '80vh',
        paging: false,
        columns: [
            {
                "data": "a.ARTNR"
            }
            ,{
                "data": "ak.ARTKAT"
            }
            ,{
                "data": "a.ARTBEZ"
            }
            ,{
                "data": "a.ARTMAß"
            }
        ],
        select: "single",
        lengthChange: false,
        buttons: [
            { extend: 'create', editor: editor }
            ,{ extend: 'edit',   editor: editor }
            ,{ extend: 'remove',   editor: editor }
        ]
    } );
} );

}(jQuery));

Debug output on edit.

{data: [,…], debugSql: [{,…},…]}
data
:
[,…]
debugSql
:
[{,…},…]
0
:
{,…}
bindings
:
[{name: ":where_0", value: "-5", type: null}, {name: ":where_1", value: "5802", type: null}]
0
:
{name: ":where_0", value: "-5", type: null}
name
:
":where_0"
type
:
null
value
:
"-5"
1
:
{name: ":where_1", value: "5802", type: null}
name
:
":where_1"
type
:
null
value
:
"5802"
query
:
"SELECT  a.ARTNR as "a.ARTNR" FROM  STG_TAB_ARTIKEL a WHERE a.ARTNR = :where_0 AND a.PK_ARTIKEL_ID != :where_1 "
1
:
{query: "SELECT ARTKATNR as "ARTKATNR" FROM STG_TAB_ARTIKELKATEGORIE WHERE ARTKATNR = :where_0 ",…}
bindings
:
[{name: ":where_0", value: "-1", type: null}]
0
:
{name: ":where_0", value: "-1", type: null}
query
:
"SELECT  ARTKATNR as "ARTKATNR" FROM  STG_TAB_ARTIKELKATEGORIE WHERE ARTKATNR = :where_0 "
2
:
{,…}
bindings
:
[{name: ":where_0", value: "5802", type: null}]
0
:
{name: ":where_0", value: "5802", type: null}
name
:
":where_0"
type
:
null
value
:
"5802"
query
:
"SELECT  a.PK_ARTIKEL_ID as "a.PK_ARTIKEL_ID", a.ARTNR as "a.ARTNR", a.FK_STG_TAB_ARTKAT as "a.FK_STG_TAB_ARTKAT", a.ARTBEZ as "a.ARTBEZ", a.ARTMAß as "a.ARTMAß", ak.ARTKAT as "ak.ARTKAT" FROM  STG_TAB_ARTIKEL a LEFT JOIN STG_TAB_ARTIKELKATEGORIE ak ON ak.ARTKATNR = a.FK_STG_TAB_ARTKAT WHERE a.PK_ARTIKEL_ID = :where_0 "

Thanks for your help.
Greetings

johann_mader · September 2017

And if i try to delete i don't get any debug...

Greetings

johann_mader · September 2017

Found a solution.
I'm now using Mjoin instead of leftjoin and everything works like expected.

PHP

<?php

/*
 * Editor server script for DB table STG_TAB_KUNDEN
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

    
    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'STG_TAB_ARTIKEL', 'PK_ARTIKEL_ID' )
        ->debug(true)
        ->fields(
            Field::inst( 'ARTNR' )
                ->validator( 'Validate::unique' )
                ->validator( 'Validate::required' )
                ->validator( 'Validate::numeric' ),
            Field::inst( 'FK_STG_TAB_ARTKAT' )
                ->options( Options::inst()
                    ->table( 'STG_TAB_ARTIKELKATEGORIE' )
                    ->value( 'ARTKATNR' )
                    ->label( 'ARTKAT' )
                )
                ->validator( 'Validate::dbValues' ),
            Field::inst( 'ARTBEZ' )
                ->validator( 'Validate::notEmpty' ),
            Field::inst( 'ARTMAß' )
        )
        ->join(
            Mjoin::inst( 'STG_TAB_ARTIKELKATEGORIE' )
                ->aliasParentTable( 'a' )
                ->link('a.FK_STG_TAB_ARTKAT','STG_TAB_ARTIKELKATEGORIE.ARTKATNR')
                ->fields(
                    Field::inst( 'ARTKAT' )
                        ->set(false)
                )
        )
        ->process( $_POST )
        ->json();

<?php
>
```
JS
```
/*
 * Editor client script for DB table STG_TAB_ARTIKEL
 * Created by http://editor.datatables.net/generator
 */
?>


(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'sources/table.STG_TAB_ARTIKEL.php',
        table: '#STG_TAB_ARTIKEL',
        fields: [
            {
                "label": "Artikel:",
                "name": "ARTNR"
                
            }
            ,{
                "label": "Artikelkategorie:",
                "name": "FK_STG_TAB_ARTKAT",
                "type": "select",
                "placeholder" :  "Bitte Kategorie auswählen.."
            }
            ,{
                "label": "Bezeichnung:",
                "name": "ARTBEZ"
            }
            ,{
                "label": "Maße:",
                "name": "ARTMAß"
            }
        ]
    } );
    

    var table = $('#STG_TAB_ARTIKEL').DataTable( {
        dom: 'Bfrtip',
        ajax: 'sources/table.STG_TAB_ARTIKEL.php',
        scrollY:        '80vh',
        paging: false,
        columns: [
            {
                "data": "ARTNR"
            }
            ,{
                "data": "STG_TAB_ARTIKELKATEGORIE[].ARTKAT"
            }
            ,{
                "data": "ARTBEZ"
            }
            ,{
                "data": "ARTMAß"
            }
        ],
        select: "single",
        lengthChange: false,
        buttons: [
            { extend: 'create', editor: editor }
            ,{ extend: 'edit',   editor: editor }
            ,{ extend: 'remove',   editor: editor }
        ]
    } );
} );

}(jQuery));

Thanks for your support
Greetings

allan · September 2017

Hi Johann,

Could you let me know what version of the libraries you are using? I released 1.6.5 yesterday afternoon which I believe should address this issue. Assuming I'm correct, it is being caused by the use of the alias in the table name, which the libraries weren't correctly handling without an as which of course Oracle doesn't support.

Allan

Oracle - insert and edit problem on joined table

Oracle - insert and edit problem on joined table

Replies

Howdy, Stranger!

Categories

DataTables

Oracle - insert and edit problem on joined table

Oracle - insert and edit problem on joined table

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables