DataTable Editor server side script error

bbrindza

I have a simple SSP script that is throw this error. "Primary key element is not available in data set." Any thoughts?

<?php

// DataTables PHP library
require( $_SERVER['DOCUMENT_ROOT']."/DataTables_Editor/php/DataTables.php" );
session_start();
// 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, 'XXXD.WEB_PAGE_ELEMENT_ACCESS', array('WEB_PAGE_NAME', 'ELEMENT_NAME', 'USER_ID'))
                                
->debug(true)
    ->fields(
          Field::inst( 'WEB_PAGE_NAME' ),   
          Field::inst( 'ELEMENT_NAME' ),
          Field::inst( 'USER_ID' ),
          Field::inst( 'USER_DEPARTMENT' ),
          Field::inst( 'COMMENTS' ),
          Field::inst( 'USER_DEPARTMENT_NUMBER' )
    )
    ->process( $_POST )
    ->json();

allan

I suspect its the XXXD. prefix that is causing it issues there. Are you able to set a search path or something so that isn't required?

Allan

bbrindza

Allan ,
The XXXD. is an IBM DB2 schema name. DB2 on an IBM Power System requires the schema to be used in PHP SQL statements.

The script will return data when my compound key is no more then 2 fields.

Editor::inst( $db, 'XXXD.WEB_PAGE_ELEMENT_ACCESS', array('WEB_PAGE_NAME', 'ELEMENT_NAME'))

I get this error only when my compound key is greater than 2 fields.

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

I'm not sure how I would set a search path for a DB2 schema on an IBM i in PHP and I'm not sure that this can be achieved.

Would you have any insight on why the DataTables Editor SSP classes would fail on compound keys greater than 2 in this environment?

allan

Have you included USER_ID as a Field in the Editor list of fields? Its odd that it would work with two but not three - its just a simple for loop that part of the code, so it should be able to handle any number of fields in the compound key - as long as they are listed in the Field array.

Allan

bbrindza

Sure is . Look at my original post.

allan

