Storing/reading File Content With Varbinary Sql Server

Yii 1.1 General Discussion
1

Hello everyone,

I’m facing an interesting problem with storing file content in a varbinary(max) column (SQL Server 2012).

If I try to store it by retrieving content with PHP function "file_get_contents" it raise this error:

[i]An error occurred translating string for input param 7 to UCS-2: No mapping for the Unicode character exists in the target multi-byte code page.

[/i]

So I tried to use a different encoding method, converting binary to hex but it raises this exception:

[i]Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

[/i]

The reason why i used this type of encoding is that during the migration (with Sql Server Migration Assistant) blob content was converted into hex encoded data in a varbinary(max) column, so I needed to use "hex2bin" (PHP function) in the afterFind event.

At the moment the application uses ActiveRecord and since this issue is a consequence of a db migration (from MySQL to MSSQL) I would like to do my best to don’t rewrite the queries that save file contents by using CDbCommand.

Does someone have any idea?

Thanks in advance!

2

Hi:

I have experimented with this scenario (storing image BLOBs in a SQL Server varbinary(max) column) and it’s been painful; I have yet to find a satisfactory solution but what I’ve got so far works.

The experiment involves storing images of space objects (stars, nebulae, etc.) using MSSQL’s FILESTREAM feature to have them physically stored in the filesystem rather than inside the table. That way they are easier to manage and backup with standard MSSQL tools; they’re also accessibe (read only) to any .Net applications without going through MSSQL.

ObjImage table:




CREATE TABLE [dbo].[ObjImage](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[ObjImageID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,

	[SpaceObjectID] [int] NOT NULL,

	[MimeType] [nvarchar](50) NULL,

	[FileSize] [int] NULL,

	[FileName] [nvarchar](50) NULL,

	[FileContent] [varbinary](max) FILESTREAM  NULL,

 CONSTRAINT [PK_ObjImage] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [FSDBFS],

 CONSTRAINT [UQ__ObjImage__41623AAD80EE9DAB] UNIQUE NONCLUSTERED 

(

	[ObjImageID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] FILESTREAM_ON [FSDBFS]


GO


SET ANSI_PADDING OFF

GO


ALTER TABLE [dbo].[ObjImage]  WITH CHECK ADD  CONSTRAINT [FK_ObjImage_SpaceObject] FOREIGN KEY([SpaceObjectID])

REFERENCES [dbo].[SpaceObject] ([ID])

GO


ALTER TABLE [dbo].[ObjImage] CHECK CONSTRAINT [FK_ObjImage_SpaceObject]

GO

Yii model rules:




public function rules()

{

	return array(

		array('SpaceObjectID', 'required'),

		array('SpaceObjectID, FileSize', 'numerical', 'integerOnly'=>true),

		array('MimeType, FileName', 'length', 'max'=>50, 'allowEmpty'=> true),

		array('FileContent', 'file','types'=>'jpg, jpeg, png, bmp, pdf, doc, docx, xls, xlsx', 'maxSize'=>(4 * 1024 * 1024), 'allowEmpty'=>true),

		array('ID, SpaceObjectID, MimeType', 'safe', 'on'=>'search'),

	);

}

SpaceObject is the parent table that contains the object’s information; a record must exist there before one can add the picture.

Yii model save methods:




public function beforeSave()

{

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

    {

	$this->FileName=$file->name;

	$this->MimeType=$file->type;

	$this->FileSize=$file->size;

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

    }

    return parent::beforeSave();

}

	

public function afterSave()

{

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

    {

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

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

	$sql2 = "SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET(BULK '".$file->tempName."',SINGLE_BLOB ) AS BLOB;";

	$sql3 = "UPDATE ObjImage SET FileContent = @img WHERE ID = ".$this->ID.";";

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

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

	$rowCount=$command->execute();

    }	    

    parent::afterSave();

}

beforeSave() is particularly needed to seed the ROWGUIDCOL required for the filestream feature at $this->ObjImageID=new CDbExpression(“NEWID()”); because I haven’t found a way to make Yii handle it the same way it handles autoincrement integer primary keys.

afterSave() is my clunky workaround to avoid the character set conversion errors. I tried all kinds of PHP functions (e.g. bin2hex, iconv) for this and couldn’t make them work; MSSQL’s use of UCS2 is simply a pain to handle in UTF8 world. I ended up using a bulk insert (sorry for the 3 SQL statements but I had to build this incrementally so I wouldn’t get lost :P )

The controller and views are Gii standard; nothing special there.

This is the code to retrieve and view the picture:




<?php $this->widget('zii.widgets.CDetailView', array(

	'data'=>$model,

	'attributes'=>array(

		'ObjImageID',

		'SpaceObjectID',

		'FileName',

		'FileSize',

		'MimeType',

	    array(

		'name'=>'FileContent',

		'type'=>'image',

		'value'=>'data:'.$model->MimeType.';base64,'.base64_encode(hex2bin($model->FileContent)),

	    ),

	),

)); ?>

Hope this helps…

3

Thank you for reporting your experience, it’s really helpful.

I am pretty new to MSSQL, and I sincerely think it’s strange that you can’t just save a file content in a field as you can do in MySQL (2 lines of code: file_get_contents($data); $model->save();). I know that there are different better solutions, but in a migration context you would like to do your best to don’t rewrite tons of code.

4

JFREyes, I finally decided to use FILESTREAM and migrate everything using this method.

Regarding your afterSave function, are you assuming that your SQL Server have the permission to read temp data from your web server folders? My SQL Server instance is located on another server and not on the web server.

If I am correct, is there another solution to store file content in varbinary FILESTREAM?

5

I can’t tell if MSSQL needs permission to read the webserver’s file system because my experiment was performed on my development machine which has both installed, but I don’t recall doing anything special to the MSSQL installation.

As I mentioned before I have yet to find a permanent solution for this using PHP; I reckon it’s a walk in the park with C#.

6

Finally I got the solution.

First of all I report a complete and very helpful guide to add FILESTREAM in an existing DB with stored file content:

Instead of using BULK SQL function I converted binary data into hex




$unpacked = unpack('H*hex', $data);

$file_content = '0x' . $unpacked['hex'];

Then I edited JFReyes afterSave() function:




$sql = "USE YourDbName;";

$sql .= "DECLARE @file AS VARBINARY(MAX);";

$sql .= "SELECT @file = CONVERT( VARBINARY(MAX),$file_content );";

$sql .= "UPDATE tbl_name SET content_column = @file WHERE table_pk = " . $this->primaryKey. ";";

$command = Yii::app()->db->createCommand($sql);

$command->execute();

This worked for me!

7

it is very painful but i finally found good solution for uploading media upto 2GB in MSSQL

i just utf8_encode like utf8_encode(file_get_contents($_FILES[‘file’][‘tmp_name’]))

i use varchar(MAX) instead of varbinary(MAX) and it work fine for me using $mode->save();

when you need to download file simple echo utf8_encode($model->media).

Thanks