Yii handling “getLastInsertId” with Oracle

April 5, 2012 — JaZahn

With MySQL or SQLite, when you insert something with an auto_increment field, it will automatically deal with the ActiveRecord by putting the last inserted id into $model->id. Because Oracle needs sequences and triggers to deal with that, neither the PDO driver nor the Yii PDO code felt it necessary to deal with that. Most people are probably fine with commiting to a database and throwing in Oracle specific sequence_name.nextval.

I just added a class in the models to extend CActiveRecord. The only thing you need to do with this is have your model extend this instead of CActiveRecord and add the variable schemaName to the model:

class User extends QActiveRecord
{
	public $sequenceName = 'USERS_SEQ';	
...
Posted in ATG, Databases, Oracle, PHP, Quizmo, Yii. Tags: , , , , , . 2 Comments »

Yii Migrations: Dealing with auto_increment for Oracle and MySQL

April 4, 2012 — JaZahn

Oracle deals with auto_increment by using sequences and triggers:

create sequence test_seq
start with 1
increment by 1
nomaxvalue;

create trigger test_trigger
before insert on test
for each row
begin
select test_seq.nextval into :new.id from dual;
end;
/

And MySQL is just an auto_increment keyword on the end of a column declaration:

CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
)
So I created a simple Autoincrement class:

/protected/migrations/Autoincrement.php

Which I included in the migration file for a schema.  This class will handle checking the driver and executing the appropriate sql.
Autoincrement::up('USERS', Yii::app()->db->driverName);
Autoincrement::down('USERS', Yii::app()->db->driverName);

What needs to be remembered here is to add a “sequenceName” to the model class, that is of the form “TABLENAME_SEQ”.

Posted in ATG, Databases, MySQL, Oracle, PHP, Quizmo, Yii. Tags: , , , , , , , . No Comments »
«
»