Trouble Getting Gii Connected To Oracle

[duartix (Duartyx)]

Cheers.

I’m completely new to PHP and thus to Yii and Gii. I’ve been going through the framework tutorials and it’s been relatively painless so I though it was time to start experimenting with Oracle connections.

My trouble is apparently getting Gii to connect to Oracle. When I launch Gii (the Web version), I get:

Error 500

CDbConnection failed to open the DB connection.

Digging the PHP application log I get this stack trace:

2013/08/02 10:41:11 [error] [exception.CDbException] could not find driver

2013/08/02 10:41:11 [error] [exception.CDbException] exception ‘CDbException’ with message ‘CDbConnection failed to open the DB connection.’ in C:\xampp\php\yii-1.1.13.e9e4a0\framework\db\CDbConnection.php:387

Stack trace:

#0 C:\xampp\php\yii-1.1.13.e9e4a0\framework\db\CDbConnection.php(330): CDbConnection->open()

#1 C:\xampp\php\yii-1.1.13.e9e4a0\framework\db\CDbConnection.php(308): CDbConnection->setActive(true)

#2 C:\xampp\php\yii-1.1.13.e9e4a0\framework\base\CModule.php(387): CDbConnection->init()

#3 C:\xampp\php\yii-1.1.13.e9e4a0\framework\base\CModule.php(103): CModule->getComponent(‘db’)

#4 C:\xampp\php\yii-1.1.13.e9e4a0\framework\gii\generators\model\ModelCode.php(57): CModule->__get(‘db’)

#5 C:\xampp\php\yii-1.1.13.e9e4a0\framework\web\CFormModel.php(39): ModelCode->init()

#6 C:\xampp\php\yii-1.1.13.e9e4a0\framework\gii\CCodeGenerator.php(153): CFormModel->__construct()

#7 C:\xampp\php\yii-1.1.13.e9e4a0\framework\gii\CCodeGenerator.php(63): CCodeGenerator->prepare()

#8 C:\xampp\php\yii-1.1.13.e9e4a0\framework\web\actions\CInlineAction.php(49): CCodeGenerator->actionIndex()

#9 C:\xampp\php\yii-1.1.13.e9e4a0\framework\web\CController.php(308): CInlineAction->runWithParams(Array)

#10 C:\xampp\php\yii-1.1.13.e9e4a0\framework\web\CController.php(286): CController->runAction(Object(CInlineAction))

#11 C:\xampp\php\yii-1.1.13.e9e4a0\framework\web\CController.php(265): CController->runActionWithFilters(Object(CInlineAction), Array)

#12 C:\xampp\php\yii-1.1.13.e9e4a0\framework\web\CWebApplication.php(282): CController->run(’’)

#13 C:\xampp\php\yii-1.1.13.e9e4a0\framework\web\CWebApplication.php(141): CWebApplication->runController(‘gii/model’)

#14 C:\xampp\php\yii-1.1.13.e9e4a0\framework\base\CApplication.php(169): CWebApplication->processRequest()

#15 C:\xampp\htdocs\testeOracleYii\index.php(18): CApplication->run()

#16 {main}

REQUEST_URI=/testeOracleYii/index.php/gii/model

---

My Oracle OCI instalation was pretty minimal, I just edited PHP.INI and uncommented:

extension=php_oci8_11g.dll

I didn’t venture to get FastCGI (because my PHP version - from a straight XAMPP install - wasn’t the suggested NTS for FastCGI).

I tested OCI with this code:

<?php

$c = oci_pconnect("MYUSER", "MYPASS", "MYDB");

$s = oci_parse($c, 'select name from v$database');

$r = oci_execute($s);

$ncols = oci_num_fields($s);

echo "</tr>\n";

while (($row = oci_fetch_array($s, OCI_ASSOC + OCI_RETURN_NULLS)) != false) {

    echo "<tr>\n";

    foreach ($row as $item) {

        echo " <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;") . "</td>\n";

    }

    echo "</tr>\n";

}

?>

And it worked like a charm, spitting out the database name.

Then I proceeded to try and configure Yii to access Oracle, which I did in both main.php and console.php adding this to the ‘components’ array:

'db' => array(

            'class' => 'CDbConnection',

            'connectionString' => 'oci:dbname=MYDB/orcl;charset=UTF8',

            'username' => 'MYUSER',

            'password' => 'MYPASS',

        ),

But now when I hit the Web based Gii tool, it gives me that connection error.

Am I missing some kind of configuration?

Thanks is advance.

[jayant (Codesutras)]

This is my code for connection string

'db'=>array

		(

		    'class'=>'CDbConnection',

		    'connectionString'=>'oci:dbname=127.21.0.45:1522/ora11gv4;charset=UTF8',

			'username'=>'MYUSER',

   			'password'=>'MYPASS',

			'schemaCachingDuration' => 7200,

			'enableParamLogging'=>true,

   			'enableProfiling'=>true,

		    'emulatePrepare' => true,

		),

And its work like a charm. My application is now in production server and everything is working fine. i follow all the guidelines from this link and everything seems work fine.

Also as you are saying that it only getting problem on Gii module then i think its a strange.

What i will suggest to you here is create a one model class manually and then try to execute some sql statements in your project and lets see are you getting some kind of problem also there.

And please make sure that you have installed the correct extension on server.

[duartix (Duartyx)]

This is my code for connection string

