0

I am trying to save a file but because of that additional zero the file is not opening when downloaded from Database.

There are many questions around this but could not get an answer

Update varbinary(MAX) field in SQLServer 2012 Lost Last 4 bits

Update varbinary(MAX) column

Can some one please help me in saving a file as a seed data while goes as a postscript in my DB project

Adding more info to repro issue. 

CREATE TABLE [Thumbnail](
   [Id]        [int] IDENTITY(1,1) NOT NULL,
   [Data]      [varbinary](max) NULL
CONSTRAINT [PK_Thumbnail] PRIMARY KEY CLUSTERED 
(
[Id] ASC
) ) ON [PRIMARY]


INSERT [Thumbnail] ( Data )
SELECT * FROM OPENROWSET (BULK 'img.png', SINGLE_BLOB) AS X

if I upload an image with the above script it is working good(with odd length as well). But the same script is updated as a

insert into thumbnail (data) values(0x7364736466736466736) it is appending additional zero and I am not able to open my file back if the length is odd.

Please help.

Pavan k
  • 48
  • 10

1 Answers1

0

You can try this:

I set up a mockup to simulate your issue

DECLARE @tbl TABLE(SomeValue VARCHAR(10),TheValueAsBin VARBINARY(MAX));
INSERT INTO @tbl(SomeValue) VALUES(NULL),(''),('1'),('22'),('333'),('4444');

--With this I set a second column to the corresponding VARBINARY values

UPDATE @tbl SET TheValueAsBin = CAST(SomeValue AS VARBINARY(MAX));

--Check the intermediate result

SELECT *
      ,DATALENGTH(TheValueAsBin) AS DataLengthOfYourBinValue
FROM @tbl

/*
SomeValue   TheValueAsBin   DataLengthOfYourBinValue
NULL        NULL            NULL
            0x              0
1           0x31            1
22          0x3232          2
333         0x333333        3
4444        0x34343434      4
*/

--Now I update the VARBINARY value with a CASE using the % (modulo) operator to switch between odd and even numbers.

UPDATE @tbl SET TheValueAsBin = TheValueAsBin + CASE WHEN DATALENGTH(TheValueAsBin)%2 = 1 THEN 0x0 ELSE 0x END

--Check the final output

SELECT *
      ,DATALENGTH(TheValueAsBin) AS DataLengthOfYourBinValue
FROM @tbl

The result

SomeValue   TheValueAsBin   DataLengthOfYourBinValue
NULL        NULL            NULL
            0x              0
1           0x3100          2
22          0x3232          2
333         0x33333300      4
4444        0x34343434      4
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Thanks a lot for the help. but in my situation I have Varbinary but when I am trying to insert I am seeing additional zero at the start which I do not want to. I am only having issue with insert for seed data and update will happen from my application which is working fine. – Pavan k Dec 07 '18 at 14:35
  • select 0x25,0x425 the second one is adding zero which i want to stop. – Pavan k Dec 07 '18 at 14:36
  • @Pavank, sorry, I have no idea, what you are talking about. Please [edit your question](https://stackoverflow.com/posts/53670909/edit) in order add more details. Best was a [mcve] – Shnugo Dec 07 '18 at 15:18
  • select 0x25,0x425 you will see an additional zero will get added on the second which is causing me an issue when reading back the as this is file upload. Hope this help you understand the question – Pavan k Dec 07 '18 at 16:19
  • @Pavank Okay, I see what you mean... What do you expect? In a HEX-string you need **two** characters to encode **one** byte. A HEX-string with an odd number of characters will be padded to the next even length. What value should `0x425` represent? – Shnugo Dec 07 '18 at 16:25
  • I am understanding the reason behind this. But if I do the same thing from the application SQL server is adding odd length and I am able to read it back. but if i do from the script it is breaking because of additional zero. – Pavan k Dec 07 '18 at 16:28
  • Thanks a lot for the reply. – Pavan k Dec 07 '18 at 16:29
  • @Pavank I don't know your application, but an HEX-string like `0x425` is technically wrong... – Shnugo Dec 07 '18 at 16:38