here is some demo code. The code creates a table in QGPL named DEMOTEXT.
Writes the text "ci·ré ci·ré " to that table. Then reads that text back.
Sometimes when I run the code from the browser the text displays
correctly. Other times it displays with garbled characters at the end of
the line. Press CTRL F5 to run it again and again.
what the output looks like:
start dump demotext.
KEY:672 TEXT:abc
KEY:673 TEXT:ci·ré ci·ré ci·ré ci·ré ci·ré ci·ré ��
KEY:674 TEXT:ci·ré ci·ré ci·ré ci·ré ci·ré ci·ré ��
KEY:675 TEXT:ci·ré ci·ré ci·ré ci·ré ci·ré ci·ré ��
KEY:676 TEXT:ci·ré ci·ré ci·ré ci·ré ci·ré ci·ré ��
KEY:677 TEXT:ci·ré ci·ré ci·ré ci·ré ci·ré ci·ré ��
KEY:678 TEXT:ci·ré ci·ré ci·ré ci·ré ci·ré ci·ré ��
KEY:679 TEXT:ci·ré ci·ré ci·ré ci·ré ci·ré ci·ré ��
KEY:680 TEXT:ci·ré ci·ré ci·ré ci·ré ci·ré ci·ré ��
KEY:681 TEXT:ci·ré ci·ré ci·ré ci·ré ci·ré ci·ré ��
KEY:682 TEXT:ci·ré ci·ré ci·ré ci·ré ci·ré ci·ré ��
end dump demotext.
I ran this code on a 2nd IBM i. This one with SYSVAL QCCSID = 37
<?php
// demo of problem with reading extended character set text from ibm i.
header("Content-type: text/html");
$libl = 'QGPL' ;
$conn = demo_connect( $libl ) ;
// dump what is in the table from last run.
if ( checkTableExists($conn, 'DEMOTEXT', 'QGPL') == true )
{
dumpDemoText( $conn ) ;
}
// create table
if ( checkTableExists($conn, 'DEMOTEXT', 'QGPL') == false )
{
$sql = "create table qgpl/demotext " .
"( key int generated always as identity, " .
" text char(80) not null default ' ', " .
" primary key(key)) " ;
$stmt = db2_prepare($conn, $sql) ;
$result = as400Execute($stmt, $sql, false) ;
}
// delete contents.
$sql = "delete from qgpl/demotext " ;
$stmt = db2_prepare($conn, $sql) ;
$result = as400Execute($stmt, $sql, false) ;
// insert plain text into qgpl/demotext
$sql = "insert into qgpl/demotext ( text ) " .
"values( ? )" ;
$text = 'abc' ;
$stmt = db2_prepare($conn, $sql) ;
db2_bind_param( $stmt, 1, 'text', DB2_PARAM_IN ) ;
$result = as400Execute($stmt, $sql) ;
echo 'plain text ' . $text . ' inserted into demotext' . '<br>' ;
// insert multiple rows of ci·ré ci·ré
for( $ix = 0 ; $ix < 10 ; $ix++)
{
$sql = "insert into qgpl/demotext ( text ) " .
"values( ? )" ;
$text = 'ci·ré ci·ré ci·ré ci·ré ci·ré ci·ré' ;
$stmt = db2_prepare($conn, $sql) ;
db2_bind_param( $stmt, 1, 'text', DB2_PARAM_IN ) ;
$result = as400Execute($stmt, $sql) ;
}
echo 'multiple lines of extended ascii text ' . $text . ' inserted into
demotext' . '<br>' ;
dumpDemoText( $conn ) ;
// --------------------- demo_connect -------------------------
function demo_connect( $libl )
{
$options = array('i5_naming' => DB2_I5_NAMING_ON);
$options['i5_libl'] = $libl ;
$conn = db2_connect("*LOCAL","","", $options);
return $conn ;
}
// ----------------- dumpDemoText -----------------------
function dumpDemoText( $conn )
{
// read from qgpl/demotext
$sql = 'select a.key, a.text from demotext a ' ;
$stmt = db2_prepare($conn, $sql) ;
$result = as400Execute($stmt, $sql) ;
echo 'start dump demotext.' . '<br><br>' ;
while( $row = db2_fetch_array( $stmt ))
{
$rt_key = $row[0];
$rt_text = $row[1] ;
echo 'KEY:' . $rt_key . ' TEXT:' . $rt_text . '<br>' ;
}
echo 'end dump demotext.' . '<br><br>' ;
}
// ----------------- checkTableExists -----------------------
function checkTableExists( $conn, $table, $lib )
{
$sql = 'select a.table_name from qsys2/systables a ' .
'where a.table_name = ? and a.table_schema = ? ' ;
$stmt = db2_prepare($conn, $sql) ;
$bindTable = $table ;
$bindLib = $lib ;
db2_bind_param( $stmt, 1, 'bindTable', DB2_PARAM_IN ) ;
db2_bind_param( $stmt, 2, 'bindLib', DB2_PARAM_IN ) ;
// cannot run as400 Execute from here. bind error. why??
// $result = as400Execute($stmt, $sql) ;
$result = db2_execute($stmt) ;
$rt_table = '' ;
while( $row = db2_fetch_array( $stmt ))
{
$rt_table = $row[0];
}
if ( strlen($rt_table) > 0)
return true ;
else
return false ;
}
// ------------------------- as400Execute --------------------------
function as400Execute( $stmt, $sql = null, $silent = true )
{
$result = db2_execute($stmt) ;
if (!$result)
{
echo '<br>' . 'the db2 execute failed. ' ;
echo '<br>' . ' SQLSTATE: ' . db2_stmt_error( ) ;
echo '<br>' . ' message: ' . db2_stmt_errormsg( ) ;
if (is_null($sql) == false )
echo '<br>' . $sql ;
// write to the php error log.
$fullText = 'db2_execute failed. sqlstate:' . db2_stmt_error( ) .
' message: ' . db2_stmt_errormsg( ) ;
if (is_null($sql) == false )
$fullText = $fullText . ' ' . $sql ;
error_log( $fullText ) ;
error_log(print_r(debug_backtrace(), TRUE));
}
else if ($silent == false )
{
echo $sql . '<br>' ;
}
return $result ;
}
?>
On Wed, Apr 18, 2018 at 8:55 PM, Jon Paris <jon.paris@xxxxxxxxxxxxxx> wrote:
I'm confused Steve, It cannot be both UTF-8 and CCSID 37. UTF-8 is CCSID
1208 - "Within IBM, UTF-8 has been registered as CCSID 1208 with growing
character set (sometimes also referred to as code page 1208). As new
characters are added to the standard, this number (1208) will not change."
See https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.5.
0/com.ibm.db2.luw.admin.nls.doc/doc/c0051306.html <
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.5.
0/com.ibm.db2.luw.admin.nls.doc/doc/c0051306.html> for the full details.
I suspect you are getting some unwanted automatic conversion.
Jon Paris
www.partner400.com
www.SystemiDeveloper.com
midrange.com
