DataTable Editor issue with updating inline data

bbrindza

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();

allan

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

bbrindza

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":[]}

allan

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

bbrindza

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.

allan

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

Allan

bbrindza

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 > ``` ?>

allan

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

bbrindza

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?

bbrindza

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<

allan

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

bbrindza

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

You the man.