garbage characters at the end of string

[vividy163]

Server: DB2 7.1.0
Client: 11.5.4.0
PHP: 8.2.17
pdo_ibm: latest main branch

There is no problem with the following command line:
db2 "SELECT ONAME from MYTABLE where ID='590'"

However, retrieving data via PHP like this:

$dbh = new PDO($dsn);
$result = $dbh->query("SELECT ONAME from MYTABLE where ID='590'");
foreach($result as $row){
    echo  $row[0];
}

At the end of the string, there is a 0x00 and some other random characters.

trim($row[0],"\x0")
can remove the trash characters.

[NattyNarwhal]

What's the data type on the column ID?

[vividy163]

id is char(10), and oname is char(100).

[NattyNarwhal]

Sorry for the late reply, I'm not able to reproduce this:

$ LD_LIBRARY_PATH=/home/calvin/opt/clidriver/lib php -d error_log= -d extension=modules/pdo_ibm.so ../testpdo.php 
connecting
making
querying
string(100) "My Name 123                                                                                         "
string(200) "4d79204e616d65203132332020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020"

<?php

$dsn = "ibm:database=sample;hostname=localhost;port=60000;username=db2inst1;password=password";

echo "connecting\n";
$dbh = new PDO($dsn);

echo "making\n";
//$dbh->exec("create table mytable(id char(10), oname char(100))");
//$dbh->exec("insert into mytable (id, oname) values ('1234', 'My Name')");
//$dbh->exec("insert into mytable (id, oname) values ('590', 'My Name 123')");

echo "querying\n";
$result = $dbh->query("SELECT ONAME from MYTABLE where ID='590'");
foreach($result as $row){
    var_dump($row[0]);
    var_dump(bin2hex($row[0]));
}

Tested with PHP 8.2.18, Fedora 39, Db2 server 11.5.8.0, and the Db2 client 11.5.9.0.

[vividy163]

From your results, it seems that the trailing spaces are not trimmed for CHAR type columns. It appears that I need to apply trimming to every string with a CHAR type from DB2. Regardless of the correct answer, I will add trimming to the result set.

Thanks for your help.

[vividy163]

It seems the problem occurs when fetching multibyte strings with DB2CODEPAGE=1208, but there is no issue when using DB2CODEPAGE=932. Do you have any suggestions on how to resolve this problem?

[NattyNarwhal]

I checked with SELECT CODEPAGE FROM SYSCAT.DATATYPES WHERE TYPENAME = 'VARCHAR' to see what codepage Db2 seems to default to and it looks like it's 1208 out of the box. Setting DB2CODEPAGE=1208 or 932 via an environment variable on the client (I believe this is how it's set, not familiar with LUW) also doesn't seem to make a difference.

[vividy163]

It seems the problem only occurs with DB2 versions before 7. I have decided to give up using codepage=1208 and will convert the output string to UTF-8 using mb_convert_encoding().