How To Handle Mssql Autogenerated Column (Not Pk)

Yii 1.1 General Discussion
1

Hi all:

I’d like to use SQL Server’s filestream feature to store files (jpg/pdf/doc/etc). The way it works is that the actual file is stored in the filesystem and its metadata (but not the file BLOB) in the database. For the feature to work SQL Server requires a column with the following definition:




[ColumName] [uniqueidentifier] ROWGUIDCOL  NOT NULL,

From what I’ve learned this type of column behaves similar to a typical IDENTITY column. Yii identifies identity columns and doesn’t attempt to insert a value upon record creation. However, Yii does try to insert a NULL value into this column which SQL Server doesn’t accept.

The question is, how to I prevent Yii from inserting anything into the column? I’ve already tried leaving it out of the model validation rules, and even a SQL script for DAO access.

Any ideas are greatly appreciated.

2

If you don’t need to be able to access the attribute from your application, I can think of a hacky way to do it by overriding CActiveRecord::getAttributes()




    public function getAttributes($names=true)

    {

        $attributes = parent::getAttributes();

        $exclusions = array('ColumnName'=>'');

        return array_diff_key($attributes, $exclusions);

    }

That could be refactored into a cleaner form or into a behavior.

If you do need to be able to access the attribute in the application, you might need to override the insert and update methods instead.

I’d be interested to see what other people come up with as there may well be a less hacky approach.

3

Thanks for the tip Keith, I’ll look into it and report back.

4

Turns out I must handle it with DAO because it requires T-SQL constructs that ActiveRecord can’t handle. In the create action of the controller:




if(isset($_POST['ObjImage']))

{

	$model->attributes=$_POST['ObjImage'];

        if($model->validate())  // instead of save

	{

		$objid = $_POST['ObjImage']['SpaceObjectID'];

		$tmpfile = $_FILES['ObjImage']['tmp_name']['ImageFile'];

		$connection=Yii::app()->db;

		$sql1 = "DECLARE @img AS VARBINARY(MAX);";

		$sql2 = "SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET(BULK '".$tmpfile."',SINGLE_BLOB ) AS x;";

		$sql3 = "INSERT INTO ObjImage (ObjImageID, SpaceObjectID, Imagefile) SELECT NEWID(), ".$objid.", @img;";

		$sql = $sql1.$sql2.$sql3;

		$command=$connection->createCommand($sql);

		$rowCount=$command->execute();

		$this->redirect(array('view','id'=>$model->ID));

	}

}

I realize it needs improvement but the end result is that the filestream feature of MS SQL Server can be used with Yii.

Cheers,

5

Hi again:

I’m revisiting this issue and found out that my previous code only solved the insert scenario but not updates. I also cleaned up the code and moved it to afterSave():




public function afterSave()

{

    if($file = CUploadedFile::getInstance($this,'FileContent')) {

        $conn = new PDO(Yii::app()->db->connectionString, Yii::app()->db->username, Yii::app()->db->password);

        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $tsql = "UPDATE ObjImage SET FileContent = ? WHERE ID = ".$this->ID.";";

        $uploadPic = $conn->prepare($tsql);

        $fileStream = fopen($file->tempName, 'rb');

        $uploadPic->bindParam(1,$fileStream,PDO::PARAM_LOB,0,PDO::SQLSRV_ENCODING_BINARY);

        $uploadPic->execute();

    }

    parent::afterSave();

}

so the controller’s actionCreate() looks better:




if(isset($_POST['ObjImage'])) {

	$model->attributes=$_POST['ObjImage'];

        $uploadedFile = CUploadedFile::getInstance($model, 'FileContent');

        if ($uploadedFile != null) {

            $model->FileName = $uploadedFile->name;

            $model->MimeType = $uploadedFile->type;

            $model->FileSize = $uploadedFile->size;

            $model->ObjImageID = new CDbExpression("NEWID()");

        }

	if($model->save()) {

		$this->redirect(array('view','id'=>$model->ID));

        }

}

and it seems that the ROWGUID() issue is solved. However, the problem I’m having now seems to be that in actionCreate() the $_POST[‘ObjImage’] array contains a null value for the FileContent attribute (as the actual uploaded file is in the $_FILES array) but in actionUpdate() the FileContent attribute has been populated by the model loading in the form. When submitted SQL Server complains about an “Implicit conversion from data type nvarchar to varbinary(max) is not allowed.”.

So I need a way to tell $_POST that the attribute is binary (I don’t know if it’s even possible) or else will have to override the update method in the model because by the time afterSave() executes it’s too late. Any ideas?

Thanks,