Bug #79059 [Asn->Opn]: PDO ODBC removes strings with encoded chars from results retrieved from IBM i
Edit report at https://bugs.php.net/bug.php?id=79059&edit=1
ID: 79059
Updated by: cmb@php.net
Reported by: mdalco at gmail dot com
Summary: PDO ODBC removes strings with encoded chars from
results retrieved from IBM i
-Status: Assigned
+Status: Open
Type: Bug
Package: PDO ODBC
Operating System: Linux Centos 7
PHP Version: 7.3.13
-Assigned To: cmb
+Assigned To:
Block user comment: N
Private report: N
New Comment:
> It seems that the driver when coupled with PDO translates the
> different encoding from internal DB2 charset to Unicode only on
> INSERT and UPDATE statements and not on the resultset.
Something like that may actually happen. It's not really about
INSERT/UPDATE vs. SELECT, but rather about SQLBindCol() vs.
SQLGetData()/SQLPutData(). According to the IBM
documentation[1][2], the former is potentially subject to
automatic character encoding conversion, while the latter
apperently is not. Futhermore, if the PDO::ODBC_ATTR_ASSUME_UTF8
(which is not Windows only, like currently documented) is set,
SQLPutData() calls are not affected on non Windows systems, but
SQLGetData() and SQLBindCol() are.
[1] <https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/odbc/src/tpc/db2z_fnbindcol.html>
[2] <https://www.ibm.com/support/knowledgecenter/en/SSSNY3_10.1.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0000642.html>
Previous Comments:
------------------------------------------------------------------------
[2020-10-01 14:44:34] cmb@php.net
Thanks! That is certainly useful information. And of course,
good to hear that you found a viable workaround. :)
> AllowUnsupportedChar = 0
> ForceTranslation = 0
That might be settings to play with. Maybe relevant:
<https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/rzatv/rzatvfaqodbc.htm>.
> It seems that the driver when coupled with PDO translates the
> different encoding from internal DB2 charset to Unicode only on
> INSERT and UPDATE statements and not on the resultset.
I shall look into this ASAP.
------------------------------------------------------------------------
[2020-10-01 13:13:37] mdalco at gmail dot com
Just updated to the latest iaccess 1.1.0.14-1 IBM driver for Centos 7.
The actual output is:
string(0) ""
But I recently found a workaround that requires casting the charset encoding directly in the SQL
SELECT statement:
SELECT CLCCLI, CAST(CLDRSO AS VARCHAR(255) CCSID 1208) AS CLDRSO FROM $$LIBFA0.CLIEN01L WHERE
CLCCLI=31
(1208 is the IMB DB2 character encoding corresponding to Unicode).
The output becomes:
var_dump(bin2hex($row['CLDRSO']));
string(62) "532e412e4445532045415558204d494ec38952414c4553204427455649414e"
print_r($row['CLDRSO']);
S.A.DES EAUX MINÃRALES D'EVIAN
that is the expected output.
I eventually refactored all my queries to cast all strings. Not elegant but effective.
I digged around IBM driver options (odbc.ini) and documentation, but it doesn't allow to set a
default charset:
[AS400]
Description = IBM i Access ODBC Driver 64-bit
Driver = IBM i Access ODBC Driver 64-bit
System = 10.1.3.10
UserID = ***
Password = ***
Naming = 0
DefaultLibraries = $$LIBFA0
Database =
ConnectionType = 0
CommitMode = 2
ExtendedDynamic = 1
DefaultPkgLibrary = QGPL
DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 1
MaxFieldLength = 32
BlockFetch = 1
BlockSizeKB = 128
ExtendedColInfo = 0
LibraryView = ENU
AllowUnsupportedChar = 0
ForceTranslation = 0
Trace = 0
Strangely I have no problems with INSERT and UPDATE statements and outside PHP PDO extension.
It seems that the driver when coupled with PDO translates the different encoding from internal DB2
charset to Unicode only on INSERT and UPDATE statements and not on the resultset.
------------------------------------------------------------------------
[2020-10-01 10:15:01] cmb@php.net
Well, that is obviously a character encoding issue, but the
details are totally unclear to me. Maybe a first step is to check
what PDO_ODBC actually delivers, i.e. by changing
print_r($row);
to
var_dump(bin2hex($row['CLDRSO']));
If the non ASCII are actually omitted, that should report a string
length of 58 for "S.A.DES EAUX MINÃRALES D'EVIA". Anyway, please
report the actual output.
Also check whether the driver configuration allows to set a
default character encoding.
------------------------------------------------------------------------
[2020-01-03 10:34:30] mdalco at gmail dot com
Description:
------------
If I update the IBM iAccess driver to the last version (iaccess 1.1.0.12) on my server, I can no
longer retrieve text data that include encoded chars (e.g. à èìòù and other).
The query just runs and retrieves the row, but the fields containing a non ASCII char are empty.
If I try to execute the same query with the isql unixODBC utility everything works fine:
SQL> SELECT CLCCLI, CLDRSO FROM $$LIBFA0.CLIEN01L WHERE CLCCLI=31
+---------+-------------------------------+
| CLCCLI | CLDRSO |
+---------+-------------------------------+
| 31 | S.A.DES EAUX MINÃRALES D'EVIA|
+---------+-------------------------------+
SQLRowCount returns -1
1 rows fetched
From PHP:
$connection = new PDO('odbc:AS400', $user, $password);
$result = $connection->query('SELECT CLCCLI, CLDRSO FROM $$LIBFA0.CLIEN01L WHERE
CLCCLI=31');
while($row = $result->fetch(PDO::FETCH_ASSOC))
print_r($row);
Output:
Array
(
[CLCCLI] => 31
[CLDRSO] =>
)
If I switch back to an older version of i access driver (iSeriesAccess 6.1.0) everything works fine,
and the same previous script outputs:
Array
(
[CLCCLI] => 31
[CLDRSO] => S.A.DES EAUX MINÃRALES D'EVIA
)
isql works fine with both drivers.
Test script:
---------------
$connection = new PDO('odbc:AS400', $user, $password);
$result = $connection->query('SELECT CLCCLI, CLDRSO FROM $$LIBFA0.CLIEN01L WHERE
CLCCLI=31');
while($row = $result->fetch(PDO::FETCH_ASSOC))
print_r($row);
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=79059&edit=1
Thread (5 messages)