[piwer]

Hello,

Is there any other solution of using sequences in Oracle than Sequence + trigger?
afaik this is not efficient, and i would like pass just nextval to my PK like this:

$model->user_id = 'USERS_SEQ.nextval';

This should do. But if i do that, Yii passes a string to Oracle, and of course it throws an error.

I`ve found out that Yii has $sequenceName in db classes, but how to use that? Where to assign it?

[Sander]

I think you're looking for CDbExpression. It's usage is described in http://www.yiiframew...creating-record

[ricardograna]

Maybe like this:

$model->user_id = new CDbExpression('USERS_SEQ.nextval');

But I think sequence+trigger is still efficient

[piwer]

ricardograna, on 15 January 2010 - 06:51 AM, said:

Maybe like this:

$model->user_id = new CDbExpression('USERS_SEQ.nextval');

But I think sequence+trigger is still efficient

Works as a charm! Thanks. To be honest i was looking for the CDbExpression but had hole in my brain today :-)

but...
When i redirect page after saving it (normal procedure)like this:

$model->attributes=$_POST['vt_cust_m'];
$model->CUST_ID=new CDbExpression('VT_CUST_M_SEQ.nextval');
if($model->save())
	$this->redirect(array('view','id'=>$model->CUST_ID));

it redirects me to url:

http://localhost/vt/index.php?r=vt_cust_m/view&id=VT_CUST_M_SEQ.nextval

so the $model instance took the string as the PK.
i`ll try to find workaround for that :-)

[piwer]

one more thing.. what about $sequenceName? i understand the mechanism is not implemented.. yet ? I read somewhere in Definitive Guide changelog:

Version 1.0.9 September 6, 2009
- Bug: Set sequenceName in Oracle tables to be empty string so that yiic model command generates correct validation rules for PK (Qiang)

some time ago, that if you want to use sequences u must set it .. somewhere, but i cannot find it now. Weird.

[ricardograna]

Probably it will work. I checked schema implementation and I think it will work.


It is not as it wasn't implemented. It could not be automatically implemented, because Oracle doens't allow us to define a explicity connection between Tables and Sequences (as it is in Postgres), so there is no way to automatically catch the corresponding sequence for the table.

Then, I had to set sequenceName to NULL, otherwise, PK rules would not be Ok.



Anyway, I don't like to reference sequence names in my code, then I prefer triggers.

[piwer]

well, yes. I`m also for triggers, although i`m not oracle master :-)
It`s just a friend of mine, told me, that with larger tables, performance issues can appear.