Difference between #6 and #5 of Using Yii with Oracle through PDO

unchanged
Title
Using Yii with Oracle through PDO
unchanged
Category
Tips
unchanged
Tags
oracle, pdo oci php
changed
Content
When you are developing an webapp with Yii that will be using Oracle RDBMS you
should take a look at these issues in which you may run into it.

### Forcing charset
Oracle database **does not support setting charset in configuration file with
charset parameter**, i.e. like this:

~~~
[php]
//Database component
'db'=>array
(
	'class'=>'CDbConnection',
	'connectionString'=>'oci:dbname=10.10.4.106:1521/orcl',
	'charset'=>'UTF8'
),
~~~

You have to use DSN (connectionString) for this purpose, for example like that:

~~~
[php]
'connectionString'=>'oci:dbname=10.10.4.106:1521/orcl;charset=UTF8'
~~~

There was a bug in Yii 1.1.4 which caused that an attempt to pass charset as in
first example would cause an error, because Yii tried to set charset with SET
NAMES statement, which is not supported by PL/SQL on board Oracle. This bug has
been corrected, but even so, if you use second approach, you'll be able to force
Oracle to return result in any charset encoding you want (if it is supported by
Oracle, of course).

### Setting login information
Some (all?) versions of Oracle don't support passing login and password as a
part of DSN string. You'll have to pass it like this:

~~~
[php]
'db'=>array
(
	'class'=>'CDbConnection',
	'connectionString'=>'oci:dbname=10.10.4.106:1521/orcl;charset=UTF8'
	'username'=>'###',
	'password'=>'###',
),
~~~

In case of these version, if you pass login and password via DSN but leave login
and password parameters empty, you'll receive error that login attempt failed.

### Errors in DSN
Oracle has a very bad feature which causes that, if you have an error in your
DSN, it will first **try to connect with provided login and password on
localhost** and only if this attempt fail, it will issue an error.

I had three computers (A, B and C), two with installed Oracle and with the same
copy of database (A and B) and third without Oracle at all. I was developing my
app on machine A but trying to connect to machine B. I made a mistake in DSN.
Forgot about **dbname=** part, which is mandatory in Oracle and not obligatory
in other RDBMSes.

Instead of return an error, saying that my destination (B) machines is not
reachable and connection can't be established, I was kept connected to my
localhost copy of database (machine A) and the app run smudgily. I then move app
from machine A to machine C and my app again attempted to connect machine B,
which failed and was also unable to connect to localhost and only then issued an
error message.

I spend hours trying to find why exactly the same code is not working on machine
C, while it works with no problems on machine A? Only to find out that app on
neither of these machines is connecting to machine B (due to errors in DSN), but
tries to connect to localhost.

### Names case-sensitivity
Column names in Oracle are **case-sensitive**. This means that if you use all
uppercase characters when creating a table (common behaviour among Oracle
developers) and then try to access it with model and ActiveRecord for example
like this:

~~~
[php]
$users = Users::model()->findAll();

foreach($users as $user)
{
        echo 'logn = '.$user->LOGIN.'<br />';
        echo 'pass = '.$user->pass.'<br /><br />';
}
~~~
You will get data only for first iteration and for first field in it. When
evaluating second field, you app will throw exception saying that _Property
Users.pass is undefined_.

Tip: The cause is seems to be only partially Oracle-related. Oracle does not
reliesrely on case-sensitivity of column names as long as
they are not specified within quotation marks. If so, it takes exact column name
spelling as provided. Therefore, it seems that somewhere in Yii code underlying
below ActiveRecord there have to be a part which escapes table name with
quotation mark, making above mentioned problems.

### Active Record and Oracle

There is a large performance degradation when using AR in connections to Oracle,
especially on a slow server. Original code written for AR works fine for MySQL
and other RDBMS but fails on Oracle as it is using one of the slowest queries.
One of possible solutions for this has been presented in article
"[Incresing AR performance in connections with
Oracle](http://www.yiiframework.com/wiki/118/incresing-ar-performance-in-connections-with-oracle/
"Incresing AR performance in connections with Oracle")".
Write new article