更新varbinary(MAX)列 [英] Update varbinary(MAX) column

问题描述

我正在尝试更新varbinary(MAX)列，它实际上存储了用户从网站上载的Word文件的已保存数据.

I am trying to update a varbinary(MAX) column, it actually stores saved data of a Word file that an user uploaded from a website.

发生的事情是用户上传了错误的文件，因此我需要更新该列以反映正确的文件.

What happened was an user uploaded a wrong file so I need to update the column to reflect a correct file.

我所做的是，在测试机上我上传了正确的文件，因此将其保存到数据库中，我可以看到并复制"varbinary(MAX)值"，并用它替换错误的文件

What I did was that in a testing machine I uploaded the correct file so it was saved to the database and I can see and copy the "varbinary(MAX) value" and use it to replace the wrong one

该值看起来像:0x504B03041400060008000 ........ FBB9

The value looks like: 0x504B03041400060008000........FBB9

我尝试了直接的UPDATE查询:

I tried a straight forward UPDATE query:

UPDATE my_table Set datafile =  0x504B03041400060008000........FBB9

它说有1行受到影响，但是从字体端网站下载后无法打开该文件，我注意到保存的值是0x0504B03041400060008000........FBB(还有1个额外的 0 0x之后，最后的 9 消失了

It says 1 row(s) affected, but that file can't be open after downloaded from the font-end web, I notice that saved value is 0x0504B03041400060008000........FBB (1 more extra 0 after 0x and the 9 at the end disappeared)

我该怎么做?

推荐答案

正在添加额外的0，因为您将其设置为的整个值都是奇数. SQL Server将使用额外的0填充它以使其均匀.

The extra 0 is being added because the entire value you are setting it to is odd. SQL Server will pad it with an extra 0 to make it even.

declare @varmax varbinary(max) 
set @varmax = 0x1234567         --odd
select @varmax                  --returns 0x01234567 with the padded 0

set @varmax = 0x12345678        --even
select @varmax                  --returns 0x12345678

您的9被丢弃是因为您输入的奇数字节填充了最大值.因此，插入了0，但是这溢出了最大字节数，因此似乎也被截断了.我能够复制您的错误...但还不知道解决方法.

Your 9 is being dropped because you are entering an odd number of bytes that fills the max value. So, a 0 is inserted but this overflows the max number of bytes and thus is also truncated it seems. I was able to replicate your error... but don't know a way around it yet.

这是我用于测试的值.忽略结果...但是您可以将其复制到自己的SSMS中，并查看正确的输出.

Here is the value i used for your test. Ignore the results... but you can copy them into your own SSMS and see the correct output.

http://rextester.com/LMGQ8686

这篇关于更新varbinary(MAX)列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！