Error in Editor -DataTables warning: table id=budgetRows_G58201 - Invalid JSON response.

bbrindza

It appears DataTable editor is does not use my POST parms for the where clause.

Console log error in debug

SELECT COUNT(id) as "cnt" FROM NWFF.PROJLOC WHERE PKRGNO = :where_0 AND PKCUST = :where_1 AND PKPROD = :where_2
SELECT COUNT(id) as "cnt" FROM NWFF.PROJLOC WHERE PKRGNO = ? AND PKCUST = ? AND PKPROD = ?

<br />
<b>Warning</b>: db2_prepare(): Statement Prepare Failed in <b>/www/zendphp74/htdocs/DataTables_Editor/php/Database/Driver/Db2/Query.php</b> on line <b>107</b><br />
<br />
<b>Warning</b>: db2_bind_param() expects parameter 1 to be resource, bool given in <b>/www/zendphp74/htdocs/DataTables_Editor/php/Database/Driver/Db2/Query.php</b> on line <b>130</b><br />
<br />
<b>Warning</b>: db2_bind_param() expects parameter 1 to be resource, bool given in <b>/www/zendphp74/htdocs/DataTables_Editor/php/Database/Driver/Db2/Query.php</b> on line <b>130</b><br />
<br />
<b>Warning</b>: db2_bind_param() expects parameter 1 to be resource, bool given in <b>/www/zendphp74/htdocs/DataTables_Editor/php/Database/Driver/Db2/Query.php</b> on line <b>130</b><br />
<br />
<b>Warning</b>: db2_execute() expects parameter 1 to be resource, bool given in <b>/www/zendphp74/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, bool given in <b>/www/zendphp74/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":[]}

POST Payload

draw: 1
columns[0][data]: BD$01
columns[0][name]:
columns[0][searchable]: true
columns[0][orderable]: true
columns[0][search][value]:
columns[0][search][regex]: false
columns[1][data]: BD$02
columns[1][name]:
columns[1][searchable]: true
columns[1][orderable]: true
columns[1][search][value]:
columns[1][search][regex]: false
columns[2][data]: BD$03
columns[2][name]:
columns[2][searchable]: true
columns[2][orderable]: true
columns[2][search][value]:
columns[2][search][regex]: false
columns[3][data]: BD$04
columns[3][name]:
columns[3][searchable]: true
columns[3][orderable]: true
columns[3][search][value]:
columns[3][search][regex]: false
columns[4][data]: BD$05
columns[4][name]:
columns[4][searchable]: true
columns[4][orderable]: true
columns[4][search][value]:
columns[4][search][regex]: false
columns[5][data]: BD$06
columns[5][name]:
columns[5][searchable]: true
columns[5][orderable]: true
columns[5][search][value]:
columns[5][search][regex]: false
columns[6][data]: BD$07
columns[6][name]:
columns[6][searchable]: true
columns[6][orderable]: true
columns[6][search][value]:
columns[6][search][regex]: false
columns[7][data]: BD$08
columns[7][name]:
columns[7][searchable]: true
columns[7][orderable]: true
columns[7][search][value]:
columns[7][search][regex]: false
columns[8][data]: BD$09
columns[8][name]:
columns[8][searchable]: true
columns[8][orderable]: true
columns[8][search][value]:
columns[8][search][regex]: false
columns[9][data]: BD$10
columns[9][name]:
columns[9][searchable]: true
columns[9][orderable]: true
columns[9][search][value]:
columns[9][search][regex]: false
columns[10][data]: BD$11
columns[10][name]:
columns[10][searchable]: true
columns[10][orderable]: true
columns[10][search][value]:
columns[10][search][regex]: false
columns[11][data]: BD$12
columns[11][name]:
columns[11][searchable]: true
columns[11][orderable]: true
columns[11][search][value]:
columns[11][search][regex]: false
order[0][column]: 0
order[0][dir]: asc
start: 0
length: 10
search[value]:
search[regex]: false
salespersonNumber: 5
customerNumber: 2572
productNumber: G58201

SSP Script

<?php
session_start();

$salespersonNumber=trim($_POST['salespersonNumber']);
$customerNumber=trim($_POST['customerNumber']);
$productNumber=trim($_POST['productNumber']);

