Support for SQL Server RowVersion

Yii 2.0 General Discussions
1

I have an app running on Azure SQL (SQL Server). I recently added a new RowVersion column to a table and now the Create button errs:

“…Cannot insert an explicit value into a timestamp column. Use INSERT without a column list to exclude the timestamp column , or insert a DEFAULT into the timestamp column…”

Can anyone give any guidance to resolve this?
What should the model be for a RowVersion column? What rule?

I’ve tried searching the guide and Google for Yii2 info relating to RowVersion and seem to be unable to locate any information.

2

What is rowversion?
I worked on MSSQL long ago and I cannot recall what is it

3

RowVersion is the new TimeStamp, but it doesn’t store any actual date/time information it is equivalent to binary(8) column.

Yii sees it as [RV] timestamp (RV is the name of my column)

The issue seems to be the same as: https://github.com/yiisoft/yii2/issues/18606

I’ve tried setting the value to DEFAULT, but the error seems to have the values in the INSERT in the wrong order. DEFAULT does appear, just not for the right attribute. I’m lost at this point.

Is there any way to tell Yii to not include a column in the save process?

4

Perhaps try this:

In some rare cases, you may want to validate an attribute but do not want to mark it safe. You can do so by prefixing an exclamation mark ! to the attribute name

https://www.yiiframework.com/doc/guide/2.0/en/structure-models#unsafe-attributes

I guess it can be read but it will not be massively assigned thus not saved.

5

I’m not sure I follow. It is a column managed by the db. I will never save/modify it myself or through the app. It is part of the table, but not relevant to the app in any way. I don’t even have it in the form.

I’m not using any scenarios currently.

I tried doing the following in my controller action:

$model->RV = new Expression('DEFAULT');

but I still get the error:

