Editor SSP not behaving as expected with key arrays.

bbrindza

I have a simple DataTable Editor application.

When I use just 2 key values in my array it returns the row to the front -end as designed .

<?php

require( $_SERVER['DOCUMENT_ROOT']."/DataTables_Editor/php/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;

    Editor::inst( $db, 'NWFF.WEB_PAGE_ELEMENT_ACCESS', array('WEB_PAGE_NAME', 'ELEMENT_NAME'))
    ->fields(
          Field::inst( 'WEB_PAGE_NAME' ),   
          Field::inst( 'ELEMENT_NAME' ),
          Field::inst( 'USER_ID' ),
          Field::inst( 'USER_DEPARTMENT' ),
          Field::inst( 'USER_DEPARTMENT_NUMBER' ),
          Field::inst( 'COMMENTS' )
    )
 
    ->process( $_POST )
    ->json();

However if I add a third key value I get the following error.

DataTables warning: table id=elementAccessTable - Primary key element is not available in data set.

I need the 3 keys in order to update the data in the DB2 table.

DEBUG return data

SELECT  WEB_PAGE_NAME as "WEB_PAGE_NAME", ELEMENT_NAME as "ELEMENT_NAME", USER_ID as "USER_ID", USER_DEPARTMENT as "USER_DEPARTMENT", USER_DEPARTMENT_NUMBER as "USER_DEPARTMENT_NUMBER", COMMENTS as "COMMENTS" FROM  NWFF.WEB_PAGE_ELEMENT_ACCESS 

{"fieldErrors":[],"error":"Primary key element is not available in data set.","data":[],"ipOpts":[],"cancelled":[]}

<?php

require( $_SERVER['DOCUMENT_ROOT']."/DataTables_Editor/php/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;

    Editor::inst( $db, 'NWFF.WEB_PAGE_ELEMENT_ACCESS', array('WEB_PAGE_NAME', 'ELEMENT_NAME', 'USER_ID'))
    ->fields(
          Field::inst( 'WEB_PAGE_NAME' ),   
          Field::inst( 'ELEMENT_NAME' ),
          Field::inst( 'USER_ID' ),
          Field::inst( 'USER_DEPARTMENT' ),
          Field::inst( 'USER_DEPARTMENT_NUMBER' ),
          Field::inst( 'COMMENTS' )
    )
 
    ->process( $_POST )
    ->json();

allan

Is it possible that USER_ID is null for one or more rows in your table? That would cause what you are seeing.

Allan

bbrindza

Allan,

Yes there may by NULL values in the USER_ID.

There also my NULL values in the DEPARTMENT_NUMBER

So in the end my key array should look like this...

Editor::inst( $db, 'NWFF.WEB_PAGE_ELEMENT_ACCESS', array('WEB_PAGE_NAME', 'ELEMENT_NAME', 'USER_ID', 'USER_DEPARTMENT_NUMBER'))

This table allows for one of two different record keys. Either a USER_ID or a DEPARTMENT_MUMBER as part of the key value for updating a row.

How do I account for NULL Values in this scenario.

allan

The problem is here. The code currently doesn't allow for a null value as part of the compound primary key - it thinks the value is just missing. Even if it did allow it to fall through, it needs to be represented in text to allow it to be an ID for the client-side.

Is adding a serial to this table an option? That would remove the need for the compound key, while you can still keep any restrictions needed with the compound values through a validator.

Allan

bbrindza

I create a unique key column in the table and that did the trick, Thanks for your insight