recurring data conversion problems

keep on having problems with data conversion as data flows from DB2 table
to PHP to JSON to the browser.

PHP reads the data from the db2 table. Then it runs:
json_encode( $array, JSON_UNESCAPED_UNICODE )

which works most of the time. But sometimes it just returns an empty
string.

to troubleshoot that I run this code. It runs json_encode on each row of
the array. This code at least tells me which row has the encode error.

// an encode problem.
$encode_txt = json_encode( $ar1, JSON_UNESCAPED_UNICODE ) ;
if ((strlen($encode_txt) == 0 ) && ( count($ar1) > 0 ))
{
error_log('json_encode error. ' . count($ar1) . ' rows.' ) ;

// look for the row in the array with the encode problem.
$errCx = 0 ;
for( $ix = 0 ; $ix < count($ar1) ; ++$ix )
{
$row = $ar1[$ix] ;
$row_txt = json_encode( $row, JSON_UNESCAPED_UNICODE ) ;
if ((strlen($row_txt) == 0 ) && ( $errCx < 5 ))
{
error_log('row ' . $ix . ' could not encode') ;
error_log(print_r($row, TRUE)) ;
$errCx += 1 ;
}
}
}

been having this problem for a while. An early solution was to change the
CCSID of the data as it is stored in DB2. I set it to CCSID 1208. Which
seemed to correct the many problems I was having. But the problem has
persisted.

desc char(2000) not null default ' ' ccsid 1208,
notes char(2000) not null default ' ' ccsid 1208,

Is CCSID 1208 UTF-8 ? or double byte support?

to repair the data I run PHP code that reads from the table. Then it runs
utf8_encode on the column with the problem. And compares that result
against the input to the utf8_encode function. The idea being that if the
input and output are different, the data needs to be repaired to the
utf8_encode version. This actually has worked to fix things when I get
these data problems.

$sql = 'select a.catnum, a.catname, a.notes, a.desc ' .
' from repcat a ' ;

// connect and prepare the sql stmt.
$conn = as400Connect( $libl ) ;

$stmt = db2_prepare($conn, $sql) ;

// run the sql stmt.
$result = as400Execute($stmt, $sql) ;

// get back the first result set. Load into array $rsList
$ar1 = db2Stmt_ToManyRowArray( $stmt ) ;

// repair NOTES in REPCAT. loop for every row in result set.
for( $ix = 0 ; $ix < count($ar1) ; ++$ix )
{
$row = $ar1[$ix] ;
$col_names = array_keys($row) ;
$CATNUM = $row['CATNUM'] ;
$CATNAME = $row['CATNAME'] ;
$NOTES = $row['NOTES'] ;
$utf_notes = utf8_encode($NOTES) ;
if ($NOTES != $utf_notes)
{
echo $CATNAME . ' ' . $NOTES . '<br>' ;

$sql = 'update repcat a ' .
'set notes = ? ' .
'where a.catnum = ? ' ;
$stmt = db2_prepare($conn, $sql) ;
db2_bind_param( $stmt, 1, "utf_notes", DB2_PARAM_IN ) ;
db2_bind_param( $stmt, 2, "CATNUM", DB2_PARAM_IN ) ;
$result = as400Execute($stmt, $sql) ;
echo 'update of ' . $CATNAME . ' result:' . $result . '<br>' ;
}
}

the obvious solution is to scrub the data as it is updated on the IBM i. I
just do not know where to do that. And why I have to worry about it.

The user is copying and pasting descriptions and notes of a product into a
TEXTAREA in the browser. I use $.ajax to post to a PHP script which in
turns does a db2_execute to call an SQL procedure. The SQL procedure then
takes the notes as an input parameter and updates to the column in the DB2
table.

set vScrubText = replace(inVlu,x'41',' ') ;
set vScrubText = replace(vScrubText,x'3F',' ') ;
set vScrubText = replace(vScrubText,x'29',' ') ;
update repcat a
set notes = vScrubText
where a.Catnum = inCatnum ;

( when I use the SQL HEX function to look at the rows with the problems I
see X'41', X'3F', X'29'. That is the reason for running replace before
the update. But I am not sure that even applies with CCSID 1208. )

What I am looking to know is what CCSID should I use in the DB2 table. And
how should the data that the user is entering be scrubbed before updating
to the DB2 table.

thanks,