(…)

And its work like a charm.

Thanks, but doesn’t solve it.

i follow all the guidelines from this link and everything seems work fine.

So did I, that’s also where I learned how to build my connection string.

What i will suggest to you here is create a one model class manually and then try to execute some sql statements in your project and lets see are you getting some kind of problem also there.

I’ll look around the tutorials and try to do it manually…

And please make sure that you have installed the correct extension on server.

What do you mean? This is a local Netbeans PHP/XAMPP/Yii/OCI install, accessing Oracle database on a remote server. I do have a local Oracle client (not the Instant Client but the full one). Do I need to configure anything else for OCI apart from activating it in PHP.ini?

[jayant (Codesutras)]

Thanks, but doesn’t solve it.

So did I, that’s also where I learned how to build my connection string.

I’ll look around the tutorials and try to do it manually…

What do you mean? This is a local Netbeans PHP/XAMPP/Yii/OCI install, accessing Oracle database on a remote server. I do have a local Oracle client (not the Instant Client but the full one). Do I need to configure anything else for OCI apart from activating it in PHP.ini?

Ok, i understand you point. What i mean here is by extension is. check your php.ini file or check phpinfo that all oracle related drivers are also enabled or not.Another oracle-pdo extension also enable or not.Just to make sure and nothing else

And again i will say try to do some test model query task and lets see what is happening there.

Also please don’t forget to share your experience here.Since, i am also learning to work with oracle+yii ;)So, it would be great if you can share your thoughts also here.

[duartix (Duartyx)]

Ok, i understand you point. What i mean here is by extension is. check your php.ini file or check phpinfo that all oracle related drivers are also enabled or not.

OCI looks normal:

oci8


OCI8 Support	enabled

Version	1.4.10

Revision	$Id: 44bfa713983a99b3e59477f6532e5fb51b6dee94 $

Active Persistent Connections	0

Active Connections	0

Oracle Run-time Client Library Version	11.2.0.1.0

Oracle Instant Client Version	11.2

Temporary Lob support	enabled

Collections support	enabled


Directive	Local Value	Master Value

oci8.connection_class	no value	no value

oci8.default_prefetch	100	100

oci8.events	Off	Off

oci8.max_persistent	-1	-1

oci8.old_oci_close_semantics	Off	Off

oci8.persistent_timeout	-1	-1

oci8.ping_interval	60	60

oci8.privileged_connect	Off	Off

oci8.statement_cache_size	20	20

But… could this one be the culprit?

PDO

PDO support	enabled

PDO drivers	mysql, sqlite

Shouldn’t Oracle be listed on the PDO drivers?

[duartix (Duartyx)]

But… could this one be the culprit?

PDO

PDO support	enabled

PDO drivers	mysql, sqlite

Shouldn’t Oracle be listed on the PDO drivers?

Answering my own question:

No.

I edited PHP.ini so that php_pdo_oci.dll got loaded (I checked via phpinfo()), but the error is still there…

PDO

PDO support	enabled

PDO drivers	mysql, oci, sqlite

I’m running out of ideas…

[duartix (Duartyx)]

This is my code for connection string

'db'=>array

		(

		    'class'=>'CDbConnection',

		    'connectionString'=>'oci:dbname=127.21.0.45:1522/ora11gv4;charset=UTF8',

			'username'=>'MYUSER',

   			'password'=>'MYPASS',

			'schemaCachingDuration' => 7200,

			'enableParamLogging'=>true,

   			'enableProfiling'=>true,

		    'emulatePrepare' => true,

		),

Cheers codesutra!

Why [color="#FF0000"]ora11gv4[/color]? What parameter is that and where does it come from?

Thanks again.

[jayant (Codesutras)]

Cheers codesutra!

Why [color="#FF0000"]ora11gv4[/color]? What parameter is that and where does it come from?

Thanks again.

Oh glad you fixed that extension issue. by the way that is internal server hostname that is why it is looking something like this

So, dont need to concentrate on that more

[duartix (Duartyx)]

Oh glad you fixed that extension issue. by the way that is internal server hostname that is why it is looking something like this

The extension is fixed but the Model generator still won’t connect…

Now, about the hostname… what should I put there? Or how do I find what to put there?

[duartix (Duartyx)]

Well, it’s too late now…

I’ve already joined the dark side and it is working.

[duartix (Duartyx)]

Well, it’s too late now…

I’ve already joined the dark side and it is working.

As I said in the previous post, I’m off to CI which had it’s fair share of quirks of database configuration and their case sensitive issues when using table/column names, but which is now working well. Meanwhile, and just out of curiosity, I found out I could go an extra step further with yii and what seemed to be the problem was that very same connection parameter.

This just woks:

 'connectionString' => 'oci:dbname=DSII02;charset=UTF8',

Well… sort of works, now that I don’t get the connect error, I’m getting a timeout:

[color="#8B0000"]Fatal error: Maximum execution time of 30 seconds exceeded in C:\xampp\php\yii-[font="Arial"]1.1.13.e9e4a0\framework\db\CDbCommand.php on line 506[/font][/color]

… which seems in line with a another post I’ve read about Gii timing out when trying to read tables from the Oracle database dictionary: http://www.yiiframework.com/forum/index.php/topic/13698-gii-fails-to-generate-model-with-timeout/

I though it might help someone else who gets stuck when connecting, so I posted.

Thanks again for all the help.