Yii Framework Forum: Problem with Oracle PDO connectivity - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Problem with Oracle PDO connectivity Rate Topic: -----

#1 User is offline   JFReyes 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 508
  • Joined: 28-October 09
  • Location:Puerto Rico

Posted 16 December 2009 - 01:06 PM

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é
José
0

#2 User is offline   piwer 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 04-April 09

Posted 29 December 2009 - 02:34 PM

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 :)
0

#3 User is offline   ricardograna 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 376
  • Joined: 31-March 09
  • Location:Manaus/AM - Brazil

Posted 29 December 2009 - 03:56 PM

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.
Yes, It Is!
0

#4 User is offline   JFReyes 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 508
  • Joined: 28-October 09
  • Location:Puerto Rico

Posted 29 December 2009 - 07:46 PM

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
José
0

#5 User is offline   ricardograna 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 376
  • Joined: 31-March 09
  • Location:Manaus/AM - Brazil

Posted 04 January 2010 - 02:08 PM

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.
Yes, It Is!
0

#6 User is offline   lorcan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 12-May 10

Posted 12 May 2010 - 06:11 PM

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)
0

#7 User is offline   JFReyes 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 508
  • Joined: 28-October 09
  • Location:Puerto Rico

Posted 13 May 2010 - 07:26 AM

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.
José
0

#8 User is offline   lorcan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 12-May 10

Posted 14 May 2010 - 08:51 AM

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?
0

#9 User is offline   JFReyes 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 508
  • Joined: 28-October 09
  • Location:Puerto Rico

Posted 14 May 2010 - 12:37 PM

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.
José
0

#10 User is offline   lorcan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 12-May 10

Posted 14 May 2010 - 12:54 PM

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...
0

#11 User is offline   sis 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 9
  • Joined: 01-March 10

Posted 24 July 2010 - 03:34 PM

I had same problem,
use capital letters in table name and column name.
Till waiting for new yii it will work (with quotes).
0

#12 User is offline   YiieHR 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 12-September 10

Posted 13 September 2010 - 09:48 PM

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',
),
.....
0

#13 User is offline   YiieHR 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 12-September 10

Posted 13 September 2010 - 09:49 PM

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
0

#14 User is offline   codesutra 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 596
  • Joined: 15-March 11
  • Location:India

Posted 27 May 2013 - 05:06 AM

View PostJFReyes, on 13 May 2010 - 07:26 AM, said:

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.

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.
CodeSutra
0

#15 User is offline   JFReyes 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 508
  • Joined: 28-October 09
  • Location:Puerto Rico

Posted 27 May 2013 - 06:13 AM

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!
José
0

#16 User is offline   codesutra 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 596
  • Joined: 15-March 11
  • Location:India

Posted 27 May 2013 - 09:52 AM

View PostJFReyes, on 27 May 2013 - 06:13 AM, said:

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!



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.:)
CodeSutra
0

#17 User is offline   JFReyes 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 508
  • Joined: 28-October 09
  • Location:Puerto Rico

Posted 27 May 2013 - 12:59 PM

Good to know, thanks...
José
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users