{“name”:“Integrity constraint violation”,“message”:“SQLSTATE[23000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.\nThe SQL being executed was: SET NOCOUNT ON;DECLARE @temporary_inserted TABLE ([EmpId] int , [EmpNo] int NULL, [FirstName] nvarchar(255) , [LastName] nvarchar(255) NULL, [Company] nvarchar(255) NULL, [TitleId] int NULL, [Address1] nvarchar(255) NULL, [Address2] nvarchar(255) NULL, [CityId] int NULL, [ProvId] int NULL, [CountryId] int NULL, [PostalCode] nvarchar(255) NULL, [BusinessTel] nvarchar(50) NULL, [BusinessTelExt] nvarchar(50) NULL, [HomeTel] nvarchar(50) NULL, [CellularTel] nvarchar(50) NULL, [Fax] nvarchar(50) NULL, [Email] nvarchar(255) NULL, [URL] nvarchar(255) NULL, [Notes] nvarchar(500) NULL, [SpecialNotes] nvarchar(255) NULL, [DL] date NULL, [Ins] date NULL, [CurrencyCode] nvarchar(3) NULL, [TaxRateId] int , [ContractorAgreement] smallint , [ContractorAgreementDt] date NULL, [Active] smallint NULL, [XeroContactId] nchar(36) NULL, [dtCreation] datetime2 NULL, [dtModification] datetime2 NULL, [OldEmployeeId] int NULL, [RV] timestamp );INSERT INTO [employees] ([FirstName], [LastName], [Company], [TitleId], [Address1], [Address2], [CountryId], [ProvId], [CityId], [PostalCode], [BusinessTel], [BusinessTelExt], [HomeTel], [CellularTel], [Fax], [Email], [URL], [Notes], [SpecialNotes], [CurrencyCode], [TaxRateId], [ContractorAgreement], [ContractorAgreementDt], [Active], [EmpNo], [dtCreation], [RV], [OldEmployeeId], [dtModification]) OUTPUT INSERTED.[EmpId],INSERTED.[EmpNo],INSERTED.[FirstName],INSERTED.[LastName],INSERTED.[Company],INSERTED.[TitleId],INSERTED.[Address1],INSERTED.[Address2],INSERTED.[CityId],INSERTED.[ProvId],INSERTED.[CountryId],INSERTED.[PostalCode],INSERTED.[BusinessTel],INSERTED.[BusinessTelExt],INSERTED.[HomeTel],INSERTED.[CellularTel],INSERTED.[Fax],INSERTED.[Email],INSERTED.[URL],INSERTED.[Notes],INSERTED.[SpecialNotes],INSERTED.[DL],INSERTED.[Ins],INSERTED.[CurrencyCode],INSERTED.[TaxRateId],INSERTED.[ContractorAgreement],INSERTED.[ContractorAgreementDt],INSERTED.[Active],INSERTED.[XeroContactId],INSERTED.[dtCreation],INSERTED.[dtModification],INSERTED.[OldEmployeeId],INSERTED.[RV] INTO @temporary_inserted VALUES (‘j’, ‘tiemmens’, ‘jt’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ‘USD’, 21, 0, NULL, 1, 2182, ‘2022-08-18 19:21:55’, DEFAULT, NULL, NULL);SELECT * FROM @temporary_inserted”,“code”:“23000”,“type”:“yii\db\IntegrityException”,“file”:“D:\home\site\vendor\yiisoft\yii2\db\Schema.php”,“line”:676,“stack-trace”:["#0 D:\home\site\vendor\yiisoft\yii2\db\Command.php(1307): yii\db\Schema->convertException()","#1 D:\home\site\vendor\yiisoft\yii2\db\Command.php(1102): yii\db\Command->internalExecute()","#2 D:\home\site\vendor\yiisoft\yii2\db\mssql\Schema.php(782): yii\db\Command->execute()","#3 D:\home\site\vendor\yiisoft\yii2\db\ActiveRecord.php(604): yii\db\mssql\Schema->insert()","#4 D:\home\site\vendor\yiisoft\yii2\db\ActiveRecord.php(570): yii\db\ActiveRecord->insertInternal()","#5 D:\home\site\vendor\yiisoft\yii2\db\BaseActiveRecord.php(676): yii\db\ActiveRecord->insert()","#6 D:\home\site\backend\controllers\EmployeesController.php(545): yii\db\BaseActiveRecord->save()","#7 D:\home\site\backend\controllers\EmployeesController.php(192): backend\controllers\EmployeesController->saveEmployee()","#8 [internal function]: backend\controllers\EmployeesController->actionCreate()","#9 D:\home\site\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array()","#10 D:\home\site\vendor\yiisoft\yii2\base\Controller.php(178): yii\base\InlineAction->runWithParams()","#11 D:\home\site\vendor\yiisoft\yii2\base\Module.php(552): yii\base\Controller->runAction()","#12 D:\home\site\vendor\yiisoft\yii2\web\Application.php(103): yii\base\Module->runAction()","#13 D:\home\site\vendor\yiisoft\yii2\base\Application.php(384): yii\web\Application->handleRequest()","#14 D:\home\site\wwwroot\admin\index.php(17): yii\base\Application->run()","#15 {main}"],“error-info”:[“23000”,273,"[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column."],“previous”:{“name”:“Exception”,“message”:“SQLSTATE[23000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.”,“code”:“23000”,“type”:“PDOException”,“file”:“D:\home\site\vendor\yiisoft\yii2\db\Command.php”,“line”:1302,“stack-trace”:["#0 D:\home\site\vendor\yiisoft\yii2\db\Command.php(1302): PDOStatement->execute()","#1 D:\home\site\vendor\yiisoft\yii2\db\Command.php(1102): yii\db\Command->internalExecute()","#2 D:\home\site\vendor\yiisoft\yii2\db\mssql\Schema.php(782): yii\db\Command->execute()","#3 D:\home\site\vendor\yiisoft\yii2\db\ActiveRecord.php(604): yii\db\mssql\Schema->insert()","#4 D:\home\site\vendor\yiisoft\yii2\db\ActiveRecord.php(570): yii\db\ActiveRecord->insertInternal()","#5 D:\home\site\vendor\yiisoft\yii2\db\BaseActiveRecord.php(676): yii\db\ActiveRecord->insert()","#6 D:\home\site\backend\controllers\EmployeesController.php(545): yii\db\BaseActiveRecord->save()","#7 D:\home\site\backend\controllers\EmployeesController.php(192): backend\controllers\EmployeesController->saveEmployee()","#8 [internal function]: backend\controllers\EmployeesController->actionCreate()","#9 D:\home\site\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array()","#10 D:\home\site\vendor\yiisoft\yii2\base\Controller.php(178): yii\base\InlineAction->runWithParams()","#11 D:\home\site\vendor\yiisoft\yii2\base\Module.php(552): yii\base\Controller->runAction()","#12 D:\home\site\vendor\yiisoft\yii2\web\Application.php(103): yii\base\Module->runAction()","#13 D:\home\site\vendor\yiisoft\yii2\base\Application.php(384): yii\web\Application->handleRequest()","#14 D:\home\site\wwwroot\admin\index.php(17): yii\base\Application->run()","#15 {main}"]}}

6

The article also mention you can have the exclamation mark in a rule.
But if you’re not going to display the version number I guess I misunderstood what the problem is.

The version number is auto incremented or isn’t it?

7

can you post your model?

8

According to the docs, it should be auto generated but I see it in the query which make me think that you have it defined in the model. If I understand well, it should not be in your model rules at all!

9

The issue originally occurred when I had not yet added anything regarding this new column. I have since tried all sorts of variations (model, controller, …), nothing I have tried has worked and I can’t find anything in the documentation.

/**
 * This is the model class for table "employees".
 *
 * @property int $EmpId
 * @property int $EmpNo
 * @property string $FirstName
 * @property string $LastName
 * @property string $Company
 * @property int $TitleId
 * @property string $Address1
 * @property string $Address2
 * @property int $CityId
 * @property int $ProvId
 * @property int $CountryId
 * @property string $PostalCode
 * @property string $BusinessTel
 * @property string $BusinessTelExt
 * @property string $HomeTel
 * @property string $CellularTel
 * @property string $Fax
 * @property string $Email
 * @property string $URL
 * @property string $Notes
 * @property string $SpecialNotes
 * @property string $DL
 * @property string $Ins
 * @property string $CurrencyCode
 * @property int $TaxRateId
 * @property string $ContractorAgreement
 * @property string $ContractorAgreementDt
 * @property int $Active
 * @property string $XeroContactId
 * @property string $dtCreation
 * @property string $dtModification
 * @property int $OldEmployeeId
 *
 * @property EmployeesCert[] $employeesCerts
 * @property EmployeesCertifications[] $employeesCertifications
 * @property ProjectsLegs[] $projectsLegs
 * @property EmployeesServices[] $employeesServices
 */
class Employees extends \yii\db\ActiveRecord
{
    // public function beforeSave($insert)
    // {
    //     if (!parent::beforeSave($insert)) {
    //         return false;
    //     }
    //     if ($this->isNewRecord) {
    //         // $this->Active = 1;
    //         // $this->OldEmployeeId = ''; // null;
    //         $this->RV = new Expression('DEFAULT'); // null;
    //         // $this->dtCreation = date("Y-m-d H:i:s");
    //         // $this->dtModification = date("Y-m-d H:i:s");
    //     } else {
    //         // $this->dtModification = date("Y-m-d H:i:s");
    //     }
    //     return true;
    //     // return parent::beforeSave($insert);
    // }

    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'employees';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['FirstName', 'CurrencyCode', 'TaxRateId'], 'required'],
            [['EmpNo', 'TitleId', 'CityId', 'ProvId', 'CountryId', 'TaxRateId', 'OldEmployeeId'], 'integer'],
            [['DL', 'Ins', 'dtCreation', 'dtModification', 'ContractorAgreementDt', 'OldEmployeeId'], 'safe'],
            [['FirstName', 'LastName', 'Company', 'Address1', 'Address2', 'PostalCode', 'Email', 'URL', 'Notes', 'SpecialNotes'], 'string', 'max' => 255],
            [['BusinessTel', 'BusinessTelExt', 'HomeTel', 'CellularTel', 'Fax'], 'string', 'max' => 50],
            [['XeroContactId'], 'string', 'max' => 36],
            [['CurrencyCode'], 'string', 'max' => 3],
            [['Active', 'ContractorAgreement'], 'string', 'max' => 1],
            [['FirstName', 'LastName', 'Company', 'Address1', 'Address2', 'PostalCode', 'BusinessTelExt', 'Notes', 'SpecialNotes', 'OldEmployeeId'], 'filter', 'filter' => 'trim'],
            [['LastName', 'Company', 'Address1', 'Address2', 'PostalCode', 'Email', 'URL', 'Notes', 'SpecialNotes', 'BusinessTel', 'BusinessTelExt', 'HomeTel', 'CellularTel', 'Fax', 'OldEmployeeId', 'dtModification'], 'default'], //Ensure blanks are saved as Nulls!
            [['CurrencyCode'], 'exist', 'skipOnError' => true, 'targetClass' => LstCurrencies::className(), 'targetAttribute' => ['CurrencyCode' => 'CurrencyCode']],
            [['TaxRateId'], 'exist', 'skipOnError' => true, 'targetClass' => LstTaxRates::className(), 'targetAttribute' => ['TaxRateId' => 'TaxRateId']],
        ];
    }

