Problem with Oracle PDO connectivity

Archives Bug Discussions
1

Hi all:

I’m new to PHP and frameworks so perhaps what I’m doing is wrong and I can’t tell but here it goes…

Environment:

  • Oracle 10g (10.2.0.4) under Windows Server 2003 SP2

  • Oracle Apache server 1.3

  • PHP 5.2.11 for Windows

  • Yii 1.1rc.r1585

Background:

Apache wouldn’t load the php_pdo_oci.dll extension from PHP 5.2.11 but the PHP CLI did load it. With it, yiic would allow the creation of models and cruds but since Apache didn’t recognize it, whenever I tried to access the list.php page for the model I would get a “driver not found” error. I downgraded to the dll from EasyPHP 2.0b (PHP 5.0?) as both Apache and the CLI seem to recognize it.

The problem:

If I write a simple page with the following statement:

$conn = new PDO(‘oci:dbname=mydb’, ‘myusername’, ‘mypassword’);

it executes fine with no errors. However, when setting up the Yii connection in protected\config\main.php as follows:

‘db’=>array(

'class'=>'CDbConnection',


'connectionString'=>'oci:mydb',


'username'=>'myusername',


'password'=>'mypassword',

and then attempt to create a model using yiic I get the following error message:

exception ‘CDbException’ with message 'CDbConnection failed to open the DB conne

ction: SQLSTATE[HY000]: pdo_oci_handle_factory: ORA-12560: TNS:protocol adapter

error ext\pdo_oci\oci_driver.c:579)’ in I:\WEB\yii\framework\db\CDbConnection.php:262

I have also tried the long connectionString ‘oci://servername:1521/mydb’ to no avail.

The question is, do I have a .dll problem, a syntax problem or is it a Yii bug? Any help will be greatly appreciated.

Regards,

José

2

Make sure You have php_pdo_oci8.dll and php_oci8.dll extensions dissabled (in Your php.ini), leave only one that suits Your env, that is php_pdo_oci.dll. I had the same prob few days ago :)

3

The same above and certify yourself that you are using Oracle CLIENT 10+ version (install OracleXE on your dev machine and it shoul work properly).

Oracle Client 9 and PHP 5.2 doesn’t work well together on Windows.

4

Hi:

Thanks for your replies. I kept working at it until (it seems) I found the right combination about a week ago:

php.ini:

extension=php_oci8.dll

extension=php_pdo_oci.dll

plus… (and this completed the trick):

main.php:

‘connectionString’=>‘oci:dbname=mydb’,

It looks like ‘connectionString’=>‘oci:’ (Oracle version) requires ‘dbname=’. It has been working fine since I made the change. If it’s not that explicit it won’t work. Thanks again.

Regards,

Jose

5

php_oci8 is not necessary (This extension is just for "pure" oci connections until oci version 8, for compatibility).

And yes, I didn´t notice you missing "dbname" at first post.

6

Have a similar problem…

can connect to my oracle db using codeigniter but have trouble using shell command in Yii. Gives me the fallowing error:




>> model cities

exception 'CDbException' with message 'CDbConnection failed to open the DB conne

ction: SQLSTATE[42S02]: pdo_oci_handle_factory: ORA-12154: TNS:could not resolve

 the connect identifier specified

 (ext\pdo_oci\oci_driver.c:579)' in C:\wamp\www\yii-1.1.2\framework\db\CDbConnection.php:267

my main.php looks like this:




'connectionString' => 'oci:host=localhost/XE;dbname=""',

// Tried both connectionStrings

//'connectionString'=>'oci:dbname=mydb',

//'connectionString' => 'oci:host=localhost/XE;dbname=mydb',

'emulatePrepare' => true,

'username' => '***',

'password' => '***',

'charset' => 'utf8',

When I change connectionString dbname=XE




>> model cities

exception 'CDbException' with message 'CDbConnection failed to open the DB conne

ction: SQLSTATE[HY000]: General error: 922 OCIStmtExecute: ORA-00922: missing or

 invalid option

 (ext\pdo_oci\oci_driver.c:341)' in C:\wamp\www\yii-1.1.2\frame

work\db\CDbConnection.php:267

When I got rid of “‘charset’ => ‘utf8’,” line, I get this error




Warning: Invalid argument supplied for foreach() in C:\wamp\www\yii-1.1.2\framework\cli\commands\shell\ModelCommand.php on line 158

Warning: the table 'cities' does not exist in the database.

Line 158 of ModelCommand.php is:




foreach ($table->foreignKeys as $fkName => $fkEntry)

and it lead me to the COociShema.php file, but it looks like it should




if($schema===$this->getDefaultSchema())

	$names[]=$row['TABLE_NAME'];

else

	$names[]=$row['SCHEMA_NAME'].'.'.$row['TABLE_NAME'];

any clue what might be wrong? (I have "cities" table in my oracle db)

7

After my initial posts I solved the problem using




'connectionString'=>'oci:dbname=mydb',

and making sure that the Oracle client (Windows) and tnsnames.ora were correctly configured. I’m not sure what’s wrong with your setup but perhaps this post can help you.

8

I get the error




Warning: Invalid argument supplied for foreach() in C:\wamp\www\yii-1.1.2\framework\cli\commands\shell\ModelCommand.php on line 158

Warning: the table 'cities' does not exist in the database.

Does that mean that the connection is established? or how can I check?

9

It seems that the connection is established. Check the post I mentioned before because I think it deals with a recent change in Oracle connectivity regarding quotes around table and column names.

