php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #36797 Problem using UTF-8 database with pdo_oci
Submitted: 2006-03-20 15:22 UTC Modified: 2016-03-08 01:40 UTC
Votes:12
Avg. Score:4.8 ± 0.6
Reproduced:10 of 10 (100.0%)
Same Version:3 (30.0%)
Same OS:4 (40.0%)
From: mauroi at digbang dot com Assigned: ramsey (profile)
Status: No Feedback Package: PDO OCI
PHP Version: 5.2.4 OS: Win XP SP2
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2006-03-20 15:22 UTC] mauroi at digbang dot com
Description:
------------
I'm trying to use a AL32UTF8 database with PDO, but it seems that the charset is not correctly set (I get funny characters in the output).
I've tried the method described in (http://www.oracle.com/technology/pub/articles/php_experts/otn_pdo_oracle5.html) with no success.
I'm using Oracle instant client for Windows.

Reproduce code:
---------------
sql:

create table foo (field varchar(10));

php:

$a = new PDO('oci:dbname=server;charset=UTF-8', 'user', 'password');

$a->beginTransaction();

$b = $a->prepare('insert into foo (field) values (?)');
$b->bindValue(1, '?????');
$b->execute();

$c = $a->prepare('select * from foo');
$c->execute();
var_dump($c->fetchAll());

$a->rollBack();

Expected result:
----------------
the result with the row with ????? correctly displayed


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-03-20 15:25 UTC] tony2001@php.net
Try with "AL32UTF8" instead of "UTF-8".
 [2006-03-20 15:29 UTC] mauroi at digbang dot com
I've tried but the same happens.
I've also tried the complete NLS_LANG (AMERICAN_AMERICA.AL32UTF8) with no success.

Thanks!
 [2007-06-05 16:23 UTC] ramsey@php.net
I'm also having the same problem in PHP 5.2.2. I've set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 and my PDO DSN is oci:dbname=foo;charset=UTF-8. These don't seem to have any affect. The characters I send to the DB through PDO_OCI are still getting converted to ???????. The DB charset is AL32UTF8. I'm using Linux.

When I change the DSN charset to AL32UTF8, then there is no output on the page, nor are there errors reported in the error log. PHP appears to crash but gives no response as to why.
 [2007-06-05 18:44 UTC] ramsey@php.net
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; "
}
 [2007-06-05 18:45 UTC] ramsey@php.net
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 19:15 UTC] pajoye@php.net
As information, I use  'oci:...charset=AL32UTF8' with latest IC and oracle 10g as server and works (5.2.2 and cvs).
 [2007-06-06 20:02 UTC] ramsey@php.net
maggie@intermaggie.com 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-06 20:39 UTC] ramsey@php.net
Assigning to myself to update the PDO_OCI documentation to include discussion about setting charsets for Oracle.
 [2008-10-03 13:55 UTC] neggelandia at hotmail dot com
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.
 [2009-04-25 14:38 UTC] jani@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5.2-latest.tar.gz
 
For Windows:

  http://windows.php.net/snapshots/


 [2009-05-03 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2016-03-08 01:40 UTC] sixd@php.net
-Package: PDO related +Package: PDO OCI
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Sat Sep 26 06:01:23 2020 UTC