Calcio
February 3, 2016, 1:39pm
1
Hi guys!
I face a problem when I try update any register. O need to use a beforeSave method to implement an Oracle auto-increment (the famous sequence.nextval)
<?php
namespace app\models;
use Yii;
use yii\db\ActiveRecord;
class OracleActiveRecord extends ActiveRecord
{
/**
* Overriding this method to get oracle sequence
*/
public function beforeSave($insert)
{
if (Yii::$app->db->driverName === 'oci' && $this->isNewRecord && $insert) {
try {
$sql = "SELECT SEQ_RISCO.NEXTVAL FROM DUAL";
$result = Yii::$app->db->createCommand($sql)->queryOne();
return $result['NEXTVAL'];
} catch (Exception $e) {
echo("Error: $e\n");
}
} else {
// not implemented yet, doubt this will work
// return parent::getLastInsertID();
}
}
}
In my Model, I call the beforeSave again to can save a Insert, However this implementation (I don’t know why) broken my update.
I try and search for examples but I’m could find it yet.
I need help to convince my coworkers adopt Yii2 here.
Thanks in advance.
Bizley
February 3, 2016, 2:27pm
2
From the BaseActiveRecord class doc:
* When overriding this method, make sure you call the parent implementation like the following:
*
* ```php
* public function beforeSave($insert)
* {
* if (parent::beforeSave($insert)) {
* // ...custom code here...
* return true;
* } else {
* return false;
* }
* }
* ```
Calcio
February 3, 2016, 2:49pm
3
@Bizley , I try do it in beforeSave in OracleActiveRecord and Risco model, and it do not work yet.
Bizley
February 3, 2016, 8:24pm
4
I’m sorry, I’m not able to help you with oracle here, my comment was about the beforeSave() method and how it should be implemented.
By the way - isn’t this already ready to use? I mean setting the new value of primary key automatically there.
jacmoe
February 3, 2016, 8:36pm
5
Edit: never mind that
Bizley is right about how to properly override the function.
And, btw: you only need to check if $insert is true ( a new record will insert ).
Calcio
February 4, 2016, 12:38pm
6
Thanks @Bizley and @jacmoe .
I use you hint about the override the method and it’s works. The new method in my OracleActiveRecord is:
public function beforeSave($insert)
{
if (parent::beforeSave($insert) && Yii::$app->db->driverName === 'oci') {
try {
$sql = "SELECT SEQ_RISCO.NEXTVAL FROM DUAL";
$result = Yii::$app->db->createCommand($sql)->queryOne();
return $result['NEXTVAL'];
} catch (Exception $e) {
echo("Error: $e\n");
}
} else {
return false;
}
}
On the models that extends OracleActiveRecord , it’s no have changes.
Thanks so much!
Bizley
February 4, 2016, 1:23pm
7
This method should return boolean value so
return $result['NEXTVAL'];
is wrong for sure (and will be treated as boolean anyway). Also there is no return statement in case of catching exception.
My previous question about “ready to use” class is still valid. Quick look at the the oci Schema class - isn’t there this code you are using already in?
Calcio
February 4, 2016, 5:17pm
8
Bizley:
This method should return boolean value so
return $result['NEXTVAL'];
is wrong for sure (and will be treated as boolean anyway). Also there is no return statement in case of catching exception.
My previous question about “ready to use” class is still valid. Quick look at the the oci Schema class - isn’t there this code you are using already in?
This way it’s work for me. About the getLastInsertID() I tried to use but it’s not work for me. And I don’t find any example how to use it.
I think this method only return the ID already created, not a new ID
jacmoe
February 4, 2016, 5:53pm
9
I am wondering why you are even trying to get a new ID when you have told Oracle to auto increment ids?
By the way, when I mentioned that you should use $insert I meant like this:
if($insert) {
// a new insert is to be performed
}
Calcio
February 4, 2016, 6:15pm
10
jacmoe:
I am wondering why you are even trying to get a new ID when you have told Oracle to auto increment ids?
By the way, when I mentioned that you should use $insert I meant like this:
if($insert) {
// a new insert is to be performed
}
Oracle don’t have auto increment, they use a sequence to do it, but the sequence it’s not incremented automatically. I have to create it manually on my software. I couldn’t find a solution to my problem, so I adapted a solution that I use on the CodeIgniter.
If yii2\db\oci\Schema.php had a method like getSequence() or getSequeceID() using something like $db->createCommand(“SELECT {$sequenceName}.NEXTVAL FROM DUAL”)->queryOne(); , I do not need try to write a workaround.
Calcio
February 4, 2016, 6:41pm
11
A little refectory…
public function beforeSave($insert)
{
if (parent::beforeSave($insert) && Yii::$app->db->driverName === 'oci') {
try {
$this->ID = Yii::$app->db->createCommand("SELECT SEQ_RISCO.NEXTVAL FROM DUAL")->queryScalar();
return $this->ID;
} catch (Exception $e) {
echo("Error: $e\n");
}
} else {
return false;
}
}
With it, remove the method public function beforeSave($insert) from you model that extends OracleActiveRecord .
Bizley
February 4, 2016, 8:14pm
12
Ok, I don’t know about oracle db but at least let’s make this method right.
public function beforeSave($insert)
{
if (parent::beforeSave($insert)) {
if ($insert && Yii::$app->db->driverName === 'oci') {
try {
$this->ID = Yii::$app->db->createCommand("SELECT SEQ_RISCO.NEXTVAL FROM DUAL")->queryScalar();
return true;
} catch (Exception $e) {
Yii::error($e->getMessage());
return false;
}
}
return true;
}
return false;
}
