Forcing database charset when connecting to Oracle


(Tomasz) #1

Hi there,

I read CDbConnection.charset property description:

http://www.yiiframework.com/doc/api/CDbConnection#charset-detail

only to found out not so nice for me notice: “The property is only used for MySQL and PostgreSQL databases”. What about other RDBMs? I’m using Yii, which if I’m not mistaken is using UTF-8 by default when connecting to Oracle, but I have Oracle tables in Win-1250 and absolutelly no idea how to force connection or Oracle to retrieve data in UYF-8 (I DON’T want to change Yii coding to something different than UTF-8, if it is possible anyway) or how to force Oracle to do on-the-file conversion Win-1250 to UTF-8.

Thanks in advance for any idea.


(Tomasz) #2

Still can’t understand, why charset property in Yii is supported only for MySQL ad PostgreSQL, if Oracle 9.2 and up DOES support it as well? The only problem is that Yii uses SQL query “SET NAMES” which is not supported by OCI. In OCI you have to pass extra parameter in connection / configuration string.

Example and citation from ADODB Manual (see Section Examples of Connecting to Databases / Oracle (oci8) / f. ADOdb dsn). I have to use ADODB because Yii lacks of charset support / implenation for Oracle:


$dsn = 'oci8://user:pwd@tnsname/?charset=WE8MSWIN1252';

which, if user want query results in UTF-8, would have to be modified to form:


$dsn = 'oci8://user:pwd@tnsname/?charset=AL32UTF8';

Since charset support for Oracle database requires not to much code change (extra parameter sent in connection string and NOT using SET NAMES for Oci) I would personally consider it’s lack as a bug in Yii. Is there any chance, Yii will support charset for Oci in next releases? I would personally be very gratefull as my idea of using framework consists of using ONLY this framework without need of use extra solutions / modules / classes (like ADODB).

Regards,

Trejder


(Mh) #3

You could open a ticket and reference this topic there. You’ll notice that the dev team is very alert and does an excellent job on fixing these things.

http://code.google.com/p/yii/


(Tomasz) #4

Thanks for the idea, Mike. Done so.


(Tomasz) #5

Well, the problem seems to be already solved!

Yii Framework and PDO itself IS SUPPORTING different charsets when connecting to Oracle database.

The problem seems to be in Oracle driver itself. If one make any mistake in DSN (connectionString), driver does not return any error message only tries to connect to local, default database. If success, user may assume that connection was estabilished and parameters were ignored.

In my situation DSN was:


'oci:dbname=//10.10.4.106:1521/orcl;charset=UTF8'

Notice two slash before hostname. Correct DSN format is:


'oci:dbname=10.10.4.106:1521/orcl;charset=UTF8'

Even both PHP (+Yii) and Oracle was setup on the same machine, I used host-related addressing to be able to move developed application to any other computer for tests. This way I assumed that connection is estabilished, only either Oracle, PDF or Yii ignores charset definition. The truth is that an error in DSN cause Oracle driver to connect to local, default database which in this situation was the same DB. Since whole DSN was ignored (as having errors) charset was also wrong. I noticed this when moved webapp to a different computer for tests only to find out somehow strange error "username or password incorrect".

After a short investigation I realised that driver was connecting to local DB not the one specified. When I corrected mistake in DSN both host and charset were correctly recognised and I finally got my data with right encoding.