// 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,
    DataTables\Editor\ValidateOptions;
 
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'NWFF.PROJLOC' )
    ->fields(
            
        Field::inst( 'BD$02' ),
        Field::inst( 'BD$03' ),
        Field::inst( 'BD$04' ),
        Field::inst( 'BD$05' ),
        Field::inst( 'BD$06' ),
        Field::inst( 'BD$07' ),
        Field::inst( 'BD$08' ),
        Field::inst( 'BD$09' ),
        Field::inst( 'BD$10' ),
        Field::inst( 'BD$11' ),
        Field::inst( 'BD$12' )
************    )
    
    ->where( function ( $q ) use ( $salespersonNumber, $customerNumber, $productNumber ) {
        $q->where( 'PKRGNO', $salespersonNumber );
        $q->where( 'PKCUST', $customerNumber );
        $q->where( 'PKPROD', $productNumber );
    } )
    
    ->debug(true)
    ->process( $_POST )
    ->json();
 

allan

What version of the Editor libraries are you using there? Line 107 is empty in the current file. Likely it is this line that is failing:

$this->_stmt = db2_prepare($resource, $paramSql);

After that line try adding:

if (! $this->_stmt) {
  echo db2_stmt_errormsg();
}

That might give us a clue what is going wrong.

Allan

bbrindza

Hi Allan,

DataTables Editor v1.6.5

I added that code.

**console log **
<br />
<b>Warning</b>: db2_prepare(): Statement Prepare Failed in <b>/www/zendphp74/htdocs/DataTables_Editor/php/Database/Driver/Db2/Query.php</b> on line <b>108</b><br />
Column or global variable ID not found. SQLCODE=-206<br />
<b>Warning</b>: db2_bind_param() expects parameter 1 to be resource, bool given in <b>/www/zendphp74/htdocs/DataTables_Editor/php/Database/Driver/Db2/Query.php</b> on line <b>133</b><br />
<br />
<b>Warning</b>: db2_bind_param() expects parameter 1 to be resource, bool given in <b>/www/zendphp74/htdocs/DataTables_Editor/php/Database/Driver/Db2/Query.php</b> on line <b>133</b><br />
<br />
<b>Warning</b>: db2_bind_param() expects parameter 1 to be resource, bool given in <b>/www/zendphp74/htdocs/DataTables_Editor/php/Database/Driver/Db2/Query.php</b> on line <b>133</b><br />
<br />
<b>Warning</b>: db2_execute() expects parameter 1 to be resource, bool given in <b>/www/zendphp74/htdocs/DataTables_Editor/php/Database/Driver/Db2/Query.php</b> on line <b>140</b><br />
<br />
<b>Warning</b>: db2_stmt_error() expects parameter 1 to be resource, bool given in <b>/www/zendphp74/htdocs/DataTables_Editor/php/Database/Driver/Db2/Query.php</b> on line <b>143</b><br />
{"fieldErrors":[],"error":"DB2 SQL error = ","data":[],"ipOpts":[],"cancelled":[],"debugSql":[]}

Here is my Query.php

<?php
/**
 * DB2 database driver for DataTables libraries.
 */

namespace DataTables\Database;
if (!defined('DATATABLES')) exit();

use DataTables\Database\Query;
use DataTables\Database\DriverDb2Result;

/**
 * DB2 driver for DataTables Database Query class
 *  @internal
 */
class DriverDb2Query extends Query {
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Private properties
     */
    private $_stmt;

    private $_editor_pkey_value;

    private $_sql;

    protected $_identifier_limiter = null;

    protected $_field_quote = '"';

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Public methods
     */

    static function connect( $user, $pass='', $host='', $port='', $db='', $dsn='' )
    {
        if ( is_array( $user ) ) {
            $opts = $user;
            $user = $opts['user'];
            $pass = $opts['pass'];
            $port = $opts['port'];
            $host = $opts['host'];
            $db   = $opts['db'];
            $dsn  = isset( $opts['dsn'] ) ? $opts['dsn'] : '';
        }

        $connStr = 'DATABASE='.$db.';HOSTNAME='.$host;
        if ( $port ) {
            $connStr .= ';PORT='.$port;
        }
        $connStr .= ';UID='.$user.';PWD='.$pass.';AUTHENTICATION=server';

//$conn = db2_connect( 'DATABASE=SAMPLE;HOSTNAME=localhost;PORT=50000;UID=db2inst1;PWD=mylifehasbeen;AUTHENTICATION=server', 'db2inst1', 'mylifehasbeen' );


        //$conn = db2_connect($connStr, $user, $pass);
        $conn = db2_connect($db, $user, $pass);

        if ( ! $conn ) {
            // If we can't establish a DB connection then we returna DataTables
            // error.
            $e = 'Connection failed: '.db2_conn_error().' : '.db2_conn_errormsg();

            echo json_encode( array(
                "error" => "An error occurred while connecting to the database ".
                    "'{$db}'. The error reported by the server was: ".$e
            ) );
            exit(0);
        }
    //  echo var_dump($conn);
        return $conn;
    }

