phporacle-databaseutf-8character-encodingoci8

Dealing with eacute and other special characters using Oracle, PHP and Oci8

Hi I am trying to store names into an Oracle database and fetch them back using PHP and oci8.

However, if I insert the é directly into the Oracle database and use oci8 to fetch it back I just receive an e

Do I have to encode all special characters (including é) into html entities (ie: é) before inserting into database ... or am I missing something ?

Thx

UPDATE: Mar 1 at 18:40

found this function: http://www.php.net/manual/en/function.utf8-decode.php#85034

function charset_decode_utf_8($string) {
    if(@!ereg("[\200-\237]",$string) && @!ereg("[\241-\377]",$string)) {
        return $string;
    }
$string = preg_replace("/([\340-\357])([\200-\277])([\200-\277])/e","'&#'.((ord('\\1')-224)*4096 + (ord('\\2')-128)*64 + (ord('\\3')-128)).';'",$string);
$string = preg_replace("/([\300-\337])([\200-\277])/e","'&#'.((ord('\\1')-192)*64+(ord('\\2')-128)).';'",$string);
return $string;
}

seems to work, although not sure if its the optimal solution

UPDATE: Mar 8 at 15:45

Oracle's character set is ISO-8859-1.
in PHP I added: 

putenv("NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1");

to force the oci8 connection to use that character set. Retrieving the é using oci8 from PHP now worked ! (for varchars, but not CLOBs had to do utf8_encode to extract it )
So then I tried saving the data from PHP to Oracle ... and it doesnt work..somewhere along the way from PHP to Oracle the é becomes a ?

UPDATE: Mar 9 at 14:47

So getting closer. After adding the NLS_LANG variable, doing direct oci8 inserts with é works.

The problem is actually on the PHP side. By using ExtJs framework, when submitting a form it encodes it using encodeURIComponent.
So é is sent as %C3%A9 and then re-encoded into é.
However it's length is now 2 (strlen($my_sent_value) = 2) and not 1. And if in PHP I try: $my_sent_value == é = FALSE

I think if I am able to re-encode all these characters in PHP back into lengths of byte size 1 and then inserting them into Oracle, it should work.

Still no luck though

UPDATE: Mar 10 at 11:05

I keep thinking I am so close (yet so far away).

putenv("NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9"); works very sporadicly.

I created a small php script to test:

header('Content-Type: text/plain; charset=ISO-8859-1');
putenv("NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9");
$conn= oci_connect("user", "pass", "DB");
$stmt = oci_parse($conn, "UPDATE temp_tb SET string_field = '|é|'");
oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);

After running this once and loggin into the Oracle Database directly I see that STRING_FIELD is set to |¿|. Obviously not what I had come to expect from my previous experience.
However, if I refresh that PHP page twice quickly.... it worked !!!
In Oracle I correctly saw |é|.

It seems like maybe the environment variable is not being correctly set or sent in time for the first execution of the script, but is available for the second execution.

My next experiment is to export the variable into PHP's environment, however, I need to reset Apache for that...so we'll see what happens, hopefully it works.

Solution

  • This is what I finally ended up doing to solve this problem:

    Modified the profile of the daemon running PHP to have:

    NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

    So that the oci8 connection uses ISO-8859-1.

    Then in my PHP configuration set the default content-type to ISO-8859-1:

    default_charset = "iso-8859-1"

    When I am inserting into an Oracle Table via oci8 from PHP, I do:

    utf8_decode($my_sent_value)

    And when receiving data from Oracle, printing the variable should just work as so:

    echo $my_received_value

    However when sending that data over ajax I have had to use:

    utf8_encode($my_received_value)