Saving accented characters from PHP script to Oracle DB
I'm trying to save accented chars èòàèì to an Oracle DB in a VARCHAR2 field. I've put开发者_高级运维
<html>
<head>
<meta http-equiv="Content-type" value="text/html; charset=utf-8">
</head>
<body>
<?php
header('Content-type: text/html; charset=utf-8');
....
//and here i make the insert into the DB:$str=utf8_encode("JeanPièrre"); // or $str="JeanPièrre" ... is the same, it does not run
$sql="insert into TABLE(nvar) values('".$str."')";
$stmt = oci_parse($ora_conn, $sql) or die(oci_error().$query);
oci_execute($stmt);
But accented character is not saved correctly, I see JeanPi??rre
What can I do? Pls help me.
Thanks in advance!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
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
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.
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.
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'è');
I had the same problem and fixed it with utf8_decode();
$textForDB = utf8_decode("Áà");
I have changed the setting in php.ini file and it worked. The following needs to be enabled in php.ini
mbstring.internal_encoding = UTF-8;
加载中,请稍侯......
精彩评论