[igordonin]

Hi all!!

This problem has been driving me nuts these past weeks and I just can't get it solved. I'm not very much familiar with Linux/Unix OS and that might be getting in the way of sorting this thing out.

Here's what's happening. I run applications with languages on ISO-8859-1 (latin). It's an old 9i DB that's been up for at least 8 years. I think it started as v8 and then was upgraded to 9i, but I'm not the DBA, so I'm not sure. What I'm sure is that this DB was created using latin language patterns, and that several applications are currently running fine based on that DB.

So I developed my own PHP web site and hosted it in a shared CentOS4 server which is in en_US. That's when my problems started.

Now every query run through PHP pages returns ? replacing latin characters. That doesn't happen on my testing environment, which is Windows in Brazilian Portuguese (latin language), nor on TOAD. I've run several tests to make sure it's not an Apache or PHP issue, or even HTML headers. Those are all fine. It's a sure thing that the queries are being returned with replaced characters.

Now, here's some info:
OS: CentOS4
Server: Apache 1 stable
Parser: PHP 4 stable
DB: Oracle 9i
Client: 10g v_10.1.3 (not instant client)
Client Install Folder: /u01/app/oracle

HTML Headers:

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

File httpd.conf settings (Apache Config):
AddLanguage pt .pt
AddLanguage pt-BR .pt-br
LanguagePriority pt-BR en ca cs da de el eo es et fr he hr it ja ko ltz nl nn no pl pt ru sv zh-CN zh-TW
ForceLanguagePriority Prefer Fallback
AddDefaultCharset ISO-8859-1
AddCharset ISO-8859-1 .iso8859-1 .latin1
AddCharset ISO-8859-2 .iso8859-2 .latin2 .cen

On CentOS, if I run the command locale, that's the output (US English):
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

The output of the command locale -a shows a whole lot of languages, inclusive (these are Brazilian Portuguese and Portugal Portuguese):
pt_BR
pt_BR.iso88591
pt_BR.utf8
pt_PT
pt_PT@euro
pt_PT.iso88591
pt_PT.iso885915@euro
pt_PT.utf8


I've read elsewhere that the Oracle Client will prioritize the OS language settings when returning queries. I don't know if there's any truth in that, but if so, is it possible to override that?

Also some people have told me I might have to edit the NLS_LANG settings in the Oracle Client profile to
NLS_LANG=BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1
but nobody has been able to tell me where that profile is located, so I don't even know what's it's set to, if anything.


That's all the info I've got.

Any help will be extremely appreciated.

[anacedent]

It may be just me, but I am unclear on how many different systems & different databases are involved in your problem description.

Adding to the mix "Oracle client" has me even more confused.

AFAIK, Oracle does NOT care about any LC* environmental variables, but can behave differently to some/many/most NLS* variables.

It is unclear to this reader, whether you have a data storage issue or a data display issue.

If the 'foreign' characters are not being stored properly in the database, there is little you can do to remedy the situation; other than starting over with a DB configured to hold the incoming characters.

If it is a display issue, then you should be able to tweak 1 or more things to correct this problem.

HTH & YMMV!

[igordonin]

Hi! Thank you very much for your reply.

I think I understood most of what you wrote, but I'm not familiar with the terms "AFAIK", "HTH" & "YMMV"! =( Sorry, bad English ...

It is unclear to this reader, whether you have a data storage issue or a data display issue.

I haven't tried to insert any data throu this system because it was not online, it was just in my computer (the DB was online, but the PHP pages were localhost). All the inserts were made throu my testing environment, and all the data WAS inserted properly.

If the 'foreign' characters are not being stored properly in the database, there is little you can do to remedy the situation; other than starting over with a DB configured to hold the incoming characters.

The DB is configured to hold incoming characters. I know that for a fact because I've testing it for months. When the inserts were made throu the PHP pages that run in my localhost, ok. The data is also displayed properly in my localhost environment, and it's also displayed properly in TOAD.

The problem is only taking place in this CentOS4 remote server in which the PHP pages will in fact be hosted. There I have not tried to insert any data to the DB, but I think the data would not be inserted properly if I tried. Displaying data returns replaced characters, not ok. There's certainly a display issue. I just don't know if there's an insert issue, but I'll perform some tests and post back.

If it is a display issue, then you should be able to tweak 1 or more things to correct this problem.

