Hi there everyone,
Hope you can help with a couple of pointers on how we can use the Yii framework to access
PL/SQL within an Oracle database. We do not need the application to be database agnostic, it
in fact only needs to access and manipulate data via Oracle’s PL/SQL.
The question I am trying to answer is :
Can OCI8 be used with CDbConnection within main.php ? If not, how can one use an OCI8 based connection
as an application component.
Thanks for any help !
I am having the same issue…
If I do not set "$_defaultSchema" in my COciSchema.php file to the schema I am trying to use I get "Table or View does not exist" in Gii model generator. This much is expected.
However, if I do set the "$_defaultSchema" to the schema I wish to use, I get the following error in gii when I try to generate a model:
On the command line I can use the same credentials I use in main.php and connect using sqlplus. In my DbTest (phpunit test) I get a pass using the following test:
<?php
/*
* Database unit tests
*/
class DbTest extends CTestCase
{
/*
* Test connection to the database
*/
public function testConnection()
{
$this->assertNotEquals(NULL, Yii::app()->db);
}
}
?>
Does anyone have any clues?
I did a little more back tracking and I grabbed the query that came into Oracle when trying to generate a model.
Here is what oracle was sent from Yii:
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
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 = 'NETOPS_SCHEMA'
and
C.table_name = 'NETOPS_HOSTS' and D.constraint_type <> 'P'
order by
d.constraint_name,
c.position
I tried running this through sqlplus and also get an error.
The SQL is correct but there seems to be an oracle bug that matches the problem that is fixed in oracle 11.2.2
On the page we see:
Oracle produces the error:
The Oracle Bug # associated with the ORA 7445 error is:
The patch for Oracle version 11.x prior to 11.2.2:
Our current Oracle version is: 11.2.1
The bug is said to be fixed in: 11.2.2
The oracle approved work-around prior to patch or upgrade is:
alter session set "_optimizer_improve_selectivity"=FALSE;
My question is where would we add that in Yii?
What file would that be added in and how?
Past 2 months I’m developing project with Yii (Windows Apache2.2, PHP 5.2.14) and Oracle DB (10r1 and 10r2), so, can share some experience on this.
DON’T use Yii 1.1.4 stable branch with Oracle, it has some bugs, which, I’ve found, are fixed in trunk.
Yii works well for me with both drivers pdo_oci with oracle’s instantclient10 and oci8 through this extension: http://www.yiiframework.com/extension/phppdo/
I had some issues, for example:
gii crashes if recyclebin objects found (keep your DB clean, delete tables with purge option),
needed to rewrite all date attributes in models beforeSave() method
like this:
$this->some_date=new CDbExpression("TO_DATE('".$this->some_date."','YYYY-MM-DD')");
I’m using Yii AR intensively, and some commands via DAO, where I need speed.
In resume I’m very happy and proud to use Yii with Oracle 
Thank you for the reply, I appreciate it. Your answers do leave me some more questions though.
If not the latest stable release, then are you suggesting the latest SVN unstable or are you suggesting an older version?
also
Are you saying you have to use both the pdo_oci AND the oci8 module?
We have been working under the impression that we are only supposed to use the pdo_oci module. What modules are you loading and what extra options are you using in the php.ini when loading?
<EDIT>
Quick update…
I just checked out the SVN trunk (1.1.5?) and tried the same tests.
It was the same issue as before even with the trunk.
</EDIT>
In this case I suggest you wait until Nov. 14 when 1.1.5 will be released as boss says here.
I’m saying both drivers works well, but they must be used separately - you have to choose one of them. Also there is some limitations and known bugs in pdo_oci (http://us2.php.net/pdo_oci), so pay attension if you will use this one.
Nothing special php.ini, just loaded pdo and pdo_oci extensions, and pointed path to oracle’s client in environment variables. As I see you are using pdo_oci right now, that is enough
What depends on your issue - I think you can try to set in your db connection settings initSQLs or execute command right before using other queries. I had case:
$connection=Yii::app()->db;
$command=$connection->createCommand('alter session set NLS_SORT=BINARY_CI')->execute();
$command=$connection->createCommand('alter session set NLS_COMP=LINGUISTIC')->execute();
While this is not common case I don’t think this will be fixed in framework’s core, but your are free to report a bug.
Regards, Tomas
Thanks for all the help…
I was able to find a work around for getting Gii to generate the code. The bug (which I believe is an oracle issue) was in the query itself.
In the ‘framework/db/schema/oci/COciSchema.php’ file there is a function to find table contraints. I listed the actual SQL above.
When running that SQL by hand I get an oracle error and here is why and the fix:
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, /* <--- added comma so I can add the following line */
C.table_name /* <--- If we do not also select this line we cause oracle error */
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 = 'NETOPS_SCHEMA'
and
C.table_name = 'NETOPS_HOSTS'
and
D.constraint_type <> 'P'
order by
d.constraint_name,
c.position
For some reason we have to add ‘C.table_name’ to the columns to select or Oracle will generate an error and core dump!
This should not be necessary, using a join on columns not viewed is acceptable in SQL as far as I know. I do know that adding the columns acts as a work around though.
The actual code in the file now works for model/crud generation and looks like this:
$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;
*NOTE the added ‘, C.TABLE_NAME’ at the end of the line before the ‘FROM’ part starts.
The next error I am now having is that the generated code fails with the following error:
Doing more back tracking, I placed a vardump in the code in the COciSchema.php file at the end of the function ‘findColumns’.
Here is where it gets odd again… I added a var_dump and an exit right above the return line and var_dumped the resulting $table object. This var_dump showed ALL columns and the properties for them. So it clearly does find the table despite the fact that the error says ‘table or view does not exist’.
I am still backtracking to see where that error is generated and why and will update on my findings as I find them.
<EDIT>
I am not seeing the problem. I have dug deep into the CActiveRecord, CDbCommandBuilder, CDbSchema, and others.
From inside the protected|views|nETOPSHOSTS|index.php, if I comment out the CListView section and var_dump the $dataProvider variable I get a dumped view of all columns and column descriptions along with other data.
This tells me that the table is seen and can be queried (at least for details on structure).
However CListView (using the same $dataProvider variable returns the error ‘table or view does not exist’
I am stumped… any help here would be appreciated.
</EDIT>
Hello David,
So, are you saying that the data provider you are using with the CListView widget is returning the correct data as expected?..i.e.:
print_r($yourDataProvider->getData());
gives you what you would expect from your table?
I have not tried ‘$yourDataProvider->getData()’ but I have tried print_r($dataProvider). Since that is what is passed to CListView that is what I dumped.
The answer to that question though is yes. It actually shows the tables column names, their field types, etc…
I was thinking of posting a bug on this but I am not sure this is relevant to Yii.
As I stated above, this is more of an Oracle bug that I happen to have found a valid workaround for that will not break other patched versions of oracle.
At least for Gii anyway, I am not sure where the SQL is generated for using the CListView but I am sure a similar workaround can be used.
I just changed:
print_r($dataProvider);
to
print_r($dataProvider->getData());
print_r($dataProvider) returns a detailed structure of the table…
Obviously there is quite a bit more but I chopped the rest to make this post smaller.
print_r($dataProvider->getData()) however returns an oracle error:
Also, FWIW, if your application is in debug mode:
define(‘YII_DEBUG’,true);
in your main index.php file, then Yii should be logging ALL queries to the default logging route location: protected/runtime/application.log
Clearly it is getting a db error when trying to execute the query. You might want to take a look in this file and see what, exactly, is being executed.
The error log shows:
The query ‘SELECT COUNT(*) FROM NETOPS_HOSTS t’ I believe is trying to get the number of records for pagination in the CListView. NETOPS_HOSTS is a valid table in the schema NETOPS_SCHEMA however I do not see that it is trying to use the schema.
<EDIT>
No, since I am trying to dump the contents of $dataProvider->getData() with print_r this should not have anything to do with CListView. I am not sure what getData() does behind the scenes but it does look like it is trying to get a count of all records and not get the actual records. Maybe it does both… count first then load data in return.
</EDIT>
When I built the model/crud I used ‘NETOPS_SCHEMA.NETOPS_HOSTS’ so it should have stored that schema and used it correct?
Before this attempt I also tried setting $_defaultSchema in COciSchema to ‘NETOPS_SCHEMA’ as well. Same results. It seems like the schema just gets ignored.
Actually I was slightly in error in my previous post.
The errors above are only obtained if I set $_defaultSchema in COciSchema.php to my schema.
If I do not set $_defaultSchema in that file I end up with this in logs:
Note there is no SQL listed here and the error is no longer ‘table or view’ error but now it is now…
‘The table “NETOPS_HOSTS” for active record class “NETOPSHOSTS” cannot be found in the database.’
This does seem to indicate that the schema is being used but why would it fail to find a table that was found using Gii?
This code was all generated by the successful run of Gii.
Hello again,
I think schema is taken from your login credentials (username) and there is no need to define schema when you creating models. I have created 70+ models without defining schema.
SomeModel.php
public function tableName()
{
return 'SOME';
}
But I had same problem with caching enabled - that is - if you made some changes in schema, but schema attributes is taken from cache then “it seems like the schema just gets ignored”
//comment out all caching features
// 'cache'=>array(
// 'class'=>'CDbCache',
// ),
'db'=>array(
'connectionString' => 'oci:dbname=192.168.0.1/WEBAPP;charset=utf8',
'username' => 'DEV_SCHEMA',
'password' => '<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/ohmy.gif' class='bbc_emoticon' alt=':o' />)',
//'schemaCachingDuration'=>3600,
),
In our implementation we have 2 schemas accessible with the same login.
NETOPS_APP
NETOPS_SCHEMA
They store different data for different purposes but the key here is that the username matches neither.
I am trying to access the latter ‘NETOPS_SCHEMA’ but the default schema for this username is the first schema ‘NETOPS_APP’.
From NetBeans (using connection manager) I can access this schema and run queries using the same credentials. I can also drop out to command line and use sqlplus and use the same credentials and get to tables in this schema. I just always have to prefix all table names with the schema name (NETOPS_SCHEMA.NETOPS_HOSTS) as I did with the model builder in Yii.
That said however, that should be the purpose of the $_defaultSchema I believe. This should be just for this purpose is it not?
Since this is a freshly cretaed webapp there is nothing beyond the database connection and enabling Gii done to the main.php file. So caching is not an issue here.
I think you’d better go that way (from http://www.yiiframework.com/doc/guide/1.1/en/database.ar#defining-ar-class)
If I can drop a line into discussion…
Maybe this article will help. But I must admit that I reported (solved) there only basic problems with using Yii and Oracle.
I think you misunderstood… I am not trying to use 2 databases or schemas with this app. I am only using 1 schema on 1 oracle server. It just so happens that the username has access to 2 schemas on that server.
The application should only ever use 1 of these schemas and never use the other.
Basically this app is being written for the data in NETOPS_HOSTS not for the data in NETOPS_APP. The NETOPS_APP has a whole different purpose and is used by another app written in java.
Thank you I have read that article. However this is not an error with the dsn. The dsn is fine and in fact was used to build the model and crud in Gii. I also use the same credentials and connection info in NetBeans and in sqlplus.
Also, as for the error log reporting the query as ‘select count(*) from NETOPS_HOSTS t’ I was looking at that to see why getData would would try to get a count of records vs getting the records themselves as the name suggests.
Here is what I see and I have a question on the core code…
File: <YII_HOME>/framework/db/schema/CDbCommandBuilder.php
Code:
if(is_string($criteria->select) && stripos($criteria->select,'count')===0)
$sql="SELECT ".$criteria->select;
else if($criteria->distinct)
{
if(is_array($table->primaryKey))
{
$pk=array();
foreach($table->primaryKey as $key)
$pk[]=$alias.'.'.$key;
$pk=implode(', ',$pk);
}
else
$pk=$alias.'.'.$table->primaryKey;
$sql="SELECT COUNT(DISTINCT $pk)";
}
else
$sql="SELECT COUNT(*)";
$sql.=" FROM {$table->rawName} $alias";
$sql=$this->applyJoin($sql,$criteria->join);
Shouldn’t this be:
//<--- note the false in the next line and not 0
if(is_string($criteria->select) && stripos($criteria->select,'count')===false)
$sql="SELECT ".$criteria->select;
else if($criteria->distinct)
{
if(is_array($table->primaryKey))
{
$pk=array();
foreach($table->primaryKey as $key)
$pk[]=$alias.'.'.$key;
$pk=implode(', ',$pk);
}
else
$pk=$alias.'.'.$table->primaryKey;
$sql="SELECT COUNT(DISTINCT $pk)";
}
else
$sql="SELECT COUNT(*)";
$sql.=" FROM {$table->rawName} $alias";
$sql=$this->applyJoin($sql,$criteria->join);
I read this on ‘stripos’ at ‘http://php.net/manual/en/function.stripos.php’:
when dumping the variable ‘$criteria’ using prin_r right before that if, and then the ‘$sql’ variable right after, I see an issue. The $criteria->select is ‘STRING’ and it contains ‘’ not 'count()’.
The resulting SQL should have been ‘select * from’ and not ‘select count(*) from’. Because stripos returns false and not 0 as the code is checking for we will always go into the else which wraps the criteria in a ‘count()’.
In all other occurrences of stripos in code you do use false in stead of 0 so is it safe to assume that this is a typo or was missed in previous changes?
That’s true, I misunderstood, sorry.
Last try and I give up - look here http://www.yiiframework.com/forum/index.php?/topic/2179-postgresql-schemas-not-possible/, at post #6.
Just change for oracle "ALTER SESSION SET CURRENT_SCHEMA = NETOPS_HOSTS;"