php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #79059 PDO ODBC removes strings with encoded chars from results retrieved from IBM i
Submitted: 2020-01-03 10:34 UTC Modified: 2020-10-06 14:04 UTC
From: mdalco at gmail dot com Assigned:
Status: Open Package: PDO ODBC
PHP Version: 7.3.13 OS: Linux Centos 7
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2020-01-03 10:34 UTC] 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);


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-10-01 10:15 UTC] cmb@php.net
-Status: Open +Status: Feedback -Assigned To: +Assigned To: cmb
 [2020-10-01 10:15 UTC] 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-10-01 13:13 UTC] mdalco at gmail dot com
-Status: Feedback +Status: Assigned
 [2020-10-01 13:13 UTC] 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 14:44 UTC] 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-06 14:04 UTC] cmb@php.net
-Status: Assigned +Status: Open -Assigned To: cmb +Assigned To:
 [2020-10-06 14:04 UTC] cmb@php.net
> 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>
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Thu Oct 22 10:01:24 2020 UTC