Meanwhile, could you help me with the displaying issue? =)

Again, thanks for helping.

[anacedent]

>but I'm not familiar with the terms "AFAIK", "HTH" & "YMMV"! =
As Far As I Know (AFAIK)
Hope This Helps (HTH)
You're Mileage May Vary(YMMV) -[your results may be different from mine]

You have three separate components between the DB & the end user;
PHP, Apache & Browser that can result in garbled characters.
Since you are convinced that data is correctly stored in the DB,
I suggest you post this problem in PHP and/or Apache forums,
because I am clueless when it come to globalization (working with non-ASCII characters).
I suspect the fix is nothing more than setting 1 or more Unix environmental
variables for either the PHP process or the Apache process.
I can not begin to guess as to which variables or what values need to be changed.

[igordonin]

I agree, but not entirely. Don't take me for an expert, but this is what I think happens:

The end user requests a page, and now I'm ignoring all the DNS requests and going straight from the end user browser to the Internet Server.
1) The Internet server (in my case an Apache Server running on an old CentOS4) receives the request and "calls" the page;
2) The PHP reads the document and uses the supplied DB conn data to connect to a DB throu
3) a DB client (in my case, Oracle 10g Client 10.1.3). The DB client parses the queries to the DB and receives a result, which it then reports to the Apache/PHP server;
4) which, lastly, puts up an HTML page that will be seen by the end user.

See, I don't think that Apache nor PHP can interpret results from DBs without the required clients. Maybe some softwares from the same organization can do that, like Oracle IAS (Internet Application Service) and EM (Enterprise Manager).

That been said, I know I've got the Apache/PHP settings covered. What I may be missing are some configuration files from the UNIX, like you said, or maybe the Oracle client.

Now, just for an example, there is a whole folder dedicated to Apache inside the Oracle 10.1.3 client folder (/u01/app/oracle/product/10.1.3/Apache). As a matter of fact, there are folders to all major ISs. Digging deeper inside that folder I found a file called apachectl (/u01/app/oracle/product/10.1.3/Apache/bin) which had these entries:

ORACLE_HOME=/u01/app/oracle/product/10.1.3; export ORACLE_HOME
#NLS_LANG=${NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P 1"}; export NLS_LANG
NLS_LANG=${NLS_LANG="BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1"}; export NLS_LANG

I commented out the AMERICAN_AMERICA stuff and placed the BRAZILIAN PORTUGUESE instead. Still no success.

Now I'm really confused, and I'm wondering: does anyone know for a fact that the client cannot be set for some charset, or maybe that the client doesn't prioritize OS language settings?

I know this is not a CentOS forum, and I don't expect it to be. I'm just wondering if anyone had to work around language settings defined by the client or the OS.

Thanks all.

[anacedent]

>I commented out the AMERICAN_AMERICA stuff and placed the BRAZILIAN PORTUGUESE instead.
Did you restart Apache are making the configuration change?

[igordonin]

Yes, I did restart the apache service. I didn't boot the computer though. Sadly, that'll be harder to do ...

[anacedent]

I came across the following URL
http://www.adviesenzo.nl/examples/ph...l_charset_fix/
Yes, I realize it deals with MYSQL & not Oracle,
but I suspect similar changes might be necessary for Oracle.

[igordonin]

Hi! Thank you very much for replying, anacedent.

I got it fixed!!

You know what, you were right all along! I've changed so many config settings I can't tell for a fact that, and I quote you here:,

the fix is nothing more than setting 1 or more Unix environmental variables for either the PHP process or the Apache process.

But true enough I had something missing! The NLS_LANG environmental var was not being set upon Apache init! I don't know how I missed that!

Anyways, after doing all that's already reported here, I got it fixed by doing this:

1. I created a "on database logon" trigger and forced Brazilian Portuguese NLS settings to my web user (the one with no privileges that I use to connect PHP to the DB).

2. I added the NLS_LANG="BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1" to the "httpd" file in "/etc/init.d". Someone told me that file might be named "apache" instead. Also, init.d was in fact a shortcut to "/etc/rc.d/init.d".

3. Finally, but I don't know if this played any part at all in all this, I changed my UNIX locale setting in that same "httpd" file to "LANG=pt_BR.iso88591".

That's it. Hope this helps someone.

Once again, many thanks to anacedent who so kindly helped me out.

Cheers!