ansaurus

Question

Saving accented characters from PHP script to Oracle DB

Answer 1

+5 A:

Make sure your tables have a UTF8 character set and make sure the connection with your database is using UTF8. Searching for a Oracle example I found this:

resource oci_connect  ( string $username  , string $password  [, string $connection_string  [, string $character_set  [, int $session_mode  ]]] )

Which will become something like

$conn = oci_connect('insertUsername', 'insertPassword', 'insertHostname', 'AL32UTF8');

Full documentation:

http://nl3.php.net/oci_connect

Stegeman 2010-08-24 08:10:18

I've tried but it does not work, i still save bad chars :-(

Cris 2010-08-26 10:15:55

can you insert the chars using some other method? For example sql*plus? And if you echo the chars back to the screen after inserting them are they properly accented? Just trying to see if your issue is with oracle, the driver, or php ..

Doon 2010-08-26 10:21:31

i think it's a PHP issue, because if i make an insert via sql*plus, it runs fine. I've set NLS_LANG variable to AMERICAN_AMERICA.UTF8; i use php5 over oracle instant client

Cris 2010-08-26 10:27:07

Can you add some extra code you used when trying this solution?

Stegeman 2010-08-26 10:58:03

i've no changed php code, simply added export NLS_LANG="AMERICAN_AMERICA.UTF8" in /etc/profile

Cris 2010-08-26 11:04:59

It must have something to do with the connection settings. What is the output of this: echo getenv("NLS_LANG");

Stegeman 2010-08-26 11:41:58

it gives: AMERICAN_AMERICA.UTF8

Cris 2010-08-26 14:07:59

This is my last shot: Try to define your encoding directly into the oci_connect function. Use the AL32UTF8 like in the example above. Although the documentation says it will do automatically, it apparently does not. And don't use the utf_encode around JearPièrre, this may cause trouble too.

Stegeman 2010-08-27 06:57:38

Answer 2

A:

If your source file, which has this line:

$str=utf8_encode("JeanPièrre");

is already encoded in UTF-8 (i mean just file), then you don't have to encode "JeanPièrre", because it's already in UTF-8.

Meta tag with encoding is only for display or handling forms purpose - it don't affect your source code and backend work.

Remember that meta tag and encoding type of file are not the same. You can check and convert yor files by many programs, see notepad++, you should work on encoding "UTF-8 without BOM".

Also be sure, that your DB/table/connection are set to UTF-8 if they have such option.

Check also your way to get data from database - maybe inserting is ok, but characters are broken when querying to fetch data.

killer_PL 2010-08-26 10:40:59

i've tried to save text file in UTF8 and putting $str="JeanPièèèèrre" but without success. If i select data from sql*plus i see bad chars and see good chars for records inserted from sql*plus not using PHP

Cris 2010-08-26 10:57:17

Answer 3

+1 A:

What is your NLS_CHARACTERSET? (settings directly in the database). Value may be different from NLS_LANG (different from encoding, wher client run). This value you will find run the following SQL query:

SELECT PARAMETER, VALUE FROM NLS_DATABASE_PARAMETERS;

Output from my database look like this (my NLS_CHARACTERSET=AL32UTF8):

SQL> col PARAMETER format a25
SQL> col VALUE format a50
SQL> SELECT PARAMETER, VALUE FROM NLS_DATABASE_PARAMETERS;

PARAMETER                 VALUE
------------------------- --------------------------------------------------
NLS_LANGUAGE              AMERICAN
NLS_TERRITORY             AMERICA
NLS_CURRENCY              $
NLS_ISO_CURRENCY          AMERICA
NLS_NUMERIC_CHARACTERS    .,
NLS_CHARACTERSET          AL32UTF8
NLS_CALENDAR              GREGORIAN
NLS_DATE_FORMAT           DD-MON-RR
NLS_DATE_LANGUAGE         AMERICAN
NLS_SORT                  BINARY
NLS_TIME_FORMAT           HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT      DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT        HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT   DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY         $
NLS_COMP                  BINARY
NLS_LENGTH_SEMANTICS      BYTE
NLS_NCHAR_CONV_EXCP       FALSE
NLS_NCHAR_CHARACTERSET    AL16UTF16
NLS_RDBMS_VERSION         9.2.0.6.0

20 rows selected.

The problem may be either with encoding settings your client (I do not mean PHP function "utf8_encode", but really encoding your workstation, where the script runs). Or it's different encoding database and the value NLS_CHARACTERSET.

Probably identical problem is described on this page:

http://www.databaseanswers.org/ora_nls_support.htm

Martin Mares 2010-08-26 21:52:59

Answer 4

A:

I'm concerned about this line.

$sql="insert into TABLE(nvar) values('".$str."')";

Rather than binding the string value through a variable, it is simply concatenating it into the insert. This is poor practice in Oracle. An example of a bind usage is :

$sql = "insert into TABLE(nvar) values( :str )";
$stmt = oci_parse($ora_conn, $sql) or die(oci_error().$query);
ocibindbyname($stmt,":str",&$str,6);
oci_execute($stmt);

So I'd start by fixing that. There's a small chance that will resolve the error. It gets mentioned here but it isn't particularly clear. Basically it implies there's some difference regarding literals between doing

INSERT INTO table VALUES ('è');

and

INSERT INTO table VALUES (N'è');

Gary 2010-08-26 23:30:03

Answer 5

A:

Hi all, i've solved my problem by adding NLS_LANG=American_America.WE8ISO8859P1 to the ENV variable in /etc/init.d/apache2 file.

This made PHP sending correct chars to the Oracle DB.

Cris 2010-08-27 12:48:17

ansaurus

tags:

views:

answers:

Saving accented characters from PHP script to Oracle DB

related questions