What I don’t get is when I review the error message, the columns don’t match the value sequence. RV is the last column yet the DEFAULT is list third to last?! I never thought adding a column would break my application like this, but RowVersion is a necessity for MSSQL, so I don’t know what to do at this point.

What is also odd is the fact that updates work just fine. It is only the Create action that fails like this even though both use the same function to perform the save.

protected function saveEmployee($model, $modelsDrivers, $modelsDriversServices, $modelsDriversCertifications)
    {
        $transaction = Yii::$app->db->beginTransaction();
        try {
            if ($go = $model->save(false)) {  //****This is the line the error is being flagged on****
                // loop through each Driver
                foreach ($modelsDrivers as $i => $modelsDriver) {

as you can see validation shouldn’t be an issue as I am using save(false).

10

I found https://github.com/yiisoft/yii2/issues/19074 and so switched the column to allow NULLs, but it made no difference, still errs.

11

Customized save() for the create action?
(insert followed by an update)

Edit: The quoted text has an error.
Correct error mesage is:

SQLSTATE[HY000]: General error: 20018 Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column

So it is with a column list.

Add attribute list to save()?
https://www.yiiframework.com/doc/api/2.0/yii-db-baseactiverecord#save()-detail

12

How? How can I exclude the column?

13

See my edit above.

This is interesting:

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one rowversion column. Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column.

https://dba.stackexchange.com/questions/210820/how-are-sqlserver-timestamp-rowversion-values-created

14

Just pass list of attributes you want to save. See: BaseActiveRecord, yii\db\BaseActiveRecord | API Documentation for Yii 2.0 | Yii PHP Framework

15

This is a strange thing indeed!
What happens if you run insert manually on SSMS?

16

Add attribute list to save()?
BaseActiveRecord, yii\db\BaseActiveRecord | API Documentation for Yii 2.0 | Yii PHP Framework

That seems very promising. I will try it, won’t be until much later in the day though, and will report back.

Thank you for pointing me to the save documentation and helping me.

17

Thank you for pointing me to this. I will try it later today, but it look very promising. I’ll report back.

1 Like
18

I must be doing something wrong.

$model->save(false, [
                'EmpId', 'EmpNo', 'FirstName', 'LastName', 'Company', 'TitleId', 'Address1', 'Address2', 'CountryId', 'ProvId', 'CityId', 'PostalCode',
                'BusinessTel', 'BusinessTelExt', 'HomeTel', 'CellularTel', 'Fax', 'Email', 'URL', 'Notes', 'SpecialNotes', 'CurrencyCode', 'TaxRateId', 'ContractorAgreement',
                'ContractorAgreementDt', 'Active', 'XeroContactId', 'dtCreation', 'dtModification', 'OldEmployeeId'
            ])

and yet, with no RV, I get the exact same error with RV specified?

I have an Idea that might work via a db trigger, far from ideal. I’ll play with that and post back.

19

What do you get as stack trace right now?

20

Same as before, no change.