DataTable Editor issue with updating inline data

DataTable Editor issue with updating inline data

bbrindzabbrindza Posts: 308Questions: 70Answers: 1

I am getting on error when updating my back-end database using inline row editing. 1.3.3+: A system error has occurred

My guess is the row id, it contains odd characters, 0c950202 between the actual data. I am not sure where this is coming from or what it is. We use DB2.

The row id should be id="row_SOU55148R01871"

KEY array = 'PKLOC', 'PKRGNO', 'PKSLNO', 'PKCUST', 'PKPROD'

PKLOC = SOU
PKRGNO = 5
PKSLNO = 5
PKCUST = 148
PKPROD = R01871

SSP script

<?php
session_start();

$salespersonNumber=trim($_REQUEST['salespersonNumber']);
$customerNumber=trim($_REQUEST['customerNumber']);
$productNumber=trim($_REQUEST['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;
 
// Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'NWFF.PROJLOC', array('PKLOC', 'PKRGNO', 'PKSLNO', 'PKCUST', 'PKPROD' ))
    
        ->debug(true)
        
        ->fields(
       
        Field::inst( 'PKLOC' ) -> set(false),  // Used to disable editing to key field
        Field::inst( 'PKRGNO' ) -> set(false),  // Used to disable editing to key field
        Field::inst( 'PKSLNO' ) -> set(false),  // Used to disable editing to key field
        Field::inst( 'PKCUST' ) -> set(false),  // Used to disable editing to key field
        Field::inst( 'PKPROD' ) -> set(false),  // Used to disable editing to key field
        
        Field::inst( 'BD$01' ),
        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 );
        } )
        
    ->process( $_POST )
    ->json();

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 62,435Questions: 1Answers: 10,246 Site admin

    What is the response from the server? Normally it will have an error message in it indicating what the problem is. If it doesn't, check the error logs for the server or add:

    error_reporting(E_ALL);
    ini_set('display_errors', '1');
    

    At the top of your SSP script.

    Allan

  • bbrindzabbrindza Posts: 308Questions: 70Answers: 1

    Here are the errors

    SELECT * FROM NWFF.PROJLOC WHERE PKLOC = :where_0 AND PKRGNO = :where_1 AND PKSLNO = :where_2 AND PKCUST = :where_3 AND PKPROD = :where_4
    <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 />
    UPDATE NWFF.PROJLOC SET BD$01 = :BD$01 WHERE PKLOC = :where_0 AND PKRGNO = :where_1 AND PKSLNO = :where_2 AND PKCUST = :where_3 AND PKPROD = :where_4
    Token $01 was not valid. Valid tokens: USE SKIP WAIT WITH FETCH LIMIT ORDER WHERE OFFSET. SQLCODE=-104<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>135</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>135</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>135</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>135</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>135</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>142</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>145</b><br />
    {"fieldErrors":[],"error":"DB2 SQL error = ","data":[],"ipOpts":[],"cancelled":[],"debugSql":[]}

  • allanallan Posts: 62,435Questions: 1Answers: 10,246 Site admin

    Many thanks. It looks like the root issue is the $ in the column names. The column names aren't being escaped in the insert statement, so we end up with:

    UPDATE NWFF.PROJLOC SET BD$01 = ....
    

    which it looks like isn't valid in DB2.

    If I understand the DB2 documentation correctly, then the identifier should have double quotes around it?

    UPDATE NWFF.PROJLOC SET "BD$01" = ....
    

    Assuming that to be correct, find this line in the copy of the code you are running and replace with:

    protected $_identifier_limiter = '"';
    

    Allan

  • bbrindzabbrindza Posts: 308Questions: 70Answers: 1

    That did not seem to work. It could be the $ in the column name (BD$01). Allow me to test out this theory and I will get back to you.

  • allanallan Posts: 62,435Questions: 1Answers: 10,246 Site admin

    Yup, that is what I was assuming it was. If it were the CLI, how would you write that query normally?

    Allan

  • bbrindzabbrindza Posts: 308Questions: 70Answers: 1

    In the IBM world we use IBM i Client Access Solutions where we test out SQL results prior to using them in a PHP .

    The following SQL works in our CLI environment

    update NWFF.PROJLOC set BD$01='6.3' where PKLOC='SOU'and PKSLNO='5'and PKRGNO='5'and PKCUST='148'and PKPROD='R01871

    Keep in mind that IBM tables allow for column names that contain $ or # .

    Also in the following test PHP script, the BD$01 column is used and updates the column without any problem.

    ```php
    <?php

    require_once '../../nwffunctions/i5db2connect.php';

    $sql = "update NWFF.PROJLOC set BD$01='6.3' where PKLOC='SOU'and PKSLNO='5'and PKRGNO='5'and PKCUST='148'and PKPROD='R01871'";

    // PROJLOC update
    $stmt = db2_prepare( $connection, $sql )
    or die("<br>Prepare failed!". db2_stmt_errormsg());

    db2_execute( $stmt )
    or die("<br>Execute failed". db2_stmt_errormsg());

    db2_close ( $connection );
    
    <?php > ``` ?>
  • allanallan Posts: 62,435Questions: 1Answers: 10,246 Site admin

    How interesting - thank you!

    Two things:

    1. Are you able to tweak that Editor so it only has columns that don't have a $ in them? If an edit on such a setup works then it would confirm that the $ is the issue.
    2. If you comment this line back in, what is the response from the server? (Note Editor will give an error since it will not be valid JSON! Also you need to load the table and then make the change to the file, otherwise the table won't load due to the invalid JSON!).

    Thanks,
    Allan

  • bbrindzabbrindza Posts: 308Questions: 70Answers: 1
    edited April 2023

    Allan,

    I remove the column names that have a $ in them and add 2 new column that 2 that do not have a $ .

    It works

    What is you thought on the handling this? And where in the ssp classes is this failing?

  • bbrindzabbrindza Posts: 308Questions: 70Answers: 1

    When I use the column with $ tis is what I see in the debug.
    UPDATE NWFF.PROJLOC SET BD$01 = :BD$01 WHERE PKLOC = :where_0 AND PKRGNO = :where_1 AND PKSLNO = :where_2 AND PKCUST = :where_3 AND PKPROD = :where_4
    Token $01 was not valid. Valid tokens: USE SKIP WAIT WITH FETCH LIMIT ORDER WHERE OFFSET. SQLCODE=-104<

  • allanallan Posts: 62,435Questions: 1Answers: 10,246 Site admin
    Answer ✓

    Perfect - thank you. I think I've just worked it out - I'd assumed that the parameter names would always be :[a-zA-Z\-_0-9]*. The problem isn't the column name, it is the binding parameter. Since the db2 functions don't bind on name, but rather on position, I had to use a regex to swap the indexes in:

    '/(:[a-zA-Z\-_0-9]*)/'

    If you were to replace that (both instances of it - lines 101 and 108) with:

    '/(:[a-zA-Z\-_0-9\$]*)/'
    

    then I believe that should allow it to spring into action.

    Regards,
    Allan

  • bbrindzabbrindza Posts: 308Questions: 70Answers: 1
    edited April 2023

    Bingo! That did it. Will this change be in the latest version of DataTable Editor for the DB2 driver?

    You the man.

Sign In or Register to comment.