Oops - sorry - missed that! Could you show me the SQL that it is executing? (in the JSON return since you've got debugging enabled).

Allan

bbrindza

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

bbrindza

Any more thought regarding this?

bbrindza

Allan ,

I need to revisit this issue. You never replied back to me.

allan

Hi,

Sorry, the db2 aspect hasn't evolved much (I think you are the only one using it), so I'm not sure what's going on here. The JSOn return doesn't actually show any executed SQL there. Are you using the 1.9 release of Editor?

Allan

bbrindza

No I am not using that version. Has the ssp PHP scripts in the 1.9 version changed?

bbrindza

If you recall you and I modified the versions of the PHP server side script to run in our DB2 environment.

allan

Yes, I remember. Those changes should have been folded into the current release. It might be worth trying the 1.9.0 files (backing up the old ones, or moving them to a temporary location) and see if you are still getting the same error.

Allan

bbrindza

I will try that and get back to you with results.

bbrindza

Hi Allan,

It has been a while since this post, however, I need to resurrect this issue. I have installed the latest version of Editor-PHP-1.9.2. I am still having the same issue.

Any thoughts on how to resolve this?

allan

If you add ->debug(true) just before the ->process(...) call, what does the JSON response from the server show please?

Thanks,
Allan

bbrindza

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

bbrindza

This is the updated script using the DB2 library name.

<?php
// DataTables PHP library
require( $_SERVER['DOCUMENT_ROOT']."/DataTables_Editor/php/DataTables.php" );
//session_start();
// 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', 'NWFF.WEB_PAGE_ELEMENT_ACCESS.WEB_PAGE_NAME')
 Editor::inst( $db, 'NWFF.WEB_PAGE_ELEMENT_ACCESS', array('NWFF.WEB_PAGE_ELEMENT_ACCESS.ELEMENT_NAME', 'NWFF.WEB_PAGE_ELEMENT_ACCESS.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' )
    )
 //  ->debug(true)
    ->process( $_POST )
    ->json();

allan

I suspect you'll need to include the NWFF.WEB_PAGE_ELEMENT_ACCESS. prefix in the field names as well.

Allan

bbrindza

I will give that a try.

bbrindza

OK . We got further in . But new errors.

<br />
<b>Warning</b>: db2_prepare(): Statement Prepare Failed in <b>/www/zendphp7/htdocs/DataTables_Editor/php/Database/Driver/Db2/Query.php</b> on line <b>107</b><br />
<br />
<b>Warning</b>: db2_execute() expects parameter 1 to be resource, boolean given in <b>/www/zendphp7/htdocs/DataTables_Editor/php/Database/Driver/Db2/Query.php</b> on line <b>137</b><br />
<br />
<b>Warning</b>: db2_stmt_error() expects parameter 1 to be resource, boolean given in <b>/www/zendphp7/htdocs/DataTables_Editor/php/Database/Driver/Db2/Query.php</b> on line <b>140</b><br />
{"fieldErrors":[],"error":"DB2 SQL error = ","data":[],"ipOpts":[],"cancelled":[],"debugSql":[]}

<?php

// DataTables PHP library
require( $_SERVER['DOCUMENT_ROOT']."/DataTables_Editor/php/DataTables.php" );
//session_start();
// 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', 'NWFF.WEB_PAGE_ELEMENT_ACCESS.WEB_PAGE_NAME')
 Editor::inst( $db, 'XXXD.WEB_PAGE_ELEMENT_ACCESS', array('NWFF.WEB_PAGE_ELEMENT_ACCESS.WEB_PAGE_NAME', 'NWFF.WEB_PAGE_ELEMENT_ACCESS.ELEMENT_NAME', 'NWFF.WEB_PAGE_ELEMENT_ACCESS.USER_ID'))
    ->fields(
          Field::inst( 'NWFF.WEB_PAGE_ELEMENT_ACCESS.WEB_PAGE_NAME' ),   
          Field::inst( 'NWFF.WEB_PAGE_ELEMENT_ACCESS.ELEMENT_NAME' ),
          Field::inst( 'NWFF.WEB_PAGE_ELEMENT_ACCESS.USER_ID' ),
          Field::inst( 'NWFF.WEB_PAGE_ELEMENT_ACCESS.USER_DEPARTMENT' ),
          Field::inst( 'NWFF.WEB_PAGE_ELEMENT_ACCESS.USER_DEPARTMENT_NUMBER' ),
          Field::inst( 'NWFF.WEB_PAGE_ELEMENT_ACCESS.COMMENTS' )
    )
  ->debug(true)
    ->process( $_POST )
    ->json();

bbrindza

** Disregard the post. I Had an error in the script. 'XXXD.WEB_PAGE_ELEMENT_ACCESS
Should be
'NWFF.WEB_PAGE_ELEMENT_ACCESS

Any way I am still this error ...
{"fieldErrors":[],"error":"Primary key element is not available in data set.","data":[],"ipOpts":[],"cancelled":[],"debugSql":[]}

<?php

// DataTables PHP library
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', 'NWFF.WEB_PAGE_ELEMENT_ACCESS.WEB_PAGE_NAME')
 Editor::inst( $db, 'NWFF.WEB_PAGE_ELEMENT_ACCESS', array('NWFF.WEB_PAGE_ELEMENT_ACCESS.WEB_PAGE_NAME', 'NWFF.WEB_PAGE_ELEMENT_ACCESS.ELEMENT_NAME', 'NWFF.WEB_PAGE_ELEMENT_ACCESS.USER_ID'))
    ->fields(
          Field::inst( 'NWFF.WEB_PAGE_ELEMENT_ACCESS.WEB_PAGE_NAME' ),   
          Field::inst( 'NWFF.WEB_PAGE_ELEMENT_ACCESS.ELEMENT_NAME' ),
          Field::inst( 'NWFF.WEB_PAGE_ELEMENT_ACCESS.USER_ID' ),
          Field::inst( 'NWFF.WEB_PAGE_ELEMENT_ACCESS.USER_DEPARTMENT' ),
          Field::inst( 'NWFF.WEB_PAGE_ELEMENT_ACCESS.USER_DEPARTMENT_NUMBER' ),
          Field::inst( 'NWFF.WEB_PAGE_ELEMENT_ACCESS.COMMENTS' )
    )
  ->debug(true)
    ->process( $_POST )
    ->json();