Open vividy163 opened 6 months ago
What's the data type on the column ID
?
id is char(10), and oname is char(100).
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.
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.
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?
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.
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().
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:
At the end of the string, there is a 0x00 and some other random characters.
trim($row[0],"\x0")
can remove the trash characters.