10

Did, and mailed with Horacio Segura author of changes…

Unfortunately he did not manage to get it to work…

I’ll try to fight on my own on monday…

11

I had same problem,

use capital letters in table name and column name.

Till waiting for new yii it will work (with quotes).

12

Have the same problem.

CDbConnection failed to open the DB connection: SQLSTATE[HY000]: General error: 922 OCIStmtExecute: ORA-00922: missing or invalid option

(ext\pdo_oci\oci_driver.c:341)

my config/main.php

…

‘db’=>array(

		'connectionString' => 'oci:dbname=//192.168.0.250:1521/testdb',


		'emulatePrepare' => true,


		'username' => 'test',


		'password' => 'test',


		'charset' => 'utf8',


	),

…

13

Full Error Message:

CDbException

Description

CDbConnection failed to open the DB connection: SQLSTATE[HY000]: General error: 922 OCIStmtExecute: ORA-00922: missing or invalid option

(ext\pdo_oci\oci_driver.c:341)

Source File

D:\www\yii\framework\db\CDbConnection.php(269)

00257: throw new CDbException(Yii::t(‘yii’,‘CDbConnection.connectionString cannot be empty.’));

00258: try

00259: {

00260: Yii::trace(‘Opening DB connection’,‘system.db.CDbConnection’);

00261: $this->_pdo=$this->createPdoInstance();

00262: $this->initConnection($this->_pdo);

00263: $this->_active=true;

00264: }

00265: catch(PDOException $e)

00266: {

00267: if(YII_DEBUG)

00268: {

00269: throw new CDbException(Yii::t(‘yii’,‘CDbConnection failed to open the DB connection: {error}’,

00270: array(’{error}’=>$e->getMessage())),(int)$e->getCode(),$e->errorInfo);

00271: }

00272: else

00273: {

00274: Yii::log($e->getMessage(),CLogger::LEVEL_ERROR,‘exception.CDbException’);

00275: throw new CDbException(Yii::t(‘yii’,‘CDbConnection failed to open the DB connection.’),(int)$e->getCode(),$e->errorInfo);

00276: }

00277: }

00278: }

00279: }

00280:

00281: /**

Stack Trace

#0 D:\www\yii\framework\db\CDbConnection.php(242): CDbConnection->open()

#1 D:\www\yii\framework\db\CDbConnection.php(221): CDbConnection->setActive(true)

#2 D:\www\yii\framework\base\CModule.php(363): CDbConnection->init()

#3 D:\www\yii\framework\base\CApplication.php(391): CModule->getComponent(‘db’)

#4 D:\www\yii\framework\db\ar\CActiveRecord.php(588): CApplication->getDb()

#5 D:\www\yii\framework\db\ar\CActiveRecord.php(2158): CActiveRecord->getDbConnection()

#6 D:\www\yii\framework\db\ar\CActiveRecord.php(353): CActiveRecordMetaData->__construct(Object(User))

#7 D:\www\blog\protected\models\User.php(20): CActiveRecord::model(‘User’)

#8 D:\www\blog\protected\components\UserIdentity.php(23): User::model()

#9 D:\www\blog\protected\models\LoginForm.php(50): UserIdentity->authenticate()

#10 D:\www\yii\framework\validators\CInlineValidator.php(39): LoginForm->authenticate(‘password’, Array)

#11 D:\www\yii\framework\validators\CValidator.php(184): CInlineValidator->validateAttribute(Object(LoginForm), ‘password’)

#12 D:\www\yii\framework\base\CModel.php(150): CValidator->validate(Object(LoginForm), NULL)

#13 D:\www\blog\protected\controllers\SiteController.php(88): CModel->validate()

#14 D:\www\yii\framework\web\actions\CInlineAction.php(50): SiteController->actionLogin()

#15 D:\www\yii\framework\web\CController.php(300): CInlineAction->run()

#16 D:\www\yii\framework\web\CController.php(278): CController->runAction(Object(CInlineAction))

#17 D:\www\yii\framework\web\CController.php(257): CController->runActionWithFilters(Object(CInlineAction), Array)

#18 D:\www\yii\framework\web\CWebApplication.php(324): CController->run(‘login’)

#19 D:\www\yii\framework\web\CWebApplication.php(121): CWebApplication->runController(‘site/login’)

#20 D:\www\yii\framework\base\CApplication.php(135): CWebApplication->processRequest()

#21 D:\www\blog\index.php(13): CApplication->run()

#22 {main}

2010-09-14 10:47:21 Apache/2.2.15 (Win32) mod_ssl/2.2.15 OpenSSL/0.9.8n Yii Framework/1.1.4

14

Hey can you please tell me me how could you configurw this oracle client path correctly.




and making sure that the Oracle client (Windows) and tnsnames.ora were correctly configured.

Thanks in Advance.

15

Wow, I haven’t worked with Oracle since that time 3 years ago so I’m not sure I can help you now. Assuming the server is working correctly:

  1. Make sure the Oracle client is installed and working (e.g. environment variables ORACLE_HOME, ORACLE_SID, etc. are set).

  2. Test the connection with Windows ODBC Data Source Manager.

  3. Ensure that your PHP installation has the Oracle PDO drivers.

Good luck!

16

Thanks for your quick reply :)

Anyways i have resolved that issue. Actually there was a problem with service name . SO, i have changed that and now its working fine. ;)

Thanks again for your response.:)

17

Good to know, thanks…