Yii 1.1: Using Yii with Oracle through PDO

7 followers

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:

//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:

'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:

'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:

$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 rely 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".

Total 6 comments

#16429 report it
Amazing at 2014/02/22 03:27am
TNS:listener does not currently know of service requested in connect descriptor ERROR FIX

@codesutra

A NOTE from http://www.yiiframework.com/extension/oci8pdo/ helped me fix the connection error (...TNS:listener does not currently know of service requested in connect descriptor (ext\pdo_oci\oci_driver.c:...) which i had tackled for 3 weeks.

The default in the Yii manual for oracle connection is the easy connection string which does not work with all versions of oracle (10g been my version) that is not set up properly.

The fix is to use the full connection string: 'connectionString' => 'oci:dbname=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myOracleHost.com)(PORT=myOraclePort))(CONNECT_DATA=(SERVICE_NAME/SID=myService.intern)));charset=AL32UTF8;'

Hope it helps someone.

Source: http://www.yiiframework.com/extension/oci8pdo/ FIXED: ORA-12154: TNS:could not resolve the connect identifier specified

/*
              * Note: Normally you use the Easy Connect string, but your server has 
              * to be correctly set-up for that.
              * 
              * ** Easy Connect String **
              * If you get the following error: 
              * `ORA-12154: TNS:could not resolve the connect identifier specified`,
              * go to /opt/oracle/instantclient/sqlnet.ora and change 
              * the following line:
              *    NAMES.DIRECTORY_PATH= (TNSNAMES)
              * Change this to:
              *    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
              */
//           'connectionString' => 'oci:dbname=//myOracleHost.com:1526/ccq',
             /*
              * ** Full Connection String **
              * Use this method incase your Easy Connect gives you errors and 
              * you can't edit the sqlnet.ora file.
              * You can set the charset in this string as well, add 
              * `;charset=AL32UTF8;` at the end for UTF-8.
              */
              'connectionString' => 'oci:dbname=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myOracleHost.com)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=myService.intern)));charset=AL32UTF8;',
              'username' => '',
              'password' => '',

:)

#15029 report it
Breno Mayder at 2013/09/30 06:42am
Using cache!

The cache must be configured in Oracle or the application?

#13394 report it
codesutra at 2013/05/27 05:54am
TNS:listener path issue.

Hi,

I got below error when i configure Oracle database connection in my Yii application.

" pdo_oci_handle_factory: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor (ext\pdo_oci\oci_driver.c:631) "

i am developing my application with Wamp server. i have enabled all the necessary extension in php.ini file.But i am missing something here related to configuring a path for TNS listener.

Since i am new to oracle and even also new to configure oracle with php. So, it would be great if anybody can tell me how to fix it.

Thanks in Advance.

#5033 report it
ricardograna at 2011/09/08 08:17am
@lubosdz

Very, Very insteresting!

I never thought configuring this way! I made an own class by extending the CDbConnection to make this configurations - but your way is soo much simpler. Liked it very much! Thank you!

#4992 report it
lubosdz at 2011/09/04 12:33pm
More oracle specific settings

I have tested middle size application for all database drivers supported currently by Yii. Let me add some more information:

  • reverse engineering for oracle tables schema may seem slower than for e.g. mySQL. This is caused by more complex SQL queries reading column names, types etc. However, once you turn on schema caching, performance headover will be gone. So basically, if you tune up your database connection correctly, the performance will be very similar for all database drivers.

  • column names are internally case insensitive, which means oracle will always convert unescaped column names (and tables) into UPPERCASE and return resulsets (unfortunatelly) also in UPPERCASE. To prevent from this you have to quote column names (and table names). You can use function "Yii::app()->db->quoteColumnName($column);" for that. Yii does quoting of column names and tables, however NOT always. For example in CDbCriteria the condition WHERE will not be escaped. Unfortunately, it is currently probably not possible to be fixed, and hopefully this pretty big issue will be solved in Yii 2.0. The issue has also been reported.

  • though being heavy industrial standard, oracle has some poor features resulting historically from its long way up to nowadays while keeping the backward compatability. E.g. it does not support directly OFFSET, LIMIT, has issues with case sensitivity searches, string localizations, fulltext searches etc. Following are few tips which may help to solve some most common issues:

// oracle settings for Yii
'db'=>array(
 
// various connect DSN strings (host/instance or TNS)
    //'connectionString' => 'oci:dbname=//10.8.252.175:1521/XE',
    'connectionString' => 'oci:dbname=//localhost:1521/XE',
 
    'username' => 'hr',
    'password' => 'hr',
    'charset' => 'utf8',
    'schemaCachingDuration' => '3600',
    'enableParamLogging' => true,
 
    // PDO related settings
    'attributes' => array(PDO::ATTR_CASE => PDO::CASE_LOWER),
 
    // database session settings initiated with each HTTP request
    'initSQLs' => array(
 
        // Oracle behaviour depends on particular NLS* parameter
        // List actual settings via: select * from NLS_DATABASE_PARAMETERS
 
        // example 1: decimal separator: NLS_NUMERIC_CHARACTERS => ".," means, that dot [.] = decimal separator, and comma [,] is for thousands separator
        // for PHP we must set [NLS_NUMERIC_CHARACTERS => ". "] - applicable to middle european regional settings
        "ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '. '",
 
        // example 2: setting SELECT LIKE ... to be accent and case sensitive mode:
        'ALTER SESSION SET NLS_COMP=ANSI', 
        'ALTER SESSION SET NLS_COMP=LINGUISTIC',
        'ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER',
        'ALTER SESSION SET NLS_SORT=BINARY_AI',
    ),
),

Cheers Lubos

#4967 report it
ricardograna at 2011/09/02 12:04am
From the creator of OCI support for Yii

AR performance with Oracle is not slow, you are just not using cache!

Leave a comment

Please to leave your comment.

Write new article