SQL Server varbinary column modifying inserted data

Question

We have a table in our database for storing photos, the datatype of the actual photo data is varbinary(max).

When inserting a photo from the application, the data is correctly stored and the photo can be viewed as normal.

However, I am updating another record to have the same photo and when I update/insert the existing data, an extra leading 0 seems to be applied to the data thus breaking the decoding of the image.

When the image is inserted by the application, the binary data is correct and starts with: 0xFFD8

When I copy that binary data, and use an update/insert statement to apply this photo to another record, the data changes and starts: 0x0FFD

This means that the data cannot be converted back to an image.

Google has let me down on this occasion, though it may be that I'm not using the correct search terms.

Any assistance greatly appreciated, and if I can add any more info to assist you, in assisting me, please let me know and I'll add whatever I can.

Link to a pastebin showing data before/after insert/update: http://pastebin.com/7jrtivZ8

Answer 1

The data you pasted is exactly 65533 hex characters. Including the "0x" prefix, this is 65535 which is exactly 2^16 - 1. This strongly hints that the code that generates the hexadecimal string representation of the binary data is limited to 65535 characters. Very likely, it is using a buffer of that length.

If you are using SQL Server Management Studio to retrieve a varbinary value to use in your update query, please note that the value will be truncated according to this SSMS setting:

Tools / Options / Query Results / Results to Grid / Max Characters Retrieved / Non XML Data

This setting happens to have a maximum value of 65535. If you set this max value to, say, 31, you would get the same odd-numbered string from your example:

select * from photo -- 0xFFD8FFE000104A46494600010101 -- 29-digit hex string

This StackOverflow answer explains that the when there is an odd number of hex characters, SQL server will assume there is a leading 0. So if you write this:

update Photo set photo_data = 0xF -- a 1-digit hex string

It is functionally equivalent to writing this:

update Photo set photo_data = 0x0F

And in fact selecting it out of the table will show you it is 0x0F.

Answer 2

I've managed to resolve the issue in a round about way.

Rather than inserting a new record with the data copied from an existing photo, I have created a dummy user, which I can add the photo to, and then update the id of the record to link to the correct user.