#36797 [Com]: Problem using UTF-8 database with pdo_oci

[neggelandia at hotmail dot com]

 ID:               36797
 Comment by:       neggelandia at hotmail dot com
 Reported By:      mauroi at digbang dot com
 Status:           Assigned
 Bug Type:         PDO related
 Operating System: Win XP SP2
 PHP Version:      5.1.2
 Assigned To:      ramsey
 New Comment:

I just finished setting up OCI8 to connect to an Oracle database server
and I noticed that Swedish characters (åäö and ÅÄÖ) end up as ??? in my
web browser.

I'm running Oracle Instant Client on an Ubuntu 8.04 web server with PHP
5.2.4 (latest version at the time of writing). I tried Ramsey's advice
and added the SetEnv NLS_LANG line to Apache but nothing happened. 

I can't try step one 'cause I'm not connecting using the tnsnames.ora
parameters (I use the easyconnect method or whatever it's called). 

I don't have the folder mentioned in step 3 (I only installed Oracle IC
Basic and SDK) so I can't do that either, but the whole $ORACLE_HOME
directory is chmoded to 777 so it shouldn't be a problem.

This bug is quite important to me as I need to be able to get some data
from the Oracle DB to PHP, and as I'm working for a Finnish/Swedish
company there are lots of these characters everywhere. Sure it's
readable but it's still annoying and just not perfect.

I hope someone takes the time to fix this bug or come up with a working
workaround.


Previous Comments:
------------------------------------------------------------------------

[2007-06-06 20:39:56] [EMAIL PROTECTED]

Assigning to myself to update the PDO_OCI documentation to include
discussion about setting charsets for Oracle.

------------------------------------------------------------------------

[2007-06-06 20:02:18] [EMAIL PROTECTED]

[EMAIL PROTECTED] and I debugged this issue for hours, and here's
what it came down to.

We fixed this issue by doing the following:

1. As suggested, the DSN string was modified to contain
'charset=AL32UTF8'

2. Apache now has an environment variable NLS_LANG set as follows:
NLS_LANG='AMERICAN_AMERICA.AL32UTF8'

3. Make sure that $ORACLE_HOME/nls/data and all files within are
world-readable.

------------------------------------------------------------------------

[2007-06-05 19:15:39] [EMAIL PROTECTED]

As information, I use  'oci:...charset=AL32UTF8' with latest IC and
oracle 10g as server and works (5.2.2 and cvs).

------------------------------------------------------------------------

[2007-06-05 18:45:56] [EMAIL PROTECTED]

Note that I'm not using HTML entities in the field, but rather, actual
Japanese characters. The bug report here appears to convert these chars
to HTML entities.

------------------------------------------------------------------------

[2007-06-05 18:44:26] [EMAIL PROTECTED]

Here's some more reproduce code:

<?php
$dbh = new PDO("oci:dbname=asylum;charset=UTF-8", "dev", "dev");
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Create table
$dbh->exec('create table my_test_table(my_field varchar2(100))');

// Insert data
$dbh->exec('insert into my_test_table(my_field) values (\'foo\')');
$dbh->exec('insert into my_test_table(my_field) values
(\'&#26085;&#26412;&#35486; \')');

// Get data
foreach ($dbh->query('select my_field from my_test_table') as $row) {
    var_dump($row);
}

$dbh->exec('drop table my_test_table');
?>


ACTUAL OUTPUT:

array(2) {
  ["MY_FIELD"]=>
  string(3) "foo"
  [0]=>
  string(3) "foo"
}
array(2) {
  ["MY_FIELD"]=>
  string(4) "??? "
  [0]=>
  string(4) "??? "
}


EXPECTED OUTPUT:

array(2) {
  ["MY_FIELD"]=>
  string(3) "foo"
  [0]=>
  string(3) "foo"
}
array(2) {
  ["MY_FIELD"]=>
  string(4) "&#26085;&#26412;&#35486; "
  [0]=>
  string(4) "&#26085;&#26412;&#35486; "
}

------------------------------------------------------------------------

The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
    http://bugs.php.net/36797

-- 
Edit this bug report at http://bugs.php.net/?id=36797&edit=1