    public static function transaction ( $conn )
    {
        // no op
    }

    public static function commit ( $conn )
    {
       // no op
    }

    public static function rollback ( $conn )
    {
       // no op
    }


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Protected methods
 */

 protected function _prepare($sql)
    {
        $this->_sql = $sql;
    }

    protected function _exec()
    {
 // echo $this->_sql."\n";
        $resource = $this->database()->resource();
        $bindings = $this->_bindings;

        $paramSql = preg_replace('/(:[a-zA-Z\-_0-9]*)/', '?', $this->_sql);
//echo $paramSql."\n";
//echo '<pre>'; print_r($paramSql."\n"); echo '</pre>';

        $this->_stmt = db2_prepare($resource, $paramSql);
        $stmt = $this->_stmt;

 // echo $this->_sql."\n";
 //echo $paramSql."\n";
         
        if (! $this->_stmt) {
            echo db2_stmt_errormsg();
        }
        
        preg_match_all('/(:[a-zA-Z\-_0-9]*)/', $this->_sql, $matches);
        
        //print_r( $matches );
        //print_r( $bindings);

        //$allanTest = 65;
        //db2_bind_param( $stmt, 1, 'allanTest', DB2_PARAM_IN );

        for ( $i=0, $ien=count($matches[0]) ; $i<$ien ; $i++ ) {
            for ( $j=0, $jen=count($bindings) ; $j<$jen ; $j++ ) {
                if ( $bindings[$j]['name'] === $matches[0][$i] ) {
                    $name = str_replace(':', '', $matches[0][$i]);
                    $$name = $bindings[$j]['value'];
                    //$_GLOBALS[ $name ] = $bindings[$j]['value'];

                    //echo "bind $name as ".$$name."\n";

                    db2_bind_param( $stmt, $i+1, $name, DB2_PARAM_IN );
                }
            }
        }

        //print_r( get_defined_vars() );

        $res = db2_execute($stmt);

        if (! $res) {
            throw new \Exception('DB2 SQL error = '.db2_stmt_error($this->_stmt));

            return false;
        }

        $resource = $this->database()->resource();
        return new DriverDb2Result($resource, $this->_stmt, $this->_editor_pkey_value);
    }

    protected function _build_table()
    {
        $out = array();

        for ($i = 0, $ien = count($this->_table); $i < $ien; $i ++) {
            $t = $this->_table[$i];

            if (strpos($t, ' as ')) {
                $a = explode(' as ', $t);
                $out[] = $a[0] . ' ' . $a[1];
            } else {
                $out[] = $t;
            }
        }

        return ' ' . implode(', ', $out) . ' ';
    }
}

allan

Column or global variable ID not found. SQLCODE=-206

Do you have an ID column in that table?

Allan

bbrindza

Hi Allan,

I am using idSrc: id, in the table. The id is a varible passed down from the parent row and is unique. (See line 40 in the JS provided)

Here is the expected returned ajax data

{"data":[{"nextYear_JanuaryBudget":".0","nextYear_FebruaryBudget":".0","nextYear_MarchBudget":".0","nextYear_AprilBudget":".0","nextYear_MayBudget":".0","nextYear_JuneBudget":".0","nextYear_JulyBudget":".0","nextYear_AugustBudget":".0","nextYear_SeptemberBudget":".0","nextYear_OctoberBudget":".0","nextYear_NovemberBudget":".0","nextYear_DecemberBudget":".0"}]}

