OCI8 with CDbConnection

[jefftulsa]

The getData() method in the dataprovider is doing what it says…it is getting the appropriate data to display. ‘Appropriate’ meaning having applied the proper “limit” to the sql query if pagination is being used. So, it is not getting a ALL of the data and then limiting it, it is using the faster statistical query to get the count and then applying a limit to the db criteria itself before the query for the data is made.

I am assuming you are using CActiveDataProvider as the data provider component to your CListView, is that assumption correct? This takes in an AR class name and the underlying db component (defaults to the ‘db’ application component unless otherwise specified) is what is used to connect to the db. This is where it would determine what schema to use.

If you want to provide the code you are attempting to execute, like the creation of your data provider and the way you are using CListView, it might help to track down your issue.

Also, the methods

CActiveDataProvider::fetchData()

and

CActiveDataProvider::calculateTotalItemCount()

which can be found in the class file framework/web/CActiveDataProvider.php

may be of some use in helping you troubleshoot this issue.

[ddreggors]

@Tova

That looks very promising.

It does fail though. First try failed on invalid character.

CDbConnection failed to open the DB connection: SQLSTATE[HY000]: General error: 911 OCIStmtExecute: ORA-00911: invalid character

This was due to semicolon (’;’) in ALTER SESSION statement.

Then I removed the semicolon and I get this error:

CDbConnection failed to open the DB connection: SQLSTATE[HY000]: General error: 1435 OCIStmtExecute: ORA-01435: user does not exist

the user has not changed so something is not right here either.

[ddreggors]

@Jeff

I am assuming you are using CActiveDataProvider as the data provider component to your CListView, is that assumption correct?

Yes, I believe so.

I am not doing anything more than what Gii generates. The view (and the CListView inside the view) was created by Gii so this should be the standard CActiveDataProvider.

This takes in an AR class name and the underlying db component (defaults to the ‘db’ application component unless otherwise specified) is what is used to connect to the db. This is where it would determine what schema to use.

This is where the problem exists I believe. The main.php ‘db’ section does not allow for a schema to be set. As suggested by Tova the schema is obtained by username which is incorrect (at least in our implementation). Many DBA’s setup access to multiple different schemas from 1 username. Our username does not match the schema that we are trying to access. I have no access to the DB to change that fact, and would nto be allowed to even if I had those privileges. We have other apps that use that username and make use of the other schema owned by that username. This is not uncommon and in fact is standard in many Oracle implementatinos. While this app only uses 1 of the accessable schemas the username does not match the schema and this schema is not the default for this username.

Just to backtrack for added info, I am a new Yii user that is just getting the hang of setting up Yii to use a DB. I have not experimented much beyond the examples in your book and in the demo app. I have not extended any class or tried using another DataProvider. This is a brand new app that was generated using ‘yiic webapp /var/www/html/testora’ command. All of the code to connect to the DB for crud was created by Gii not by myself. I have only modified it for testing and then I change right back. When I do modify it, I comment the original lines and do not remove them. When returning to original code I uncomment the original lines and delete my additions.

I did just try to use Tova’s suggestion but it does not seem to work for me so I will be going back to original code created by Gii now.

If you want to provide the code you are attempting to execute, like the creation of your data provider and the way you are using CListView, it might help to track down your issue.

The code is default code for CListView generated by Gii, however I have commented it now and I am simply doing as you suggeted and trying to print_r the data returned from $dataProvider->getData().

<?php

$this->breadcrumbs=array(

	'Hosts',

);


$this->menu=array(

	array('label'=>'Create new host', 'url'=>array('create')),

	array('label'=>'Manage Hosts', 'url'=>array('admin')),

);

?>


<h1>Hosts</h1>


<?php

print_r($dataProvider->getData());

//$data=$dataProvider->getData();

//                exit;

//$this->widget('zii.widgets.CListView', array(

//	'dataProvider'=>$dataProvider,

//	'itemView'=>'_view',

//)); ?>

[ddreggors]

I think we might be over-complicating the question.

A simple way to look at the problem (which does not mean there is a simple answer by the way) is:

Why does the creation of the object $dataProvider find the table and load all the column names details into the object but then the objects getData method fails to find the table/data?

This suggests a breakdown in code to me.

On one hand you not only see the table but can get all details about the column lengths, types, etc… and then using the same DB classes you can not find the same table to run a quick ‘select *’ on it.

Maybe I am incorrect in the simplicity of the problem but the facts remain true, I have provided examples above showing that the object $dataProvider is loaded with the table details correctly so it does find the table on object creation!

[ddreggors]

If your username has a default schema other than the schema you are trying to access you must make these changes…

SCHEMA ISSUES…

fixed in Yii-1.1.5.r2664 (SVN)

Will be available in stable release 1.1.6

QUOTING ISSUES…

fixed in Yii-1.1.5.r2664 (SVN)

Will be available in stable release 1.1.6

This is to provide a work around for the oracle bug that is in 11g dealing with self joins. Note the ‘, C.table_name’ at the top of the select before the FROM line.

COciSchema.php (findConstraints method):

$sql=<<<EOD

		SELECT D.constraint_type as CONSTRAINT_TYPE, C.COLUMN_NAME, C.position, D.r_constraint_name,

                E.table_name as table_ref, f.column_name as column_ref, C.table_name

        FROM ALL_CONS_COLUMNS C

        inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name

        left join ALL_constraints E on E.OWNER = D.r_OWNER and E.constraint_name = D.r_constraint_name

        left join ALL_cons_columns F on F.OWNER = E.OWNER and F.constraint_name = E.constraint_name and F.position = c.position

        WHERE C.OWNER = '{$table->schemaName}'

           and C.table_name = '{$table->name}'

           and D.constraint_type <> 'P'

        order by d.constraint_name, c.position

EOD;

Fix Gii generated code if Primary Key is NOT an int…

Gii Generated Controller NETOPSHOSTSController.php (starting at line 156):

public function loadModel($id)

{

	$model=NETOPSHOSTS::model()->findByPk($id); // Changed from findByPk((int)$id)

	if($model===null)

		throw new CHttpException(404,'The requested page does not exist.');

	return $model;

}

[EDIT]

All schema name issues are addressed in bug 1775 and fixed by 1.1.5.r2664 (current SVN trunk).

SVN r2664 page

Yii Bug 1775 page

All quoting issues are also resolved in this trunk revision.

These changes will be in the stable 1.1.6 version of Yii as well.

The workaround for the oracle bug is not yet tracked as a bug since it is not a bug in Yii code.

[/EDIT]