Here is the front-end script.
This AJAX call is at the Event listener for opening and closing 3nd level child details at

  function format3(rowData) { 
        var childTable = '<table id="budgetRows_' + rowData.productNumber + '" class="display compact wrap w-100 cell-border" width="100%">' +
            '<thead style="display:none"></thead >' +
            '</table>';
        return $(childTable).toArray();
    }
    //*****************************************************************
    // Event listener for opening and closing 3nd level child details 
    //*****************************************************************
    $('tbody').on('click', 'td.detail-level-control_3', function () {
         var tr = $(this).closest('tr');
         var row = childTable2.row(tr);
         var rowData = row.data();

     if (row.child.isShown()) {
         // This row is already open - close it
         row.child.hide();
         tr.removeClass('shown');

         // Destroy the Child Datatable
         $('#budgetRows_' + rowData.productNumber).DataTable().destroy();
     }
     else {
         // Open this row
         row.child(format3(rowData)).show();
         var id = rowData.productNumber;
         productNumber = rowData.productNumber;

         editor = new $.fn.dataTable.Editor( {
             ajax: {
                     type: 'POST',
                     url: 'ssp_getSalepersonByCustomerBillTo_BudgetData.php',
                     data: { salespersonNumber: salespersonNumber,
                             customerNumber: customerNumber,
                             productNumber: productNumber
                       },
              },
             
             table: '#budgetRows_' + id,
             idSrc: id,
             fields: [ {
                     label: 'January Budget:',
                     name:  'BD$01'
                 }, {
                     label: 'February Budget:',
                     name:  'BD$02'
                 }, {
                     label: 'March Budget:',
                     name:  'BD$03'
                 }, {
                     label: 'April Budget:',
                     name:  'BD$04'
                 }, {
                     label: 'May Budget:',
                     name:  'BD$05'
                 }, {
                     label: 'June Budget:',
                     name:  'BD$06'
                 }, {
                     label: 'July Budget:',
                     name:  'BD$07'
                 }, {
                     label: 'August Budget:',
                     name:  'BD$08'
                 }, {
                     label: 'September Budget:',
                     name:  'BD$09'
                 }, {
                     label: 'October Budget:',
                     name:  'BD$10'
                 }, {
                     label: 'November Budget:',
                     name:  'BD$11'
                 }, {
                     label: 'December Budget:',
                     name:  'BD$12'
                 }
             ]
         } );
  
         // Activate an inline edit on click of a table cell
         $('#budgetRows_' + id).on( 'click', 'tbody td:not(:first-child)', function (e) {
             editor.inline( table.cell( this ).index(), {
                 onBlur: 'submit'
             } );
         } );

         childTable3 = $('#budgetRows_' + id).DataTable({
             dom: 't',
             ajax: {
                    type: 'POST',
                    url: 'ssp_getSalepersonByCustomerBillTo_BudgetData.php',
                    data: { salespersonNumber: salespersonNumber,
                             customerNumber: customerNumber,
                             productNumber: productNumber
                        },
                },
             serverSide: true,
            // order: [[ 1, 'asc' ]],
             columns: [
                 { data: 'BD$01'},
                 { data: 'BD$02'},
                 { data: 'BD$03'},
                 { data: 'BD$04'},
                 { data: 'BD$05'},
                 { data: 'BD$06'},
                 { data: 'BD$07'},
                 { data: 'BD$08'},
                 { data: 'BD$09'},
                 { data: 'BD$10'},
                 { data: 'BD$11'},
                 { data: 'BD$12'}
             ],
             select: {
                 style:    'os',
                 selector: 'td:first-child'
               },
              ]
        } );
    }

 }); 

allan

This is a server-side error though, not client-side.

My guess is that you are defining the third parameter for the Editor::inst() factory to be ID and in fact the column name is id perhaps?

Also if you are using the Editor PHP libraries, don't define idSrc. Editor will handle it for you.

Allan

bbrindza

OK I added a compound key to the Editor::inst()

Editor::inst( $db, 'NWFF.PROJLOC', array('PKCUST', 'PKPROD') )

All error went away except for the following..

Uncaught Unable to find row identifier For more information, please refer to https://datatables.net/tn/14

However I do have a DT_RowId

{"data":[{"DT_RowId":"row_25721629eb81G58201","PKRGNO":"5","PKCUST":"2572","PKPROD":"G58201","BD$01":".0","BD$02":".0","BD$03":".0","BD$04":".0","BD$05":".0","BD$06":".0","BD$07":".0","BD$08":".0","BD$09":".0","BD$10":".0","BD$11":".0","BD$12":".0"}],"options":[],"files":[],"draw":1,"recordsTotal":1,"recordsFiltered":1,"debugSql":[]}

The row is returned to the front-end but inline editing is not working

allan

Did you remove the idSrc from the Editor Javascript as well? It should pick DT_RowId automatically, but the error you are seeing would happen if the idSrc option was still there.

Allan

bbrindza

Forget to remove the one in the editor = new $.fn.dataTable.Editor( {

All good No errors

Thanks for you help.